SQL Syntax

Host Integrator supports a subset of the SQL 92 standard for SELECT, UPDATE, INSERT, and DELETE statements. This section describes the syntax convention that Host Integrator supports.

Case Sensitivity

The SQL-92 language standard requires that the names of objects be compared without regard to letter case. Comparisons between column values, however, are case sensitive by default.

If you do not see the results you expect because of case sensitivity, you can add COLLATE CASE_INSENSITIVE to explicitly specify a case-insensitive comparison of text column values. In this example, the state value ('ri') will be compared without case:

SELECT Name, ContractDate, AcctNumber FROM Accounts WHERE MiddleInitial = 'c' AND 
State = 'ri' COLLATE CASE_INSENSITIVE AND LastName = 'smith' 

Note: SQL keywords such as JOIN or ORDERBY are recognized by Host Integrator, but not supported. You can extend Host Integrator's native SQL support using an event handler. For example, you could do the following in an event handler:

  1. Pass in an SQL string.
  2. Remove the portions not supported by Verastream.
  3. Pass the remaining SQL to the model for processing.
  4. Modify the results based on the custom extensions from the original client string.

Table and Column Names

Table and column names in Host Integrator are identified using the following convention:


TableName=Identifier
ColumnName=Identifier
Identifier=RegularIdentifier | DelimitedIdentifier


A regular identifier is a string of not more than 128 characters; the first character must be a letter (upper or lower case), while the rest can be any combination of upper or lower case letters, digits, and the underscore character. No SQL reserved words can be used.


A delimited identifier is any string of not more than 128 characters enclosed in double quotes. The double quote character is represented by two immediately adjacent double quotes.


Table and column names are not case sensitive.

Literals

Literal={CharacterString | Number }


Character strings are written as a sequence of characters enclosed in single quotes. A single quote character is represented by two immediately adjacent single quotes. Any comparisons between literals and columns must be between the same type: strings can only be compared to strings and numbers can only be compared to numbers.

Expressions

Expression = { Expression + Expression | Expression - Expression | Expression * Expression | Expression / Expression | - Expression | ( Expression ) | Literal | ColumnName }


Conditions

Condition = { Condition OR Condition | Condition AND Condition | NOT Condition | (Condition) | Comparison }

Comparison = Expression { = | <> | < | <= | > | >= | LIKE } Expression

SimpleCondition = { SimpleCondition OR SimpleCondition | SimpleCondition AND SimpleCondition | (SimpleCondition) | SimpleComparison)

SimpleComparison = ColumnName { = | LIKE } Literal


A distinction is made between Conditions and SimpleConditions because only SimpleConditions can be used as inputs to a procedure. SimpleConditions can be used in any WHERE clause, but Conditions may be used only in a SELECT statement's WHERE clause because the results can be filtered. Both Conditions and SimpleConditions can refer only to columns from one table. Joins and subqueries (SELECT statements inside another SQL statement) are not supported.

Note: In the examples for the SELECT, UPDATE, INSERT, and DELETE statements assume
that the SQL statements used with a model can resolve to a procedure in the model.

SELECT Statement Syntax

Use the following syntax for SELECT statements (Arguments between ([ ]) are optional and those between ({ }) are required:


SELECT [DISTINCT] {column-list} FROM {table} [WHERE {condition}] [ORDER BY {column-list}]


Arguments:

[DISTINCT] -- Specifies that all rows returned be unique. If there are two identical rows, one is removed from the output.

{column-list} -- Any valid table column name(s).

{table} -- The name of the table.

{condition} -- Any condition or simple condition.

SELECT statements return only those rows exactly matching the WHERE clause. The results are sorted in the order specified in the ORDER BY clause. For procedure resolution, the table name is taken from the FROM clause, the filter parameters are taken from the WHERE clause, and the outputs are taken from the SELECT and WHERE clauses.

Examples:

 SELECT * FROM Patients WHERE AdmitNum = 20000
 SELECT AdmitNum, SSN FROM Patients WHERE LastName LIKE 'W'
 SELECT DISTINCT AdmitNum, SSN FROM Patients WHERE LastName LIKE 'W'
 SELECT * FROM Patients WHERE LastName LIKE 'W' ORDER BY AdmitNum
 SELECT * FROM Patients WHERE LastName LIKE 'W' AND FirstName = 'JOHN' ORDER BY  AdmitNum
 

UPDATE Statement Syntax

Use the following syntax for UPDATE statements:


UPDATE {table} SET {{column} = {value} [, ...]} [WHERE {simple-condition}]

Arguments:

{table} -- The name of the table.

{column} -- Any valid table column.

{value} -- Valid values are characters and numbers.

{Simple-condition} -- Simple conditions can contain characters and numbers and comparisons (=, <>, <, <=, >, and =).

UPDATE statements update all records matching the WHERE clause with the values in the SET clause. For procedure resolution, the table name is taken from the UPDATE clause, the filter parameters are taken from the WHERE clause, and the data parameters are taken from the SET clause.

Arguments between ([ ]) are optional and those between ({ }) are required.

Example:

UPDATE Patients SET FirstName = 'Colin', LastName = 'Moulding', AdmitYear = 1999  
WHERE AdmitNum = 56564
 

INSERT Statement Syntax

Use the following syntax for INSERT statements:


INSERT INTO {table} [({column-list})] VALUES {value-list}


Arguments:

{table} -- The name of the table.

{Column-list} -- Any valid table column name(s). If no column list is defined, the order of the columns is taken from the table definition.

{values-list} -- Valid values are characters and numbers.

INSERT statements add a record to the specified table. For procedure resolution, the table name is taken from the INSERT INTO clause. Data parameters are taken from the VALUES clause.

Arguments between ([ ]) are optional and those between ({ }) are required.

Examples:

INSERT INTO Patients (AdmitNum, FirstName, LastName, Room) VALUES (31415,    'Doe', 'John', '123')
INSERT INTO Patients (AdmitNum, FirstName, LastName, Room) VALUES (31415, 'Doe',    'John', '123'), (31416, 'Doe', 'Jane', '131')

DELETE Statement Syntax

Use the following syntax for DELETE statements:

DELETE FROM {table} [WHERE {simple-condition}]


Arguments:

{table} -- The name of the table.

{Simple-condition} -- Simple conditions can contain characters, numbers, and comparisons (=, <>, <, <=, >, and >=).

The delete statement deletes a record from the specified table. For procedure resolution, the table name is taken from the DELETE FROM clause and the filters are taken from the WHERE clause.

Arguments between ([ ]) are optional and those between ({ }) are required.

Example:

DELETE FROM Patients WHERE AdmitNum = 31415
   
 

SQL Syntax Restrictions

Host Integrator supports the SQL-92 arguments and features, with the following exceptions:

SELECT Name, ContractDate, AcctNumber FROM Accounts WHERE MiddleInitial = 'c'    
AND State LIKE RI AND LastName = 'smith'

SQL predicate (logical test) restrictions

 

Related Topics
Bullet SQL overview