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: "Peter C. Norton" <spacey-freetds.org AT ssr.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Translation of strings/literals to varbinary?
  • Date: Wed, 8 Dec 2010 12:24:11 -0500

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




Archive powered by MHonArc 2.6.24.

Top of Page