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