[DB-SIG] ODBC parameters and T-SQL functions

M.-A. Lemburg mal@lemburg.com
Mon, 10 Mar 2003 18:13:02 +0100


Andy Todd wrote:
> Afternoon all. I am writing a script in Python 2.2 using mxODBC talking to SQL Server 2000.
> 
> I'm getting errors when I pass an mxDateTime object as a parameter to a SQL statement. But its only when the parameter is subsequently used in a T-SQL function, specifically DATEDIFF. Elsewhere (for instance in a where clause) I can pass this same value in without a hitch.
> 
> Has anyone seen this before? The reason I ask is because this works with the win32all odbc package but not with mxODBC. Here is an echo of my interactive session;
> 
> 
>>>>from mx import ODBC
>>>>from mx import DateTime
>>>>db=ODBC.Windows.connect('Wine')
>>>>cursor=db.cursor()
>>>>myDate=DateTime.DateFrom('31-DEC-2002')
>>>>stmt = "SELECT share_number ,datediff(Month, date_of_election, ?) FROM member_details"
>>>>cursor.execute(stmt, (myDate,))
> 
> ceback (most recent call last):
>   File "<interactive input>", line 1, in ?
> ProgrammingError: ('37000', 0, '[Microsoft][ODBC SQL Server Driver]Syntax error or access violation', 4498)

Could be that the T-SQL function is generating output using PRINT
or that it uses RAISEERROR to report an error with the parameters
or values.

Also see:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnodbc/html/odbcsql.asp

Another possibility is that the ODBC driver for SQL Server
does not support the syntax you are using or that bound parameters
are not allowed at that place in the SQL.

I'd simply add the date verbatim:

stmt = "SELECT share_number ,datediff(Month, date_of_election, %s) FROM member_details" % \
        myDateTime.date

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source  (#1, Mar 10 2003)
 >>> Python/Zope Products & Consulting ...         http://www.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
Python UK 2003, Oxford:                                     22 days left
EuroPython 2003, Charleroi, Belgium:                       106 days left