Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Including SQL Content

The SQL 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 results.

To use the SQL method, you may either explicitly use the <include> tag with a method attribute of SQL, or implicitly define the <include> tag as using the SQL 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.

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.

Note: While there exists a single persistent database connection for each Apache httpd 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

Attribute

Description

dbname

The database to connect with (defaults to the same name as the connecting user)

host

The hostname to connect to

user

The username to connect with (defaults to the user running Apache)

password

The password to use, if authentication is required

port

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

Example 13-27. Connecting to a non-default database

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

Warning

For LXP 0.8, if you wish to nest a SQL include within another SQL include, the nested include must 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.

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.

Example 13-28. Including SQL content

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

Example 13-29. Using SQL object variable values

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

Example 13-30. Selecting SQL results into an LXP object

<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>
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire