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.

In addition to the embedded SQL syntax, the Genero compiler supports also SQL Blocks, to let you write SQL statements inside SQL / END SQL keywords.

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 1:

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

Syntax 2: Record-definition-dependent!

INSERT INTO table-specification VALUES ( record.* )

Syntax 3: Schema-dependent, removes serial columns!

INSERT INTO table-specification VALUES record.*

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 member or an array member used as a parameter buffer to provide values.
  7. literal is any literal expression supported by the language.
  8. select-statement is a static SELECT statement with or without parameters as variables.
  9. record is the name of a record (followed by dot star in this syntax).

Usage:

The Static-SQL INSERT statement can be written in three different ways:

  1. Syntax 1: This is the most standard syntax, working with all type of database engines.
  2. Syntax 2: The fglcomp compiler will automatically generate a standard INSERT statement with the complete list of members of the record.
    The generated SQL will depend from the definition of the record.
  3. Syntax 3: This syntax requires a database schema specification (SCHEMA) and the corresponding .sch file.
    If a record member was created from a column defined as SERIAL, SERIAL8 or BIGSERIAL in the schema file, that record member will be removed from the value list, to force the server to generate a new serial value. 

When using the VALUES clause, the statement inserts a row in the table with the values specified in variables, as literals, or with NULL. If a record is available, you can specify all record members with the star notation (record.*).

When you use a select-statement, the statement insert all rows returned in the result set of the SELECT statement.

The syntax #3 can be used to avoid serial column usage in the value list: The record member corresponding to a column defined as SERIAL, SERIAL8 or BIGSERIAL in the schema file will be removed by the compiler. This is useful when using databases like SQL Server, where IDENTITY columns must be omitted in INSERT statements.

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. For SQL portability, it is not recommended that you use the select-statement syntax.

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 VALUES myrec.*  -- without serial (if one is used)
16    INSERT INTO items SELECT * FROM histitems WHERE name = myrec.name
17 END MAIN

UPDATE

Purpose:

Updates rows of a table in the current database session.

Syntax 1:

UPDATE table-specification
   SET
       column = { variable | literal | NULL }
       [,...]
   [ sql-condition ]

Syntax 2:

UPDATE table-specification
   SET ( column [,...] )
     = ( { variable | literal | NULL } [,...] )
   [ sql-condition ]

Syntax 3: Informix only!

UPDATE table-specification
   SET [table.]*
     = ( { variable | literal | NULL } [,...] )
   [ sql-condition ]

Syntax 4: Schema-dependent!

UPDATE table-specification
   SET { [table.]* | ( column [,...] ) }
     = record.*
   [ sql-condition ]

where table-specification is:

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

and sql-condition is:

WHERE { condition | CURRENT OF cursor }

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 member or an array member used as a parameter buffer to provide values.
  8. literal is any literal expression supported by the language.
  9. record is the name of a record (followed by dot star in this syntax).
  10. condition is an SQL expression to select the rows to be updated.
  11. cursor is the identifier of a database cursor.
  12. For more details about the WHERE CURRENT OF clause, see Positioned Updates.

Usage:

The Static-SQL UPDATE statement can be written in three different ways:

  1. Syntax 1: This is the most standard syntax, working with all type of database engines.
  2. Syntax 2: This syntax comes from Informix 4GL, but can also be used with other RDBMS, because the compiler converts the SQL statement to a portable syntax, using standard column assignments ( UPDATE table SET col1 = ?, col2, ?, ... ).
  3. Syntax 3: This syntax is Informix specific, and is not converted by the compiler, so it should be avoided.
  4. Syntax 4: The last syntax requires a database schema specification (SCHEMA) and the corresponding .sch file.
    The fglcomp compiler will automatically extend the SQL text with the columns identified by the record variable.
    The columns defined in the .sch file as SERIAL (code 262) will be omitted in the generated column list.
The third syntax (Syntax 3) should be avoided, since it is Informix specific. 

The fourth syntax (Syntax 4) can be used if the database schema file has been generated with the correct Informix-equivalent data types. This is especially important when using SERIAL columns or equivalent auto-incremented columns.

column with a sub-script expression (column[a,b]) is not recommended because most database servers do not support this notation.

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.

Usage:

The DELETE statement can be used to delete one or more rows from the specified database table.

Note that if you do not specify the WHERE clause, all rows in the table will be deleted. No warning will be generated by the compiler.

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 [subset-clause] [duplicates-option] { * | select-list }
  [ INTO variable [,...] ]
  FROM table-list [,...]
  [ WHERE condition ]
  [ GROUP BY column-list [ HAVING condition ] ]
  [ ORDER BY column [{ASC|DESC}] [,...] ] 

where subset-clause is:

[ SKIP { integer | variable } ]
[ {FIRST|MIDDLE|LIMIT} { integer | variable ]

where duplicates-option is:

  ALL
| DISTINCT
| UNIQUE

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. integer is an integer constant.
  7. variable is a program variable.
  8. column is a name of a table column.
  9. column-alias defines a new name to reference the column in the rest of the statement.
  10. condition is an SQL expression to select the rows to be deleted.
  11. 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.

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.

Note that Genero supports the SELECT INTO TEMP statement syntax to create temporary tables. Such statement does not return a result set.

See Result Sets Processing for more details.

SQL Blocks

Purpose:

Provides a convenient way to execute specific SQL statements that are not supported in the language as static SQL statements.

Syntax:

SQL
 sql-statement
 END SQL

where sql-statement is:

  sql-keyword
| identifier
| INTO $host-variable [,...]
| $host-variable
| {+ sql-directive }
| --+ sql-directive
| --# fgl-comment
[...]

Notes:

  1. sql-keyword is any keyword of the SQL language.
  2. identifier is a regular SQL identifier such as a table or column name.
  3. host-variable is a program variable defined in the current scope.
  4. sql-directive is a special comment to be kept in the SQL statement.
  5. fgl-comment defines a comment that will be interpreted as a regular syntax element.

Usage:

SQL Blocks start with the SQL keyword and end with the END SQL keywords. The content of the SQL block is parsed by the fglcomp compiler to extract host variables, but the SQL statement syntax is not checked. This is actually the main purpose of SQL Blocks, compared to regular static SQL statements; with SQL Blocks, you can use any recent SQL statement introduced by the latest version of your database server. Note however, that you can achieve the same result using Dynamic SQL instructions. SQL Blocks are supported for IBM Informix 4gl compatibility.

Only one SQL statement can be included in an SQL block. Using the ; semi-colon statement separator is forbidden.

Program variables can be used inside the SQL statement. However, unlike static SQL statements, each host variable must be identified with a $ dollar prefix. The list of fetch targets must be preceded by the INTO keyword, as in static SELECT statements. Complete records can be used in SQL blocks by using the dot star notation ($record.*), you can also use the THROUGH or THRU keywords), as well as array elements.

SQL blocks can also be used to declare a cursor with the DECLARE mycursor CURSOR FOR SQL ... END SQL syntax.

SQL Directives can be used inside SQL Blocks as special comments with the {+ } or --+ syntax. The SQL Directives will be kept in the SQL text that will be executed by the database server. You typically write optimizer hints with the SQL Directives syntax.

The --# specific comment is supported for backward compatibility. The SQL text following this marker will be parsed as regular SQL text, but will be ignored by other compilers. It is not recommended to use this feature.

You can check the resulting SQL statement after parsing by using the -S option of fglcomp.

Example:

01 MAIN
02    DEFINE myrec RECORD
03             key INTEGER,
04             name CHAR(10)
05          END RECORD
06    DATABASE stock
07    LET myrec.key = 123
08    SQL
09      SELECT (+EXPLAIN) items.* INTO $myrec.*
10         FROM items WHERE key=$myrec.key
11    END SQL
12 END MAIN