The Doctor What wrote:
* The Doctor What (docwhat@gerf.org) [010419 11:57]:
Does any one have an example of ZSQL being used witha normalized database? Or is ZSQL just useless?
Near as I can tell, between: * Broken type marshalling * Loosing the variable between the form and dtml-if
I don't understand this. I'm assuming that you are losing values that are not in your argument list. All you have to do is add the arguments.
* Inability to handle table.field names for variables * And enough flexibility to work around the above problems
This makes ZSQL extreamely nasty, and impossible to use with a normalized database.
Database normalization isn't really an issue. It sounds like you're really just having problems with the syntax of joins.
What's the point? ZSQL sucks, how do I talk to the DB directly?
Grab the Python database adapter and write some python classes or external methods to do what you need.
Yeah, I keep almost getting this to be useful. But damn it if I don't keep hitting a brick wall. And there are no complete examples or demos that I can find to load up.
Irritatedly yours, DocWhat
I would like to apologize for being particularly pissy. Things are quite as bad as I say up there...
My third point is only half true. I can have SQLTEST specify a column name (aka a field): <dtml-sqltest somevarname column="SQLTABLE.sqlfield" type...>
This makes things work MUCH better. So there are work arounds.
This isn't a work-around, it is the way that it is intended to be used. But
this doesn't excuse this not working: SELECT table.field1, table.field2 FROM ....
and then: <dtml-var name="table.field1"> <--doesn't work <dtml-var name="field1"> <-- does work
I know that the '.' has a special meaning, but there should be ways around this if the use wants.
Again, I think that it's a matter of understanding how ZSQL works, rather than a limitation of ZSQL. If you have column name conflicts, you can always use something like: SELECT a.field as a_field, b.field as b_field FROM ... Then refer to them in dtml like &dtml-a_field; or <dtml-var name="a_field">
I still would love some examples. Do people end up with 4 ZSQL objects per thing they manipulate in their database?: UPDATE, SELECT, INSERT and DELETE? Or do they mix them somehow?
I typically wind up with a ZSQL method for each of the CRUD operations. Sometimes I have separate selects methods for more complex joins, if the DTML code gets too ugly when I try to combine them into a single method. Example of a fairly typical join statement (for MySQL database): Arguments- begin_date:date end_date:date="2037-01-01" SELECT search_string, search_result, search_date, result_category, legal_category.name FROM search_log LEFT JOIN legal_category ON search_log.result_category=legal_category.code <dtml-sqlgroup required where> <dtml-sqltest name="begin_date" op="gt" type="nb" column="search_date"> <dtml-and> <dtml-sqltest name="end_date" type="nb" op="lt" column="search_date" optional> </dtml-sqlgroup> Good Luck -Paul