FreeTDS Development Group

["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