Re: can odbc query of MSSQL varbinary show a hexadecimal string

Il giorno mer, 10/08/2011 alle 08.31 -0400, Mike Slifcak ha scritto:
> Hi,
> 
> I've been working about a week with FreeTDS.  The mailing list archive has been helpful,
> and the documentation is straight forward.
> I'm obtaining good results with the FreeTDS driver and Perl DBD-ODBC.
> 
> Thank you for all your hard work!
> 

Thank you. Always appreciated.

> Connecting without issue to MS SQL Server 2000.
> The SQL server defines a sysprotects table with a VARBINARY field named columns.
> 
> Connecting with tsql, a
> 
> 
>     SELECT columns from sysprotects
> 
> 
> shows a hexadecimal string.
> Connecting with Perl DBD-ODBC + FreeTDS, the same SELECT shows a binary stream.
> 
> I've tried changing the query to
> 
> 
>     SELECT CAST(columns as TEXT) from sysprotects
> 
> 
> but that is not allowed.
> 
> 
>     DBD::ODBC::db prepare failed: [FreeTDS][SQL Server]
>        Statement(s) could not be prepared. (SQL-42000)
>     [state was 42000 now 22018]
> 
> 
> Using Perl DBI 1.616, DBD-ODBC 1.25, FreeTDS 0.82,
> 
> and without resorting to post-processing Perl wizardry,
> and without writing any SQL server stored procedure (read-only access to MSSQL 2000),
> 
> Is there some combination of freetds.conf/odbc.ini/odbcinst.ini settings that
> can produce a result for VARBINARY as a hexadecimal string?
> 
> Does the recent release provide that combination?
> Thank you,
> -Mike Slifcak

Server return varbinary always in binary format.
ODBC return varbinary consistency with MS implementation so is up to
upper layer (DBD::ODBC or your program) to display it as hexadecimal
string. I think you can convert varbinary to varchar in T-SQL but I
don't remember if it use hexadecimal or just interpret binary data as a
char buffer (I think the last).

Regards
  Frediano