[egenix-users] problems with MSSQL Server functions

[Moof]

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().

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.

Moof
-- 
Giles Antonio Radford - <moof at metamoof.net>
Website down, but take a peek at http://del.icio.us/moof/
Homme petit d'homme petit, s'attend, n'avale / Homme petit d'homme petit, à
degrés de bègues folles / Anal deux qui noeuds ours, anal deux qui noeuds
s'y mènent / Coup d'un poux tome petit tout guetteur à gaine - Who was badly
hurt?