Skip to Content.
Sympa Menu

freetds - Re: [freetds] Translation of strings/literals to varbinary?

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Peter C. Norton" <spacey-freetds.org AT ssr.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Translation of strings/literals to varbinary?
  • Date: Tue, 7 Dec 2010 17:59:20 -0500

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




Archive powered by MHonArc 2.6.24.

Top of Page