Zope And MySQL - Visiting The Box Office
(Page 5 of 9 )
Now, if you've worked with databases before, you will be aware that there are four basic things you can do with them:
1. Select records from a table
2. Add records to a table
3. Update existing records in a table
4. Delete records from a table
In Zope, each of these functions can be implemented via Z SQL Methods. Each Z SQL Method stores one or more SQL queries, and these queries can be executed simply by calling the method in your DTML scripts.
Let's see how this works by creating a Z SQL Method to list all the DVDs in my collection.
Go back to the Zope management interface and create a new Z SQL Method from the drop-down list. I named my method "selectAllMethod"; feel free to name yours whatever you like. Remember to associate the Z SQL Method with an appropriate Database Connection object from the drop-down list; most often, this will be the Z MySQL Database Connection you just created on the previous page.
Finally, the meat. Input the following SQL query into the query template:
SELECT * FROM dvd
This query template represents the query (or set of queries) to be executed when
the "selectAllMethod" object is called. As you will see over the next few pages, it is possible to dynamically construct this query template on the basis of environment or form variables.
Once the query template has been saved, you can test it via its Test function. When you do this, Zope will execute the newly-minted method and return the results to you for verification. This is an easy way to verify that your Z SQL Method works the way it is supposed to, and I would recommend that you do it every time you create a new one.
Right. Moving on, let's now see how this Z SQL Method can be used with DTML. Create a new DTML Method - I've called mine "list" - containing the following code:
<dtml-var standard_html_header>
<h2>My DVD Collection</h2>
<ul>
<dtml-in selectAllMethod>
<li><dtml-var title>
<br>
<font size="-1"><a href="edit?id=<dtml-var id>">edit</a> | <a
href="delete?id=<dtml-var
id>">delete</a></font>
<p>
</dtml-in>
</ul>
<a href="add">Add
a new DVD</a>
<dtml-var standard_html_footer>
As you can see, this is extremely simple. All I'm doing is invoking the "selectAllMethod"
Z SQL Method created above and then using a DTML loop (via <dtml-in>) to iterate through the result set. This is identical to the procedure you would use in Perl or PHP to retrieve the results of a database query.
Don't worry too much about the "edit", "add" and "delete" links in the list above - all will be explained shortly. For the moment, just study the manner in which the DTML Method invokes the Z SQL Method and iterates through the returned data set. Notice also that the fields of the returned record set are automatically converted to DTML variables, and can be accessed in the normal way, via the <dtml-var> construct.
Here's what the output looks like:
Next: Adding Things Up >>
More Zope Articles
More By icarus, (c) Melonfire
