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

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 is directly supported in the language (see below), but most common statements like INSERT, UPDATE, DELETE, SELECT can be executed without problems using a simple standard syntax. 

Using program variables in Static SQL statements

The syntax of Static SQL statements supports the usage of program variables directly as SQL parameters. This gives a better understanding of the source code and requires less lines as using SQL parameters with Dynamic SQL: 

01 MAIN
02    DEFINE c_num INTEGER
03    DEFINE c_name CHAR(10)
04    DATABASE stock
05    SELECT cust_name INTO c_name FROM customer WHERE cust_num = c_num
06 END MAIN

If a database column name conflicts with a program variable, you can use the @ sign as the column prefix. The compiler will treat the identifier following the @ as a table column:

01 MAIN
02    DEFINE cust_name CHAR(10)
03    DEFINE cnt INTEGER
04    DATABASE stock
05    SELECT COUNT(*) INTO cnt FROM customer WHERE @cust_name = cust_name
06 END MAIN

The @ sign will not figure in the resulting SQL statement stored in the 42m module.

Table and column names in Static SQL

In Static SQL, table and column names will be converted to lowercase by the fglcomp compiler. The SQL keywords are always converted to uppercase.

For example:

01  UPDATE CUSTOMER set CUST_name = 'undef' WHERE cust_name is null

will be converted to:

UPDATE customer SET cust_name = 'undef' WHERE cust_name IS NULL

While SQL keywords are not case sensitive for database servers, table names and column names can be case-sensitive.

For more details, see Naming database objects. 

What SQL string was generated by the compiler?

As described in the above sections, the fglcomp compiler parses the Static SQL statements and modifies them before writing the resulting SQL text to the 42m module.

You can extract all SQL statements from the source by using the -S option of fglcomp:

01 MAIN
02    DEFINE c_name CHAR(10)
03    DEFINE cnt INTEGER
04    DATABASE stock
05    SELECT COUNT(*) INTO cnt FROM customer WHERE customer.cust_name = c_name
06 END MAIN
$ fglcomp -S test.4gl
test.4gl^5^SELECT COUNT(*) FROM customer WHERE cust_name = ?

Supported Static SQL Statements

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

Lines marked with a pink background show SQL statements that 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 them in your programs if you want to write portable SQL. Other statements can be used, as long as you use standard SQL syntax.

SQL Statement Description
ALTER INDEX ... Modify the definition of an index.
ALTER TABLE ... Modify the definition of a table.
ALTER SEQUENCE ... Modify the definition of a sequence.
CREATE AUDIT ... Create audit recording for a given table.
CREATE DATABASE ... Create a database.
CREATE INDEX ... Create an index.
CREATE TABLE ... Create a table.
CREATE SEQUENCE ... Create a sequence.
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 SEQUENCE ... Delete a sequence.
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 INDEX ... Rename an index.
RENAME SEQUENCE ... Rename a sequence.
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.
TRUNCATE TABLE table ... Cleanup a table without logging changes (no rollback possible)
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 you use 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 insert 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 you use a select-statement, 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.