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

Andy Todd andy47@halfcooked.com
Tue, 11 Mar 2003 09:53:28 +0000


On Mon, Mar 10, 2003 at 06:13:02PM +0100, M.-A. Lemburg wrote:
> 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
> 

Thanks Marc-Andre,

As usual your help is timely and accurate. Passing the value as a string solves my problem. 

If I get a moment or two spare I will investigate some of the other 'standard' date functions (dateadd, datepart, etc.) and see if they exhibit the same behaviour.

Regards,
Andy
-- 
--------------------------------------------------------------------------------
>From the desk of Andrew J Todd esq - http://www.halfcooked.com/