Navigating the Sigma Web Framework SQL interface

This section provides a description of the Sigma SQL Interface, which is a simple but comprehensive mechanism for executing SQL statements on ODBC compatible databases, and optionally displaying the results of these queries in Web pages.

The RunSQL.exe program resides in the "system" directory of all Sigma Framework implementations. The program accepts as its first argument the name of a configuration file, whose contents are documented here.

The RunSQL.exe program can be executed interactively, at a command prompt, but is typically executed by either the Sigma "Macro" capability, or by a ".cmd" file. The user can define the SQL query to be executed within the configuration file, or can specify the query on the command line, after the configuration file pathname.

Prior to executing the RunSQL.exe program, the user must first configure an ODBC "Data Source Name" (DSN) in the "Control Panel > Admin Tools > Data Sources" dialog of the Windows platform. (This will require the user to log into the host Windows platform with an Administrator type login.)

Once a DSN is defined, the DSN name is specified in the configuration file, along with other directives, such as a "username", "password", "usedb", "maxresults", "listfmt", and "sqlstmt" values, described here.

If the RunSQL.exe program generates output (such as by running an SQL "select" statement) this output can be formatted as text, raw html, or xml, to support various implementation strategies.

This section contains information about the following topics:

Various other techniques can be used to interface SQL to Sigma. For example, a perfectly suitable way of updating HTML files with database query results is to specify a "listfmt" of "text", and then creating a batch file that creates the ".cnf" files within the "sigma-web" directory, as discussed in Navigating-the-Sigma-Web-Framework-interface. The batch files can be scheduled on an hourly, daily, or weekly basis using the "Scheduler" function of Sigma. This is an especially useful technique if there is some database value that takes a long time to derive, such as counting up distinct values spanning many different rows.

To debug the system, an administrator can use the System > ODBC Sigma screen. For more information, see Sigma-Web-Framework-ODBC-tab. This screen can be used to perfect and refine SQL statements interactively. The RunSQL command can also be executed at a command prompt to immediately see the output of the SQL statements, including any error output.

As with many of the Sigma facilities, the exact nature of the application will dictate the form of the usage. Ample information on standard SQL can be found in a variety of locations, including the web. Contact the vendor for specific assistance.