AxKit::XSP::ESQL - An Extended SQL taglib for AxKit eXtensible Server Pages
Add the esql: namespace to your XSP <xsp:page> tag:
<xsp:page
<xsp:page language="Perl" xmlns:xsp="http://apache.org/xsp/core/v1" xmlns:esql="http://apache.org/xsp/SQL/v2" >
And add this taglib to AxKit (via httpd.conf or .htaccess):
AxAddXSPTaglib AxKit::XSP::ESQL
This tag library provides extensive support for executing SQL statements from within XSP. This tag library is the same as the Cocoon ESQL taglib.
Note that below we use the esql: prefix as a convention, however you can use whatever prefix you like provided it is mapped to the appropriate namespace.
<esql:connection
parent: none
This is the required 'wrapper' element that declares your connection.
<esql:driver
parent: <esql:connection>
The contents of this element define the DBI driver to be used. For example, Pg, Sybase, Oracle.
You can also add an optional attribute: transactions='no' to the driver element, to indicate that this driver does not support transactions (or just that you don't want to use transactions).
<esql:dburl
The name of this tag is a hang-over from the Cocoon (Java) version. In the AxKit version this is simply anything that goes after the driver in the connection string. So for PostgreSQL you might have in here dbname=axkit, to connect to the "axkit" database. The full connect string is constructed as follows:
dbname=axkit
"dbi:$driver" . ($dburl ? ":$dburl" : "")
See your DBD driver documentation for more details on what is valid for the connection string.
<esql:username
The username to connect to the database with.
<esql:password
The password to use to connect to the database.
<esql:execute-query
This tag is a 'wrapper' tag around queries. You may have as many queries as you like within a single <esql:connection> tag.
<esql:skip-rows
parent: <esql:execute-query>
The contents of this tag (which may be an <xsp:expr>) define a number of rows to skip forward in the result set.
<esql:max-rows
The maximum number of rows to return.
<esql:query
The contents of this tag define the query to be executed.
<esql:parameter
parent: <esql:query>
This tag can be put in your SQL query everywhere you might put a ? in your SQL in DBI. ESQL is intelligent enough to create a cached statement when you do this, and only execute your code when necessary. You put an expression (or another taglib) within the parameter tag (see the example below).
<esql:results
The contents of this tag are "executed" whenever the query returns some results.
<esql:row-results
parent: <esql:results>
The contents of this tag are "executed" for each row of the results
<esql:get-columns
parent: <esql:row-results>
This tag gets all of the columns in the current row, and outputs them as <column_name>value</column_name>. If you specify an attribute tag-case="upper", all columns are upper case. Alternatively, "lower" gives you all tags in lower case. An ancestor attribute is also allowed, see "Nested Results" below for more details.
<column_name
value</column_name
tag-case="upper"
These are:
get-column get-string get-boolean get-double get-float get-int get-long get-short
(and more below)
Each of these takes either an attribute column="name", or a child tag, <esql:column> which gives the column name. Alternatively either the attribute or child element can be an integer (starting at 1) specifying the column number.
<esql:column
Also allowed is an ancestor attribute, which is an integer (default 0), which indicates how far up the nested results you go. See Nested Results below.
<esql:get-date
<esql:get-time
<esql:get-timestamp
These tags are the same as get-* above, except they also take a format="..." attribute, which contains a strftime formatting string.
format="..."
<esql:get-xml
Again the same as get-* above. This tag assumes the contents of the column are valid XML, and appends that XML into the result tree.
<esql:get-row-position
Gets the current row number. Optional ancestor attribute.
ancestor
<esql:get-column-name
Gets the column name indicated by the numbered column in the column="..." attribute, or the child <esql:column> element. The attribute/child can actually be a string (name), but then what is the point of that?
column="..."
<esql:get-column-label
Gets the label of the column. This is a hang-over from the Cocoon java implementation where sadly nobody seems to know what label is compared with name. In this case, get-column-name is always lower case, whereas get-column-label is returned in the case that the DBD driver returns it as.
<esql:get-column-type-name
Returns the TYPE_NAME of the column indicated as other get-* elements. See the DBI docs for more details.
<esql:no-results
The contents of this element are executed when the SQL returned no rows.
<esql:update-results
The contents of this element are executed when the SQL was an update statement. The number of rows updated are in the $rv variable.
$rv
With the ESQL taglib it is quite possible to do nested results. This is a way to emulate outer joins, or just better organise things. See below for an example of this.
When using nested results, you can use the ancestor attribute on any of the get-* elements to get results from higher up the ancestry of results.
Unlike the original Cocoon version of this taglib, we let you handle errors however you choose to, using the exception taglib. If an error occurs, ESQL will throw an exception. If you don't capture this exception it will propogate up to the core of AxKit, and either give a 500 internal server error, or execute the AxErrorStylesheet if one is defined. See AxKit.
<esql:connection> <esql:driver>Pg</esql:driver> <esql:dburl>dbname=axkit</esql:dburl> <esql:username>postgres</esql:username> <esql:password></esql:password> <esql:execute-query> <esql:query> select id,name from department_table where foo = <esql:parameter><xsp:expr>4 + 5</xsp:expr></esql:parameter> </esql:query> <esql:results> <header>header info</header> <esql:row-results> <department> <id><esql:get-int column="id"/></id> <name><esql:get-string column="name"/></name> <esql:connection> <esql:driver>org.postgresql.Driver</esql:driver> <esql:dburl>jdbc:postgresql://localhost/test</esql:dburl> <esql:username>test</esql:username> <esql:password>test</esql:password> <esql:execute-query> <esql:query>select name from user_table where department_id = <esql:parameter type="int"><esql:get-int ancestor="1" column="id"/></esql:parameter></esql:query> <esql:results> <esql:row-results> <user><esql:get-string column="name"/></user> </esql:row-results> </esql:results> <esql:no-results> <user>No employees</user> </esql:no-results> </esql:execute-query> </esql:connection> </department> </esql:row-results> <footer>footer info</footer> </esql:results> <esql:no-results> <department>No departments</department> </esql:no-results> </esql:execute-query> </esql:connection>
Matt Sergeant, matt@axkit.com. Original Cocoon taglib by Donald Ball
Copyright 2001 AxKit.com Ltd. You may use this module under the same terms as AxKit itself.
AxKit, DBI, Apache::AxKit::Language::XSP, the AxKit.org pages at http://axkit.org/
To install AxKit::XSP::ESQL, copy and paste the appropriate command in to your terminal.
cpanm
cpanm AxKit::XSP::ESQL
CPAN shell
perl -MCPAN -e shell install AxKit::XSP::ESQL
For more information on module installation, please visit the detailed CPAN module installation guide.