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: Frediano Ziglio <freddy77 AT gmail.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Translation of strings/literals to varbinary?
  • Date: Thu, 9 Dec 2010 09:10:09 +0100

Hi,
Bill remember correctly. When you prepare using TDS 5.0 server
(Sybase) reply with parameter type informations (with PARAMFMT token)
but TDS 7+ (Microsoft) does not give these informations. This is why
SQLDescribeParam is not implemented in FreeTDS and why MS ODBC driver
parse and issue a specific query in order to get parameter
informations in order to implement SQLDescribeParam. Why our driver is
not able to report that informations for ctlib if TDS 5.0 is used I
don't remind...

freddy77

2010/12/8 Peter C. Norton <spacey-freetds.org AT ssr.com>:
> Re: providing a Sybase dump, The packet dump I sent was from a TDS5
> connection with FreeTDS on the client side.  I am trying to see if
> it's possible to get feature parity between sybase and sql server in
> this respect so it made sense.  I hope it didn't confuse things
> further.
>
> What you're saying about parameter discovery matches what I'm seeing
> on the wire with an mssql session.  I was hoping that there was an
> option along with the "@P1, @P2 [...] create procedure [FOO etc.]"
> that could somehow provide information.  So, the model of interaction
> - calling sp_exec with the handle and arguments - doesn't seem to
> provide any opportunity to do this, and that supports what you're
> saying.
>
> So it seems like TDS 7+ doesn't provide this, TDS 5 does, and the only
> way to infer the types of a in a placeholder is to first parse SQL and
> then either call catalog procedures (with ODBC) or call sp_help, et
> al. and parse the output.  Does that sound about right?
>
> So, is there a way to get SQL Server to be way more strict about the
> type being passed into prepared statement's stored procedure?
> Preferrably if a type conversion is obviously bogus, can it return an
> error instead of returning nothing?  It's very hard to detect the
> error when passing in "0000adad" to a binary column placeholder, and
> get back no errors.
>
> -Peter
>
> On Wed, Dec 08, 2010 at 09:52:57AM +0000, Thompson, Bill D (London) wrote:
>> 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.
>> _______________________________________________
>> 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