Section Intro: Generic Sql Interface

THE META-HTML LANGUAGE REFERENCE MANUAL

Database Commands

[TOC]

Direct Dbm Access

Section Intro: Generic Sql Interface

Generic Sql Interface

Synopsis:

With the plethora of ANSI SQL compatible databases present on the marketplace, Meta-HTML provides a single comprehensive interface which may be used to access any of those which are supported.

Such databases include mSQL, mySQL, Oracle, SyBase, Informix, and many other ODBC compliant databases.

Using the Meta-HTML Generic SQL Interface (GSQL), you can more rapidly develop and inexpensively prototype your application -- then migrate to full-blown production scale without writing a single line of code!

For example, you might build an in-house HR application using the MySQL database. Once you are happy with the application flow and GUI, you can port the application to Oracle 8i by changing two lines of code:

<sql::set-database-type mysql>
<set-var app::dsn = "host=localhost;database=hr">
    ...
<sql::set-database-type odbc>
<set-var app::dsn = "HOST=production;database=HR;SVT=Oracle 8i;...">

Commands:

sql::admin-prog
sql::affected-rows
sql::allow-system-tables
sql::cursor-get-column
sql::database-column-info
sql::database-columns
sql::database-columns-info
sql::database-delete-record
sql::database-exec-query
sql::database-exec-sql
sql::database-load-record
sql::database-next-record

sql::database-query
sql::database-query-info
sql::database-save-package
sql::database-save-record
sql::database-set-options
sql::database-table-info
sql::database-tables
sql::database-tables-info
sql::get-database-type
sql::host-databases
sql::number-of-rows
sql::package-to-table

sql::query-get-column
sql::recent-query
sql::search-form-params
sql::set-database-type
sql::set-row-position
sql::sql-error-message
sql::sql-transact
sql::standard-search-form
sql::standard-search-handler
sql::translate-column
sql::with-open-database

More Information:

The Meta-HTML Generic SQL Interface works by providing all of the underlying functionality for each specific database type at a generic top-level interface which never has to change. You specify which type of database that you would like to use with a single function call, using sql::set-database-type.

Although there are specific low-level functions implementing the database connectivity, the higher-level routines transparently make extremely efficient use of them to handle your requests.

Because the low-level functions exist, you can easily write code which copies data from one type of database (say mSQL) to another type (say an Oracle ODBC database).

<sql::admin-prog Simple

Returns the complete path of the executable program which is used to perform administration functions on the underlying SQL database. For example, /usr/local/Hughes/bin/msqladmin, or /usr/bin/mysqladmin.

<sql::affected-rows CURSOR Simple

Return the number of rows affected by the last SQL query execution, associated with the database referenced indirectly by CURSOR. This may be completely unrelated to the query which created the cursor object, depending on the underlying database which is in use.

<sql::allow-system-tables &optional [ALLOW?] Simple

For those underlying database engines which keep information about the database structure within the database itself, a request to <sql::host-databases ...> can return a great deal of information, most of which is uninteresting to an application. Call this function with an argument of "true" to allow such system tables to be returned, or with no argument to disallow them.

<sql::cursor-get-column CURSOR COLUMN-NUMBER Simple

Called with a CURSOR (the result from a call to sql::database-exec-query), which has been filled in by at least one call to sql::database-next-record, this function returns the data from the current result set, in the column numbered COLUMN-NUMBER (starting at column 0).

If there is no result set, or if the result set is empty, the empty string is returned.

 <sql::with-open-database db dsn="host=localhost;database=admin">
   <set-var query="select * from users">
    <sql::database-exec-query db <get-var-once query> cursor=row>
    <set-var any? = <sql::database-next-record row>>
    <when <get-var-once any?>>
      Result: <sql::cursor-get-column row 0>
    </when>
 </sql::with-open-database>

<sql::database-column-info DBVAR TABLE FIELDNAME Simple

Returns an association list of properties on the specified COLUMN in the table TABLE.

Example:

 ((NAME . realname)
  (LENGTH . 30)
  (TYPE . GSQL_CHAR)
  (IS_UNIQUE . "")
  (IS_NULLABLE . true)
  (QUALIFIER . "")
  (OWNER . "")
  (TYPENAME . "")
  (PRECISION . 0)
  (SCALE . 0)
  (RADIX . 0)
  (TABLE . accounts))

This is actually implemented by fetching information on all columns and selecting the specified one. This means that you should use the command sql::database-columns-info if you are looking for information on several columns, as opposed to the specific column mentioned by COLUMN.

<sql::database-columns DBVAR TABLENAME &key [RESULT=VARNAME] Simple

Returns a newline separated list of the columns present in TABLENAME in the open SQL database referenced by DBVAR.

The keyword RESULT=VARNAME argument specifies the name of a variable to put the result array into; if not present, the array is returned to the caller.

<sql::database-columns-info DBVAR TABLE &key [RESULT=VARNAME] Simple

Analogous to sql::database-column-info, this function returns an array of association lists where each entry in the array is the information about a single column.

<sql::database-delete-record DBVAR KEY &key [TABLE=TABLENAME] [KEYNAME=FIELDNAME] Simple

Deletes the specified record in the database referenced by DBVAR. The record to delete is specified by KEY and KEYNAME. An SQL DELETE query is issued which will delete all records in the table named TABLE whose column named FIELDNAME matches the value of KEY.

You must supply values for TABLE and KEYNAME.

<sql::database-exec-query DBVAR QUERY-STRING &key [CURSOR=VARNAME] Simple

Executes the SQL query in QUERY-STRING by sending it to the open database connection referenced by DBVAR.

QUERY-STRING can be any Meta-HTML expression which evaluates to a valid SQL query or command string.

Returns a cursor object, which can be passed to sql::database-next-record, sql::number-of-rows, or sql::set-row-position.

If the keyword argument CURSOR=VARNAME is given, the returned cursor value is stored in VARNAME.

<sql::database-exec-sql DBVAR QUERY-STRING Simple

Executes the SQL query in QUERY-STRING, on the open database connection specified by DBVAR.

Returns TRUE if no errors are encountered.

This function differs from sql::database-exec-query in that it does not return any cursor; i.e., there is no way to get results back from the execution of your SQL command. Thus, it is most useful for sending commands which expect no return values, such as a SQL DELETE statement:

 <sql::database-exec-sql 
    db "DELETE FROM maillists WHERE list = '<get-var-once listname>'">

<sql::database-load-record DBVAR KEY &key [TABLE=TABLENAME] [KEY=FIELDNAME] [PACKAGE=PACKAGENAME] Simple

Load variable values from the database referenced by DBVAR and KEY into the package specified by PACKAGENAME (defaulting to the current package if not supplied).

TABLENAME and FIELDNAME must be supplied -- they specify the table and primary key field to be operated on.

<sql::database-next-record CURSOR &key [COLNAMES=NAMELIST] [PREFIXTABLENAMES=TRUE] [PACKAGE=PACKAGENAME] Simple

Fill the variables in PACKAGENAME with the values of the next record which last satisfied the search referenced by CURSORVAR.

If PACKAGENAME is not supplied, then return an alist of the values instead.

Each subsequent call gets a subsequent record from the list of results, until all of the results are exhausted.

Returns true if there are any records left in the search results, or the empty string if not.

If COLNAMES is supplied, then the column values of this result are bound sequentially to the comma-separated list of names in NAMELIST instead of the field names in the result set.

 <sql::database-next-record cursor package=tmp
             colnames="a.name, a.partnum, b.name">
 <get-var-once tmp::b.name>
 <get-var-once tmp::a.name>

If COLNAMES is non-null, or the database connection option SQL-PREFIX-TABLENAMES is non-null, then for each column in the result set, use the column's table name, if it exists, as a prefix to the column name as the variable name.

<sql::database-query DBVAR QUERY &key [COLNAMES=NAMELIST] [PREFIXTABLENAMES=TRUE] [FORMAT=FEXPR] [KEYS=VARNAME] [KEYNAME=FIELDNAME] [WINDOW-START] [WINDOW-LENGTH] Simple

Select and optionally format records in the database referenced by DBVAR according to the criterion in FEXPR.

QUERY is an SQL query, which is expected to return a list of rows.

For each result row, EXPR is then evaluated, with the column values of each row bound to their corresponding column name. If the result of that evaluation is not an empty string, then that record is selected for further processing by either FORMAT, KEYS, or to return in plain text the list of keys. If FORMAT=FEXPR is present, FEXPR is an expression to evaluate in the context of the database fields, (as with EXPR).

The value of "true" for EXPR is specially optimized to for those queries where no additional processing is necessary.

The example below shows an SQL query which is formatted as rows of an HTML table. In this case, the EXPR is simply the constant true, and all of the selection of records is done via the SQL query itself. (Note that EXPR could be used to impose additional conditions to decide whether to invoke FEXPR on a row.

 <table border=1>
   <tr> <th>Name</th> <th>Age</th> </tr>
   <sql::with-open-database db dsn="DATABASE=ROLO;HOST=localhost">
     <sql::database-query db true
         "SELECT * FROM people
          WHERE name like 'Washington' ORDER BY lastname"
         format=<group <tr>
                       <td> <get-var lastname>, <get-var firstname> </td>
                       <td> <get-var-once age> </td>
                       </tr>>>
   </sql::with-open-database>
 </table>

If KEYS=VARNAME is given, then VARNAME is the name of a variable to receive the array of keys which satisfied the query. If you specify an argument for KEYS, you must also specify which column to collect the values from, using the KEYNAME=FIELDNAME keyword argument.

If the optional argument COLNAMES=NAMELIST is supplied, then for each row, column values are bound sequentially to these comma separated names instead of the column names in the result set.

 <sql::database-query db true <get-var-once query>
    colnames="foo.name, bar.name"
    format=<group FOO.NAME IS <get-var-once foo.name> 
                  BAR.NAME IS <get-var-once bar.name>>>

If PREFIXTABLENAMES is non-null, or the database connection option SQL-PREFIX-TABLENAMES is non-null, then for each column in the result set, use that column's table name as a prefix to the column name as the variable name.

The WINDOW-START and WINDOW-LENGTH keywords may be used to limit the amount of data that is returned from the SQL server. For example, to get the 10 most recently hired employees from the database, one might write:

 <sql::database-query db true window-start=0 window-length=10
    format=<array-append <package-to-alist> ^::results>
    "SELECT * FROM employees ORDER BY hiring_date DESC">

<sql::database-query-info CURSOR &key [RESULT=VARNAME] Simple

Returns an array of association lists giving information about each column in the result set specified by CURSOR.

If the keyword argument RESULT=VARNAME is given, the resultant array is placed into that variable -- otherwise it is simply returned.

<sql::database-save-package DBVAR KEY PACKAGE &key [TABLE=TABLENAME] [KEYNAME=FIELDNAME] Simple

Save the variables in PACKAGE associated with the value KEY in column KEYNAME in the table TABLENAME of the database referenced by DBVAR.

This only saves variables which have names matching existing table column names. Package prefixes are stripped from the variables, and the symbol name is used as the column name to store the data. Symbol names and column names are treated in a case-insensitive manner.

The implementation is to first attempt to do a SQL INSERT into the table, and if that fails to try a UPDATE query, with KEYNAME = KEY.

Example: Say we want to save some information about employee Kate Mulgrew, in a table of employees, which has a primary key field named "id", and we want to save this record with id=103:

 <set-var record::name="Kate Mulgrew" record::age=45
          record::salary=34000 record::dept=travel>

 <set-var saved? =
    <sql::database-save-package db 103 record table=employees keyname=id>>

If a variable in the package you are saving corresponds to a column with a numeric field type, and the value of the variable is the empty string, the system will attempt to store a NULL value into this field of the record. If the table does not support NULL values on that column, the operation will fail.

<sql::database-save-record DBVAR KEY &optional [VAR...] &key [TABLE=TABLENAME] [KEY=FIELDNAME] Simple

Write a record to the open SQL database connection referenced by DBVAR, using KEY as the value of the primary key, on the table TABLE. The keyword argument KEY is the SQL column name of the column that you are specifying as the primary key.

For each VAR, treat the name of VAR as a column name, and set that column's value to the value stored within VAR.

An UPDATE is initially attempted to store the data. If the UPDATE fails, then an INSERT is attempted.

NOTE: If the key field for table you are using is not configured as the primary key, then it is possible to create duplicate entries in the database.

If you pass in a variable VAR which does not match a column name in TABLE, that variable is silently ignored.

If you try to save character data into a numeric field, the INSERT or UPDATE will fail.

As always, the most recent query executed is available by calling the function sql::recent-query, and error messages may be retrieved by calling the function sql::sql-error-message.

<sql::database-set-options DBVAR &key [OPTION-NAME=VALUE] Simple

Sets various database-specific options.

Possible values for OPTION-NAME are:

SQL-ESCAPE-CHARACTER-CODE: The character which is used to escape single quotes in strings.
SQL-TRUNCATE-COLUMNS: When set to a non-empty string, queries composed by sql::database-save-record and sql::database-save-package will have character string field data automatically truncated to the maximum column width of the field being stored into, before the query is ever sent to the SQL server.
SQL-PREFIX-TABLENAMES: For sql::database-next-record and sql::database-query, prefix the names of symbols for column of the result set with the name table to which the column belongs.

<sql::database-table-info DBVAR &optional [TABLENAME] &key [RESULT=VARNAME] [TABLETYPE=REGEXP] [TABLEQUALIFIER=REGEXP] [TABLEOWNER=REGEXP] Simple

Returns an array of association lists, one per table in the open SQL database referenced by DBVAR.

Each alist contains database-specific information about the table, including at least the name of the table.

 ((name . "table_name"))

The keyword arguments starting with "table..." are ANSI SQL regular expression patterns. They are used if supplied, ignored if not.

The keyword RESULT=VARNAME argument specifies the name of a variable to put the result array into; if not present, the array is returned to the caller.

<sql::database-tables DBVAR &key [RESULT=VARNAME] Simple

Return the names of the tables in the database referenced by DBVAR as a newline separated list. If VARNAME is supplied, it is the name of the variable to receive the table names.

<sql::database-tables-info DBVAR &optional [TABLENAME] &key [RESULT=VARNAME] [TABLETYPE=REGEXP] [TABLEQUALIFIER=REGEXP] [TABLEOWNER=REGEXP] Simple

Returns an array of association lists, one per table in the open SQL database referenced by DBVAR.

Each alist contains database-specific information about the table, including at least the name of the table.

 ((name . "table_name"))

The keyword arguments starting with "table..." are ANSI SQL regular expression patterns. They are used if supplied, ignored if not.

The keyword RESULT=VARNAME argument specifies the name of a variable to put the result array into; if not present, the array is returned to the caller.

<sql::get-database-type Simple

Return the type of the underlying SQL database which will be accessed by the various SQL::... functions. The possibilities are one of

msql: The Minerva mSQL database from Hughes,
mysql: The MySQL database,
mysqlper: A persistant connection version of MySQL,
podbc: The PostGres database, using the ODBC interface lib,
odbc: A generic ODBC database, such as Oracle or Sybase.

<sql::host-databases &optional [HOST] Simple

Return an array of the database names present on HOST (defaults to localhost.

This command is only meaningful to the mSQL and mySQL database engines -- calling it on ODBC databases always returns an empty array.

<sql::number-of-rows CURSOR Simple

Return the number of rows SELECTed by the last SQL query execution, associated with the database referenced indirectly by CURSOR.

<sql::package-to-table DBVAR PACKAGE TABLE &key [METHOD] Simple

Save the variables in PACKAGE in the table TABLE of the database referenced by DBVAR.

If you don't specify a METHOD, then the implementation first attempts to do an SQL UPDATE on TABLE if, and only if, there is a record in the table already that exactly matches the primary keys in the data in PACKAGE. Otherwise, an INSERT statement is used. If METHOD is specified, then only that method is tried.

Both the INSERT and UPDATE attempts use the primary keys of the table in a WHERE clause in order to ensure the uniquness of this record. If you want a sloppier update, please see sql::database-save-package.

<sql::query-get-column Simple

Undocumented.

<sql::recent-query Simple

Returns the last SQL query statement that was sent to the underlying SQL database. Can be useful in debugging.

<sql::search-form-params ALIST &key [DSN] [TABLE] [KEY] [EDITURL] [DEFCOL] [TITLE] Simple

Get or set the standard search form parameters. Always returns an association list of the current search form parameters, and can optionally set them to new values. In typical usage, this function might be called as follows:

 <set-var orig-params =
    <sql::search-form-params DSN=<mydsn> table=CLIENTS>>
 <sql::standard-search-form posted>
 <set-var ignore = <sql::search-form-params <get-var-once orig-params>>>

We have provided a simple but useful generic search form for use in your Meta-HTML pages. Calling sql::standard-search-form both produces the search form, and the results.

You should set the following variables before calling SQL::STANDARD-SEARCH-FORM:

sql::search-dsn -- The DSN string for the database.
sql::search-dbtable -- The name of the table in the database to search.
sql::search-keyname -- The name of the key field in the database.
sql::search-editurl -- The url of a single record editor.
sql::search-defcol -- The default column to display in the list.
sql::search-title -- The title to display in the input form.

Optionally, you can specify columns that you would like not to be offered as columns that may be searched for, in the array sql::search-ignore-cols[].

<sql::set-database-type TYPE Simple

Create a command set called SQL::... which contains all of the functions normally used for accessing ODBC, mySQL, or mSQL databases. This allows you to write a complete application using the mySQL database, sql::with-open-database and friends, and then to painlessly "port" it to ODBC, simply by calling this function with an argument of odbc.

The possible values for TYPE are

msql: The Minerva mSQL database from Hughes,
mysql: The MySQL database,
mysqlper: A persistant connection version of MySQL,
podbc: The PostGres database, using the ODBC interface lib,
odbc: A generic ODBC database, such as Oracle or Sybase.

<sql::set-row-position CURSOR POS Simple

Set the current row number position in the result set from the last query. This operates on a CURSOR object.

<sql::sql-error-message Simple

Return the current error message from recent SQL statements.

<sql::sql-transact Simple

Currently unimplemented.

<sql::standard-search-form &optional [PACKAGE] &key [FSIZE] [HREF] [TITLE] [BGCOLOR] [FGCOLOR] [TITLE-BGCOLOR] [TITLE-FGCOLOR] Simple

Display a complete form suitable for searching for a record from the database and table specified in sql::search-dsn and sql::search-dbtable.

Optional keyword argument HREF specifies the destination page -- it defaults to the current page if not supplied. Optional keyword FSIZE is the size of the font to use in the form -- this has the effect of growing or shrinking the form.

When this form is posted to, it gathers all of the columns which match the specified search criteria, and then displays the results in a table, linking each to the sql::search-editurl. When only one result is found, redirects to the page specified in search::editurl, providing a message in the session, displayable with show-message, which explains why.

For more information on what happens when this form is posted to, please see sql::standard-search-handler.

<sql::standard-search-handler PACKAGE &key [FSIZE] [HREF] Simple

Perform a database search on the search table using the criteria in PACKAGE. Variables in PACKAGE must include:

csf-search (simply must be set)
csf-column (the name of the column to search on)
csf-operator (either "LIKE" "NOT LIKE" or "=")
csf-value (the text to search for)

Produces a table with the contents of the specified column linked with an href to sql::search-defurl?.

You can overide the information which is displayed for each record that matches the search criteria by creating a function called sql::search-result-name. If this function exists, it should take two arguments: the column name that was being searched for, and an association list representing the data of the current record.

An example might be to always display the last and first name of any search where the column name to be searched contained the string "name":

 <defun sql::search-result-name col alist>
   <if <match <get-var-once col> "name" caseless=true>
       <concat <alist-get-var alist last_name>
               ", "
               <alist-get-var alist first_name>>
     <alist-get-var alist <get-var-once col>>>
 </defun>

<sql::translate-column COL Simple

Return a pretty version of the column name specified in COL, by translating it through the array SQL::COLUMN-TRANSLATIONS. Each member of that array is a column name followed by a colon, followed by a pretty version of that column name. If COL doesn't appear in the list of possible names, then it is simply capitalized.

<sql::with-open-database DBVAR &key [DSN=DSN-STRING] [NOLOCK=TRUE] body </sql::with-open-database> Complex

Opens the database specified by DSN (database service name) string, and stores a referent to it in DBVAR. If the connection fails, the value of DBVAR is the empty string.

DSN should contain a minimum of "HOST=hostname;DATABASE=dbname" in order to connect successfully to an mSQL database. Although the older HOST=HOST DATABASE=DBNAME keyword arguments are still supported, they have been deprecated, and the DSN=DSN-STRING form is preferred.

If NOLOCK=TRUE is supplied, then no lock file on the server is created. This is useful when you don't want to block other processes from accessing the database for the duration of the sql::with-open-database form.

Edit Section
Function Index
Variable Index