<html>
<head>
<!-- This file has been generated by unroff 1.0, 03/11/09 12:56:22. -->
<!-- Do not edit! -->
<STYLE TYPE="text/css">
<!--
A:link{text-decoration:none}
A:visited{text-decoration:none}
A:active{text-decoration:none}
OL,UL,P,BODY,TD,TR,TH,FORM { font-family: arial,helvetica,sans-serif;; font-size:small; color: #333333; }
H1 { font-size: x-large; font-family: arial,helvetica,sans-serif; }
H2 { font-size: large; font-family: arial,helvetica,sans-serif; }
H3 { font-size: medium; font-family: arial,helvetica,sans-serif; }
H4 { font-size: small; font-family: arial,helvetica,sans-serif; }
-->
</STYLE>
<title>ploticus: sql database interface</title>
<body bgcolor=D0D0EE vlink=0000FF>
<br>
<br>
<center>
<table cellpadding=2 bgcolor=FFFFFF width=550><tr>
<td>
<table cellpadding=2 width=550><tr>
<td><br><h2>sql database interface</h2></td>
<td align=right>
<small>
<a href="../doc/welcome.html"><img src="../doc/ploticus.gif" border=0></a><br>
Version 2.41 Mar2009
<td></tr></table>
</td></tr>
<td>
<br>
<br>
<title>Manual page for sql_database_interface(PL)</title>
</head>
<body>
<h2>SQL database interface</h2>
The <b>#sql</b> directive
provides a convenient interface for building and invoking SQL commands, and
capturing the results.<tt> </tt>
By default <b>#sql</b> points to
<a href="../../sqlman/html/shsql_home.html">
SHSQL
</a>
(which is bundled with QUISP).<tt> </tt>
<a href="otherdb.html">
Interfaces to other databases are also possible.<tt> </tt>
</a>
<p>
A number of
<a href="#examples">
#sql examples
</a>
are provided below; see also the
<a href="http://quisp.sourceforge.net/cgi-bin/quisp?rtn=index">
live demo on sourceforge.<tt> </tt>
</a>
<br><br><br>
<h2>#sql - #endsql</h2>
Issue an SQL command.<tt> </tt>
The SQL command can be one or more lines in length.<tt> </tt>
Script variables and directives such as <tt>#if</tt> may be embedded in order to conditionally build an SQL command
(but #shell commands cannot be interleaved).<tt> </tt>
Command status is available via
<a href="#functions">
$sqlerror()
</a>
(a nonzero value indicates an error).<tt> </tt>
For <tt>SELECT</tt> commands, the number of retrieved rows is available via
<a href="#functions">
$sqlrowcount()
</a>
and individual result rows can be fetched using
<a href="#functions">
$sqlrow().<tt> </tt>
</a>
<p>
Usage:
<pre>
#sql [<i>ichannel</i>] [<i>mode</i>]
<i>sql command</i>
...
#endsql
</pre>
Where convenient, a single-line form may be used:
<br>
<tt>#sql [</tt><i>ichannel</i>] [<i>mode</i><tt>] </tt><i>sql command</i>
<p>
<b>ichannel</b> is <tt>1</tt>, <tt>2</tt>, <tt>3</tt> or <tt>4</tt>.
When <i>ichannel</i> is not specified channel 1 is assumed.<tt> </tt>
The channel number may be preceded by a pound sign <tt>#</tt> if desired.<tt> </tt>
Multiple channels are useful in nested contexts, eg. a retrieval on channel 1 is in progress, and
a retrieval is being done (on channel 2) for every channel 1 result row.<tt> </tt>
The database access functions below also accept a channel argument.<tt> </tt>
See example 8 below which uses channels.<tt> </tt>
<p>
<b>mode</b> determines the disposition of retrieval results. If no <i>mode</i> is specified,
the default is #processrows. Available modes include:
<dl>
<dt><dd><p>
<b>#processrows</b> - executes any type of SQL command. For SQL SELECT commands, the quisp application must then fetch result
rows using subsequent calls to the $sqlrow() function (see below). For INSERT, UPDATE, and DELETE commands nothing else
needs to be done. This mode is the default if no mode is specified.<tt> </tt>
<dt><dd><p>
<b>#load</b> - Executes an SQL SELECT command, fetches the first result row,
and loads each result field/column into a variable having the same name as the field/column.<tt> </tt>
A convenient way to retrieve several items in cases where a single result row is expected.<tt> </tt>
If the SELECT command produces no result rows, all result variables will all be set to ""
(for this reason using SELECT * may not be a good idea in this mode).<tt> </tt>
<dt><dd><p>
<b>#dump</b> - Executes an SQL SELECT command and writes the results directly to standard output.
<dt><dd><p>
<b>#dumptab</b> - Executes an SQL SELECT command. Result fields/columns are written to standard output delimited by tabs.<tt> </tt>
<dt><dd><p>
<b>#dumphtml</b> - Executes an SQL SELECT command. Result fields/columns are written to standard output as HTML table rows.<tt> </tt>
<dt><dd><p>
<b>#dumpsilent</b> - Executes an SQL SELECT command, but doesn't display any result lines; however the row count will be
available via <tt>$sqlrowcount()</tt>.<tt> </tt>
<br><br>
</dl>
<p>
<b>Empty retrievals / No more rows - behavior</b>
<p>
In #load mode, SQL SELECTs that don't find any rows will set their result variables to "".<tt> </tt>
While this makes testing for empty retrievals easy, it can sometimes cause unexpected side effects in the quisp page script,
when the retrieved data field names are the same as pre-existing quisp variable names. For instance:
<pre>
#set id = 954
#sql #load select * from people where id = @id
</pre>
If this SQL SELECT doesn't return any rows, a quisp variable corresponding to each result column name (including <tt>id</tt>)
will be set to "", which could cause problems if @id is expected to do anything else later in the code.<tt> </tt>
<p>
In #processrows mode, the $sqlrow() function will set all result quisp variables
to "" any time that no more rows can be fetched. This includes the case where the SQL SELECT didn't return any rows.<tt> </tt>
As above, this can cause unexpected side effects in the quisp page script.<tt> </tt>
<a name=sqlblankrow></a>
<br><br><br>
<h2>#sqlblankrow</h2>
<p>
<tt>#sqlblankrow </tt><i>dbtable</i>
<p>
For every field defined in database table <i>dbtable</i>, initialize a variable to <tt>""</tt>.<tt> </tt>
This operator is useful when setting up a form for new content entry, where the form
fields correspond to fields in a database table.<tt> </tt>
<a name=sqlcgivars></a>
<br><br><br>
<h2>#sqlcgivars</h2>
Get an ordinary CGI user variable for every field defined
for database table <i>dbtable</i>.<tt> </tt>
Typical use is on a form
<a href="forms.html">
formtarget page;
</a>
using <tt>#sqlcgivars</tt> lets you avoid having to enumerate all fields in a <tt>#cgivar</tt> statement.<tt> </tt>
The CGI user variable names must exactly match the database table field names.<tt> </tt>
<p>
Usage: <tt>#sqlcgivars </tt><i>dbtable</i> <tt>[overwrite]</tt>
<p>
Example: <tt>#sqlcgivars people</tt>
<p>
Example: <tt>#sqlcgivars people overwrite</tt>
<p>
Unless the <tt>overwrite</tt> option is given,
variables that are already in use (have already been given a value) will NOT be set
by <tt>#sqlcgivars</tt>.<tt> </tt>
<p>
If any of the variables need to be captured using special conversions (eg.<tt> </tt>
<tt>#cgilistvar</tt>, <tt>#cgitextvar</tt>, or <tt>#cgimultivar</tt>), these operators must be invoked
<b>before</b> <tt>#sqlcgivars</tt>, and the <tt>overwrite</tt> option must not be used.<tt> </tt>
<p>
Caution: any fields not present in the submitted form must be passed to the formtarget page using
<a href="forms.html">
#pass;
</a>
otherwise any existing contents of those fields will be lost.<tt> </tt>
<a name=sqlbuild></a>
<br><br><br>
<h2>#sqlbuild</h2>
Automatically builds an SQL <tt>INSERT</tt> command or most of an <tt>UPDATE</tt> command.<tt> </tt>
Traditionally in systems where SQL is submitted by middleware, the SQL command is coded manually, like in
the INSERT command in example 5 below, and the developer must manually enumerate all fields, as well as code
for quoting, conversion of blank fields to NULL, and escape out any embedded quotes.<tt> </tt>
<p>
If <tt>#sqlbuild</tt> is used, much of this work is done for you; see examples 6 and 7 below.<tt> </tt>
For UPDATE, the developer must supply a trailing where clause as shown.<tt> </tt>
<tt>#sqlbuild</tt> assumes that a script variable exists for each database field,
and has the same name as the database field.
<tt>#sqlbuild</tt> is typically used in a
<a href="cgiforms.html">
formtarget page
</a>
where a data record has been submitted from a form, and where the fields have
been loaded using
<a href="cgivar.html">
#sqlcgivars.<tt> </tt>
</a>
<p>
Usage: <tt>#sqlbuild </tt><i>umode dbtable defaultqmode</i> [omit=<i>fields</i>] [noquote=<i>fields</i>] [quote=<i>fields</i>]
<dl>
<dt><dd><p>
<i>umode</i> is one of <tt>insert</tt>, <tt>update</tt>, or <tt>any</tt> (<tt>new</tt> is same as <tt>insert</tt>).<tt> </tt>
<dt><dd><p>
<i>dbtable</i> is the name of the database table being updated.<tt> </tt>
<dt><dd><p>
<i>defaultqmode</i> is the default quoting mode, either <tt>quote</tt> or <tt>noquote</tt>. Unless listed in the exceptions,
all fields are treated this way.<tt> </tt>
<dt><dd><p>
The <tt>omit=</tt> parameter (optional) names fields that shouldn't be included at all.<tt> </tt>
<dt><dd><p>
The <tt>noquote=</tt> parameter (optional) names fields that shouldn't be quoted, for when <i>defaultqmode</i> is <tt>quote</tt>.<tt> </tt>
<dt><dd><p>
The <tt>quote=</tt> parameter (optional) names fields that should be quoted, when <i>defaultqmode</i> is <tt>noquote</tt>.<tt> </tt>
</dl>
<p>
<i>fields</i> should be a comma-delimited list of fieldnames with no embedded whitespace.<tt> </tt>
<p>
Here are the <tt>#sqlbuild</tt> conversion rules.
<p>
Any embedded double quote (") characters are converted to single quotes (avoids the need for embedded escape characters and
works well with HTML form tags).
<p>
Empty fields are converted to <tt>null</tt>.<tt> </tt>
<p>
An entry for every <i>dbtable</i> field is generated (except those mentioned if <tt>omit=</tt> is used) in logical table order.<tt> </tt>
<p>
If <i>umode</i> is set to <tt>any</tt> then an "update ... orinsert" command is generated (new 4/14/08).<tt> </tt>
<p>
Hint: during development don't put the <tt>#sqlbuild</tt> within a <tt>#sql</tt> / <tt>#endsql</tt> right away;
first put it within a <tt><pre></tt> / <tt></pre></tt> and just see the SQL that it builds.<tt> </tt>
<br><br><br>
<a name=functions></a>
<h2>Functions</h2>
These functions may be used in conjunction with the <tt>#sql</tt> command.<tt> </tt>
Many of the functions take an argument <tt>dbc</tt> which specifies the database
connection path, an integer between 1 and 4 (if omitted, <tt>1</tt> is assumed).<tt> </tt>
<br><br>
<p>
<b>$sqlrow( dbc )</b>
<dl>
<dt><dd><p>
Get the next SQL SELECT result row.<tt> </tt>
Result fields are loaded into variables that use the same names as the SQL result columns.<tt> </tt>
NULL fields will be converted to "".<tt> </tt>
Returns 0 on success, 1 if no more result rows, or an error code > 1.<tt> </tt>
When attempting to fetch a row and there are no more result rows available,
field name result variables will all be set to "" (which can cause unexpected script side effects, see No more rows, above).<tt> </tt>
Example 3 below shows the use of this function.<tt> </tt>
<br><br><br>
</dl>
<p>
<b>$sqlrowcount( dbc )</b>
<dl>
<dt><dd><p>
Return the number of result rows produced by the most recently invoked SQL command.<tt> </tt>
<br><br><br>
</dl>
<p>
<b>$sqlpushrow( dbc )</b>
<dl>
<dt><dd><p>
Causes the next call to <tt>$sqlrow()</tt> to get the same row again.<tt> </tt>
<br><br><br>
</dl>
<p>
<b>$sqlerror( dbc )</b>
<dl>
<dt><dd><p>
Returns the completion status of the most recently invoked SQL command.<tt> </tt>
Zero indicates success; nonzero indicates an error.<tt> </tt>
<br>
Example: <tt>#if $sqlerror() != 0 ...</tt>
<br><br><br>
</dl>
<p>
<b>$sqltabdef( dbc, table )</b>
<dl>
<dt><dd><p>
Returns a comma-delimited list of the field names in <i>table</i>.
This requires its own free db channel (if used on same channel as a retrieval in progress
it will intefere).<tt> </tt>
Example:
<pre>
#set names = $sqltabdef( people )
#set n_names = $count( "*", @names )
</pre>
<br><br><br>
</dl>
<p>
<b>$sqlprefix( dbc, prefix )</b>
<dl>
<dt><dd><p>
Set a prefix to be prepended to all result field names retrieved
by the next SELECT command. This may be used to prevent result field names from
colliding with existing script variable names.<tt> </tt>
Must be used after the
<a href="sql.html">
#sql
</a>
statement and before <tt>$sqlrow()</tt>.<tt> </tt>
The prefix remains in effect for the current query only.<tt> </tt>
<br>
Example: <tt>#call $sqlprefix( "s" )</tt>
<br><br><br>
</dl>
<p>
<b>$sqlstripprefix( dbc, prefix )</b>
<dl>
<dt><dd><p>
Indicates that <i>prefix</i> should be removed from
the beginning of any result field name where it appears, for example to
remove table name prefix from join results.<tt> </tt>
Must be used after the
<a href="sql.html">
#sql
</a>
statement, and before <tt>$sqlrow()</tt>.<tt> </tt>
Remains in effect for the current query only.<tt> </tt>
<br>
Example: <tt>#call $sqlstripprefix( "people." )</tt>
<br><br><br>
</dl>
<p>
<b>$sqlwritable()</b>
<dl>
<dt><dd><p>
Returns 0 if the current process can write to the database.<tt> </tt>
Returns 1 if writes are prohibited because of config file readonly attribute.<tt> </tt>
Returns 2 if writes cannot be done because of unix file or directory permissions issues.<tt> </tt>
<br><br><br>
</dl>
<p>
<b>#mode nullrep</b>
<dl>
<dt><dd><p>
You can control how database <tt>null</tt> fields are represented within scripts.<tt> </tt>
By default they are represented as zero-length strings.
Use <tt>nbsp</tt> to convert NULLs to the HTML non-breaking space character <tt>&nbsp;</tt>
which is useful for avoiding dead table cells.<tt> </tt>
<pre>
</pre>
Note that #mode is not a function but rather a script operator, and should be invoked
before result rows are fetched.<tt> </tt>
<br><br><br>
<a name=examples></a>
</dl>
<h2>Examples</h2>
In <tt>./qexamples</tt> are a number of examples that do various things,
see the README file there.<tt> </tt>
<p>
<b>Example 1. Invoke a simple SQL command and display the results:</b>
<pre>
<pre>
#sql #dump select * from classlist
</pre>
#if $sqlrowcount() != 0
<h3>Nothing found</h3>
#endif
</pre>
<p>
<b>Example 2. Similar to above but display results as HTML table rows:</b>
<pre>
#set SEARCH = "gib"
<table cellpadding=2>
#sql #dumphtml select * from classlist where lastname like "@SEARCH*"
</table>
#if $sqlrowcount() != 0
<h3>Nothing found</h3>
#endif
</pre>
<p>
<b>Example 3. Process result rows one at a time:</b>
<pre>
#set MINCORR = 0.7
#sql
select * from correlations
where pearson > @MINCORR
#endshell
<table cellpadding=2>
#while $sqlrow() == 0
<tr><td>@var1</td><td>@var2</td><td>@pearson</td><td>N = @n</td></tr>
#endloop
</table>
#if $sqlrowcount() < 1
<h3>No correlations computed</h3>
#endif
</pre>
<p>
<b>Example 4. Use an SQL command to load some variables:</b>
<pre>
#set ID = 908
#sql #load select lastname, firstname, email from people where id = @ID
Name: @lastname, @firstname &nbsp; email: @email <br>
</pre>
<p>
<b>Example 5. Issue an SQL INSERT command, without using #sqlbuild:</b>
<pre>
#sql
insert into people (id, lastname, firstname, email )
values ( @id, "@lastname", "@firstname", "@email", balancedue )
#endsql
#if $sqlerror() != 0
<h3>An error occurred!</h3>
#endif
</pre>
<p>
<b>Example 6. Issue an SQL INSERT command, using #sqlbuild to handle quoting, etc.:</b>
<pre>
#sql
#sqlbuild insert people quote noquote=id,balancedue
#endsql
</pre>
<p>
<b>Example 7. Issue an SQL UPDATE command, using #sqlbuild to handle quoting, etc:</b>
<pre>
#sql
#sqlbuild update people quote omit=id noquote=balancedue
where id = 908
#endsql
</pre>
<p>
<b>Example 8. Nested SQL SELECT commands using channels 1 and 2:</b>
<pre>
#sql select caseid from cases order by caseid
#while $sqlrow() = 0
#sql #2 select details from casedetails where caseid = @caseid
#while $sqlrow( 2 ) = 0
....
#endloop
#endloop
</pre>
<p>
<b>Example 8.</b>
<a href="forms.html#de_examp">
A data entry form example
</a>
<br>
<br>
</td></tr>
<td align=right>
<a href="../doc/welcome.html">
<img src="../doc/ploticus.gif" border=0></a><br><small>data display engine <br>
<a href="../doc/Copyright.html">Copyright Steve Grubb</a>
<br>
<br>
<center>
<img src="../gallery/all.gif">
</center>
</td></tr>
</table>
<br>
<center>
<table><tr><td>
Ploticus is hosted at http://ploticus.sourceforge.net</td><td> </td><td>
<a href="http://sourceforge.net/projects/ploticus"><img src="http://sflogo.sourceforge.net/sflogo.php?group_id=38453&type=12"
width="120" height="30" border="0"
alt="Get ploticus data display engine at SourceForge.net. Fast, secure and Free Open Source software downloads" /></a>
</td></tr></table>
</center>
<p><hr>
Markup created by <em>unroff</em> 1.0, <tt> </tt> <tt> </tt>March 11, 2009.
</body>
</html>
distrib
>
Mandriva
>
2010.0
>
i586
>
media
>
contrib-release
>
by-pkgid
>
ed05dc5f357c49fdded780f52b150dce
>
files
>
116
