[Zope] Two mysql / timestamp related fixes

[Tino Wildenhain]

Gaute Amundsen schrieb:
> On Thursday 08 March 2007 17:27, Tino Wildenhain wrote:
>> Gaute Amundsen schrieb:
>>> Hi.
> <snip>
>>> 1 )
>>> First I got a ValueError 'query' when using a very simple zsql method.
>>> (consisting of only "<dtml-var sql >")
>> Thats a very bad idea. Better make zsql methods
>> for every query 
> 
> I know the orthodoxy :-/
> I'd rather have a proper DB library, (or model layer, if you will) 
> and build my SQL in python. 

So you want to use SQLAlchemy? Feel free to write a product
as wrapper :-)

> To build anything complex or generalized in dtml is a PAIN.

Is it? Can you come up with examples?

> And a sea of too specific templates is even worse.

Well from some level of complexity you could also use
database functions. But if its so complex, why using
MySQL?

>> instead of 
>> rebuilding your own (obviously not working)
>> templating for SQL. 
> 
> I's not a template, it's a wrapper to get around the whole mess.
> A hack in fact ;)
> 
> If a template this simple "<dtml-var sql >" is "obviously not working"
> then it is ZSQLMethods that have a problem, not me.

No, the content of your variable sql is wrong :-)
<dtml-var sql> would indeed work if it contains valid sql
code.

> (it's working fine now.)
> 
>> Quoting is done automatically 
>> when you use <dtml-sqlvar ...>
> 
> I can use sql_quote when I need to, thankyou.

this does not need to work identical - but current
adaptors indeed do their handmade quoting.

> 
> The only thing that I worry about is transaction support.
> "insert foo; select last_inserted" works when built inside the template with 
> dtml, but not with the exact same statement passed to this "wrapper".
> Probably another bug, but I have not taken time to dig properly into it yet.

Yes, MySQL doesnt want ; as sql-delimiter. It rather wants \0 and sql
methods hack around it for you if you put the template there.

Transactions are maintained thrughout your session (read: request)
so there is no need to squeeze it all into one ZSQL call. Every
subsequent ZSQL call inside your request will be in the same
database transaction.


Regards
Tino