[Zope] Accessing ZSQL Methods from outside Zope

[Thomas B. Passin]

[Richard Moon]

> I've got a Zope site (www.tunedb.org) with a MySQL backend and a load of
> data. People are asking me if they can connect to that database
(read-only)
> somehow, perhaps using the SQL queries I have already set up, or perhaps
by
> writing their own queries and accessing MySQL directly.
>
> Has anyone done anything like this ? Can you point me in the right
direction ?
>

Sure, it's not hard to do.  Create a ZSQL method that takes one parameter.
That parameter would be a complete select statement.  Your method will thus
be able to execute any query against the database.  Your main dtml page
calls the ZSQL method with this parameter.  Or better, and what I usually
do, use another DHTML method to take the form data and create the query from
it.  Then pass that query to the ZSQL method.  I even have pages where I do
nested queries and can perform inserts and updates, all by reusing the same
generic ZSQL method.

You can use the methods discussed a few weeks ago on the list to find out
the column names so you can create a table to fit the returned data without
knowing the exact query beforehand.  I do this all the time (I only learned
how to do it in Zope a few weeks ago, but I've been doing it in Cold Fusion
for years).

Of course, you don't really want to allow anyone to have any kind of access,
but you can build up a query based on form data.   I recommend providing
custom queries you create based on what your users want.  This is the safest
way to proceed, since you can make sure that only those queries are
executed.

Or give them a textarea input and let them write their own select statements
if they know how.  But make sure you scan the returned select statements to
make sure they can't do anything you don't want, like DELETE!

This can be fun, and your users will really appeciate it.  Let us know how
it comes out.

Cheers,

Tom P