Re: PDO_odbc: Parameter binding

Never mind, I just tried the same test (with and without the extra bindParam() parameters) and got the same result (line 20 is the $stmt->execute() call in my test script). I have included the DB2 message. dbs@thefuzz:~> php bind_test.php PHP Fatal error: Uncaught exception 'PDOException' with message '-99999 SQLExecute: -99999 [IBM][CLI Driver] CLI0100E Wrong number of parameters. SQLSTATE=07001 [SQL State 07001] (/home/dbs/PDO_ODBC-0.1.1/odbc_stmt.c:80)' in /home/dbs/bind_test.php:20 Stack trace: #0 {main} thrown in /home/dbs/bind_test.php on line 20 Content-type: text/html X-Powered-By: PHP/5.0.2 dbs@thefuzz:~> db2 "? cli0100" CLI0100E Wrong number of parameters. Explanation: The number of parameters specified in SQLSetParam or SQLBindParameter was less than number of parameters in the SQL statement. User Response: Respecify the SQL statement or provide more input parameters through SQLSetParam or SQLBindParameter. On Fri, 26 Nov 2004 11:43:20 -0500, Dan Scott <denials@gmail.com> wrote: > I'm working (slowly) on the docs at > http://docs.php.net/ref.pdo.html. > This discussion should help. > > Can you try leaving out the optional PDO_PARAM_STR, 64 parameters from > your call to bindParam? > > Dan > > > > > On Fri, 26 Nov 2004 16:30:40 +0100, Daniel Garcia <txa@quintopino.com> wrote: > > > > Hi, > > > > Finally got this simple one working, but instead of binding the > > parameters I passed them as parameters for the execute(): > > > > $dbh = new PDO("odbc:iSeriesDSN", $user, $pass); > > $dbh->setAttribute(PDO_ATTR_ERRMODE, PDO_ERRMODE_EXCEPTION); > > $dbh->exec("create table test(name varchar(15) not null primary key, > > value varchar(255))"); > > > > // insert some data using a prepared statement > > $stmt = $dbh->prepare("insert into test (name, value) values (?,?)"); > > $name = 'Foo - ' . date("H:i:s"); > > $value = 'Bar'; > > $stmt->execute(array($name, $value)); > > > > FYI, I tried binding the parameters, but that didn't work (most likely > > because of wrong syntax, could not find good docs): > > > > $stmt = $dbh->prepare("insert into test (name, value) values (?,?)"); > > $stmt->bindParam(1, $name, PDO_PARAM_STR, 64); > > $stmt->bindParam(2, $value, PDO_PARAM_STR, 64); > > $name = 'Foo - ' . date("H:i:s"); > > $value = 'Bar'; > > $stmt->execute(); > > > > Error: 30007 SQLExecute: 30007 [unixODBC][IBM][iSeries Access ODBC > > Driver]Wrong number of parameters. [SQL State 07002] > > (/tmp/pdo_odbc/PDO_ODBC-0.1.1/odbc_stmt.c:80) - > > > > Thanks for your replies ! > > Daniel > > > > > > > > > > Dan Scott wrote: > > > Hmm, here's what I get when I run that same test > > > script on SuSE Professional 9.2 connecting to DB2 Universal Database > > > 8.2 using PHP 5.0.2 compiled with --enable-debug -- the problem is > > > that DB2 simply doesn't accept named host variables in dynamic SQL. > > > > > > =========== > > > dbs@thefuzz:~> php simple_test.php > > > Content-type: text/html > > > X-Powered-By: PHP/5.0.2 > > > > > > Error: -312 SQLExecute: -312 [IBM][CLI Driver][DB2/LINUX] SQL0312N > > > The host variable "name" is used in a dynamic SQL statement, a view > > > definition, or a trigger definition. SQLSTATE=42618 > > > [SQL State 42618] (/home/dbs/PDO_ODBC-0.1.1/odbc_stmt.c:80) - line 16 > > > > > > dbs@thefuzz:~> db2 "? sql0312" > > > > > > SQL0312N The host variable "<host-name>" is used in a dynamic > > > SQL statement, a view definition, or a trigger > > > definition. > > > > > > Explanation: > > > > > > The host variable "<host-name>" appears in the SQL statement, but > > > host variables are not allowed in dynamic SQL statements, in the > > > SELECT statement of a view definition, or in the triggered action > > > of a trigger definition. > > > > > > The statement cannot be processed. > > > > > > User Response: > > > > > > Use parameter markers (?) instead of host variables for dynamic > > > SQL statements. Do not use host variables and parameter markers > > > in view or trigger definitions. > > > > > > sqlcode : -312 > > > > > > sqlstate : 42618 > > > ========== > > > > > > I should also note that I'm connecting directly to the DB2 database > > > using PDO_ODBC compiled with --with-ibm-db2 (so not unixODBC), that > > > line 16 in the error message above corresponds to the $stmt->execute() > > > call, and that no backtrace will be forthcoming because the script > > > doesn't actually seg fault in this case. However, the backtrace > > > pointer will be useful as I've managed to consistently trigger seg > > > faults in some other test scripts. > > > > > > Dan > > > > > > P.S. Wez, sorry for spamming you with my original reply -- I wish > > > gmail was smart enough to recognize mailing lists and automatically > > > use the list address as the default reply-to address. Ah well... > > > > > > On Thu, 25 Nov 2004 09:44:20 -0500, Wez Furlong <kingwez@gmail.com> wrote: > > > > > >>A backtrace would be warmly welcomed: > > >>http://bugs.php.net/bugs-generating-backtrace.php > > >>It will show exactly where the problem is. > > >> > > >>--Wez > > >> > > >> > > >> > > >> > > >>On Thu, 25 Nov 2004 13:49:03 +0100, Daniel Garcia <txa@quintopino.com> > > >>wrote: > > >> > > >>>Hi, > > >>> > > >>>I'm trying to connect from SuSE Linux 9.0 through unixODBC to a DB2 > > >>>database living in an IBM iSeries server. I have successfully used this > > >>>layout in different projects, mainly using PEAR DB for database access. > > >>>Now I'd like to use PDO to call stored procedures. > > >>> > > >>>The problem is that I can't bind parameters (not even for a simple SQL > > >>>query), since PDO_odbc silently dies. > > >>> > > >>>Following Wez's example in > > >>>http://netevil.org/node.php?nid=44, I tried > > >>>this code: > > >>> > > >>>try { > > >>> $dbh = new PDO("odbc:iSeriesDSN", "user", > > >>> "pass"); > > >>> $dbh->setAttribute(PDO_ATTR_ERRMODE, PDO_ERRMODE_EXCEPTION); > > >>> > > >>> $dbh->exec("create table test(name varchar(255) not null > > >>> primary key, > > >>>value varchar(255))"); > > >>> > > >>> // insert some data using a prepared statement > > >>> $stmt = $dbh->prepare("insert into test (name, value) values > > >>>(:name, :value)"); > > >>> $stmt->bindParam(':name', $name, PDO_PARAM_STR, 64); > > >>> $stmt->bindParam(':value', $value, PDO_PARAM_STR, 64); > > >>> $name = 'Foo'; > > >>> $value = 'Bar'; > > >>> $stmt->execute(); > > >>> > > >>>} catch (PDOException $e) { > > >>> echo "Error: " . $e->getMessage() . " - line " > > >>> . $e->getLine(); > > >>>} > > >>> > > >>>Connection is established, the 'test' table is created fine, but the > > >>>execution crashes when it arrives to the *prepare* statement. > > >>> > > >>>I'm not getting an error from the iSeries server (I do receive errors > > >>>though if I try to create an existing table). > > >>> > > >>>Could it be a problem of PDO_odbc? > > >> > > >>-- > > >>PECL development discussion Mailing List (http://pecl.php.net/) > > >>To unsubscribe, visit: > > >>k?åÓW5|ŸI˜ŠNÓNÚhttp://www.php.net/unsub.php > > >> > > >> > > > > -- > > > > > > PECL development discussion Mailing List (http://pecl.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > >