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


SQL examples
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>
#endloop


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.


#sql #load
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
        ......
      #endloop
  #endif



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.
#sql
    #sqlbuild update people quote omit=id noquote=balancedue
    where id = 908
#endsql


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:

#sqlblankrow tablename
Initialize a quisp var to "" for every field in tablename. Sometimes useful in building html forms.


#sqlcgivars tablename
Do a #cgivar call for every field in tablename. Sometimes useful in capturing user form input.


#sql identity
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