FreeTDS Development Group

["Thompson, Bill D (London)" <bill.d.thompson AT baml.com>]

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.