[egenix-users] problems with MSSQL Server functions

[M.-A. Lemburg]

Moof wrote:
> M.-A. Lemburg wrote:
> 
> 
>>>>SELECT     *
>>>>FROM         GetBestRates_Moof('20050121', '20050127', - 1, - 1, 0,
>>>>1) tab
> 
> 
>>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.
> 
> 
> Sorry, no, I didn't explain correctly.
> 
> GetBestRates_Moof returns a set of results, which to all intents and
> purposes *is* a table, and can be queried as if it were one. Think of it as
> a bounded view that is generated on the fly.
> 
> I cannot have a permanent view defined for this data due to the rather
> horrid database design that I have to live with for the moment. I don't need
> to or want to calculate the lowest rates for every single day in the
> entirety of time, which is what I'd have to do with a view, so instead I
> generate it for the number of days defined in the interval (which above is
> 2005-01-21 to 2005-01-27). In fact, internally, it appears that the function
> creates the range of dates as a resultset and promptly joins it to a bunch
> of other complicated stuff.
> 
> I find working with dates the most taxing thing abotu this particular
> database, especially as sometimes I'm working on real time, and sometimes
> I'm working on the defined date. If you check in at 1am on the 23rd, say, it
> still has to be counted as if you checked in on the 22nd for accounting and
> availability purposes as you're still sleeping the night in the hotel and
> need to be charged for it. The day switches over once close of business has
> been declared by the receptionist, which is about 5am for most of our hotels.
> 
> Anyway, that's just me blathering on.

Thanks for the background infos.

> Does it still make sense as to why execute() isn't doing the right thing?
> surely it should return an error more akin to "command cannot be prepared"
> or something?

I guess this is a quirk in the MS SQL ODBC driver. The error
message doesn't contain enough information to be able to tell
what is going wrong, e.g. it is possible the the driver
simply ignores the ?-mark parameters in the statement and
thus assumes that the number of parameters is 0 rather than 6.
Just guessing here... for a complete analysis, you'd have to
run mxODBC in a debugger.

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