You are here:

SQL

SQL statement has the SQL … END SQL structure, it is used for execution of SQL statements, which cannot be embedded into the 4GL source code. Only preparable statements can be used in this statement, with some exceptions.

SQL block

A preparable SQL statement that the database server supports

Some of the SQL statement can be embedded into the Querix 4GL (these are almost all of the statements supported by the Informix database version 4.10 and some of the statements that are supported by the later versions). Other SQL statements must be prepared by means of the PREPARE statement, if the database can execute the prepared statements.

The SQL…END SQL statement provides an alternative to the PREPARE statement. It automatically prepares, executes and frees the SQL statements. The example below illustrates the usage of the ALTER TABLE statement with DISABLED keyword. A statement like this is simply put within the SQL…END SQL statement, if it does not produce input and does not require input.

SQL

ALTER TABLE order_info MODIFY (client CHAR(20), contact_info DISABLED)

END SQL

If you try to embed this statement into the 4GL code, it will produce an error, because DISABLED keyword is not supported by the embedded ALTER TABLE statement.

You can include only SQL statements into the SQL … END SQL statement.

Host variables

SQL … END SQL statement can accept host variables, which should be prefixed by a dollar sign ($) either with or without a white space between the sign and the name of the variable. The host variables serve as input and output parameters.

SQL

INSERT INTO my_table (first_col, second_col)

SELECT TRIM(A.fname) || " " || TRIM(A.lname),

B.whatever FROM my_table2 A, my_table3 B

WHERE A.Filter = $my_array[i].item

AND B.Filter MATCHES $var1

END SQL

The variables prefixed by the dollar symbols are not the database entries, they are host variables. Sometimes SQL identifiers are prefixed by the "at" symbol (@) to distinguish them from the 4GL identifiers.

Returned values

Values obtained by the SQL…END SQL statement can be returned to the 4GL program, if you use the SELECT INTO and EXECUTE PROCEDURE INTO SQL statements. The host variables and returned values stick to the same rules (they must be prefixed by the dollar sign):

SQL

SELECT someProced ($var2)

INTO $f_var, FROM someTabl WHERE PkColumn=$pkval

END SQL

The SELECT INTO variable and EXECUTE PROCEDURE INTO SQL statements can be used within the SQL…END SQL statement but they cannot be prepared by means of the PREPARE statement. This is an exception to the rule that only those SQL statements that can be prepared can be used in the SQL…END SQL statement.

Cursors in SQL…END SQL

Cursor names are not mangled within the SQL…END SQL statement. You must ensure that all the conflicts connected with the cursor name are resolved, before you can reference a cursor within the SQL…END SQL statement. The declaration of a cursor must precede the SQL…END SQL statement in which it is referenced.

DECLARE c_su SCROLL CURSOR WITH HOLD FOR

SQL

SELECT TRIM(Firstname)||" " || TRIM (Lastname)

INTO $var1 FROM someTable WHERE PkColumn > $pkvar

END SQL

Statements excluded from the SQL block

Only preparable statements can be used in the SQL…END SQL statement, with some exceptions. The SQL…END SQL block can include only SQL and SPL statements (not 4GL statements). Below is the list of some SQL statements that are used in some Informix versions, but which are not supported by the SQL…END SQL statement:

ALLOCATE COLLECTION

ALLOCATE DESCRIPTOR

ALLOCATE ROW

CHECK TABLE

CONNECT

CREATE PROCEDURE FROM

DEALLOCATE COLLECTION

DEALLOCATE DESCRIPTOR

DEALLOCATE ROW

DECLARE

DESCRIBE

DISCONNECT

EXECUTE

EXECUTE IMMEDIATE

FETCH

FLUSH

FREE

GET DESCRIPTOR

GET DIAGNOSTICS

INFO

LOAD

OPEN

OUTPUT

PREPARE

PUT

REPAIR TABLE

SET AUTOFREE

SET CONNECTION

SET DEFERRED_PREPARE

SET DESCRIPTOR

UNLOAD

WHENEVER

You cannot use the CREATE PROCEDURE statement in the SQL…END SQL block. Use the embedded CREATE PROCEDURE FROM file-name statement instead.

Restrictions

PREPARE and SQL…END SQL statements cannot be included into one another.
The question marks (?) that replace the user supplied values can be used only in the prepared statements, not in the SQL…END SQL statements.
If you include a semicolon symbol (;) after an SQL…END SQL statement, it will produce no error but will also have no effect.
If you separate two statements within the SQL…END SQL statement with the help of the semicolon (;), a compile-time error will occur.
4gl variables of the STRING datatype cannot be used in SQL statements (except for the DATABASE statement).

Comment symbols and optimization directives

Hash symbol (#) cannot be used as a comment symbol within the SQL…END SQL block. Only the double braces ({ }) and double hyphen (--) symbol to mark the comments.

The optimization directives are passed to the database symbols, if they are created by means of the standard notation for these features (in Informix version 7.3 and above). The optimization directives should be enclosed in braces and preceded by the plus symbol (+) which indicates that the code within the braces is an optimizer directive. The directives can follow the DELETE, SELECT, or UPDATE statements within the SQL…END SQL statement.