SQL CONSTRUCT QUERY Statement

< > Query Statements Connection Statements Example Flow Chart Table of Contents

The SQL CONSTRUCT QUERY statement constructs an SQL statement text string from a list of data items and literal values. A directive that specifies formatting may optionally precede each data item or literal value argument.

SqlTextString (output). This argument must refer to a nonnumeric data item. The argument specifies the data item where the constructed SQL text string will be stored. The output text string is set to an empty string at the beginning of the SQL CONSTRUCT QUERY statement and then values from the remainder of the argument list are appended in the order specified.

DirectiveN (input). This optional nonnumeric argument specifies formatting by use of one of the pre-defined directive values, which are as follows:

sql-DirFixed ‑ append the value specified by the following DataItemOrLiteralN argument with its full length, including any trailing spaces.

sql-DirTrim ‑ append the value specified by the following DataItemOrLiteralN argument with all trailing spaces trimmed.

sql-DirQtFixed ‑ append the value determined by surrounding the full length of the value specified by the following DataItemOrLiteralN argument with SQL literal quote characters. Also, any SQL literal quote characters in the supplied value are doubled. That is, append a valid SQL character string literal from the full COBOL data item value, including any trailing spaces.

sql-DirQtTrim ‑ append the value determined by surrounding the trailing space trimmed value specified by the following DataItemOrLiteralN argument with SQL literal quote characters. Also, any SQL literal quote characters in the supplied value are doubled. That is, append a valid SQL character string literal from the COBOL data item value, excluding any trailing spaces.

sql-DirQtID ‑ append the value determined by surrounding the trailing space trimmed value specified by the following DataItemOrLiteralN argument with the data source dependent SQL identifier quote characters for the connection specified by a prior sql-DirSetDS in the same SQL CONSTRUCT QUERY statement.

sql-DirDate ‑ append the value specified by the following DataItemOrLiteralN argument as an ODBC date literal. The following DataItemOrLiteralN argument must specify a date in the format YYYYMMDD or YYYYDDD, but may be either numeric or nonnumeric. (For more information on date formats, see the topic InstantSQL Data Conversion.)

sql-DirTime ‑ append the value specified by the following DataItemOrLiteralN as an ODBC time literal. The following DataItemOrLiteralN argument must specify a time in the format HHmmSScc, HHmmSS, HHmm, or HH, but may be either numeric or nonnumeric. (For more information on time formats, see the topic InstantSQL Data Conversion.)

sql-DirStamp ‑ append the value specified by the following DataItemOrLiteralN as an ODBC timestamp literal. The following DataItemOrLiteralN argument must specify a timestamp, but may be either numeric or nonnumeric. A timestamp value must, at a minimum, specify the date in the format YYYYMMDD or YYYYDDD and may in addition specify the time in the format HHmmSScc, HHmmSS, HHmm, or HH. (For more information on timestamp formats, see the topic InstantSQL Data Conversion.)

sql-DirSetDS ‑ set the connection handle for the data source to be used to establish the identifier quote characters for the directive sql-DirQtID. The following DataItemOrLiteralN argument must specify the connection handle. This directive is required only when the sql-DirQtID directive is used. It must precede any uses of the sql-DirQtID directive in the same statement, but need be done only once in any SQL CONSTRUCT QUERY statement in which it is required. This directive does not append any value to the SQL statement text string being constructed.

DataItemOrLiteralN (input). This argument may specify a numeric or nonnumeric value except that, when the directive sql-DirSetDS precedes it, this argument must specify a numeric integer value with at least six digits of precision. The argument provides a value to be appended to the SQL text string being constructed, except that, when the directive sql-DirSetDS precedes it, the value must be a connection handle that identifies the connection to be used for subsequent uses of the directive sql-DirQtID. Numeric values are always converted to text strings before being appended. If no directive precedes this argument, the value is appended with trailing spaces trimmed and then one trailing space added. If two or more values are to be concatenated without space separators, then the sql-DirTrim directive can be used with the first values.

The SQL CONSTRUCT QUERY statement is provided for convenience. Its use is optional. In many cases, the SQL statement text can be coded as a literal in the SQL PREPARE QUERY statement. The COBOL MOVE and STRING statements can also be used to construct queries in data items. The SQL CONSTRUCT QUERY statement is most useful when data items need to be space trimmed or SQL character-string, date, time or timestamp literal values must be constructed.

The size of the query that can be constructed is limited only by the size of the supplied COBOL data item for the SqlTextString argument. A convenient data item in which to construct queries is sql-QrySQL defined in the copy file lisqldqy.cpy. This data item is defined to be 4096 characters in length.

There is no particular COBOL data item value that will cause InstantSQL to convert a supplied data item value to the word "NULL" in the constructed query. For example, a date value of 00000000 (all zeroes) will cause a bad date value error rather than causing "NULL" to be inserted. The COBOL program can interpret the value itself and choose to insert either "NULL" or the converted data value string by using two or more SQL CONSTRUCT QUERY statements. Click on the word Example here or in the topic header to see sample code for inserting a nullable date value. (Note that the form of an SQL statement may also need to change if the value may be NULL. For example, an expression that compares a column to a value cannot compare for equality to NULL. The comparison operators are never true if either or even both of the values are NULL. Instead, the expression must be modified to test the column IS NULL.)

SQL CONSTRUCT QUERY Statement Examples:

           SQL CONSTRUCT QUERY
               sql-QrySQL,
               sql-DirSetDS sql-ConnectionHandle, *> set connection
               "SELECT * FROM ",
               sql-DirQtID sql-TableName,   *> quoted table name
               " WHERE HireDate = ",
               sql-DirDate MyDateField.     *> SQL date literal

           SQL CONSTRUCT QUERY
               sql-QrySQL,
               "INSERT INTO Employee(Name, StartTime) VALUES(",
               sql-DirQtTrim, WS-EMPLOYEE-NAME,      *> SQL character literal
               sql-DirTime, WS-EMPLOYEE-START-TIME. *> SQL time literal
               ")".

           MOVE WS-HIRE-DATE TO WS-MY-DATE. *> date may be zero
           PERFORM CONSTRUCT-NULLABLE-DATE. *> build date literal
           SQL CONSTRUCT QUERY
               sql-QrySQL,
               "INSERT INTO Employees(LastName, HireDate)"
               "VALUES(",
               WS-LAST-NAME,
               ",",
               WS-MY-DATE-STRING,      *> constructed date literal
               ")".

       CONSTRUCT-NULLABLE-DATE.
      * This paragraph constructs a nullable date literal
      * string. For this example, the COBOL program represents
      * NULL dates with all zeroes.
           IF WS-MY-DATE = ZERO         *> Zero date implies NULL
             MOVE "NULL" TO WS-MY-DATE-STRING *> NULL date
           ELSE
             SQL CONSTRUCT QUERY              *> non-NULL date
                 WS-MY-DATE-STRING,
                 sql-DirDate, WS-MY-DATE
           END-IF.