[egenix-users] problems with MSSQL Server functions

[M.-A. Lemburg]

Moof wrote:
> M.-A. Lemburg wrote:
> 
>>Moof wrote:
>>
>>
>>>Hi,
>>>
>>>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,
>>>self.idHotel))
>>
>>
>>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
> running.
> 
> Does that help?

Well in standard SQL you would probably want to specify the
table name somewhere and from the function into the SELECT
clause:

SELECT  GetBestRates_Moof('20050121', '20050127', - 1, - 1, 0, 1) as tab
FROM some_table

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:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcparameter_markers.asp

-- 
Marc-Andre Lemburg
eGenix.com

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 ! ::::