I was about to follow up to Mike's suggestion of a CAST with "That
will never work because ... " but I decided to try it out and .. it
works!!!:
for i in e.execute(sql.text("select result = dateadd(day, CONVERT
(INTEGER,:days), getdate())"), dict(days = 7)):
print i
(datetime.datetime(2009, 6, 15, 11, 32, 21, 880000),)
for i in e.execute(sql.text("select dateadd(day, CONVERT
(INTEGER,:days) AS result, getdate())"), dict(days = 7)):
print i
# This fails with:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', '[42000]
[FreeTDS][SQL Server]Statement(s) could not be prepared. (8180)')
u'select dateadd(day, CONVERT(INTEGER,?) AS result, getdate())' [7]
This is very interesting and surprising to me as my understanding of
the rules for placeholders for prepared statements in SQL Server and
Sybase limit their use to:
• In place of one or more values in an insert statement
• In the set clause of an update statement
• In the where clause of a select or update statement
I don't know what it is about the result= syntax that makes it work
but it is good to know. However, there are a couple of problems with
dateadd and friends (datediff,datepart, datename) in mssql and
Sybase: the datepart parameter is not bindable (or, rather, I have
never been able to figure out how to do it). It must be *unquoted*
literal in the set:
year yy
quarter qq
month mm
week wk
day dd
dayofyear dy
weekday dw
hour hh
minute mi
second ss
millisecond ms
calweekofyear cwk
calyearofweek cyr
caldayofweek cdw
Mike, have you any suggestions on how to declare the various date-
related function in SA so that the first parameter is passed through
as an unquoted literal (maybe with some client-side checks to make
sure it is in the list above) and not as a bind parameter?
pjjH
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
