• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 3. CONNECTING ZOPE TO EXTERNAL R... > Using Z SQL Methods to Submit SQL St...

Using Z SQL Methods to Submit SQL Statements

Using the Test View is not a very comfortable way to send SQL statements if you frequently want to add new records to the database or want to search the database. An easier way to do this is to make use of Z SQL methods and DTML methods. The DTML methods will contain forms where you can enter a new record or a search query. They then send the entered data to Z SQL methods that build a complete SQL statement and send it via the connection to the database. You need a Z SQL method for each type of SQL statement you want to use. However, because you can give arguments to the Z SQL method, it is possible to create a Z SQL method in such a way that it can be used for different varieties of an SQL statement. In this way, you can create templates for your SQL statements.

Note

Before creating a Z SQL method, you need to make sure that there is a database connection either in the same folder or in a folder above.


Creating a Z SQL Method

To create a Z SQL method, go to the folder where you want to use the Z SQL method and select the Z SQL method option from the drop-down menu. Your browser is automatically directed to the form shown in Figure 3.15.

Figure 3.15. Adding a Z SQL method.


Choose an ID and a Title for that particular Z SQL method, for example

ID: addRecord
Title: Adds a New Record


Now you need to choose which of the accessible database connections the Z SQL method is to use. Select the respective connection from the drop-down Connection ID menu. Next come the arguments—or parameters—that the SQL statement will need to be complete. For example, if you want to enter a new record in the Addressbook database that we created a few pages ago, you need the following arguments:

Name FirstName Address City ZIP Phone

Note that there are no commas between the arguments. You can also leave the Arguments text area empty if you do not need any arguments for the desired SQL statement.

Now that you have entered the parameters, you can define the SQL statement for this specific Z SQL method. When defining the statement, you will need <dtml> commands to insert arguments into the statement. To finish creating the Z SQL method, enter the code in Listing 3.1 into the Query Template text area:

Listing 3.1. Query Template

1: INSERT INTO Addressbook
2: (Name, FirstName, Address, City, ZIP, Phone)
3: VALUES
4: ('<dtml-sqlvar Name type=string>',
5: '<dtml-sqlvar FirstName type=string>',
6: '<dtml-sqlvar Address type=string>',
7: '<dtml-sqlvar City type=string>',
8: <dtml-sqlvar ZIP type=int>,
9:'<dtml-sqlvar Phone type=string>')

Note that because ZIP needs to be an integer, there are no quotes around <dtml-sqlvar ZIP type=int>.

There are three ways to finish this Z SQL method:

  • By clicking the Add button, which will return you to the Contents View of the folder

  • By clicking the Add and Edit button, which will take you directly to the Edit View of the new Z SQL method

  • By clicking the Add and Test button, which will take you directly to the Test View of the Z SQL method

After you click one of the buttons, the Z SQL method is created and can be called from DTML methods.

Calling the Z SQL Method from a DTML Method

You will need two DTML methods to use the Z SQL method you just created

  • One for the form where the data for a new record can be entered

  • One that calls the Z SQL method and sends the data from the form to it

Create a new DTML method called addnewRecordForm_html in the MyAddressbook folder and enter the code in Listing 3.2.

Listing 3.2. addNewRecordForm_html (DTML Method)

 1: <dtml-var standard_html_header>
 2: <h1>Add a New Record</h1>
 3: <form action="result_html" method=post>
 4:   <table bgcolor="#CCCCCC" width="100%">
 5:   <tr>
 6:     <td><b>Name:</b></td>
 7:     <td><input type="text" name="name" size=20></td>
 8:   </tr>
 9:   <tr>
10:     <td><b>First Name:</b></td>
11:     <td><input type="text" name="first_name" size=20></td>
12:   </tr>
13:   <tr>
14:     <td><b>Address:</b></td>
15:     <td><input type="text" name="address" size=40></td>
16:   </tr>
17:   <tr>
18:     <td><b>City:</b></td>
19:     <td><input type="text" name="city" size=15></td>
20:   </tr>
21:   <tr>
22:     <td><b>ZIP Code:</b></td>
23:     <td><input type="text" name="zip:int" size=5></td>
24:   </tr>
25:   <tr>
26:     <td><b>Phone:</b></td>
27:     <td><input type="text" name="phone" size=15></td>
28:   </tr>
29:   <tr>
30:     <td colspan=2><input type="submit" value="Add Record"></td>
31:   </tr>
32:   </table>
33: </form>
34: <dtml-var standard_html_footer>


					  

As you can see in the <form> tag (line 3), this form calls the DTML method result_html. This DTML method is the one that actually calls the Z SQL method and then tells the user whether the new record was entered successfully. Create another DTML method using the code in Listing 3.3.

Listing 3.3. result_html (DTML Method)

 1: <dtml-var standard_html_header>
 2: <dtml-try>
 3:   <dtml-call "addRecord(Name=_['name'],
 4: FirstName=_['first_name'],
 5: Address=_['address'],
 6: City=_['city'],
 7: ZIP=_['zip'],
 8: Phone=_['phone'])">
 9:   <dtml-call "REQUEST.set('status', 'ok')">
10: <dtml-except>
11:   <dtml-call "REQUEST.set('status', 'failed')">
12: </dtml-try>
13: <dtml-if "_['status']=='ok'">
14:   <h1>New record was successfully added.</h1>
15:   <form action="index_html">
16:     <input type="submit" value="OK">
17:   </form>
18: <dtml-else>
19:   <h1>Adding the new record failed. No record was added.</h1>
20:   <table>
21:     <tr>
22:       <td>
23:         <form action="addNewRecordForm_html">
24:           <input type="submit" value="Try again">
25:         </form>
26:       </td>
27:       <td>
28:         <form action="index_html">
29:           <input type="submit" value="OK">
30:         </form>
31:       </td>
32:     </tr>
33:    </table>
34: </dtml-if>
35: <dtml-var standard_html_footer>


					  

In this DTML method, the Z SQL method is called in a <dtml-call> tag (line 3). After the name of the Z SQL method, you declare the parameters in parentheses. First, you name the name of the argument as it is declared in the Z SQL method, and then you give the name of the variable as it is defined in the entry form.

The <dtml-try> tag (line 2) is used here to set the 'status' variable, depending on whether the insertion was successful (line 9) or not (line 11). If the 'status' variable has the value 'ok', the user is informed of the successful insertion and can go on to the index_html page by clicking a Submit button (lines 15–17). If the insertion failed for any reason, the 'status' variable will contain the value 'failed'. In that case, the user is informed that there was an error and he or she can either try again to enter the data (lines 23–25) or go on to the index_html page (lines 28–30).

Creating a Search Form and a Result Page

You can also create a search form for your database. Again, you need a Z SQL method and two DTML methods. You need

  • A Z SQL method that contains the SELECT...FROM... statement

  • A DTML method that contains a search form

  • A DTML method that shows the search results

First, create a new Z SQL method with the following data:

ID: searchAddressbook
Title: Search the Addressbook Table
Database Connection ID: MyAddressbookConnection
Arguments: Name FirstName Address City ZIP Phone
Query Template:
SELECT *FROM Addressbook
WHERE
(Name='<dtml-sqlvar Name type=string>' OR
 FirstName='<dtml-sqlvar FirstName type=string>' OR
 Address='<dtml-sqlvar Address type=string>' OR
 City='<dtml-sqlvar City type=string>' OR
 ZIP=<dtml-sqlvar ZIP type=int> OR
 Phone='<dtml-sqlvar Phone type=string>')


Now create a DTML method called searchAddressbookForm_html with the code in Listing 3.4.

Listing 3.4. searchAddressbookForm_html

 1: <dtml-var standard_html_header>
 2: <h2><dtml-var title_or_id> <dtml-var document_title></h2>
 3: <form action="searchResults_html" method=post>
 4:   <table width="100%">
 5:   <tr>
 6:     <td><b>Name:</b></td>
 7:     <td><input type="text" name="name" size=20></td>
 8:   </tr>
 9:   <tr>
10:     <td><b>First Name:</b></td>
11:     <td><input type="text" name="first_name" size=20></td>
12:   </tr>
13:   <tr>
14:     <td><b>Address:</b></td>
15:     <td><input type="text" name="address" size=40></td>
16:   </tr>
17:   <tr>
18:     <td><b>City:</b></td>
19:     <td><input type="text" name="city" size=15></td>
20:   </tr>
21:   <tr>
22:     <td><b>ZIP Code:</b></td>
23:     <td><input type="text" name="zip:int" value="00000" size=5></td>
24:   </tr>
25:   <tr>
26:     <td><b>Phone:</b></td>
27:     <td><input type="text" name="phone" size=15></td>
28:   </tr>
29:   <tr>
30:     <td colspan=2><input type="submit" value="Search Addressbook"></td>
31:   </tr>
32:   </table>
33: </form>
34: <dtml-var standard_html_footer>


					  

Note the 'value' declaration in the <input> tag for the ZIP code (line 23). It is important to define a default value here because the SQL statement will return an error if you don't. This is only necessary for integer and float values because there are no quotation marks surrounding the <dtml-sqlvar> tag in the SQL statement.

As you can see from the <form> tag (line 3), you need another object—"searchResults_html". This is the second DTML method you need that will call the Z SQL method and show the results of the search. Create this second DTML method with the code in Listing 3.5.

Listing 3.5. searchResults_html (DTML Method)

 1: <dtml-var standard_html_header>
 2: <h2><dtml-var title_or_id> <dtml-var document_title></h2>
 3: <table width="100%" border=2>
 4:   <tr>
 5:     <th>Name</th>
 6:     <th>First Name</th>
 7:     <th>Address</th>
 8:     <th>City</th>
 9:     <th>ZIP Code</th>
10:     <th>Phone</th>
11:   </tr>
12:   <dtml-in "searchAddressbook(Name=name,
13:                     FirstName=first_name,
14:                     Address=address,
15:                     City=city,
16:                     ZIP=zip,
17:                     Phone=phone)">
18:   <tr>
19:     <td><dtml-var name></td>
20:     <td><dtml-var firstname></td>
21:     <td><dtml-var address></td>
22:     <td><dtml-var city></td>
23:     <td><dtml-var zip></td>
24:     <td><dtml-var phone></td>
25:   </tr>
26:   </dtml-in>
27: </table>
28: <dtml-var standard_html_footer>


					  

The important part of this DTML method is the <dtml-in> loop (lines 12–26). It is necessary to call the Z SQL method within a <dtml-in> tag and not just a <dtml-var> tag so that you can go through the list of records the database sends back and show the information from each record that you want to show.

The <dtml-in> tag calls the Z SQL method searchAddressbook (line 12). The parameters are given within the parentheses after the name of the Z SQL method: first, the name of the argument as it is declared in the Z SQL method and then, after the equals sign, the name of the variable as it is defined in the search form. Within the loop, you can then show the results by calling the column names of the table (lines 19–24). However, the column names have to be given with lowercase characters, even if you declared a column with a capital first letter. The database is not case sensitive and returns the table entries with the columns in lowercase characters. Zope, however, is case sensitive and so you must use lowercase characters to call the columns or Zope will not know the variables.

Updating a Record

Writing a dynamic SQL statement to update a record in a table is more complex because there are so many varieties on what you want to update and in which record. For example, you may want to change the last name of a certain person from Smith to Jones. However, you only want to change Ellen Smith's name and not John Smith's. The easiest way to so this is to update the whole record. That way, you just have to hand over all columns of the old record as well as all of the new one.

Create a new Z SQL method with the following data:

ID: updateRecord
Arguments: Name FirstName Address City ZIP Phone
 NewName NewFirstName NewAddress NewCity NewZIP NewPhone
Query Template:
UPDATE Addressbook
SET
Name='<dtml-sqlvar NewName type=string>',
FirstName='<dtml-sqlvar NewFirstName type=string>',
Address='<dtml-sqlvar NewAddress type=string>',
City='<dtml-sqlvar NewCity type=string>',
ZIP=<dtml-sqlvar NewZIP type=int>,
Phone='<dtml-sqlvar NewPhone type=string>'
WHERE
Name='<dtml-sqlvar Name type=string>' AND
FirstName='<dtml-sqlvar FirstName type=string>' AND
Address='<dtml-sqlvar Address type=string>' AND
City='<dtml-sqlvar City type=string>' AND
ZIP=<dtml-sqlvar ZIP type=int> AND
Phone='<dtml-sqlvar Phone type=string>'


As you can see, there are ten arguments because the Addressbook table has five columns and you need both the old entry and the new one.

To update a record, you need a form similar to the one you need to add a new entry. However, it needs to be modified so that the old entry can be seen in the text fields of the form and can also be handed over hidden to the Z SQL method. Create a new DTML method with the code in Listing 3.6.

Listing 3.6. updateEntryForm_html (DTML Method)

 1: <dtml-var standard_html_header>
 2: <h1>Update the Record</h1>
 3: <form action="updateRecord_html" method=post>
 4: <input type=hidden name="name" value="<dtml-var name>">
 5: <input type=hidden name="firstname" value="<dtml-var firstname>">
 6: <input type=hidden name="address" value="<dtml-var address>">
 7: <input type=hidden name="city" value="<dtml-var city>">
 8: <input type=hidden name="zip:int" value="<dtml-var zip>">
 9: <input type=hidden name="phone" value="<dtml-var phone>">
10: <table bgcolor="#CCCCCC" width="100%">
11: <tr>
12:   <td><b>Name:</b></td>
13:   <td><input type="text" name="newname" value="<dtml-var name> "size=20></td>
14: </tr>
15: <tr>
16:   <td><b>First Name:</b></td>
17:   <td><input type="text" name="newfirst_name" value="<dtml-var firstname>" size=20></td>
18: </tr>
19: <tr>
20:   <td><b>Address:</b></td>
21:   <td><input type="text" name="newaddress" value="<dtml-var address>" size=40></td>
22: </tr>
23: <tr>
24:   <td><b>City:</b></td>
25:   <td><input type="text" name="newcity" value="<dtml-var city>" size=15></td>
26: </tr>
27: <tr>
28:   <td><b>ZIP Code:</b></td>
29:   <td><input type="text" name="newzip:int" value="<dtml-var zip>" size=5 maxsize=5></td>
30: </tr>
31: <tr>
32:   <td><b>Phone:</b></td>
33:   <td><input type="text" name="newphone" value="<dtml-var phone>" size=15></td>
34: </tr>
35: <tr>
36:   <td colspan=2><input type="submit" value="Update Record"></td>
37: </tr>
38: </table>
39: </form>
40: <dtml-var standard_html_footer>


					  

The old entry values are handed over to this update form from a different DTML method (index_html) that we will create a little later. In lines 4–9, the old entry values are given as hidden so that they can be transferred together with the new values to the Z SQL method later. In lines 11–34, the part of the form that can be seen and filled out is defined. The old entry values are put into the text fields so that the user can see what he or she is about to change and also because this way he or she does not need to re-enter the values that don't need to be changed.

Now there is one question: From where do you get the old record entry? You could create an index_html page that shows all entries of the Addressbook table and also gives the user a button with which he or she can update an existing entry. This button will give the old entry over to the update form. Listing 3.7 contains the code for the index_html page.

Listing 3.7. index_html (DTML Method)

 1: <dtml-var standard_html_header>
 2: <h2><dtml-var title_or_id></h2>
 3: <table>
 4: <tr>
 5:   <th>Name</th>
 6:   <th>First Name</th>
 7:   <th>Address</th>
 8:   <th>City</th>
 9:   <th>Zip code</th>
10:   <th>Phone</th>
11:   <th></th>
12: </tr>
13: <dtml-in "getAll()">
14: <tr>
15: <form action="." method=post>
16:   <td><dtml-var name> <input type=hidden name="name" value="<dtml-var name>"></td>
17:   <td><dtml-var firstname> <input type=hidden name="firstname" value="<dtml-var firstname>"></td>
18:   <td><dtml-var address> <input type=hidden name="address" value="<dtml-var address>"></td>
19:   <td><dtml-var city> <input type=hidden name="city" value="<dtml-var city>"></td>
20:   <td><dtml-var zip> <input type=hidden name="zip" value="<dtml-var zip>"></td>
21:   <td><dtml-var phone> <input type=hidden name="phone" value="<dtml-var phone>"></td>
22:   <td>
23:     <input type="submit" name="updateEntryForm_html:method" value="Update">
24:     <input type="submit" name="deleteEntry:method" value="Delete">
25:   </td>
26: </form>
27: </tr>
28: </dtml-in>
29: </table>
30: <dtml-var standard_html_footer>


					  

This DTML method shows a table with the column names of the Addressbook table as headlines (lines 4–12). The rest of the table is created by using the Z SQL method getAll (line 13) that consists of a simple 'SELECT * FROM Addressbook' without any arguments. Going through the result of this Z SQL method, you can create the table rows and enter the different entries of the Addressbook (lines 14–21). In the last table cell of each row, there needs to be an Update button that sends the entries' contents to the updateEntryForm_html (the code will be given shortly) and there is also a Delete button that will be needed later (lines 23 and 24). You may have noticed that there is no real value for the action of the form (line 15). This is a Zope-specific feature. You can have different actions using one single form. In our case, we have two different actions—update and delete. The action is given as the name in the declaration of the respective button (lines 23 and 24). Note that you need to add :method to the name value as follows:

name="updateEntryForm_html:method".

The action value in your form must be "." or Zope will not recognize this special kind of action.

Now you only need the DTML method that calls the Z SQL method. Therefore, create a new DTML method called updateRecord_html with the code in Listing 3.8.

Listing 3.8. updateRecord_html (DTML Method)

 1: <dtml-var standard_html_header>
 2: <dtml-try>
 3: <dtml-call "updateRecord(Name=_['name'],
 4:                 FirstName=_['firstname'],
 5:                 Address=_['address'],
 6:                 City=_['city'],
 7:                 ZIP=_['zip'],
 8:                 Phone=_['phone'],
 9:                 NewName=_['newname'],
10:                 NewFirstName=_['newfirst_name'],
11:                 NewAddress=_['newaddress'],
12:                 NewCity=_['newcity'],
13:                 NewZIP=_['newzip'],
14:                 NewPhone=_['newphone'])">
15: Entry has been successfully updated.
16: <form action="index_html" method=post>
17: <input type="submit" value="OK">
18: </form>
19: <dtml-except>
20: Error while updating. Entry was <b>NOT</b> updated.
21: <form action="." method=post>
22:   <input type=hidden name="name" value="<dtml-var name>">
23:     <input type=hidden name="firstname" value="<dtml-var firstname>">
24:     <input type=hidden name="address" value="<dtml-var address>">
25:     <input type=hidden name="city" value="<dtml-var city>">
26:     <input type=hidden name="zip" value="<dtml-var zip>">
27:     <input type=hidden name="phone" value="<dtml-var phone>">
28:     <input type="submit" name="updateEntryForm_html:method" value="Try Again">
29:     <input type="submit" name="index_html:method" value="Return to Index">
30: </form>
31: </dtml-try>
32: <dtml-var standard_html_footer>


					  

This DTML method works like the DTML method result_html that we used when adding a new entry. It calls the Z SQL method updateRecord and hands over the ten arguments the Z SQL method expects. Should there be an error, the DTML method will inform the user (line 20) and give him or her the choice of trying again (line 28) or returning to index_html (line 29).

Deleting a Record

To delete a record from the Addressbook table, create a new Z SQL method using the following data:

ID: deleteRecord
Arguments: Name FirstName Address City ZIP Phone
Query Template:
DELETE FROM Addressbook
WHERE
Name='<dtml-sqlvar Name type=string>' AND
FirstName='<dtml-sqlvar FirstName type=string>' AND
Address='<dtml-sqlvar Address type=string>' AND
City='<dtml-sqlvar City type=string>' AND
ZIP=<dtml-sqlvar ZIP type=int> AND
Phone='<dtml-sqlvar Phone type=string>'


Again, you need a DTML method that calls this Z SQL method and hands over the necessary information, such as Name, FirstName, and so on. Create a DTML method called deleteEntry using the code in Listing 3.9.

Listing 3.9. deleteEntry (DTML Method)

 1: <dtml-var standard_html_header>
 2: <h2><dtml-var title_or_id> <dtml-var document_title></h2>
 3: <dtml-try>
 4:   <dtml-call "deleteRecord(Name=_['name'],
 5:                 FirstName=_['firstname'],
 6:                 Address=_['address'],
 7:                 City=_['city'],
 8:                 ZIP=_['zip'],
 9:                 Phone=_['phone'])">
10:   Entry was successfully deleted.
11: <dtml-except>
12:   An error occurred. Entry was <b>NOT</b> deleted.
13: </dtml-try>
14: <form action="index_html" method=post>
15:   <input type="submit" value="OK">
16: </form>
17: <dtml-var standard_html_footer>

The DTML method index_html we used in the section before contains a Delete button. This button can be used now when deleting an entry from the Addressbook table. The Delete button in index_html calls the DTML method deleteEntry and hands over the entry's values. The DTML method then calls the Z SQL method deleteRecord and either tells the user that the entry was deleted successfully (line 10) or that an error occurred (line 12). A Submit button (line 15) takes the user back to the DTML method index_html.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint