Query templates are used to generate SQL statements based on Web request input data and other Zope data. Consider the following example of a query template:
WHERE part_no = <!--#sqlvar part_number type=string-->
The intention is to dynamically create a query using data that either comes in with the HTTP REQUEST or is otherwise available to the SQL Database Method object (e.g., acquired data, folder property data, etc.). In this example, an sqlvar tag was used to insert a value into an sql statement.
Consider an HTML form that contains the following INPUT tag:
<INPUT NAME="part_number" TYPE="text" SIZE="5">
If the user entered the value 123G into the part_number input box then the query template shown above would be evaluated to:
SELECT * FROM jim WHERE part_no = '123G'
An ordinary var tag could be used as well, however, the sql_quote var tag attribute should be used to make sure that sql quote characters are handled correctly. The var tag would be useful if special var tag features like the lower attribute were needed:
WHERE part_no = <!--#var part_number sql_quote lower-->.
Because query templates are themselves document templates you have access to all of the DTML constructs. This includes all of the looping, conditional and iterative commands. Consider the following query template:
SELECT * FROM sales where part_no in (
<!--#in list_promotional_items-->
<!--#unless sequence-start-->,<!--#/unless-->
'<!--#var promo_item_part_number fmt=sql-quote-->'
<!--#in list_promotional_items-->
This SQL Database Method would executes another SQL Database Method called list_promotional_items and, for every row in the result, insert a case in the SQL in list. In this example, the variable promo_item_part_number is in the results of the list_promotional_items query.
The query templates can get their variables from either the HTTP request or from any variables available in the Folder containing the SQL Database Method object. It is often useful to develop SQL Database Methods with this lookup order/variable resolution in mind. The following rules control the order in which variable lookup is performed, depending on whether or not the variable name in question is or is not in the arguments property of the SQL Database Method .
Variables in the folder containing the SQL Database Method . |
||
It is often necessary to execute more than one SQL statement in a single HTTP REQUEST. SQL Database Methods define a variable, sql_delimiter , that can be used to divide individual SQL statements. Consider the following two SQL statements which debit a user's checking account and credit a loan account by the same amount:
UPDATE checking_account_balances WHERE
<!--#sqltest customer_number column=customer_no type=string-->
SET balance = balance - <!--#sqlvar loan_payment type=float-->
UPDATE loan_account_balances WHERE
<!--#var customer_number column=customer_no type=string-->
SET balance = balance + <!--#sqlvar loan_payment type=float-->
Note that no more than one SQL select statement may be used in a single SQL Database Method .
Previous Chapter | Next Chapter | Up | Next Section | Contents