sql server function native parameter bind error

Question

I'm using the following software stack on Ubuntu 10.04 Lucid LTS to connect to a database:

1. python 2.6.5 (ubuntu package)
2. pyodbc git trunk commit eb545758079a743b2e809e2e219c8848bc6256b2
3. unixodbc 2.2.11 (ubuntu package)
4. freetds 0.82 (ubuntu package)
5. Windows with Microsoft SQL Server 2000 (8.0)

I get this error when trying to do native parameter binds in arguments to a SQL SERVER function:

Traceback (most recent call last):
 File "/home/nosklo/devel/testes/sqlfunc.py", line 32, in <module>
   cur.execute("SELECT * FROM fn_FuncTest(?)", ('test',))
pyodbc.ProgrammingError: ('42000', '[42000] [FreeTDS][SQL
Server]SqlDumpExceptionHandler: Process 54 generated fatal exception
c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this
process.\r\n (0) (SQLPrepare)')

Here's the reproduction code:

import pyodbc
constring = 'server=myserver;uid=uid;pwd=pwd;database=db;TDS_Version=8.0;driver={FreeTDS}'

con = pyodbc.connect(constring)
print 'VERSION: ', con.getinfo(pyodbc.SQL_DBMS_VER)

cur = con.cursor()
try:
   cur.execute('DROP FUNCTION fn_FuncTest')
   con.commit()
   print "Function dropped"
except pyodbc.Error:
   pass

cur.execute('''
   CREATE FUNCTION fn_FuncTest (@testparam varchar(4))
   RETURNS @retTest TABLE (param varchar(4))
   AS
   BEGIN
       INSERT @retTest
       SELECT @testparam
       RETURN
   END''')
con.commit()

Now the function is created. If I try to call it using a value direct in the query (no native binds of values) it works fine:

cur.execute("SELECT * FROM fn_FuncTest('test')")
assert cur.fetchone()[0] == 'test'

However I get the error above when I try to do a native bind (by using a parameter placeholder and passing the value separately):

cur.execute("SELECT * FROM fn_FuncTest(?)", ('test',))

Further investigation reveals some weird stuff I'd like to relate:

• Everything works fine if I change TDS Version to 4.2 (however, version report from sql server is wrong -- using TDS version 4.2 I get '95.08.0255' instead of the real version '08.00.0760').
• Everything works fine for the other two types of functions -> functions that return a value and functions that are just a SELECT query (like a view) both work fine. You can even define a new function that returns the result of a query on the other (broken) function, and this way everything will work, even when doing native binds on the parameters. For example: CREATE FUNCTION fn_tempFunc(@testparam varchar(4)) RETURNS TABLE AS RETURN (SELECT * FROM fn_FuncTest(@testparam))
• Connection gets very unstable after this error, you can't recover.
• The error happens when trying to bind any type of data.

How can I pursue this further? I'd like to do native binds to function parameters.

Answer 1

Did you install the latest service pack for Microsoft SQL Server 2000? If not, could you start with that and update the question accordingly?

Answer 2

Ultimately, this probably isn't the answer you're looking for, but when I had to connect to MSSQL from Perl two or three years ago, ODBC + FreeTDS was initially involved, and I didn't get anywhere with it (though I don't recall the specific errors, I was trying to do binding, though, and it seemed the source of some of the trouble).

On the Perl project, I eventually wound up using a driver intended for Sybase (which MSSQL forked off from), so you might want to look into that.

The Python wiki has a page on Sybase and another on SQL Server that you'll probably want to peruse for alternatives:

http://wiki.python.org/moin/Sybase

http://wiki.python.org/moin/SQL%20Server