SQL Dialect of the Host Integrator Logging System

Overview

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. This syntax applies to the following utilities:

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 will return 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

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.

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 wish to match a "_" or a "%" literally, you must specify an escape character using escape and use it in the expression. Thus, the following examples:

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 "%".

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".

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

Return to top