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: "Thompson, Bill D (London)" <bill.d.thompson AT baml.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Translation of strings/literals to varbinary?
  • Date: Wed, 08 Dec 2010 09:52:57 +0000

Hi Peter,

Just on this last, it looks to me like the packet dumps you have
provided have come from an interaction with a Sybase database (using the
Sybase libraries?).

The packet returned:

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

contains a couple of tokens - e7 and 20, which are Sybase (TDS 5.0)
specific.
in token.c they are:

TDS5_DYNAMIC_TOKEN
and
TDS5_PARAMFMT2_TOKEN

If I recall correctly (and it *is* a long time ago), our problem with
parameter discovery on prepared statements with SQL server is that the
MS versions of TDS don't feed back these tokens which describe the
parameters of the prepared statement.

My understanding may be confused by the passage of time, I'm sure Freddy
will correct me if I'm wrong :-)

Bill


-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Peter C. Norton
Sent: 07 December 2010 22:59
To: FreeTDS Development Group
Subject: Re: [freetds] Translation of strings/literals to varbinary?


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
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

----------------------------------------------------------------------
This message w/attachments (message) is intended solely for the use of the
intended recipient(s) and may contain information that is privileged,
confidential or proprietary. If you are not an intended recipient, please
notify the sender, and then please delete and destroy all copies and
attachments, and be advised that any review or dissemination of, or the
taking of any action in reliance on, the information contained in or attached
to this message is prohibited.
Unless specifically indicated, this message is not an offer to sell or a
solicitation of any investment products or other financial product or
service, an official confirmation of any transaction, or an official
statement of Sender. Subject to applicable law, Sender may intercept,
monitor, review and retain e-communications (EC) traveling through its
networks/systems and may produce any such EC to regulators, law enforcement,
in litigation and as required by law.
The laws of the country of each sender/recipient may impact the handling of
EC, and EC may be archived, supervised and produced in countries other than
the country in which you are located. This message cannot be guaranteed to be
secure or free of errors or viruses.

References to "Sender" are references to any subsidiary of Bank of America
Corporation. Securities and Insurance Products: * Are Not FDIC Insured * Are
Not Bank Guaranteed * May Lose Value * Are Not a Bank Deposit * Are Not a
Condition to Any Banking Service or Activity * Are Not Insured by Any Federal
Government Agency. Attachments that are part of this EC may have additional
important disclosures and disclaimers, which you should read. This message is
subject to terms available at the following link:
http://www.bankofamerica.com/emaildisclaimer. By messaging with Sender you
consent to the foregoing.




Archive powered by MHonArc 2.6.24.

Top of Page