PDO_ODBC/MSSQL with prepared statements

i'm using PHP 5.1.2 (with IIS6, which is a long story), and trying to
manipulate a database with PDO_ODBC accessing a MS SQL Server database.
i can execute normal queries with no problem. but when i start executing
prepared statements i run into problems. has anyone successfully used
multiple parameter prepared statements with MSSQL and PDO? one thing
that would help is if i could get the full text of whatever PDO is
trying to send to SQL Server, does anyone know of a way to get that?

i set up a test table zTestTable, with a TestID autoincrement primary
key int, a Test (nvarchar(200)) and a TestNumber(int). when i execute
the following code, it inserts a row with the value 1 for Test and
TestNumber. the same thing happens if i try to use ? parameters instead
of named parameters.

$oConnection = new PDO($sDSN);
$sSQL = 'INSERT INTO zTest_TBL (Test, TestNumber) VALUES (:Test,
:TestNumber) ';
$oStatement = $oConnection->prepare($sSQL);
$s='test insert string passing an array to execute';
$i=1;
$oStatement->execute(array(':Test'=>$s,':TestNumber'=>$i));
print_r($oStatement->errorInfo());

the second bit of code is an attempt to bind parameters and run the same
insert. this one inserts a row with NULLs for Test and TestNumber.

$oConnection = new PDO($sDSN);
$sSQL = 'INSERT INTO zTest_TBL (Test, TestNumber) VALUES (:Test,
:TestNumber) ';
print $sSQL;
$oStatement = $oConnection->prepare($sSQL);
$s2='test insert string binding a parameter';
$oStatement->bindParam(':Test', $s2, PDO::PARAM_STR);
$oStatement->bindParam('TestNumber', $i, PDO::PARAM_INT);
$oStatement->execute();

any ideas?

thanks in advance,

--travis

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: PDO_ODBC/MSSQL with prepared statements

apparently the first problem is a known bug in 5.1.1 and 5.1.2.
http://bugs.php.net/bug.php?id=35671&edit=1

the second one i can't explain yet. the syntax is correct from the best
i can determine from what little documentation is out there. has anyone
successfully implemented this with MSSQL/ODBC? i could switch back to
5.1.0 if that is what is needed.

--travis
Travis Raybold wrote:

> i'm using PHP 5.1.2 (with IIS6, which is a long story), and trying to
> manipulate a database with PDO_ODBC accessing a MS SQL Server
> database. i can execute normal queries with no problem. but when i
> start executing prepared statements i run into problems. has anyone
> successfully used multiple parameter prepared statements with MSSQL
> and PDO? one thing that would help is if i could get the full text of
> whatever PDO is trying to send to SQL Server, does anyone know of a
> way to get that?
>
> i set up a test table zTestTable, with a TestID autoincrement primary
> key int, a Test (nvarchar(200)) and a TestNumber(int). when i execute
> the following code, it inserts a row with the value 1 for Test and
> TestNumber. the same thing happens if i try to use ? parameters
> instead of named parameters.
>
> $oConnection = new PDO($sDSN);
> $sSQL = 'INSERT INTO zTest_TBL (Test, TestNumber) VALUES (:Test,
> :TestNumber) ';
> $oStatement = $oConnection->prepare($sSQL);
> $s='test insert string passing an array to execute';
> $i=1;
> $oStatement->execute(array(':Test'=>$s,':TestNumber'=>$i));
> print_r($oStatement->errorInfo());
>
> the second bit of code is an attempt to bind parameters and run the
> same insert. this one inserts a row with NULLs for Test and TestNumber.
>
> $oConnection = new PDO($sDSN);
> $sSQL = 'INSERT INTO zTest_TBL (Test, TestNumber) VALUES (:Test,
> :TestNumber) ';
> print $sSQL;
> $oStatement = $oConnection->prepare($sSQL);
> $s2='test insert string binding a parameter';
> $oStatement->bindParam(':Test', $s2, PDO::PARAM_STR);
> $oStatement->bindParam('TestNumber', $i, PDO::PARAM_INT);
> $oStatement->execute();
>
> any ideas?
>
> thanks in advance,
>
> --travis
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php