[prev in list] [next in list] [prev in thread] [next in thread] 

List:       python-db-sig
Subject:    [DB-SIG] ODBC parameters and T-SQL functions
From:       andy47 () halfcooked ! com (Andy Todd)
Date:       2003-03-11 9:53:28
Message-ID: 20030311095327.GA692 () LATITUDE6
[Download RAW message or body]

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/


[prev in list] [next in list] [prev in thread] [next in thread]