[egenix-users] problems with MSSQL Server functions

[M.-A. Lemburg]

Moof wrote:
> 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?
> 
> 
> The above was sent privately, as I was expectign a rpley to go directly to
> the list, and it didn't.
> 
> FWIW, I tried executedirect() which works flawlessly. So it must have
> something to do with whatever it is that execute() does differently to
> executedirect().

Makes sense: .executedirect() sends the command directly to the
server. It doesn't prepare the command for execution first, which
.execute() does in order to be able to cache the preparation
step for subsequent calls with different parameters.

I still wonder why the server doesn't complain, though. I've
never seen a database that allows using table selection based
on bound parameters and this is actually the first time I've
seen a stored procedure being used to determine the table
name.

> Given that all I'm doing is calling a function which does all the hard work,
> I expect I won't get much of a performace gain out of execute() in this
> case. But I'm still intrigued as to why one works and the other doesn't.

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