|
SQL database interfaceThe #sql directive provides a convenient interface for building and invoking SQL commands, and capturing the results. By default #sql points to SHSQL (which is bundled with QUISP). Interfaces to other databases are also possible.
A number of
#sql examples
are provided below; see also the
live demo on sourceforge.
#sql - #endsqlIssue an SQL command. The SQL command can be one or more lines in length. Script variables and directives such as #if may be embedded in order to conditionally build an SQL command (but #shell commands cannot be interleaved). Command status is available via $sqlerror() (a nonzero value indicates an error). For SELECT commands, the number of retrieved rows is available via $sqlrowcount() and individual result rows can be fetched using $sqlrow().Usage: #sql [ichannel] [mode] sql command ... #endsqlWhere convenient, a single-line form may be used: #sql [ichannel] [mode] sql command ichannel is 1, 2, 3 or 4. When ichannel is not specified channel 1 is assumed. The channel number may be preceded by a pound sign # if desired. 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. The database access functions below also accept a channel argument. See example 8 below which uses channels. mode determines the disposition of retrieval results. If no mode is specified, the default is #processrows. Available modes include:
Empty retrievals / No more rows - behavior In #load mode, SQL SELECTs that don't find any rows will set their result variables to "". 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: #set id = 954 #sql #load select * from people where id = @idIf this SQL SELECT doesn't return any rows, a quisp variable corresponding to each result column name (including id) will be set to "", which could cause problems if @id is expected to do anything else later in the code.
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.
As above, this can cause unexpected side effects in the quisp page script.
#sqlblankrow#sqlblankrow dbtable
For every field defined in database table dbtable, initialize a variable to "".
This operator is useful when setting up a form for new content entry, where the form
fields correspond to fields in a database table.
#sqlcgivarsGet an ordinary CGI user variable for every field defined for database table dbtable. Typical use is on a form formtarget page; using #sqlcgivars lets you avoid having to enumerate all fields in a #cgivar statement. The CGI user variable names must exactly match the database table field names.Usage: #sqlcgivars dbtable [overwrite] Example: #sqlcgivars people Example: #sqlcgivars people overwrite Unless the overwrite option is given, variables that are already in use (have already been given a value) will NOT be set by #sqlcgivars. If any of the variables need to be captured using special conversions (eg. #cgilistvar, #cgitextvar, or #cgimultivar), these operators must be invoked before #sqlcgivars, and the overwrite option must not be used.
Caution: any fields not present in the submitted form must be passed to the formtarget page using
#pass;
otherwise any existing contents of those fields will be lost.
#sqlbuildAutomatically builds an SQL INSERT command or most of an UPDATE command. 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.If #sqlbuild is used, much of this work is done for you; see examples 6 and 7 below. For UPDATE, the developer must supply a trailing where clause as shown. #sqlbuild assumes that a script variable exists for each database field, and has the same name as the database field. #sqlbuild is typically used in a formtarget page where a data record has been submitted from a form, and where the fields have been loaded using #sqlcgivars. Usage: #sqlbuild umode dbtable defaultqmode [omit=fields] [noquote=fields] [quote=fields]
fields should be a comma-delimited list of fieldnames with no embedded whitespace. Here are the #sqlbuild conversion rules. Any embedded double quote (") characters are converted to single quotes (avoids the need for embedded escape characters and works well with HTML form tags). Empty fields are converted to null. An entry for every dbtable field is generated (except those mentioned if omit= is used) in logical table order. If umode is set to any then an "update ... orinsert" command is generated (new 4/14/08).
Hint: during development don't put the #sqlbuild within a #sql / #endsql right away;
first put it within a <pre> / </pre> and just see the SQL that it builds.
FunctionsThese functions may be used in conjunction with the #sql command. Many of the functions take an argument dbc which specifies the database connection path, an integer between 1 and 4 (if omitted, 1 is assumed).$sqlrow( dbc )
$sqlrowcount( dbc )
$sqlpushrow( dbc )
$sqlerror( dbc )
$sqltabdef( dbc, table )
$sqlprefix( dbc, prefix )
$sqlstripprefix( dbc, prefix )
$sqlwritable()
#mode nullrep
ExamplesIn ./qexamples are a number of examples that do various things, see the README file there.Example 1. Invoke a simple SQL command and display the results: <pre> #sql #dump select * from classlist </pre> #if $sqlrowcount() != 0 <h3>Nothing found</h3> #endif Example 2. Similar to above but display results as HTML table rows: #set SEARCH = "gib" <table cellpadding=2> #sql #dumphtml select * from classlist where lastname like "@SEARCH*" </table> #if $sqlrowcount() != 0 <h3>Nothing found</h3> #endif Example 3. Process result rows one at a time: #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 Example 4. Use an SQL command to load some variables: #set ID = 908 #sql #load select lastname, firstname, email from people where id = @ID Name: @lastname, @firstname email: @email <br> Example 5. Issue an SQL INSERT command, without using #sqlbuild: #sql insert into people (id, lastname, firstname, email ) values ( @id, "@lastname", "@firstname", "@email", balancedue ) #endsql #if $sqlerror() != 0 <h3>An error occurred!</h3> #endif Example 6. Issue an SQL INSERT command, using #sqlbuild to handle quoting, etc.: #sql #sqlbuild insert people quote noquote=id,balancedue #endsql Example 7. Issue an SQL UPDATE command, using #sqlbuild to handle quoting, etc: #sql #sqlbuild update people quote omit=id noquote=balancedue where id = 908 #endsql Example 8. Nested SQL SELECT commands using channels 1 and 2: #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
Example 8.
A data entry form example
|
![]() data display engine Copyright Steve Grubb ![]() |
Ploticus is hosted at http://ploticus.sourceforge.net |
|