The Formatting Escapes of dbCGI

Contents

Introduction

% substitutions apply throughout a dbCGI input file.

Values received after a question mark in the URL are split into arguments at the '+' character (normally used to represent the space character). You can still represent the space character using its hexadecimal representation - %20. (Note that this % character is not being used for formatting, it is part of the URL).

Form variables received via the POST method are decoded. Note that variables received by the GET method are not, so you must use the POST method for forms.

These arguments and variables can be substituted in the HTML, or in commands issued to the database. Further, the results of queries issued to the database can be formatted into HTML.

The formatting escapes provide a powerful mechanism for presenting the output of queries in any way desired, and for providing forms which can update the database.

Inserting the percent character - %%

Because the % character is used as the formatting escape character, you need to double it up to insert a literal % in the results.

Example:

We aim to capture 80%% of the world market for wigwams for geese bridles before January 1997.

The current process ID - %$

The current process ID is usually used to generate unique file names for the %[@filename%] formatting escape.

Example:

This dbCGI query is being run as process %$

Unique sequence numbers - %#

The sequence number is a unique number which starts at 0 when the dbCGI process starts. Every time an output record is formatted, it is increased by 1. The primary use of this is to generate unique file names for the %[@filename%] escape.

Example:

<sql format> This is record number %# output since I started on your request </sql>

Output columns - %nd

The format for this escape is really: %w.p:nd, where w is the field width, p is the precision and n is the column number in the ouput record, starting at 1.

For all column types, w specifies the minimum number of characters that should be used to display the column. If fewer characters are used, the column will be padded with spaces on the left for numeric columns, and on the right for other columns. It is only useful to specify a width in <PRE> and <XMP> sections.

For numeric column types, p specifies the number of decimal places to be displayed. For other columns, it specifies the maximum number of characters of data to be displayed. If no precision is supplied, a default is chosen based on information supplied by the DBMS.

You can omit the width and/or precision as follows:

This escape should only be used inside the sql format command.

Example: Output columns 2, then 3, then 1, with column 1 displayed to 2 decimal places.

<sql format> %2d %3d %.2:1d<BR> </sql>

Output column headings - %nh

n is the column number, starting at 1.

This escape displays the column name for the specified column of the output record. It should only be used in the sql format or sql headings commands.

Example: display the column headings for column 2, then 3, then 1.

<sql headings> %2h %3h %1h </sql>

QUERY_STRING arguments - %na

n is the argument number, starting at 1.

Arguments to a dbCGI script are received after a question mark ('?') in the URL used to access the script. dbCGI divides the string into arguments at '+' characters. These are normally used to represent the space character. Literal '+' and space characters can be supplied within an argument using their hexadecimal representation (%20 for space and %2B for '+').

Thus the following URL:

http://host.name/sybdbcgi/test.sql?abc%20123+def%2Bghi+jklmn contains three arguments: %1a=abc 123 %2a=def+ghi %3a=jklmn Example: substitute the values of arguments 1, 2 and 3 into an SQL query <sql query conn1> SELECT * FROM customer WHERE first_name = %2a AND last_name = %3a AND title = %1a </sql>

The error message text - %e, %c and %n

These escapes should only be used inside sql error commands. They substitute to the error message text, the command that caused the error, and the error number.

Example:

<sql error> Error '%e' (number %n) occurred in <STRONG>%c</STRONG><BR> </sql>

The value that caused the validation failure - %v

This should only be used in the FORMAT name of the sql valarg and sql valform commands.

<sql valform ZIPCODE> RANGE=0123456789- FORMAT=The value of %v is not a valid ZIP code. </sql>

Escaping strange characters - %{text%}

This escape is usually used in hypertext links to convert arguments or output from the database into a form usable as arguments to another dbCGI script.

Text contained in this escape has all characters other than alphanumerics converted to their hexadecimal representation.

Example: Insert a hypertext link where we pass argument 1 through the link, and include column 3 as argument 2. Label the link with the text from column 5.

<sql format> <A HREF="nextlev.sql?%{%1a%}+%{%3d%}">%5d</A> </sql>

Including text only if a result column is not null - %n(text%)

n is the column number being tested.

This escape should only be used in sql format commands. The text contained between %n( and %) is only included if the value in the specified column is not null.

Example: only include the heading 'Known Aliases' if field 5 is not null.

<sql format> <H1>Description of %3d %2d</H1> Height: %1d Sex: %4d %5( <H2>Known aliases</H2> %5d %) </sql>

Suppressing repeated values - %[!n1,n2,n3,...:text%]

n1, n2, n3 and so on are column numbers.

This escape should only be used in sql format commands. The enclosed text is only displayed if the value of one of the listed columns has changed since the previous record. This is used to generate a "report break" effect.

Example: columns 1, 2, and 3 are being used as headings, so they should only be displayed when they change, but we want column 2 to be displayed if column 1 is displayed, and column 3 to be displayed if column 2 has been displayed:

<sql format> %[!1:<H1>Country: %1d</H1>%] %[!1,2:<H2>State: %2d</H2>%] %[!1,2,3:<H3>City: %3d</H2>%] %4d %5d<BR> </sql>

Storing data to a file - %n[@filename%]

n is the column number of a character or binary data column.

This escape should only be used in sql format commands. The specified column must be either binary (BLOB) data or character data. The contents of the column are stored in the named file. You can then put references to that file in your output HTML.

Example: Display the GIF image contained in the record, with the caption underneath.

<sql format> %1[@/usr/local/htdocs/temp/pic%$_%#.gif%] <IMG SRC="/temp/pic%$_%#.gif" HEIGHT=%2d WIDTH=%3d><BR> Caption: %4d </sql>

Executing a shell command - %[|command%]

The primary use of shell commands is for performing manipulations on files saved. You should use this escape with caution - including arguments or form variables may result in security violations.

Example: if your DBMS does not support BLOBs, but does support long text fields, store the data in uuencoded format, and have a script which converts the uuencoded data.

<sql format> %1[@/usr/local/htdocs/temp/pic%$_%#.uu%] %[|/usr/local/bin/uudecode_to /usr/local/htdocs/temp pic%$_%#.uu pic%$_%#.gif%] %[|rm -s /usr/local/htdocs/temp/pic%$_%#.uu%] <IMG SRC="/temp/pic%$_%#.gif" HEIGHT=%2d WIDTH=%3d><BR> Caption: %4d </sql>

Doubling up special characters - %['text%], %["text%] and %[\text%]

These escapes are primarily useful for converting string data to a format which is usable in SQL. They cause single quotes, double quotes, and back slashes respectively which occur in the input to be copied to the output twice.

Example: Use argument 1 to condition the value of a character field:

<sql query conn1> SELECT * FROM customer WHERE last_name = '%['%1a%]' </sql>

Prevent HTML reformatting - %[-text%]

When dbCGI outputs character values it performs the following reformatting to ensure that valid HTML is produced:

This escape prevents this reformatting. The only time this escape is useful is in <PRE> and <XMP> sections.

Example: Output argument 1 in a <PRE> section.

The value of argument 1 is: <PRE> %[-%1a%] </PRE>

Substitute the value of a form variable - %[=var%]

This escape substitutes the value of a form variable received via the POST method. If the form variable was not received, nothing is substituted. An extension to this escape is %[=var:text%], in which the text is substituted if the named form variable was not received.

Example:

<sql query conn1> SELECT town_name FROM zip_codes WHERE zip_code = '%[=ZIPCODE:90000%]' </sql>

Substitute text if a form variable was received - %[?var:text%]

This escape substitutes the text if the named form variable was received.

Example: Only include the WHERE clause if the ZIPCODE variable was received.

<sql query conn1> SELECT town_name FROM zip_codes %[?ZIPCODE:WHERE zip_code = '%[=ZIPCODE%]%]' </sql>

Substitute text if a form variable was not received - %[~var:text%]

This escape substitutes the text if the named form variable was not received.

Example: if the ZIPCODE variable was not received, include the zip_code field in the query.

<sql query conn1> SELECT town_name %[~ZIPCODE:,zip_code%] FROM zip_codes %[?ZIPCODE:WHERE zip_code = '%[=ZIPCODE%]%]' </sql>

Set the value of a form variable - %[*var:text%]

This escape sets the value of the named form variable to the value supplied. The variable can then be used as if it had been received from the user.

Example: The table "SEQUENCE" contains one record, with one field - seqno. This example increments the value of seqno, then places the new value in the form variable "SEQNO" for use in creating a unique ID in a later INSERT statement.

<sql execute conn1> UPDATE SEQUENCE SET seqno = seqno + 1 </sql> <sql format> %[*SEQNO:%1d%] </sql> <sql query conn1> SELECT seqno FROM SEQUENCE </sql>