method in LXP offers a great amount of power through direct connectivity to PostgreSQL.
It allows for the embedding of 100% dynamic, database results directly within a web page without the need to call out to a
programming language, create explicit connection or statement programming objects, or even to parse and format the
To use the
method, you may either explicitly use the
<include> tag with a method attribute of
SQL, or implicitly define the <include> tag as
method by setting the value of the sql attribute to
the SQL statement you wish to execute. In the following example, the SQL method is implied as a result of specifying a
value for the sql attribute:
<include sql="SELECT * FROM pg_database">
Like each of the parsing methods, the <include> tag loops between its opening
<include> and closing </include> tags for
each row returned from a successfully executed SQL query.
When using the
inclusion method, the src attribute is
used within the <include> tag to define the database source to connect to. If
this attribute is omitted, LXP will attempt to connect to its persistent database connection, if one exists.
Note: While there exists a single persistent database connection for each Apache
process, the LXP module actually maintains the connection—not Apache.
The format of this connection string will be familiar to anyone who has connected to PostgreSQL through C or PHP.
It is a single, character string, within which there are several sub-attributes describing the data source. Available
sub-attributes are shown in Table 13-2.
Table 13-2. Database Connection Attributes
The database to connect with (defaults to the same name as the connecting user)
The hostname to connect to
The username to connect with (defaults to the user running Apache)
The password to use, if authentication is required
The port to connect to (Defaults to 5432)
Within the src attribute's value, attribute pairs are separated by whitespace, and an equal
sign separates each attribute from its value. The order in which the database attributes appear is not
Example 13-27 shows the execution of a SQL query, which uses a connection to a
database called example, on a host named db_server, with the username john.
Example 13-27. Connecting to a non-default database
<include sql="SELECT * FROM users ORDER BY username ASC"
src="dbname=example host=db_server user=john">
User: <field /><br />
For LXP 0.8, if you wish to nest a SQL include within another SQL include,
the nested include
have an explicit src attribute defined,
even if it is connecting to the default database connection. This restriction is corrected with LXP 0.8.1.
Column values can be accessed in one of two ways while iterating through a SQL inclusion region; either through
the general <field> tag, or through the this
object, which is populated with a value for each column upon each row iteration.
Like the XML inclusion, a name attribute can be applied to a
<field> tag in order to specify which column is to be displayed. Otherwise, the
column values are displayed in the order they were targeted by the query, from left to right, with each successive use
of the <field> tag.
Alternatively, the values of each column can be accessed by a variable named this.
is the name of the column to be identified. For example, the following two tags
would output the same value within an included SQL region:
<field name="id" />
<putvar name="this.id" />
The main reason for the existence of the this object is so that branching logic,
and variable substitution, can be performed using the values of the returned SQL result set. Example 13-28 executes a SQL query, and formats its output conditionally through the use of
Example 13-28. Including SQL content
<include sql="SELECT datname, datdba AS user_id FROM pg_database">
<strong><field /></strong><br />
<setvar owned_databases="$owned_databases @this.datname" />
<field /><br />
When executing a SQL query, some special variable values containing data about the current result set are assigned
to an LXP object called sql. These are:
The sql.numrows variable value contains the number of rows retrieved by the
query. The sql.numcols (and its sql.numfields alias)
variable value contains the number of columns in each row. When looping between
<include> and </include>, the
sql.row variable value contains the numeric index of the current row, counting from
1, while the sql.offset variable value contains the
numeric index of the current row counting from 0.
Example 13-29 uses the the sql.row variable to
display the current row index within the looped <include> region. In addition,
the sql.numrows variable is used after the query results are displayed to show how many
rows were retrieved.
Example 13-29. Using SQL object variable values
<include sql="SELECT * FROM pg_user ORDER BY usename LIMIT 5">
User #<putvar name="sql.row" />: <putvar name="this.usename" /><br />
Selected <putvar name="sql.numrows" /> rows.
The output of Example 13-29 would look like this:
User #1: allen<br />
User #2: barbara<br />
User #3: ben<br />
User #4: corwin<br />
User #5: david<br />
Selected 5 rows.
If you prefer to execute a SQL query only as a means to have access to the result set returned (bypassing the
automatic looping iteration of the <include> tag), you may supply the
setvars attribute with the name of an LXP object to be populated with the query
results, and immediately close the region with a closing </include> tag.
For result sets with a single row returned, this approach sets a variable named
column in the row, where
is the name specified by the setvars
is the name of a column returned by the query. For result sets with
more than a single row, square-brackets containing an offset describing the row number are appended to the column name
(e.g., object.column, object.column, etc.).
Example 13-30 executes a query on the
pg_user table, to retrieve three columns about a particular user.
Example 13-30. Selecting SQL results into an LXP object
<include sql="SELECT usename, usesuper, usecreatedb
WHERE usesysid = $userid"
User name: <putvar name="userinfo.usename"><br />
<strong>This user can create databases.</strong><br />
<strong>This user is a superuser.</strong><br />
Error: No user was found.