Setting the database source

When using the SQL 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.

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.

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 important.

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.

<lxp>
  <include sql="SELECT * FROM users ORDER BY username ASC"
           src="dbname=example host=db_server user=john">
    User: <field /><br />
  </include>
</lxp>

Accessing column values

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. column , where column 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 branching logic.

<lxp>
  <include sql="SELECT datname, datdba AS user_id FROM pg_database">
    <if this.user_id="$userid">
      <strong><field /></strong><br />
      <setvar owned_databases="$owned_databases @this.datname" />
    </if>
    <else>
      <field /><br />
    </else>
  </include>
</lxp>

Accessing SQL meta-data

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:

• sql.numrows

• sql.numcols

• sql.numfields (alias to sql.numcols)

• sql.row

• sql.offset

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.

<lxp>
  <include sql="SELECT * FROM pg_user ORDER BY usename LIMIT 5">
    User #<putvar name="sql.row" />: <putvar name="this.usename" /><br />
  </include>
  <br />
  Selected <putvar name="sql.numrows" /> rows.
</lxp>

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 />
  
  <br />
  Selected 5 rows.

Setting SQL object variables

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 object . column for each column in the row, where object is the name specified by the setvars attribute, and column 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[0], object.column[1], etc.).

Example 13-30 executes a query on the pg_user table, to retrieve three columns about a particular user.

<lxp>
  <include sql="SELECT usename, usesuper, usecreatedb
                       FROM pg_user
                       WHERE usesysid = $userid"
           setvars="userinfo"></include>
  
  <if sql.numrows="1">
    User name: <putvar name="userinfo.usename"><br />
    <if userinfo.usecreatedb='t'>
      <strong>This user can create databases.</strong><br />
    </if>
    <if userinfo.usesuper='t'>
      <strong>This user is a superuser.</strong><br />
    </if>
  </if>
  <else>
    Error: No user was found.
  </else>
</lxp>