Previous Chapter | Up | Contents

Inserting optional tests with sqlgroup


It is sometimes useful to make inputs to an SQL statement optional. Doing so can be difficult, because not only must the test be inserted conditionally, but SQL boolean operators may or may not be needed depending on whether other, possibly optional, comparisons have been done. The sqlgroup tag automates the conditional insertion of boolean operators.

The sqlgroup tag is a block tag. It can have any number of and and or continuation tags. The attributes of the sqlgroup tag are shown in table See Attributes of the sqlgroup tag.

Attributes of the sqlgroup tag

name

description

required

The required attribute is used to flag groups that must include at least one test. This is useful when you want to make sure that a query is qualified, but want to be very flexible about how it is qualified.

where

The where flag is used to cause an sql "where" to be included if a group contains any text. This attribute is useful for queries that may be either qualified or unqualified.

The sqlgroup tag checks to see if text to be inserted contains other than whitespace characters. If it does, then it is inserted with the appropriate boolean operator, as indicated by use of an 'and' or 'or' tag, otherwise, no text is inserted.

Suppose we want to find people with a given first or nick name, city or minimum and maximum age. Suppose we want all inputs to be optional, but want to require some input. We can use DTML source like the following:

select * from people

<!--#sqlgroup required where-->

<!--#sqlgroup-->

<!--#sqltest name column=nick_name type=nb multiple optional-->

<!--#or-->

<!--#sqltest name column=first_name type=nb multiple optional-->

<!--#/sqlgroup-->

<!--#and-->

<!--#sqltest home_town type=nb optional-->

<!--#and-->

<!--#if minimum_age-->

age >= <!--#sqlvar minimum_age type=int-->

<!--#/if-->

<!--#and-->

<!--#if maximum_age-->

age <= <!--#sqlvar maximum_age type=int-->

<!--#/if-->

<!--#/sqlgroup-->

If we evaluated this template with values set for home town and name, we would get an SQL query like the following:

select * from people

where

((nick_name='Jim'

or first_name='Jim'

)

and home_town='Cleveland'

)

This example illustrates how groups can be nested to control boolean evaluation order. It also illustrates that the grouping facility can also be used with other DTML tags like if tags.

A

Acquiring Data and Behavior 18

arguments 21, 24, 25

C

Caching results 26

Connection Browse 19

connection id 25

Connection Properties 19

Connection Status 19

Create a database method 9

Creating Database Connections 19

Creating Search Interfaces 12

Creating SQL database methods 21

D

Database Connections 19

Database Method Classes 26

Defining result classes 18

Direct Traversal 26

Document Template Markup Language (DTML) 10

Document Template Markup Language Tags 27

E

Establish a database connection 9

G

Getting Started 9

I

Inserting equality comparisons with sqltest 28

Inserting optional tests with sqlgroup 28

Inserting values with the sqlvar tag 27

Introduction 7

M

Maximum Number of Rows Retrieved 26

O

Object Access 18

Object-Relational Data Integration 17

P

Purpose 8

Q

query template 21, 25

S

SQL Database Methods 21

string 21

T

Testing and debugging SQL database-methods 23

The relational data object model 17

title 25

V

var 24

Z

Z Gadfly Database Connection 9

Z SQL Database Methods 12


1. A number of Zope-based products provide searchable objects. Other searchable objects that are available for Zope include Tabula Collections, Confera Topics, and Zope Network Clients.

2. Parameters can be passed explicitly using an expr attribute in the in tag. Parameters are passed using Python "keyword" parameter syntax. For example, to call the lookup_product method with a part_number of `2', the following in tag would be used:

<!--#in expr="lookup_product(part_number='2')"
size=50 start=query_start-->

3. See See Batch Processing in the Document Template Markup Language User's Guide.

4. This includes the ISO standard format: yyyy-mm-dd hh:mm:ss

5. An input 1-2-1997 is interpreted as January 2.

6. Python is a very high-level object-oriented programming language. For more information on Python, visit http://www.python.org .

7. For security reasons, the Python file cannot be uploaded via the Web. You must have access to the file system of the server that is running Zope.

Previous Chapter | Up | Contents