[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