Error binding sql server parameter parameter

Question

I use the following software stack on Ubuntu 10.04 Lucid LTS to connect to the database:

• python 2.6.5 (ubuntu package)
• pyodbc git trunk commit eb545758079a743b2e809e2e219c8848bc6256b2
• unixodbc 2.2.11 (ubuntu package)
• freetds 0.82 (ubuntu package)
• Windows with Microsoft SQL Server 2000 (8.0)

I get this error when trying to bind my own parameters in arguments to the 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 replay 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 a function is created. If I try to call it using the direct value in the query (there are no built-in value bindings), 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 my own binding (using the placeholder parameter and passing the value separately):

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

Further research reveals some strange things that I would like to say:

• Everything works fine if I change the version of TDS to 4.2 (however, the version of the report from the sql server is incorrect - using version TDS 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 only a SELECT query (as a form) works fine. You can even define a new function that returns the result of the request to another (broken) function and thus, everything will work , even when your own bindings to the Parameters are performed . For example: CREATE FUNCTION fn_tempFunc(@testparam varchar(4)) RETURNS TABLE AS RETURN (SELECT * FROM fn_FuncTest(@testparam))
• After this error, the connection becomes very unstable, you cannot restore.
• An error occurred while trying to bind any data type.

How can I continue this? I would like to make my own bindings to function parameters.

Answer 1

Ultimately, this is probably not the answer you are looking for, but when I had to connect to MSSQL with Perl two or three years ago, ODBC + FreeTDS was initially involved and I didn’t go anywhere (although I don’t remember the specific errors , I tried to make a binding, though, and this seemed to be the source of some problems).

In the Perl project, I ended up working with a driver designed for Sybase (from which MSSQL disconnected), so you can look at it.

The Python wiki has a page on Sybase and another on SQL Server, which you probably want to look at alternatives:

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

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