My favorites | Sign in
Project Home Downloads Wiki
READ-ONLY: This project has been archived. For more information see this post.
Search
for
  Advanced search   Search tips   Subscriptions
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
Status:  Investigating
Owner:  ----


 
Reported by nosklo@gmail.com, Jun 28, 2010
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
Here's a link to the freetds list thread I created

http://lists.ibiblio.org/pipermail/freetds/2010q2/026123.html


Jun 29, 2010
#2 nosklo@gmail.com
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
Project Member #3 mkleehammer
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
#4 septem...@gmail.com
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'
>>> 

Powered by Google Project Hosting