Follow-up on proposed CONSTRUCT changes

Hi, Here are the revised proposals for the CONSTRUCT statement to support ANSI databases better, and in particular, the Informix-Gateway (DRDA) product. The only issue not formally covered below is the quote character generated by CONSTRUCT; the current proposal is to use single quotes unconditionally, but if this is going to cause major problems, we could probably add yet another option along the lines of: OPTIONS CONSTRUCT QUOTE { '"' | '''' } to allow just about anything to be specified. If this was required, then the default would be double quote with MATCHES (backwards compatability), and single quote with LIKE (the whole point of the exercise). As another possibility, we could simply assume that LIKE uses single quotes and MATCHES uses double quotes, cutting down on the size of the grammar and still achieving all the other objectives except for the ultimate in flexibility. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> *************************************************************************** Changes required in 4GL for DRDA compatibility (Bug# 19466) This is mostly an "end-user" perspective of the nature of the changes required in 4GL so that applications written in 4GL can work with DB2 (or other DRDA servers) via the Informix-Gateway. Double-quotes vs. Single-quotes: -------------------------------- 4GL should preserve the quote characters used by the programmer when it generates ESQL or C output. Currently, even if the 4GL code uses single-quotes for character string literals, the 4GL preprocessor converts them to double-quotes, as shown in the following example: Current behaviour: a.4gl: INSERT INTO T VALUES (1, 2.0, 'ABC') a.ec: $ insert into t values ( 1 , 2.0 , "ABC" ); a.c: " insert into t values ( 1 , 2.0 , \\"ABC\\" )" Required behaviour: a.4gl: INSERT INTO T VALUES (1, 2.0, 'ABC') a.ec: $ insert into t values ( 1 , 2.0 , 'ABC' ); a.c: " insert into t values ( 1 , 2.0 , 'ABC' )" a.4gl: INSERT INTO T VALUES (1, 2.0, "ABC") a.ec: $ insert into t values ( 1 , 2.0 , "ABC" ); a.c: " insert into t values ( 1 , 2.0 , \\"ABC\\" )" An alternative proposal is that the compiler should automatically convert the double quotes into single quotes and, obviously, retain the single quotes as single quotes, so the second required example above would become: a.4gl: INSERT INTO T VALUES (1, 2.0, "ABC") a.ec: $ insert into t values ( 1 , 2.0 , 'ABC' ); a.c: " insert into t values ( 1 , 2.0 , 'ABC' )" The automatic conversion may be preferable to preserving quote characters, since pre-written 4GL applications can be then potentially used with DB2 with a "one-line" change setting the construct options. Neither proposal should affect the I4GL programmer unless they post-process the ESQL/C or C files produced by the I4GL compiler. CONSTRUCT Statement: -------------------- In general, it should be possible to indicate that the WHERE clause generated by the CONSTRUCT statement should conform to ANSI (SQL89) syntax. This should be only be done by explicit programmer request. Specifically, the following modifications are required: * Some new clauses should be added to the OPTIONS statement to help control the behaviour of CONSTRUCT. The proposed syntax is shown with the defaults being shown by the ! suffix. The default escape is the backslash character. The clauses would be comma separated, as with all the other clauses in the OPTIONS statement. OPTIONS [ CONSTRUCT USING { LIKE | MATCHES! } ] [ CONSTRUCT ESCAPE 'x' ] [ CONSTRUCT { NOT! } MAPPING METACHARACTERS ] * Note that the defaults preserve the current behaviour. * If the application is working with CONSTRUCT USING MATCHES, it will behave as it does currently. If working with CONSTRUCT USING LIKE, it will recognize the wildcard characters '%' and '_' and use the LIKE predicate. Presently, CONSTRUCT recognizes only '*', '?', '[' and ']' and uses MATCHES predicate. Requiring the program to explicitly request LIKE predicates should minimize backward compatability problems. Programmers and users should understand that LIKE is less flexible than MATCHES. * The programmer can specify which escape character is to be used (and specified) with the LIKE and MATCHES clauses. The default escape would be the backslash and would not be specified with ESCAPE qualifier to the LIKE or MATCHES clauses. The syntax shown above should ideally allow a variable (VARCHAR or CHAR) as an alternative to a literal string. Only the first character of the string would be significant, and a null string would reset the default escape. This escape character can be entered by the user to search for a literal occurrence of one of the metacharacters. To search for a literal instance of the escape character, two escapes would be typed. If the escape character was not used by the user, it would not be specified in the output clause. For example, suppose that the program sets the escape character to '@' and is using LIKE. If user typed "35@%" in the field for table.column, then the output from CONSTRUCT should be: table.column = '35%' If the user typed "%35@%%", then the output would be: table.column like '%35@%%' escape '@' * If CONSTRUCT is using LIKE, the '*' and '?' characters are not special and are not mapped to '%' and '_'. If such behaviour was deemed desirable, then the MAPPING METACHARACTERS option should be used. Character classes (enclosed in '[' and ']') would never be mapped since these are not handled by LIKE at all. If CONSTRUCT is mapping metacharacters (and using LIKE) and the user typed "*35*" in the field for table.column, then the output from CONSTRUCT should be: table.column like '%35%' For symmetry, if CONSTRUCT is using MATCHES and is mapping metacharacters, then '%' should be translated to '*' and '_' to '?'. This is self-consistent and backwards compatable as the default mode does not map metacharacters. * Note that it is expected that the designers of a system will define a single standard escape character for all programs, and so the OPTIONS CONSTRUCT ESCAPE statement will normally only be executed once per program, and that during the start up phase. However, it can be changed at the programmer's whim, but at the risk of confusing the user. Likewise, all programs will normally use either CONSTRUCT USING LIKE or CONSTRUCT USING MATCHES consistently, and will all use CONSTRUCT MAPPING METACHARACTERS or not consistently. * Wh