<emit source="sql"></emit>

Provided by module: Tags: SQL tags

Use this source to connect to and query SQL databases for information. The result will be available in variables named as the SQL columns.

NULL values in the SQL result are mapped to a special null value. That value expands to the empty string if inserted, and tests as false with <if variable> and true with <if variable-exists>.

Compatibility note: If the compatibility level is 4.5 or lower, an SQL NULL value instead maps to an undefined value in RXML, which is similar to that the RXML variable doesn't exist at all. That makes both <if variable> and <if variable-exists> return false for it, among other things.

Attributes

db="database"

Which database to connect to, among the list of databases configured under the "DBs" tab in the administrator interface. If omitted then the default database will be used.

host="url"

A database URL to specify the database to connect to, if permitted by the module settings. If omitted then the default database will be used.

The database URL is on this format:

driver://[username[:password]@]host[:port][/database]

where driver is the database protocol, e.g. "odbc", "mysql", "oracle", "postgres", etc.

For compatibility this can also be a database name as given to the "db" attribute.

module="string"

Access the local database for the specified Roxen module, if permitted by the module settings. This attribute is deprecated.

query="SQL statement"

The actual SQL-statement.

bindings="name=variable,name=variable,..."

Specifies binding variables to use with this query. This is comma separated list of binding variable names and RXML variables to assign to those binding variables.

Note: For some databases it is necessary to use binding variables when inserting large datas. Oracle, for instance, limits the query to 4000 bytes.

<set variable='var.foo' value='texttexttext'/> <sqlquery query='insert into mytable VALUES (4,:foo,:bar)' bindings='foo=var.foo,bar=form.bar'/>

charset="string"

Use the specified charset for the sent SQL statement and returned text values.

This will cause all SQL queries to be encoded with this charset. It will also normally cause all string results to be decoded with this charset, but there are exceptions as explained later. If the database connection supports it, the connection will be configured to use this charset too (at least MySQL 4.1 and later has such support).

In many cases, it is difficult for the SQL interface to tell text and binary data apart in results. That is a problem since a text string should be decoded according to the charset while a binary octet string must not be decoded.

If the connection supports it, using the special value unicode as charset is guaranteed to handle both text and binary result strings correctly so you don't have to worry about it. In that case you can assume the SQL query and text results covers the full Unicode range, and the connection will handle the charset issues internally. This is known to be supported with MySQL 4.1 and later. An RXML run error is thrown if it isn't supported.

Otherwise, all string values are assumed to be text and are therefore decoded using the given charset. You can turn it off for specific columns through the "binary-result" attribute.

If you use none as charset in this attribute then the charset handling described here is disabled for this query. That is useful to override a charset in the "Default charset" module setting.

The charset specification in this attribute can optionally be a list like this:

charset="recode-charset, connection-charset"

In this form, the recode-charset is used by Roxen to recode the query and results, and connection-charset is sent to the database driver to use for the connection. This is useful if the database server uses nonstandard names for its character sets. E.g. MySQL spells cp1252 as "latin1" (which is the closest you get to iso-8859-1 there), so to use that you'd say "cp1252,latin1" in this attribute. This list form is not applicable for the unicode case.

Compatibility note: In Roxen 4.5 this attribute only configured the charset for the connection; it didn't do any conversion of the query nor the results. This behavior still remains if the compatibility level is 4.5 or lower. You can also achieve the same effect by specifying "none,whatever" as charset.

binary-result="column names"

A comma separated list of columns in the result to not treat as text and decode according to the "charset" attribute or the "Default charset" module setting. As a special case, no result column is decoded if the value is empty.

This is only applicable if a charset is being used and it isn't "unicode" (or "broken-unicode").