quisp's SQL interface - details
Quisp comes bundled with shsql. Primary access is via
quisp's #sql directive. The main quisp page has
some starter info and examples; this page gives some additional working details.
Quisp may be modified to access other DBMS APIs, see dbinterface.c
Example 1: Issue an SQL "select" command and use the #load option to copy the values of "name" and "desc" into quisp vars of same name.
#sql #load select name, desc from dict where id = "@reqid"
Example 2: Issue an SQL "select" command and process result rows one by one.
#sql select name, desc from dict order by name
#while $sqlrow() = 0
#+ Name = @name .... description = @desc <br>
Example 3: Issue an SQL "delete" command.
#sql delete from places where name = "Trelawney"
You'd invoke SQL "insert" or SQL "update" similarly.
Capturing sql retrievals
The main quisp page has some retrieval examples.
When SQL result fields are captured into quisp each is loaded into a quisp variable having
the same name as the database field name.
Any fields that are null will set the quisp variable to "".
Any underscore (_) found in a field will be converted to a space, since in shsql-compliant
files this is how spaces are encoded (your application will need to convert space back to
underscore for things like email addresses, eg. #set email = $change( " ", "_", @email ).
If you're capturing the result of a join, the database field names will be eg. s.id, s.name, r.total
and these become the quisp variable names, verbatim with the periods.
If you're capturing the result of an aggregate like $count() or $avg() you should use (as rowcount) or (as avg) in the sql
to give these a usable name.
The #load option is a convenience feature that can be used with sql commands that
are expected to return just one row. See example 1 above.
For every field present in the sql result, a quisp variable of the same name will
be loaded with the value (in example 1 these would be name and desc).
There's no need to use $sqlrow() to get rows,
If the retrieval does not return any rows all the quisp variables involved will be set to "".
If the retrieval finds more than one row the last one gotten will be in effect.
Cannot be used with "select into".
Getting sql result row count – $sqlrowcount()
Example: #set nrows = $sqlrowcount()
This count is taken before any "select distinct" uniqueness processing.
You can use it with any #sql invocation including #load or "select into".
For inserts, updates, and deletes the return value will be 0.
Getting sql result rows – $sqlrow()
The $sqlrow() function gets the next row for the most recent #sql retrieval.
It returns 1 when there are no more rows, otherwise it returns 0.
Usually called as part of a #while loop that iterates
until all rows are gotten. On each call it loads a quisp var for every field.
On last call, when there are no more rows, all the vars will be loaded with "".
Stripping off join prefix – $sqlstripprefix()
When a join result is captured all fields will have table prefixes, eg. a.id and a.desc.
To get rid of the prefixes use eg. #call $sqlstripprefix( "a." )
This should be done after the #sql but before $sqlrow().
Adding a prefix – $sqlprefix()
Similarly to above, it's sometimes useful to add a prefix to all fieldnames in a retrieval to avoid
colliding with variables already in use.
Eg. #call $sqlprefix( "beta." )
This should be done after the #sql but before $sqlrow().
Using multiple db connections / channels
By default #sql uses channel 0. You can do 2 or more concurrent sql operations
by using the additional channels 1, 2, or 3. For example:
#sql2 select name, desc from topics
#if $sqlrowcount(2) > 0
#while $sqlrow(2) = 0
Build SQL insert / update commands – #sqlbuild
This directive will build an SQL insert or update command for you, saving some tedium.
Quisp vars for each field in tablename will be used to build the command; any not present
will end up as null in the database.
#sqlbuild insert|update|new|any tablename quote [omit=fields] [noquote=fields]
Example 1: build a SQL "insert" command for the "people" table, getting values from quisp vars with same names
as fields in "people", and quoting all fields except "id" and "balancedue".
#sqlbuild insert people quote noquote=id,balancedue
Example 2: build and execute a SQL "update" command for the people table. No update will be requested for the "id" field.
All fields will be quoted except "balancedue". As opposed to example 1 this sql command will actually be executed
because it is within #sql / #endsql. Also, because it's an "update" command, a where-clause must be included as shown.
#sqlbuild update people quote omit=id noquote=balancedue
where id = 908
Notes: in the first #sqlbuild parameter "new" means the same thing as "insert";
"any" will do a shsql "update ... orinsert" command (do an update if possible otherwise an insert),
and a where-clause must be supplied as with "update".
It's good practice to do as in example 1 and examine the built sql before letting it be executed.
Any embedded double-quotes (") will be changed to single-quotes.
Here are some additional directives that are useful in the quisp/shsql context:
Initialize a quisp var to "" for every field in tablename. Sometimes useful in building html forms.
Do a #cgivar call for every field in tablename. Sometimes useful in capturing user form input.
Tell shsql who the user is, for logging purposes. In this example we get the
http REMOTE_ADDR var which has the user's IP address, and use that as the identity
for shsql transaction logs.
#set user = $getenv( REMOTE_ADDR )
#sql identity @user
#sql sqlmode translog
Turn transaction logging off (0) or on (1). Typical use is to
suppress transaction logging of a large repetitive update.
#sql translog 0