File format

In the simplest case a dbCGI file is an HTML file which has dbCGI specific directives and formatting instructions embedded in it. In the more complex case a dbCGI file may have dbCGI directives be dominant, with only small amounts of HTML included.

dbCGI directives are expressed using an HTML-like syntax where the directive is introduced by <sql ...>, followed by optional content and then </sql>. Their contents may be HTML, an SQL command, or something else specific to the type of directive. Directives exist to open and close connections to the database, execute SQL queries and commands, format the output of SQL queries, perform arithmetic, and assignments to variables, and to generate loops and conditional code sections.

Formatting escapes are introduced by the "%" character. They are normally used to cause some computed value to be inserted into the HTML at that point, although they can be used for other purposes. Values inserted by a formatting escape include variable values and data returned by an SQL query.

Below is the layout of a sample dbCGI file for Informix.

<html>

  <head>
    <title>Kawseq dbCGI for Informix Test</title>
  </head>

  <body>

    <!-- Initialise the environment -->
    <sql init>
      INFORMIXDIR=/usr/informix
      SQLEXEC=/usr/informix/lib/sqlturbo
      TBCONFIG=tbconfig
    </sql>

    <!-- Connect to the database -->
    <sql connect conn1>
      DATABASE=stores
    </sql>

    <!-- State how to format the query output -->
    <sql format>

       <!-- Display a heading for the state field (the
            8th field in the table) if it has changed
            since it was last displayed.
       -->

       %[!8:<h1>%8d</h1>%]
       
         <!-- Display a heading for the city field (the
              7th field in the table) if either it or
              the State field has changed since it was
              last displayed.
         -->

         %[!7,8:<h2>%7d</h2>%]

           <!-- Display a heading for the zipcode field
                (the 9th field in the table) if any of
                it, the city field or the State field
                has changed since they were last
                displayed
           -->

           %[!7,8,9:<h3>%9d</h3>%]

             <!-- Always display the customer name as a
                  level 4 heading
             -->
             <h4>%4d</h4>

             <p>
               %3d %2d<br />
               %5d<br />
               <!-- Only display a line for field 6 if
                    it it not null
               -->
               %6(%6d<br />%)
               %10d<br />
             </p>
    </sql>

    <!-- And execute the SQL query -->
    <sql query conn1>
       SELECT
              *
       FROM
              customer
       ORDER BY
              state,
              city,
              zipcode
    </sql>

    <!-- Then disconnect from the database -->
    <sql disconnect conn1 />

    <!-- Clean up after ourselves -->
    <sql uninit />
  </body>
</html>

The query above accesses the customers table from the Informix sales demonstration database and prints a list with breaks by state, city and zipcode (columns 8, 7 and 9). Column 6, which contains an optional second address line, is only displayed if it is populated.