SQLNamedQuery

Name

SQLNamedQuery -- Specify a query and an identifier for SQLShowInfo and SQLLog

Synopsis

SQLNamedQuery [ "name" limit|regex|ip value]

Default

(docs incomplete)

Context

server config, <Global>, <VirtualHost>, <Anonymous>, <Limit>, .ftpaccess

Module

mod_sql

Compatibility

1.2.5rc1 and later

Description

SQLNamedQuery specifies a query and an identifier (name) for later use by SQLShowInfo and SQLLog.

It is strongly recommended that you read documentation on the LogFormat and ExtendedLog directives, as the meta-sequences available to SQLNamedQuery are largely equivalent.

The first parameter, name, should be unique across all named queries and must not contain spaces. The result of re-using a name is undefined.

The second parameter, type, is the type of query, either "SELECT", "UPDATE", "INSERT", or "FREEFORM". See the note below for information on FREEFORM type queries.

The third parameter is the substance of the database query itself; this should match the form of the second parameter. The meta-sequences accepted are exactly equivalent to the LogFormat directive except the following are not accepted:

and the following is in addition to the LogFormat meta-sequences:

The correct form of a query will be built from the directive arguments, except in the case of FREEFORM queries which will be sent directly to the database. The examples below show the way queries are built from the arguments.

The fourth parameter, table, is only necessary for UPDATE or INSERT type queries, but is required for those types.

Note: FREEFORM queries are a necessary evil; the simplistic query semantics of the UPDATE, INSERT, and SELECT type queries do not sufficiently expose the capabilities of most backend databases. At the same time, using a FREEFORM query makes it impossible for mod_sql to check whether the query type is appropriate, making sure that a SELECT query is not used in a SQLLog directive, for instance. Wherever possible, it is recommended that a specific query type be used.

See also

SQLShowInfo SQLLog LogFormat ExtendedLog

Examples

SQLNamedQuery count SELECT "count from users where userid='%u'"

creates a query named "count" which could be used by SQLShowInfo to inform a user of their login count. The actual query would look something like "SELECT count FROM users WHERE userid='matilda'" for user "matilda".

SQLNamedQuery updatecount UPDATE "count=count+1 WHERE userid='%u'" users

creates a query named "updatecount" which could be used by SQLLog to update a user login counter in the table users. The actual query would look something like "UPDATE users SET count=count+1 WHERE userid='persephone'" for user "persephone".

SQLNamedQuery accesslog INSERT "now(), '%u'" accesslog

creates a query named "accesslog" which could be used by SQLLog to track access times by clients. The actual query would look something like "INSERT INTO accesslog VALUES (now(), 'pandora')" for user "pandora". Note that this may be too simplistic for your table structure, since most databases require data for all columns to be provided in an INSERT statement of this form. See the following FREEFORM query for an example of something which may suit your needs better.

SQLNamedQuery accesslog FREEFORM "INSERT INTO accesslog(date, user) VALUES (now(), '%u')"

creates a query named "accesslog" which could be used by SQLLog to track access times by clients. The actual query would look something like "INSERT INTO accesslog(date, user) VALUES (now(), 'tilda')" for user "tilda".