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 ? Many thanks Richard Richard Moon richard@dcs.co.uk
[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
On 8 May 2001, at 20:29, Richard Moon wrote:
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 ?
I have an IE "single-page web application" that uses ADO 2.6 to download recordsets in XML format from Zope. I have an extension that converts the results of an SQL Method into an ADO recordset. I suppose you could do something similar.. Just deliver the data in whatever format they need, like plain XML. Brad Clements, bkc@murkworks.com (315)268-1000 http://www.murkworks.com (315)268-9812 Fax netmeeting: ils://ils.murkworks.com AOL-IM: BKClements
participants (3)
-
Brad Clements -
Richard Moon -
Thomas B. Passin