How to add a File System Z SQL Method

First step, see the how-to MySQL connectivity in Zope/Plone

That will get you going with your Z SQL Method using the ZMI.  It is specific to MySQL but the basic outline should work for any ZSQLDA.  Ignore my question and comments at the end, they are misleading at best.

But, you want all of your logic on the file system or you would not be reading this.  This how-to explains how I create Z SQL Methods on the file system.  It may not be the best or only way, but it does work.

FSZSQLMethod objects use the extension .zsql, the basename of the file will be your z_sql_method_id.   The file has a special structure with a
dtml-comment section at the beginning followed by your SQL statement with dtml-sqlvar tags used for any arguments needed.

Here is an example .zsql file:

<dtml-comment>
connection_id : your_connection_id
arguments : your_argument1 your_argument2
</dtml-comment>
select * from your_table
where afield = <dtml-sqlvar your_argument1 type="string"> and 
      anotherfield = <dtml-sqlvar your_argument2 type="string">

Within the dtml-comment block the connection_id parameter is reguired.  All other parameters are optional.  The only parameter I've used is arguments, the arguments required for your SQL statement.  There are several other parameters available, the use of some is obvious from the name, others are less obvious and I will only list them here, not document their use.

• connection_id
• title
• arguments
• max_rows
• max_cache
• cache_time
• class_name
• class_file
• connection_hook
• allow_simple_one_argument_traversal

All the gory details are available in the file Products/CMFCore/FSZSQLMethod.py

Use your new FS Z SQL Method in templates exactly the same way as you would use a Z SQL Method added through the ZMI.

It would be nice to also be able to define the Database Connection on the file system but I was not able figure that one out.