[freetds] Translation of strings/literals to varbinary?

Peter C. Norton spacey-freetds.org at ssr.com
Tue Dec 7 17:59:20 EST 2010


Looking at DBD::Sybase, it looks like according to the sybase ct-lib
API docs, the conversion could accomplished either by the application,
or under the covers by the library, by calling ct_dyndesc() and
setting the appropriate data types on the context.  It doesn't look
like freetds's ctlib supports this, though, since ct_dyndesc() doesn't
appear to be implemented.

Could anyone suggest a reasonable way to reproduce the behavior of the
Sybase libraries?  It is convenient to be able to pass in an un-packed
representation of the varbinary as a string, but more importantly it's
extremely inconvenient to have it fail to select anything, but not
report a type mismatch of some kind.

>From doing packet dumps on the connection between a host and the
database, it seems that type info about prepared columns are returned
in the TDS stream, but I can't identify where in the various *tds,
*cmd, *ctx, etc structures the returned type info is stored, so I
don't know where to try to take action to fix this.  An example of the
traffic back-and-forth looks like this:

Client->Server:

  000002D2  0f 01 00 a8 00 00 00 00  e7 9d 00 01 00 04 44 42 ........ ......DB
  000002E2  44 31 94 00 63 72 65 61  74 65 20 70 72 6f 63 20 D1..crea te proc 
  000002F2  44 42 44 31 20 61 73 20  53 45 4c 45 43 54 20 73 DBD1 as  SELECT s
  00000302  70 6e 20 46 52 4f 4d 20  63 6f 72 70 2e 2e 61 73 pn FROM  corp..as
  00000312  73 65 74 5f 69 64 5f 74  6f 5f 73 70 6e 5f 6d 61 set_id_t o_spn_ma
  00000322  70 20 57 48 45 52 45 20  61 73 73 65 74 5f 69 64 p WHERE  asset_id
  00000332  20 3d 20 3f 20 61 6e 64  20 61 73 73 65 74 5f 69  = ? and  asset_i
  00000342  64 20 3d 20 3f 20 61 6e  64 20 61 73 73 65 74 5f d = ? an d asset_
  00000352  69 64 20 3d 20 3f 20 61  6e 64 20 61 73 73 65 74 id = ? a nd asset
  00000362  5f 69 64 20 3d 20 3f 20  61 6e 64 20 61 73 73 65 _id = ?  and asse
  00000372  74 5f 69 64 20 3d 20 3f                          t_id = ? 

(repeated the asset_id = ? five times to make it easier to discern a pattern).

Received Server->Client:

  000001D5  04 01 00 91 00 00 00 00  e7 07 00 20 00 04 44 42 ........ ... ..DB
  000001E5  44 31 20 3e 00 00 00 05  00 00 00 00 00 00 00 00 D1 >.... ........
  000001F5  00 00 2d 08 00 00 00 00  00 00 00 00 00 00 2d 08 ..-..... ......-.
  00000205  00 00 00 00 00 00 00 00  00 00 2d 08 00 00 00 00 ........ ..-.....
  00000215  00 00 00 00 00 00 2d 08  00 00 00 00 00 00 00 00 ......-. ........
  00000225  00 00 2d 08 00 61 2e 00  00 00 01 00 00 04 63 6f ..-..a.. ......co
  00000235  72 70 03 64 62 6f 13 61  73 73 65 74 5f 69 64 5f rp.dbo.a sset_id_
  00000245  74 6f 5f 73 70 6e 5f 6d  61 70 03 73 70 6e 10 00 to_spn_m ap.spn..
  00000255  00 00 07 00 00 00 38 00  fd 00 00 02 00 00 00 00 ......8. ........
  00000265  00                                               .

So, it looks like the 2d08 is type info based on tds.h:

	SYBBINARY = 45,		/* 0x2D */

I suspect that if this info is present somewhere in available
structures I can find a way to make this work in the odbc or ct-lib
libraries to inspect the expected types, and send the correct types
over the wire, as Sybase'se ct-lib does.  So, where do these buggers
hide when the prepare is sent? 

Thanks,

-Peter

On Mon, Dec 06, 2010 at 06:41:10PM -0500, Peter C. Norton wrote:
> I should mention that I'm testing this against SQL Server 2008r2.  I
> would like to test this on sybase as well, but my prior problems with
> connecting to Sybase ASE 15 with DBD::ODBC still stand.
> 
> -Peter
> 
> On Mon, Dec 06, 2010 at 06:38:07PM -0500, Peter C. Norton wrote:
> > I'm wondering if there is any documentation on the expected behavior
> > when a prepared statement is passed a representation of a varbinary?
> > I'm asking because when using perl's DBD::Sybase linked to the sybase
> > ctlib, I can set up the following prepared statement:
> > 
> >     my $sth = $dbh->prepare("SELECT foo_varbinary from db..mapped_value_map WHERE thing_id = ?");
> >     
> > I can then do the following, all of which return identical rows:
> >     my $vbinary = "00038600be24829d";
> >     $sth->execute(pack('H*', $vbinary);
> > or
> >     $sth->exeute($vbinary);
> > or
> >     $sth->execute("0x" . $vbinary);
> > 
> > However, when using DBD::ODBC via FreeTDS, only the first
> > representation of the varbinary works.  Is this something that
> > *should* work?  Or is the failure of the 2nd and 3rd forms an
> > oversight?  Is there any documentation on what values/representations
> > should be posible for doing this?
> > 
> > Thanks,
> > 
> > -Peter
> >   
> > 
> > _______________________________________________
> > FreeTDS mailing list
> > FreeTDS at lists.ibiblio.org
> > http://lists.ibiblio.org/mailman/listinfo/freetds
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds



More information about the FreeTDS mailing list