Why Z SQL Methods suck:

To really understand why Z SQL Methods suck so much, you probably have to understand them in a societal context. I won't really provide that, but I will note that the ZODB has not known the neglect that Zope RDBMS support has known. RDBMS users are second class in the Zope world. But instead I will present you a list, with no particular order:

There's several ways to write a query, depending on how many of the Z SQL features you use (e.g., dtml-and, dtml-sqltest, dtml-sqlgroup, etc). They all look quite different, and some will feel very foreign to someone who knows SQL but not Z SQL.

It uses DTML, or DTML-like constructs. Most of the things that are bad about DTML for templates are also bad for DTML generating SQL. You could write entire books about why DTML is bad.

Connection paths are pretty much hard-coded. You can't tell a method to use a different connection. This would be useful if you wanted to use database-level security, and so you need two separate connections with separate users. You might be able to do this with acquisition, but whole books could be written on why acquisition is bad. Well, it would probably be a chapter in the DTML book.

It usually looks like normal SQL, but is far enough away from SQL to be difficult to work with in source form, since it uses <dtml-sqlvar> instead of parameters like ? or even %s. It's usually a long way from Z SQL source to something you can feed to EXPLAIN.

It is difficult to debug. There's one poorly documented feature where you can to pass src__ in as a keyword argument to see what the SQL source actually is. That's about all the debugging you get. AFAIK, there's no good log of the SQL queries sent, how long they took, how many queries there were, etc.

It has crufty parameters. Put "a, b, c" in , and you get parameters with names like "a,", "b," and "c". Default parsing seems wonky too. A little error checking would be nice.

It doesn't signal errors properly -- if you forget to pass in a keyword argument, it's pretty much ignored. Or if you pass in extra keyword arguments. Why have a parameter list at all?

It doesn't deal with failures very well. Why not give me the SQL in the exception when there's an error? It doesn't deal with any of the hard parts of failure in general, connection failures, concurrency conflicts, etc.

You can't nest or factor your methods well. This is largely DTML's fault, but Z SQL makes it worse since all methods are concrete and imply not just SQL generation, but also SQL execution. You can't save values (e.g., get an ID from a sequence then do an insert), or use control structures, etc., except using plain DTML constructs which have their own issues. For instance, consider turning a list of items into SQL for use with IN, like (<dtml-in items prefix=loop> <dtml-unless loop_start> , </dtml-unless> <dtml-sqlvar loop_item type=int> </dtml-in>). That's lame, and you can't abstract out the lameness. Well, maybe with the use of src__ and other stuff, but that's it's own lameness. (Hmm... I see now there's an option to dtml-sqlvar to handle this particular case... so many options, but I can't use any of them from the outside, nest dtml expressions, etc., so I can't build higher level structures)

While we're on the subject, every issue is dealt with using another dtml-sqlvar option or another tag, there's not a good set of complete orthogonal functions.

SQL Brains are cool, if still little under powered. But they don't see updates unless you go twiddle all sorts of forms everytime you do an update. Annoying.

You can't set any attributes on result rows. Even if you know why it might not be a good idea. Even if you try really hard. (Though you can add a dummy column to your query, and then reassign the column.) This is annoying particularly when using brains.

Caching is stupid. You can cache, but you can't invalidate the cache. There's a product out there that provides a cacheable SQL method, but it's a whole separate product, and you can't easily switch types, or even easily upgrade.

All methods return result objects with multiple rows. It's common to see:

<dtml-in "get_some_object(id=whatever)">
  <dtml-var some_column>...
</dtml-in>

when in fact you only expect there to be one row in the result. This is where DTML's suckiness gets involved (dtml-in, dtml-with, all the flattened namespaces, etc).

You start needing all sorts of methods for little things. Like, say you want to fetch a value from a sequence, like SELECT nextval('sequence_name') as seq. Do you create a new method for each sequence? What an annoying process! (ZMI partially at fault) And you get lameness like <dtml-in get_sequence_name><dtml-var seq></dtml-in> Oh, it hurts me just to type this! Or container.get_sequence_name()[0][0]. This leads to code being highly coupled. You want to put a Python Script in place of the Z SQL method for some reason? Now you have to return something like [[id]]. Or [{'seq': id'}]? Only way to find out is to seek all references... oh, the coupled pain! (This is why I keep a firm Python Script barrier between my Z SQL methods and my "good code" -- I don't want Z SQL cruftiness to infect everything else with it's lameness)

No positional parameters. Seems like an okay idea, except for all the methods that are fetch-row-with-this-id, where positional is better. (Something else for the Python Script wrapper)

SQL methods are often tightly bound to specific scripts, templates, etc. But it's hard to express binding, and they are just more cruft to search through in the ZMI. If something is tightly coupled, I want to know it, I want to know that changes effect only one place, or that I shouldn't use it from the outside. At least inline SQL gives you this.

dtml-sqlvar doesn't have any good types. type=date please? Some attempt at a database compatibility layer would be nice. Strings and floats are easy. Solving difficult problems is actually useful, just solving easy problems is a programming cop-out.

Doesn't do anything with database introspection. Again, I know it's hard, but hard is useful. And it's not that hard.

The test tab is really lame. I won't go into all of why it's lame. It could be useful, but it's only distracting. (I might think I can just test a quick query in it, but I'm always wrong.) Didn't anyone use the test pane before calling it finished?

I'd say it felt good to vent, except I'm going to have to work with Z SQL methods again tomorrow, and venting doesn't make up for that. Pity me!