Back to Contents

Static SQL Statements

Summary:

See also: Transactions, Positioned Updates, Dynamic SQL, Result Sets, SQL Errors.

What are Static SQL Statements?

Static SQL Statements are SQL instructions that are a part of the language syntax. Static SQL Statements can be used directly in the source code as a normal procedural instruction. The static SQL statements are parsed and validated at compile time. At runtime, these SQL statements are automatically prepared and executed by the runtime system.

Program variables are detected by the compiler and handled as SQL parameters.

The following example defines two variables that are directly used in an INSERT statement:

01 MAIN
02    DEFINE iref INTEGER, name CHAR(10)
03    DATABASE stock
04    LET iref = 65345
05    LET name = "Kartopia"
06    INSERT INTO item ( item_ref, item_name ) VALUES ( iref, name )
07    SELECT item_name INTO name
08      FROM item WHERE item_ref = iref
09 END MAIN

Warning: Make sure that a column name does not have the same name as a program variable, otherwise the program variable will be used: 

01 MAIN
02    DEFINE cust_name CHAR(10)
03    DATABASE stock
04    SELECT cust_name INTO cust_name
05      FROM customer WHERE item_ref = 1
06 END MAIN

Using Static SQL Statements clarifies the source code (you do not need to use Dynamic SQL Instructions to prepare and execute the SQL statement), but you cannot modify the SQL text at runtime.

A limited number of SQL statements (see below) is directly supported in the language, however most common statements like INSERT, UPDATE, DELETE, SELECT can be executed without problems using a simple standard syntax. 

Supported Static SQL Statements

The following table shows all SQL statements supported by the language as Static SQL Statements.

Informix only! Nearly all of these statements are specific to IBM Informix SQL language. These are supported for backward compatibility with the IBM Informix 4GL compiler, and it is not recommended that you use these statements in your programs if you want to write portable SQL. However, statements marked with a green rectangle in the 'P' column can be used, as long as you use standard SQL syntax.

SQL Statement P Description
ALTER INDEX ...   Modify the structure of an index.
ALTER TABLE ... C Modify the structure of a table.
CREATE AUDIT ...   Create audit recording for a given table.
CREATE DATABASE ...   Create a database.
CREATE INDEX ... C Create an index.
CREATE TABLE ...   Create a table.
CREATE SYNONYM ...   Create a synonym for a database table or view.
CREATE TEMP TABLE ...   Create a temporary table.
CREATE VIEW ...   Create a view.
DELETE FROM ...   Delete rows in a table.
DROP AUDIT ...   Remove audit for a given table.
DROP INDEX ...   Delete an index.
DROP SYNONYM ...   Delete a table or view synonym.
DROP TABLE ...   Delete a table.
DROP VIEW ...   Delete a view.
GRANT ...   Grant access rights.
INSERT INTO ...   Insert rows into a table.
RECOVER TABLE ...   Re-build an SE database table from log files.
RENAME COLUMN ...   Rename a table column.
RENAME TABLE ...   Rename a table.
REVOKE ...   Revoke access rights.
ROLLFORWARD DATABASE ...   Put an SE database in a safe state.
SELECT ...   Select rows from a table.
SELECT ... INTO TEMP ttab   Create a temporary table from a result set.
SET EXPLAIN ...   Enable/disable query execution plan trace.
SET LOG ...   Set the logging of an On-line database.
START DATABASE ...   Initialize an SE database.
UPDATE table ...   Update rows in a table.
UPDATE STATISTICS ...   Collect statistics information for the query optimizer.

INSERT

Purpose:

Inserts a row in a table in the current database session.

Syntax:

INSERT INTO table-specification [ ( column [,...] ) ]
{
  VALUES ( { variable | literal | NULL } [,...] )
|
  select-statement
}

where table-specification is:

[dbname[@dbserver]:][owner.]table

Notes:

  1. dbname identifies the database name. Informix only!
  2. dbserver identifies the Informix database server (INFORMIXSERVER). Informix only!
  3. owner identifies the owner of the table, with optional double quotes. Informix only!
  4. table is the name of the database table.
  5. column is a name of a table column.
  6. variable is a program variable, a record or an array used as a parameter buffer to provide values.
  7. When using records, you can specify all record members with the star notation (rec.*).
  8. literal is any literal expression supported by the language.
  9. select-statement is a static SELECT statement with or without parameters as variables.
  10. When you use the VALUES clause, the statement inserts a row in the table with the values specified in variables, as literals, or with NULL.
  11. When you use a select-statement, the statement inserts all rows returned in the result set of the SELECT statement.

Warnings:

  1. For portability, it is not recommended that you use the select-statement syntax.
  2. When a select-statement is used, the columns returned by the result set must match the column number and data types of the target table.

Example:

01 MAIN
02    DEFINE myrec RECORD
03             key INTEGER,
04             name CHAR(10),
05             cdate DATE,
06             comment VARCHAR(50)
07          END RECORD
08    DATABASE stock
09    LET myrec.key     = 123
10    LET myrec.name    = "Katos"
11    LET myrec.cdate   = TODAY
12    LET myrec.comment = "xxxxxx"
13    INSERT INTO items VALUES ( 123, 'Practal', NULL, myrec.comment )
14    INSERT INTO items VALUES myrec.*
15    INSERT INTO items SELECT * FROM histitems WHERE name = myrec.name
16 END MAIN

UPDATE

Purpose:

Updates rows of a table in the current database session.

Syntax 1:

UPDATE table-specification
   SET
       column = { variable | literal | NULL }
       [,...]
   [ WHERE { condition | CURRENT OF cursor } ]

Syntax 2: Informix only!

UPDATE table-specification
   SET { ( [table.]* ) | ( column [,...] ) }
     = ( { variable | literal | NULL } [,...] )
   [ WHERE { condition | CURRENT OF cursor } ]

where table-specification is:

[dbname[@dbserver]:][owner.]table

Notes:

  1. dbname identifies the database name. Informix only!
  2. dbserver identifies the Informix database server (INFORMIXSERVER). Informix only!
  3. owner identifies the owner of the table, with optional double quotes. Informix only!
  4. table is the name of the database table.
  5. column is a name of a table column.
  6. column can be specified with a sub-script expression (column[a,b]). Informix only!
  7. variable is a program variable, a record or an array used as a parameter buffer to provide values.
  8. literal is any literal expression supported by the language.
  9. condition is an SQL expression to select the rows to be updated.
  10. cursor is the identifier of a database cursor.
  11. For more details about the WHERE CURRENT OF clause, see Positioned Updates.

Warnings:

  1. column with a sub-script expression (column[a,b]), is not recommended because most database servers do not support this notation.
  2. Although a few database servers support Syntax 2, it is strongly recommended that you use Syntax 1 only.

Example:

01 MAIN
02    DEFINE myrec RECORD
03             key INTEGER,
04             name CHAR(10),
05             cdate DATE,
06             comment VARCHAR(50)
07          END RECORD
08    DATABASE stock
09    LET myrec.key     = 123
10    LET myrec.name    = "Katos"
11    LET myrec.cdate   = TODAY
12    LET myrec.comment = "xxxxxx"
13    UPDATE items SET
14         name    = myrec.name,
15         cdate   = myrec.cdate,
16         comment = myrec.comment
17     WHERE key = myrec.key
18 END MAIN

DELETE

Purpose:

Deletes rows from a table in the current database session.

Syntax:

DELETE FROM table-specification
   [ WHERE { condition | CURRENT OF cursor } ]

where table-specification is:

[dbname[@dbserver]:][owner.]table

Notes:

  1. dbname identifies the database name. Informix only!
  2. dbserver identifies the Informix database server (INFORMIXSERVER). Informix only!
  3. owner identifies the owner of the table, with optional double quotes. Informix only!
  4. table is the name of the database table.
  5. condition is an SQL expression to select the rows to be deleted.
  6. cursor is the identifier of a database cursor.
  7. For more details about the WHERE CURRENT OF clause, see Positioned Updates.

Warnings:

  1. If you do not specify the WHERE clause, all rows in the table will be deleted.

Example:

01 MAIN
02    DATABASE stock
03    DELETE FROM items WHERE name LIKE 'A%'
04 END MAIN

SELECT

Purpose:

Selects rows from one or more tables in the current database session.

Syntax:

select-statement [ UNION [ALL] select-statement ] [...]

where select-statement is:

SELECT [{ALL|DISTINCT|UNIQUE}] { * | select-list }
  [ INTO variable [,...] ]
  FROM table-list [,...]
  [ WHERE condition ]
  [ GROUP BY column-list [ HAVING condition ] ]
  [ ORDER BY column [{ASC|DESC}] [,...] ] 

where select-list is:

{ [@]table-specification.*
| [table-specification.]column
| literal
} [ [AS] column-alias ]
[,...]

where table-list is:

{ table-name
| OUTER table-name
| OUTER ( table-name [,...] )
}
[,...]

where table-name is:

table-specification [ [AS] table-alias ]

where table-specification is:

[dbname[@dbserver]:][owner.]table

where column-list is:

column-name [,...] 

where column-name is:

[table.]column

Notes:

  1. dbname identifies the database name. Informix only!
  2. dbserver identifies the Informix database server (INFORMIXSERVER). Informix only!
  3. owner identifies the owner of the table, with optional double quotes. Informix only!
  4. table is the name of the database table.
  5. table-alias defines a new name to reference the table in the rest of the statement.
  6. column is a name of a table column.
  7. column-alias defines a new name to reference the column in the rest of the statement.
  8. condition is an SQL expression to select the rows to be deleted.
  9. The INTO clause provides the list of fetch buffers. This clause is not part of the SQL language sent to the database server; it is extracted from the statement by the compiler.

Warnings:

  1. The language supports the SELECT INTO TEMP statement to create temporary tables (this statement does not return a result set).

Usage:

If the SELECT statement returns only one row of data, you can write it directly as a procedural instruction. However, you must use the INTO clause to provide the list of variables where column values will be fetched: 

01 MAIN
02    DEFINE myrec RECORD
03             key INTEGER,
04             name CHAR(10),
05             cdate DATE,
06             comment VARCHAR(50)
07          END RECORD
08    DATABASE stock
09    LET myrec.key = 123
10    SELECT name, cdate
11      INTO myrec.name, myrec.cdate
12      FROM items
13      WHERE key=myrec.key
14 END MAIN

If the SELECT statement returns more than one row of data, you must declare a database cursor to process the result set:

01 MAIN
02    DEFINE myrec RECORD
03             key INTEGER,
04             name CHAR(10),
05             cdate DATE,
06             comment VARCHAR(50)
07          END RECORD
08    DATABASE stock
09    LET myrec.key = 123
10    DECLARE c1 CURSOR FOR
11      SELECT name, cdate
12        FROM items
13        WHERE key=myrec.key
14    OPEN c1
15    FETCH c1 INTO myrec.name, myrec.cdate
16    CLOSE c1
17 END MAIN

The SELECT statement can include the INTO clause, but it is strongly recommended that you use that clause in the FETCH instruction only.

See Result Sets Processing for more details.