Re: Translation of strings/literals to varbinary?

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 <[email protected]>:
> 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: [email protected]
>> [mailto:[email protected]] 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
>> > > [email protected]
>> > > http://lists.ibiblio.org/mailman/listinfo/freetds
>> > _______________________________________________
>> > FreeTDS mailing list
>> > [email protected]
>> > http://lists.ibiblio.org/mailman/listinfo/freetds
>> _______________________________________________
>> FreeTDS mailing list
>> [email protected]
>> 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
>> [email protected]
>> http://lists.ibiblio.org/mailman/listinfo/freetds
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
>
_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds