Re: PDO_odbc: Parameter binding

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: http://www.php.net/unsub.php