| Issue 106: | can't bind parameter to function call using pyodbc->unixodbc->freetds->mssql stack | |
| 2 people starred this issue and may be notified of changes. | Back to list |
What steps will reproduce the problem?
1. Edit the code below to match your server/username/password info
2. Run the code
import pyodbc
constring = 'server=a;uid=b;pwd=c;database=d;TDS_Version=7.0;driver={FreeTDS}'
con = pyodbc.connect(constring)
cur = con.cursor()
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()
# this works fine:
cur.execute("SELECT * FROM fn_FuncTest('test')")
assert cur.fetchone()[0] == 'test'
# this fails:
cur.execute("SELECT * FROM fn_FuncTest(?)", ('test',))
assert cur.fetchone()[0] == 'test'
What is the expected output? What do you see instead?
I expect it to work without output. I see this error instead:
Traceback (most recent call last):
File "/home/nosklo/devel/tests/sqlfunc.py", line 32, in <module>
cur.execute("SELECT * FROM fn_FuncTest(?)", ('test',))
pyodbc.ProgrammingError: ('42000', '[42000] [FreeTDS][SQL Server]SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.\r\n (0) (SQLPrepare)')
What version of the product are you using? On what operating system?
Using latest pyodbc git trunk - commit:
eb545758079a743b2e809e2e219c8848bc6256b2
freetds 0.82; unixodbc 2.2.11; sql server 2000
Please provide any additional information below.
After the error, the cursor and the entire connection become very unstable.
I'm also reporting this to freetds list, and if I find something out I'll post here.
Jun 29, 2010
#1
nosklo@gmail.com
Jun 29, 2010
Some extra information: a) 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'). b) 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)) c) Connection gets very unstable after this error, you can't recover. d) The error happens when trying to bind any type of data.
Nov 21, 2010
This runs fine on Windows, so I'm really suspecting FreeTDS. (I get a lot of FreeTDS related issues, so I am going to see if I can setup a set of shared tests with their development group.) Can you provide an ODBC trace of the failure, and I'll compare it with what I see on Windows?
Status:
Investigating
Labels: OpSys-Linux
Apr 19, 2011
this run fine on my computer
ubuntu 10.10
freetds 0.82
unixodbc 2.2.14
python 2.6.6
sql server 2000
>>> con = pyodbc.connect('DSN=PacsDB;UID=dev;PWD=dev')
>>> cur = con.cursor()
>>> cur.execute('''
CREATE FUNCTION fn_FuncTest (@testparam varchar(4))
RETURNS @retTest TABLE (param varchar(4))
AS
BEGIN
INSERT @retTest
SELECT @testparam
RETURN
END''')
<pyodbc.Cursor object at 0xb61dcfa8>
>>> con.commit()
>>> cur.execute('select * from fn_FuncTest(?)',('test',))
<pyodbc.Cursor object at 0xb61dcfa8>
>>> cur.fetchone()[0]
'test'
>>>
|