[egenix-users] problems with MSSQL Server functions
mal at egenix.com
Fri Jan 21 18:08:45 CET 2005
> M.-A. Lemburg wrote:
>>>I seem to have a slight problem with MSSQL server.
>>>c is a cursor to an SQL Server instance:
>>>c.execute('SELECT * FROM GetBestRates_Moof( ?, ?, ?, ?, ?, ? ) tab',
>>> (fromDate, toDate, self.idCorp, idRoom, idRegimen,
>>This doesn't look like valid SQL to me. Could you explain what the
>>function does and what "tab" is for ?
> Copied straight out of my enterprise manager query section:
> SELECT *
> FROM GetBestRates_Moof('20050121', '20050127', - 1, - 1, 0, 1) tab
> which returned a valid table with the data I'm after.
> Microsoft's SQL Server seems intent on removing AS from that. It should be
> GetBestRates_Moof(..) AS tab, just to give it an easier name for later
> reference as tab.Day, tab.RPax, etc. Either way, it gives the same error
> *without* the tab bit.
> GetBestRates_Moof is a user defined function that takes two smaldatetimes
> and four ints and returns a table with columns made up of a smalldatettime,
> 5 ints, 3 floats and another int. It's a complex query that calculates the
> cheapest rate for a given range of days in one of the various hotels we're
> Does that help?
Well in standard SQL you would probably want to specify the
table name somewhere and from the function into the SELECT
SELECT GetBestRates_Moof('20050121', '20050127', - 1, - 1, 0, 1) as tab
If the function does indeed return a table name, then you cannot
use bind parameters for it. This is not allowed in ODBC. The
only way to solve the issue then is to quote the parameters
and place them into the statement string as SQL literals (like
in the above example).
More infos on where bind parameters are allowed in ODBC:
Professional Python Services directly from the Source (#1, Jan 20 2005)
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::
More information about the egenix-users