SQLNamedQuery

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, , , , , .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:

  • %{FOOBAR}e

    For LogFormat, this logs the content of environment variable "FOOBAR". This is not bavailable in mod_sql.

  • %{format}t and %t

    These two meta-sequences logged the local server time; they are not available in mod_sql. Your database undoubtedly provides another way to get the time; for example, MySQL provides the now() function.

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

 

  • %d

    The current working directory or "-" if none.

  • %{n}

    This meta-sequence is used internally by mod_sql and other third-party modules and patches to pass information to the database. Using this meta-sequence in anything other than an INSERT or UPDATE query is an error, and using this meta-sequence unless directed to by a third-party module or patch is also an error.

  • %{env:VAR}

    Starting with ProFTPD 1.3.1rc1 the SQLNamedQuery directive is able to make use of environment variables in the format "%{env:VAR}". The value of the environment variable VAR will be substituted into the SQL statement.

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.

espaceur


ceydric