To meet your needs, we constantly work to improve Querix products.
This means that Lycia documentation is developing as well.
In case you have found a certain dissonance between the provided information and the actual behavior of Lycia 3 and/or your applications, please, let us know about this via documentation@querix.com so that we can introduce the necessary changes to our documentation.
Thank you for your attention and cooperation.
Statements Excluded from the SQL Block
Comment Symbols and Optimization Directives
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, which are discussed later in this chapter.
|
Element |
Description |
|
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.
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.
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.
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
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 CLOSE 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.
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).
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.