Skip to Content.
Sympa Menu

freetds - RE: [freetds] Problem passing parameters

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Lowden, James K" <LowdenJK AT bernstein.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] Problem passing parameters
  • Date: Wed, 15 Jun 2005 09:23:46 -0400

From: Philip
Sent: Tuesday, June 14, 2005 12:19 PM

The explanation proposed for the problem of the parameter-passing does
not
hold, because the parameters pass perfectly using either tsql or isql,
which
both use FreeTDS as driver. The problem lies in the DBD-Sybase library,
and
I bet is because I may have two different libraries in my system.

It does hold. You can't reproduce these results with tsql, or any other
interactive tool.

What you're talking about is something like this:

1> create procedure #a @a int OUTPUT as select @a = 17
2> go
1> declare @b int
2> select @b = 2
3> execute #a @a=@b OUTPUT
4> -- what is the value of @b?
5> select @b
6> go
(1 row affected)

-----------
17

Because @b is 17, not 2, you assert as above. Note, however: your
scripts don't execute anything like line 5. True, when the server
executes line 3, @b gains the output value: T-SQL works correctly; the
server is filling its own variables. But it's still on the server! How
will that value get back to your application?

Once upon a time, the server sent it back as a special result row, which
the client could access via a special library call (or, as above, via a
second SELECT statement). No longer. Perl is completely helpless: it
can't fill your program's variable (bound to the output parameter)
because the server never returns the data.

You can prove this yourself. Comment out line 5 in the above script,
and execute it with bsqldb using the -v option. It will reflect the
input as it is sent to the server, and the returned metadata (and data,
of course). You'll note there's no returned data. If you examine the
TDSDUMP log, you'll find it's not there, either. To save you the
trouble, here are the returned packet tokens:

$ grep -nE 'Send|^token.*marker is' dump
[59-96 is the response from login]
59:token.c:105:tds_process_default_tokens() marker is e3(ENVCHANGE)
63:token.c:105:tds_process_default_tokens() marker is ab(INFO)
70:token.c:105:tds_process_default_tokens() marker is e3(ENVCHANGE)
73:token.c:105:tds_process_default_tokens() marker is ab(INFO)
80:token.c:105:tds_process_default_tokens() marker is e3(ENVCHANGE)
84:token.c:105:tds_process_default_tokens() marker is e3(ENVCHANGE)
87:token.c:105:tds_process_default_tokens() marker is e3(ENVCHANGE)
92:token.c:105:tds_process_default_tokens() marker is e3(ENVCHANGE)
96:token.c:105:tds_process_default_tokens() marker is fd(DONE)
113:net.c:654:Sending packet [create proc]
131:token.c:525:processing result tokens. marker is fd(DONE)
166:net.c:654:Sending packet [execute proc]
186:token.c:525:processing result tokens. marker is fd(DONE)
200:token.c:525:processing result tokens. marker is ff(DONEINPROC)
206:token.c:525:processing result tokens. marker is 79(RETURNSTATUS)
208:token.c:525:processing result tokens. marker is fe(DONEPROC)
237:net.c:654:Sending packet [comment]
255:token.c:525:processing result tokens. marker is fd(DONE)
286:net.c:654:Sending packet [comment]
298:token.c:525:processing result tokens. marker is fd(DONE)

See? No data row. It's not transmitted to the client.

Unfortunately, you're stuck. You could change servers or wrap a SELECT
around your EXECUTE commands.

The "right" way to get output parameters with DBD::Sybase is to use
placeholders. I would like to say you can use them, but unfortunately
they don't work with FreeTDS. Onc of these days....

--jkl


-----------------------------------------
The information contained in this transmission may contain privileged and
confidential information and is intended only for the use of the person(s)
named
above. If you are not the intended recipient, or an employee or agent
responsible
for delivering this message to the intended recipient, any review,
dissemination,
distribution or duplication of this communication is strictly prohibited. If
you are
not the intended recipient, please contact the sender immediately by reply
e-mail
and destroy all copies of the original message. Please note that we do not
accept
account orders and/or instructions by e-mail, and therefore will not be
responsible
for carrying out such orders and/or instructions. If you, as the intended
recipient
of this message, the purpose of which is to inform and update our clients,
prospects
and consultants of developments relating to our services and products, would
not
like to receive further e-mail correspondence from the sender, please "reply"
to the
sender indicating your wishes. In the U.S.: 1345 Avenue of the Americas, New
York,
NY 10105.




Archive powered by MHonArc 2.6.24.

Top of Page