Re: Question about FreeTDS, DBD::ODBC and bind parameters with prepar ed statements

[email protected] wrote:
> If I try to prepare a statement containing placeholders for fields with
> a binary datatype, I get the following error:
> 
> DBD::ODBC::db prepare failed: [unixODBC][FreeTDS][SQL
> Server]Statement(s) could not be prepared. (SQL-42000)
> [unixODBC][FreeTDS][SQL Server]Implicit conversion from data type
> varchar to binary is not allowed. Use the CONVERT function to run this
> query. (SQL-22018)(DBD: st_prepare/SQLPrepare err=-1)
> 
> The documentation for DBD::ODBC suggests that this can happen if the
> driver does not support SQLDescribeParam. In these cases a default
> parameter type of SQL_VARCHAR is used. 

$ grep SQLDescribeParam doc/api_status.txt 
odbc    (all)   SQLDescribeParam        unimplemented

> As far as I know
> the only way to get placeholder support in prepared statements with
> FreeTDS and DBI is DBD::ODBC, is that correct?

Yes.  

> Is this supposed to work, i.e. am I just 'doing it wrong'? 

http://msdn.microsoft.com/en-us/library/ms716298(VS.85).aspx

I don't know if it's supposed to work.  When you say "placeholders for
fields with a binary datatype", do you mean the SQL datatype is BINARY as
in http://msdn.microsoft.com/en-us/library/aa225972(SQL.80).aspx?  If so,
no implicit conversion is available, and Perl must be persuaded to bind
the parameter with a vartype of SQL_C_BINARY, too.  

When I'm trying to understand what DBD::ODBC is doing, I set the DBI trace
to 3 or 4 and TDSDUMP=stderr (or stdout, following DBI), so that I get an
intermingled dump of DBI and FreeTDS activity.  Then I can see the
functions being called and (with luck) what they're doing.  

It might be that binary parameters aren't supported by the FreeTDS ODBC
driver.  I would think they are, though.  

I would try with samples/odbc_rpc.pl.  If that's no help, perhaps
src/odbc/unittests/prepare_results.c can be modified to prove the case one
way or the other.  

HTH.  

--jkl