[freetds] parameters

James K. Lowden jklowden at freetds.org
Thu Apr 24 15:08:12 EDT 2008


Federico Alves wrote:
> I execute a SQL 2005 stored procedure
> using perl, DBD-Sybase 1.08 and DBI 1.604. My freedts version is RC4 or
> RC2. If I define my server in freetds.conf and include "tds
> version=8.0", the parameters do not get filled up. If I remove the line
> "tds version", the parameters work again, ceteris paribus.

http://www.freetds.org/faq.html#ms.output.parameters

Thank you for pointing this out.  The FAQ could be clearer.  

SP3 causes the server not to pass back output parameter data for TDS 7.0+
connections unless the RPC functions are used.  TDS 4.2 clients are
unaffected.  

You can demonstrate this yourself using fisql or any other interactive SQL
client.  Invoke a procedure with an output parameter using each protocol
version:

	1> declare @a int
	2> execute myproc @a output
	3> go

and look at the TDSDUMP log output.  You'll see the 4.2 log has an output
parameter packet and the 7.0 does not:

$ awk -F ' is ' '/marker/ {print $2}' dump.42 > marker.42 
$ awk -F ' is ' '/marker/ {print $2}' dump.70 > marker.70 
$ diff -u marker.42 marker.70
--- marker.42   2008-04-24 14:17:18.000000000 -0400
+++ marker.70   2008-04-24 14:17:29.000000000 -0400
@@ -2,21 +2,22 @@
 ab(INFO)
 e3(ENVCHANGE)
 e3(ENVCHANGE)
+ab(INFO)
+e3(ENVCHANGE)
 fd(DONE)
  e3(ENVCHANGE)
 e3(ENVCHANGE)
  ab(INFO)
 ab(INFO)
+ e3(ENVCHANGE)
+e3(ENVCHANGE)
  fd(DONE)
  fd(DONE)
  ff(DONEINPROC)
  ff(DONEINPROC)
  79(RETURNSTATUS)
- ac(PARAM)
     ^^^^^ output parameter packet 
- fe(DONEPROC)
  fe(DONEPROC)

Using DBD::Sybase, there's no way anymore to get output parameter data
with modern Microsoft servers.  Merely embedding the EXEC in the SQL
doesn't work, as we know.  And a placeholder (parameter binding) doesn't
work, because the ct-lib API relies on features not present in a Microsoft
server to determine the SQL datatype of the parameter.  (ODBC parameter
binding works because that information is provided by the caller.)  

To fix DBD::Sybase parameter binding, we've been waiting for the
improbable: either server-side support or client-side SQL parsing.  Lately
I've been thinking we could extend the ct-lib API instead, adding an
ODBC-like parameter binding function.  Of course, to take advantage of it
would mean altering DBD::Sybase and calling applications, but it would
finally close the last big gap -- placeholder support -- for DBD::Sybase
on Microsoft servers, 

--jkl



More information about the FreeTDS mailing list