Inserting Data using Z SQL Methods

Created by . Last modified on 2003/08/05.

How to Insert Data using Z SQL Methods

Inserting data

Inserting data into a relational database from Zope is as easy as any other Z SQL Method. You can do just about any SQL statement allowed by the target database in an SQL Method, and that includes INSERT and UPDATE statements. So to insert data, all you need to to do is write an SQL Method with one (or more) INSERT statements. Well, it can get a bit more interesting than that.

Arguments and Type Checking (SQLVAR)

Arguments are the values passed into SQL Methods, either from the request or in an expression (Python) call. Z SQL Methods have an input widget for specifying arguments. The usage of this widget is defined more fully in the Z SQL Methods guide. What's important here thought is a degree of type safety -- inserting the correct type of value into its column (not inserting a string where an integer is expected, for instance). Zope with Z SQL Methods provides three ways of trying to aid type safety with the database:

1. Zope input types.
   • The ZPublisher component of Zope handles the marshling of input passed in through the web (URL's, Forms) into Python objects. For example, <input type="text" name="age:int" size="3"> gets submitted to the target method as an integer, and an error gets raised at a fairly low level of Zope if, for example, the value of the field is the string 'twelve'. See the Zope documentation for more details.
2. Argument list input types.
   • Z SQL Methods argument list allows a similar type/marshalling mechanism as above. If the argument list contains 'age:int' and a string with the value of '12' is submitted as age into the SQL Method, it will get converted into an integer with the value of 12 in the SQL Method. See the Z SQL Methods documentation for more details.
3. SQLVAR DTML Tag.
   • Z SQL Methods implement some extra DTML tags to aid in writing correct (and safe!) SQL. SQLVAR is the one most likely to be used heavily in INSERT statements. SQLVAR operates similarly to the regular DTML VAR tag in that in inserts values into the SQL Method, but it has some tag attributes targeted at SQL level type safety, and dealing with inserting NULL values if desired. For the most part, this is the most preferred way with type checking and safety with Z SQL Methods. See the Z SQL Methods documentation for more details. Some examples will be given below.

An example!

So how about a simple example eh? Let's say we have a table, customers, with the following schema:

and we need to insert a new customer into it. Planet and Age allow nulls, and are (we assume) optional. We add a new Z SQL Method called sqlInsertCustomer, setup like the following:

INSERT INTO customers (
 customer_id, name, planet, age
) VALUES (
 <dtml-sqlvar customer_id type=nb>,
 <dtml-sqlvar name type=nb>,
 <dtml-sqlvar planet type=nb optional>,
 <dtml-sqlvar age type=int optional>
)

Notice the use of sqlvar. The sqlvar tag always needs a type specified. The allowable types are "string", "nb", "int", and "float". "String" and "nb" are both string types that do two important things:

1. Proper escaping (or SQL Quoting). This means that any single quotes in the value being inserted are properly escaped. This minimizes the liklihood of someone hijacking your SQL by passing in a string like:
   "foo'); DELETE FROM customers;"
which could cause possible nastiness (the inserted string would end at foo). The sqlvar string and nb types would turn this into:
   "foo''); DELETE FROM customers;"
   which is safe. (Two single quotes in SQL is like the string \' in Python).
2. The difference between string and nb is an important feature. NB stands for "non-blank", and it means a non-blank string is to be inserted. Many databases by default don't allow empty strings. If NB is used as the type and a blank string is passed, Z SQL Methods raises an error saying a non-blank string was expected.
   
   Where this is most useful is with the sqlvar attribute optional. With a sqlvar of type 'nb' and 'optional', if a blank string is encountered, the SQL Value NULL is inserted instead. The table below shows the results of passing an empty string to sqlvar with various options.

So why did we not specify age as age:int in the arguments list? Because we want an empty string (or missing value) to mean that age wasn't passed into the method (ie, a field not filled out in a form) and we want a NULL to be inserted in this case instead of an error being raised that a string was being submitted instead of an integer.

Putting this SQL Method to use

So how can we make use of this method now? We will need an HTML form (a DTML Document or Method), and an action for that form (another DTML method that calls the SQL Method and returns a message). Very simply, we can create a form like the following (in a DTML Document)

<dtml-var standard_html_header>

<h2><dtml-var title_or_id></h2>
<form action="dtPerformAddCustomer">
<table>
 <tbody>
  <tr>
   <th align="left">Customer Id</th>
   <td><input type="text" name="customer_id"></td>
  </tr>
  <tr>
   <th align="left">Name</th>
   <td><input type="text" name="name"></td>
  </tr>
  <tr>
   <th align="left"><em>Planet</em></th>
   <td><input type="text" name="planet"></td>
  </tr>
  <tr>
   <th align="left"><em>Age</em></th>
   <td><input type="text" name="age" size="3"></td>
  </tr>
 </tbody>
</table>
<input type="submit" value="Add Customer">
</form>
</table>
</form>

<dtml-var standard_html_footer>

This is a form whose action is dtPerformAddCustomer. dtPerformAddCustomer will be a DTML Method that will call the Z SQL Method created above (sqlInsertCustomer).

<dtml-var standard_html_header>

<dtml-call sqlInsertCustomer>

<h2>Customer <dtml-var name="name"> was added.</h2>

<dtml-var standard_html_footer>

This is a very simple action. Naturally more could be done here (like checking to make sure customer_id and name were submitted). Or even checking for customer_id and name and if they're there, calling sqlInsertCustomer and returning to the AddCustomer form with a message. Even better would be to have a list of customers (from a select query) on the AddCustomer page so that it's easy to see that a new customer has been added.

SQL Methods, like most methods, have an arguments list. When a web form is submitted, the values of the fields are marshalled into an object called the REQUEST (See the Zope documentation for more information on the REQUEST). The target of the form (usually a DTML Method) uses the REQUEST to present its information and call/render other objects.

When a SQL Method is called in the above fashion (<dtml-call sqlInsertCustomer>, the same as <dtml-call name="sqlInsertCustomer">), the REQUEST is used to pass in variables defined in the arguments list. Consult the ZSQL Methods guides for more information.