Understanding the SQL Dialect of the Logging System

The use of SQL provides a flexible and expressive way to locate messages of interest within the Host Integrator logging system. This topic defines the dialect of SQL supported by the Host Integrator logging system.

Functional Specification

Host Integrator supports the SQL SELECT statement and its associated WHERE clause. Nested conditional expressions in the WHERE clause are supported, using the following relational and logical operators:

AND, OR, NOT, =, <>, >=, <=, LIKE

As an example take the following SQL statement:

select msgtime, severity, msgtext from messages where sessionid = 45 and msgtime < timestamp ‘2001-09-10 10:00:00’

This statement returns a result set of three columns, the message time, its severity, and the text of the message itself, in that order, for all messages whose session identifier was 45 that occurred prior to 10 am, September 10, 2001. Many more queries are possible.

The logging system contains a single table named “messages”. This table contains the following columns:

Column Name Type Description
msgserial INTEGER Message primary key
msgtime TIMESTAMP Date/Time of message
msgid INTEGER Numeric message identifier
sessionid INTEGER Session identifier
requestid INTEGER User request identifier
cltaddr VARCHAR User's network name
user VARCHAR User's name
model VARCHAR Model or pool name
msgtext VARCHAR Text of the message
severity VARCHAR Message severity

Ordinarily, you will be comparing column names to literal values in our WHERE clauses, so conditional expression phases will look something like the following:

Expression Description

Expression Description
colname <&234 Compares a column to a numeric literal
colname = ‘Some Text’ Compares a column to a character literal
colname > TIMESTAMP ‘2001-08-12 22:12:45’ Compares a column to a TIMESTAMP literal

It is important to compare similar types to avoid a type mismatch error. Also note that if a character literal contains a single quote, as in ’Let’s’, it is escaped by a preceding single quote.

The LIKE operator performs simple pattern matching on character columns in accordance with the SQL standard. The pattern is specified as a character literal. The characters “_” and “%” have special meaning. A “_” matches any single character while a “%” matches zero to any number of characters. If you want to match a “_” or a “%” literally, you must specify an escape character using escape and use it in the expression. For example:

Expression Result
msgtext LIKE ‘entity’ Matches “entity” exactly
msgtext LIKE ‘entity%’ Matches entries that start with “entity”
msgtext LIKE ‘%entity%’ Matches entries that contain “entity”
msgtext LIKE ‘___ entity% Matches entries that start with “(any 3 letters) entity”
msgtext LIKE ‘%entity%error%’ Matches entries that contain “entity” before “error”
msgtext LIKE ‘%&%%’ ESCAPE ‘&’ Matches entries that contain “%”

Additional Example Statements

Below are several examples of supported statements. For a formal definition of what is supported, see the section: BNF Specification.

Statement Result
select * from messages where msgtime > timestamp ‘2001-23-08 10:00:00’ Returns all columns, in the default order, of any messages that occurred after 10:00 am, August 23, 2001.
select msgtime, severity, msgtext from messages where sessionid = 23 and user = ‘ralf’ Returns columns msgtime, severity, and msgtext, in that order, of any messages that originated from session 23 under the control of “ralf”.
select msgtime, severity, msgtext from messages where sessionid = 12 and msgtext like ‘%session%’ Returns columns msgtime, severity, and msgtext, in that order, of any messages that originated from session 12 and whose msgtext column contains the string “session” anywhere within it.
select cltaddr, msgtext from messages where sessionid = 45 and (cltaddr like ‘150.123%’ or cltaddr = ‘grumpy’) Returns columns cltaddr and msgtext in that order, of any messages that originated from session 45 and whose cltaddr column starts with “150.123” or whose cltaddr is “grumpy”.

BNF Specification

This section provides a detailed description of the supported grammar in BNF. (it all starts with 'select-exp').
select-exp
    ::=
   SELECT select-item-commalist FROM table-ref
		  [ WHERE cond-exp ]

select-item-commalist
    ::=   select-item [ , select-item-commalist ]

select-item
    ::=   column-ref 
        | *

table-ref
    ::=   messages

cond-exp
    ::=   cond-term
        | cond-exp OR cond-term

cond-term
    ::=	  cond-factor
        | cond-term AND cond-factor

cond-factor
    ::=  [ NOT ] cond-primary

cond-primary
    ::=   simple-cond
        | ( cond-exp )

simple-cond
    ::=  comparison-cond
        | like-cond

comparison-cond
    ::=   scalar-exp comparison-oper scalar-exp

like-cond
    ::=   char-string-exp LIKE char-string-exp [ ESCAPE char-string-exp ]

scalar-exp
    ::=   numeric-exp
        | char-string-exp
        | datetime-exp

comparison-operator
    ::=   =
        | <
        | >=
        | >
        | <=
        | <>

numeric-exp
    ::=   numeric-primary

numeric-primary
    ::=   column-ref
        | numeric-literal

char-string-exp
    ::=   character-string-primary

character-string-primary
    ::=   column-ref
        | character-literal

datetime-exp
    ::= datetime-primary

datetime-primary
    ::=   column-ref
        | timestamp-literal

timestamp-literal
    ::=  TIMESTAMP character-literal
Related Topics
Bullet Building Queries
Bullet Setting Logging Properties