Synopsis:
Meta-HTML can optionally be compiled with the MSQL
database interface library (by supplying --with-msql
to the `configure
' command). These extensions allow true SQL database interaction at many levels, providing a clean and flexible abstraction to an MSQL database.
Commands:
Function Documentation
<msql::cursor-get-column CURSOR COLUMN-NUMBER>
|
Simple
|
This can be called with a cursor which is the result from a call to database-exec-query.
It gets the data from the current result set, in column numbered COLUMN-NUMBER (starting at column 0).
<msql::database-delete-record DBVAR KEY [TABLE=TABLENAME] [KEYNAME=FIELDNAME]>
|
Simple
|
Deletes the specified record in the database referenced by DBVAR. The record to delete is specified by KEY.
You must supply TABLENAME and FIELDNAME, where is the name of the primary key field.
<msql::database-exec-query DBVAR QUERY-STRING [CURSOR=VARNAME]>
|
Simple
|
Executes the SQL query in QUERY-STRING The QUERY-STRING can be any expression Meta-HTML expression which evaluates to
a valid SQL query or command string.
Returns a cursor object, which can be passed to
msql::database-next-record
, msql::number-of-rows
, and
msql::set-row-position
.
If the CURSOR keyword arg is supplied, the returned cursor value is stored in the supplied variable name.
<msql::database-exec-sql DBVAR QUERY-STRING>
|
Simple
|
<msql::database-field-info DBVAR TABLE FIELDNAME>
|
Simple
|
Returns an alist of properties of the specified column FIELDNAME in the SQL table named TABLE:
((name . "column_name") (length . "max byte length") (type . "sqlDatatype")
(is_primary_key . "true") (is_not_null . "true"))
The returned sqlDatatype value is one of
GSQL_CHAR
GSQL_NUMERIC
GSQL_DECIMAL
GSQL_INTEGER
GSQL_SMALLINT
GSQL_FLOAT
GSQL_REAL
GSQL_DOUBLE
GSQL_VARCHAR
<msql::database-load-record DBVAR KEY [PACKAGE=PACKAGENAME] [TABLE=TABLENAME] [KEYNAME=FIELDNAME]>
|
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.
<msql::database-next-record CURSORVAR [COLNAMES=NAMELIST] [PREFIXTABLENAMES] [PACKAGE=PACKAGENAME]>
|
Simple
|
Fill the variables in PACKAGENAME (defaulting to the current package if not supplied) with the values of the next record which last satisfied the search referenced by CURSORVAR.
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 these commas-separated list of names instead of the field names in the
result set.
<msql::database-next-record mydb cursor package=tmp
colnames="a.name, a.partnum, b.name">
<get-var tmp::b.name>
<get-var tmp::a.name>
If 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.
<msql::database-query DBVAR EXPR QUERY [COLNAMES=NAMELIST] [PREFIXTABLENAMES] [QUERY=QUERY-STRING] [FORMAT=FEXPR] [KEYS=VARNAME] [KEYNAME=FIELDNAME]>
|
Simple
|
Select and optionally format records in the database DB according to the criterion in FEXPR.
QUERY is an SQL query, which returns 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 is present, it is an expression to evaluate in the context of the database fields, (as with EXPR).
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. The EXPR could be used to impose additional conditions to decide whether to invoke the format expression on a row.
<msql::with-open-database rdb database=rolodex
host=localhost>
<table border=1>
<tr><th>Name</th><th>Age</th></tr>
<msql::database-query rdb true
query="SELECT * FROM people WHERE name like 'Washington' ORDER BY lastname"
format=<prog <tr>
<td>
<get-var lastname>, <get-var firstname>
</td>
<td>
<get-var age>
</td>
</tr>>>
</table>
</msql::with-open-database>
If KEYS is specified, it is the simple name of a variable to receive the array of keys which satisfied FEXPR. If you specify an argument for KEYS, you must
also specify which column to collect the values from, using
the KEYNAME keyword argument.
If the optional argument COLNAMES 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.
<msql::database-query mydb true query=<get-var query>
colnames="foo.name, bar.name"
format=<prog FOO.NAME IS
<get-var foo.name>
BAR.NAME IS
<get-var 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 the column's table
name, if it exists, as a prefix to the column name as the variable
name.
<msql::database-save-package DBVAR KEY PACKAGE [KEYNAME=FIELDNAME] [TABLE=TABLENAME]>
|
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 fields. 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.
msql::database-save-package
is implemented 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 baz::name="Kate Mulgrew" baz::age=45 baz::salary=34000
baz::dept=travel>
<set-var result=<msql::database-save-package mydb 103 baz
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.
<msql::database-save-record DB KEY TABLE KEYNAME [VAR...] [TABLE=TABLENAME] [KEYNAME=FIELDNAME]>
|
Simple
|
Does an INSERT and UPDATE to try to store the data into TABLE using the single key KEYNAME, with value KEY.
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 have a variable which does not match a field name
in the table, that variable is silently ignored.
If you try to save character data into a numeric field,
the insert or update will fail, and you can check the
error message in msql::msql-error-message[]
<msql::database-table-fields DBVAR TABLENAME [RESULT=VARNAME]>
|
Simple
|
List all the fieldnames for a table. This must be done with a database already open, i.e., within the scope of a <msql::with-open-database>
.
Returns an array of column names, or sets the value of VARNAME if supplied.
<msql::database-tables DBVAR [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. This must be done with a database already open, i.e., within the scope of a <with-open-database>
.
<msql::host-databases [HOSTNAME] [RESULT=VARNAME]>
|
Simple
|
Return the names of the databases available on HOST as a newline separated list of strings. If VARNAME is supplied, it is the name of a variable which should receive the values.
<msql::number-of-rows CURSORVAR>
|
Simple
|
Returns the number of rows which were found as a result of the last query issued for CURSORVAR.
<msql::set-row-position CURSORVAR INDEX>
|
Simple
|
<msql::sql-transact DBVAR [ACTION=COMMIT|ROLLBACK]>
|
Simple
|
Perform transaction, to either commit or rollback all operations on
the current database connection.
ACTION can be one of COMMIT or ROLLBACK.
If unspecified, ACTION defaults to COMMIT.
NOTE: Transactions are not yet supported by MSQL.
<msql::with-open-database DBVAR [DSN=DSN] [DATABASE=DBNAME] [HOST=HOSTNAME]> body </msql::with-open-database>
|
Complex
|
Create an environment in which other MSQL database commands can be given. First, the database referenced by DBNAME is locked and opened, and the resultant database handle is bound to the variable named by DBVAR. Then, the BODY code is executed. Finally, the database is closed, and further references to DBVAR are meaningless.
Example:
<msql::with-open-database rdb database=rolodex
host=localhost>
...
</msql::with-open-database>
For compatibility with ODBC, the host/database information
can optionally be given in a ODBC style DSN string.
DSN="host=localhost;database=rolodex"
Edit Section
Function Index
Variable Index

The
META-HTML
Reference Manual V1.4
Copyright © 1995, 1996,
Brian J. Fox,
1996, 1997 Universal Access Inc.
Found a bug? Send mail to
bug-manual@metahtml.com