[Zope] Two mysql / timestamp related fixes

[Gaute Amundsen]

On Friday 09 March 2007 09:40, Tino Wildenhain wrote:
> 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 :-)

Hm.. have never quite gotten around  to writing my first product.
Looks like a very nice candidate, but no promises :)

> > To build anything complex or generalized in dtml is a PAIN.
>
> Is it? Can you come up with examples?
>
tabell:string
felter:list
INSERT INTO <dtml-var tabell sql_quote> SET
<dtml-in expr="felter.keys()">
  <dtml-let key=sequence-item value="felter[key]">
    <dtml-unless sequence-start>,</dtml-unless>
    <dtml-var key sql_quote> =
    <dtml-if expr="same_type(value, 1)">
      <dtml-sqlvar value type="int">
    <dtml-elif expr="same_type(value, 1.0)">
      <dtml-sqlvar value type="float">
    <dtml-else>
      <dtml-sqlvar value type="string">
    </dtml-if>
  </dtml-let>
</dtml-in>
<dtml-var sql_delimiter>
SELECT LAST_INSERT_ID()

and that is not _really_ complex is it?
There's the whole sqlgroup thing.

dtml is after all depreciated, so I can see no reason to learn it as torougly 
as ZSQLMethods obviously requires when I can use python instead.

> > 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?
>
Don't see quite what you mean here, but it would be just the same problems 
with PGsql.

> >> 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 did not. 
That was the whole point of this mail.

The method was "<dtml-var sql >" and the sql was "select * from foo"
care to spot the invalid code in that?

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


Hm.. interesting, but I looked into it a bit, and I can't find where this 
happens.
I see that ZRDB/DA.py does: argdata['sql_delimiter']='\0'
Tried a "query = query.replace(';','\0')" at line 444, but to no effect :)

Suggestions?
I guess this could be a problem with LAST_INSERT_ID, forcing me to use 
ZSQLMethods, or dome ugly workaround  for inserts, where I need the id 
afterwards.

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

No plans to squeze it all into one huge SQL statement. Now THAT would be ugly!
but python errors does not trigger rollback it seems. I guess only sql errors 
would do that?

regards

Gaute