Re: Are typed bound parameters working with unixODBC/FreeTDS

2010/9/30 Pierre Buyle <[email protected]>

> Hi,
>
> On Thu, Sep 30, 2010 at 4:53 PM,  <[email protected]>
> wrote:
> > Message: 7
> > Date: Thu, 30 Sep 2010 16:53:52 +0200
> > From: Frediano Ziglio <[email protected]>
> > To: FreeTDS Development Group <[email protected]>
> > Subject: Re: [freetds] Are typed bound parameters working with
> unixODBC/FreeTDS
> > A bit similar to
> >
> http://stackoverflow.com/questions/3828215/using-typed-bound-parameters-with-php-pdo-odbc-unixodbc-and-freetds
> :)
> Yes, I cross-posted my issue there :D
>
> > I don't know how PDO works but surely ODBC does not support ":foo" as
> > parameter and as ODBC specification only RPC (store procedures) support
> > named parameters so PHP should convert ":foo" to "?".
> The same code worked fine on a Windows workstation. So I guess PDO
> handles named parameters right and convert ":foo" to "?".
>
> > Our driver does not support SQLDescribeParam but there is no reason why
> PDO
> > should pass the integer value as a LONGVARCHAR as it seems to do from
> log.
> > The ODBC trace reported however is a bit too should, it would be better a
> > trace that include SQLPrepare and SQLExecute.
> Attached to this mail, a more complete unixODBC log. The log is on the
> actual script/query which more complex that the sample given code.
>
> Here is the used code
>
>    $sql = "INSERT INTO
>
> kandidaat(kandidaat_id,kandidaat_naam,kandidaat_voornaam,kandidaat_straat,kandidaat_nummer,kandidaat_bus,"
>
>  ."kandidaat_postcode,kandidaat_woonplaats,kandidaat_email,kandidaat_gsm,kandidaat_geboortedatum,kandidaat_nationaliteit,"
>
>  ."kandidaat_geslacht,kandidaat_burgerlijks_staat,kandidaat_taalcode,kandidaat_dossier_taal,kandidaat_status,zoeknaam,"
>        ."zoekvoornaam) VALUES (CAST(CAST(:candidat AS varchar) AS
>
> integer),:name,:firstname,:street,:number,:box,:zip,:city,:mail,:gsm,CAST(CAST(:birthdate
> AS varchar) AS datetime),:nationality,"
>
>  .":sex,:civil_statue,:language,:language2,1,dbo.searchvalue(:namedbo),dbo.searchvalue(:firstnamedbo));
> ";
>    $stmt = $PDO->prepare($sql);
>    $stmt->bindValue(':candidat', $candidat, PDO::PARAM_STR);
>    $stmt->bindValue(':name', $name, PDO::PARAM_STR);
>    $stmt->bindValue(':firstname', $firstname, PDO::PARAM_STR);
>    $stmt->bindValue(':namedbo', $name, PDO::PARAM_STR);
>    $stmt->bindValue(':firstnamedbo', $firstname, PDO::PARAM_STR);
>    $stmt->bindValue(':birthdate', "$birth_date", PDO::PARAM_STR);
>    $stmt->bindValue(':street', $street, PDO::PARAM_STR);
>    $stmt->bindValue(':number', $number, PDO::PARAM_STR);
>    $stmt->bindValue(':box', $box, PDO::PARAM_STR);
>    $stmt->bindValue(':zip', $zip, PDO::PARAM_STR);
>    $stmt->bindValue(':city', $city, PDO::PARAM_STR);
>    $stmt->bindValue(':mail', $mail, PDO::PARAM_STR);
>    $stmt->bindValue(':gsm', $gsm, PDO::PARAM_STR);
>    $stmt->bindValue(':nationality', $nationality, PDO::PARAM_STR);
>    $stmt->bindValue(':sex', $sex, PDO::PARAM_STR);
>    $stmt->bindValue(':civil_statue', $civil_statue, PDO::PARAM_STR);
>    $stmt->bindValue(':language', $language, PDO::PARAM_STR);
>    $stmt->bindValue(':language2', $language, PDO::PARAM_STR);
>    $stmt->execute();
>
> --
> Pierre Buyle
>
>
I checked PDO source code at
http://svn.php.net/viewvc/php/php-src/trunk/ext/pdo_odbc/odbc_stmt.c. If
SQLDescribeParam is not supported LONGVARBINARY or LONGVARCHAR is used. So
for integer it use LONGVARCHAR which lead to the error reported. It seems
PARAM_INT is not used and also there is no inference from PHP type and
parameter binded. Well... as said SQLDescribeParam is not supported and
cause an extra round-trip using MS ODBC.

freddy77