Chapter 4.25:  SQL Interface

[TOP] [UP] [PREV] [NEXT]

Caucus 4.55 adds an SQL ("Structured Query Language") interface to relational databases.  The interface uses ODBC to connect to the desired database, so in theory any modern database should be useable.  In practice, only an MySQL interface has been implemented thus far.

The database connection details are controlled by the Caucus "swebd.conf" file, in particular the parameters

• ConnectString
  which defines the ODBC driver type, the database name, and the access userid and password)

• ODBCINI
  which defines the location of the ODBC initialization file.

The rest of this page describes the CML functions that provide the SQL interface.

$sql_query_open (statement)        {protected}

"Opens" (executes) an SQL query statement, and returns (evaluates to) a handle.  If there was a syntactic or operational error in the query, it returns a handle value < 0.  (A query that returns no data, but was syntactically correct, still returns a handle >= 0.)

A query may be any valid SQL statement (that can be passed through ODBC), including:

• SELECT
• INSERT
• DELETE
• UPDATE
• CREATE TABLE
• DROP TABLE

and so on.

Note that the column names (or aliases) in a SELECT query are used as the names of the matching CML variables.  For example, in this query:

   SELECT e.userid, e.event, time t FROM events e
    

each time $sql_query_row() (see below) is called, the e.userid data is automatically placed in CML variable "userid", the e.event data is placed in variable "event", and the e.time data is placed in variable "t".

This means that all columns in a query must have a column name or an alias.  (Any leading table name or alias before the "." is stripped off to determine the CML variable name.)

Thus, for example, queries like:

   SELECT *         FROM events
   SELECT count(*)  FROM events
   SELECT max(item) FROM events

while strictly legal, will never return any data, because there's no variable(s) to put the data into.  Instead use queries like:

   SELECT field1, field2, (and so on) FROM events
   SELECT count(*)  thecount          FROM events
   SELECT max(item) themax            FROM events

Subqueries (queries within the WHERE clause of a query) are fully supported, as long as MySQL 4.1 or higher is installed.

$sql_query_row (handle)        {protected}

Fetches a row of data from the query open on handle.  The results for the row are placed in the matching CML variables, as described above.

Evalutes to 1, or 0 if no (more) rows were found.  Intended for use with SELECT queries only (although it will return 0 with no harm done if called on other kinds of queries).

$sql_query_count (handle)        {protected}

Evaluates to the "count" of rows affected by the original query.  If SELECT, this is the number of rows found.  If INSERT, DELETE, or UPDATE, it is the number of rows affected.

$sql_query_close (handle)        {protected}

Closes a query open on handle.  Queries may be closed at any point, even if all of the data has not been read.  Queries should be closed when no longer in use. 

Don't forget to close non-SELECT queries, even though they don't have calls to $sql_query_row().  If you're feeling brave (foolish?), you can even do things like:

   eval $sql_query_close ($sql_query_open (DROP TABLE sasquatch))

which will work properly even if there's no table "sasquatch".

$sql_query_select (select-statement)        {protected}

Executes select-statement, and returns all rows all at once.  (No $sql_query_row() or $sql_query_close() is needed.)

This is very useful for simple SELECT statements, where you know that you will want all of the results.  For example:

   for a b in $sql_query_select (SELECT a, b FROM X)
      "a=$unquote($(a)), size=$sizeof($unquote($(a)))<br>
      "b=$unquote($(b)), size=$sizeof($unquote($(b)))<br>
   end

will produce 2 lines of text for each row found by the SELECT query.  Note the required unquoting of the results (in case they contain multiple words).

$sql_sequence (name)        {protected}

Returns the "next" number in a sequence named name.

Different RDBMS's implement the notion of a "sequence" differently, so the specific implementation is hidden behind this function.  Essentially, it provides a way to return a guaranteed unique number within a space name. 

(The MySQL implementation uses a table called sequence_name, with one row and one column.  The table is created automagically as needed.)