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 beingused for formatting, it is part of the URL).

Form variables received via the POST method are decoded. Note that variablesreceived 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 commandsissued to the database. Further, the results of queries issued to the databasecan be formatted into HTML.

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

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 forthe %[@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 processstarts. Every time an output record is formatted, it is increased by 1. The primaryuse 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 theprecision and n is the column number in the ouput record,starting at 1.

For all column types, w specifies the minimum number ofcharacters 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 rightfor other columns. It is only useful to specify a width in <PRE> and <XMP>sections.

For numeric column types, p specifies the number ofdecimal places to be displayed. For other columns, it specifies the maximum numberof characters of data to be displayed. If no precision is supplied, a default ischosen 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 headingscommands.

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 ('?') inthe 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+jklmncontains three arguments: %1a=abc 123 %2a=def+ghi %3a=jklmnExample: 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 andsql 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 thedatabase into a form usable as arguments to another dbCGI script.

Text contained in this escape has all characters other than alphanumerics convertedto 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 thespecified 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, n3and so on are column numbers.

This escape should only be used in sql format commands. The enclosed text isonly 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 displayedwhen they change, but we want column 2 to be displayed if column 1 is displayed, and column3 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 columnmust be either binary (BLOB) data or character data. The contents of the column are stored inthe 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. Youshould use this escape with caution - including arguments or form variables may resultin security violations.

Example: if your DBMS does not support BLOBs, but does support long text fields, storethe 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 inSQL. They cause single quotes, double quotes, and back slashes respectively which occur in theinput 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 ensurethat 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 thisescape is %[=var:text%], in which the text is substituted if thenamed 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>