Re: PDO_odbc: Parameter binding

Running with PHP 5.0.2 and PDO extensions (latest from CVS) compiled with -enable-debug results in much worse problems using PDO_ODBC --with-flavour=ibm-db2: dbs@thefuzz:~> php bind_test.php PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSetConnectAttr SQL_ODBC_CURSORS: -99999 [IBM][CLI Driver] CLI0150E Driver not capable. SQLSTATE=HYC00 [SQL State HYC00]' in /home/dbs/bind_test.php:5 Stack trace: #0 {main} thrown in /home/dbs/bind_test.php on line 5 /home/dbs/bind_test.php(5) : Fatal error - Uncaught exception 'PDOException' with message 'SQLSetConnectAttr SQL_ODBC_CURSORS: -99999 [IBM][CLI Driver] CLI0150E Driver not capable. SQLSTATE=HYC00 [SQL State HYC00]' in /home/dbs/bind_test.php:5 Stack trace: #0 {main} thrown Content-type: text/html X-Powered-By: PHP/5.0.2 Line 5 of bind_test.php, by the way, is the PDO constructor: $dbh = new PDO('odbc:sample', $user, $pass); I think I might stick with the non-debug, alpha release versions as I continue with the documentation, then adjust the docs after the fact once things settle down a bit more and there's a more stable release to play with. The message for CLI0150 is: CLI0150E Driver not capable. Explanation: The operation is valid but not supported by either the driver or the data source. User Response: Specify a valid operation. :( Dan On Fri, 26 Nov 2004 12:48:44 -0500, Dan Scott <denials@gmail.com> wrote: > 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 sZu| > > > >>>têÆqÖ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: > > > >>http://www.php.net/unsub.php > > > >> > > > >> > > > > > > -- > > > > > > > > > PECL development discussion Mailing List (http://pecl.php.net/) > > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > > >