| 1 | Source records containing SQL statement | Input | Char(*) |
| 2 | Record length | Input | Binary(4) |
| 3 | Number of records provided | Input | Binary(4) |
| 4 | Language | Input | Char(10) |
| 5 | Options | Input | Char(*) |
| 6 | Statement information | Output | Char(*) |
| 7 | Length of statement information | Input | Binary(4) |
| 8 | Number of records processed | Output | Binary(4) |
| 9 | Error code | I/O | Char(*) |
The Syntax Check SQL Statements (QSQCHKS) API calls the DB2® for IBM® i SQL parser to check the syntax of an SQL statement. If a specific language is specified, the parser will scan the source records passed according to the rules of the language. If a language is not passed, the parser will scan an SQL statement using the Interactive SQL syntax rules.
No additional authority is required and no locks are acquired.
The SQL statement that is to be parsed. This parameter can be passed as source text records for an HLL or as an SQL statement.
If the statement is contained in source text records for an HLL, the SQL statements must be in the form required by the precompiler for the specified language. For example, in COBOL, the statements must be preceded by EXEC SQL and followed by END-EXEC. Multiple statements will be processed. All the records will be processed as long as enough storage is provided for the statement information.
If a language is not specified, a single SQL statement must be passed without any additional delimiters (such as EXEC SQL or ;).
The length of each record or the length of the SQL statement if language is *NONE. If language is *NONE the length must be between 1 and 65535. Record length for other languages must be at least as long as the right margin and cannot be longer than 100.
The number of source records to scan for the statement. This must be 1 if *NONE is specified for the language. If a language is specified, the number of records must be between 1 and 65535.
The programming language for which the syntax check is to be performed. Valid values include the following:
| *NONE | A syntax check is performed on the SQL statement using the Interactive SQL language syntax rules. |
| *CBL | A syntax check is performed on the SQL statement using the COBOL language syntax rules. |
| *FTN | A syntax check is performed on the SQL statement using the FORTRAN language syntax rules. |
| *PLI | A syntax check is performed on the SQL statement using the PL/I language syntax rules. |
| *RPG | A syntax check is performed on the SQL statement using the RPG language syntax rules. |
| *CLE | A syntax check is performed on the SQL statement using the ILE C language syntax rules. |
| *CBLLE | A syntax check is performed on the SQL statement using the ILE COBOL language syntax rules. |
| *RPGLE | A syntax check is performed on the SQL statement using the fixed-form ILE RPG language syntax rules. |
| *RPGLEFREE | A syntax check is performed on the SQL statement using the free-form ILE RPG language syntax rules. |
The options required by SQL to parse the statement. The options must be specified as keys. The first part of the template is the number of keys passed, followed by variable length records for each option specified. For a description of the option data and keys, see Format for Options.
| Number of options specified | BINARY(4) |
| Total number of all the options (keys) specified. If this is 0, then defaults are used for the options. | |
| Variable length option data | Variable length records containing the key indicating what key is passed, followed by the length of the data and the data. |
The structure in which to return statement information for all statements processed. For the format of the structure, see Statement Information.
The length of the area in which to return statement information. This length must be at least 68 for information to be returned for statement. If a syntax error occurs, the length must be long enough to also contain the replacement text for the message. If more than 1 statement is processed, each statement after the first requires 44 bytes plus the length of the replacement text for any syntax errors.
The number of records processed. If the number of records processed is less than the number of records provided, the either an error occurred or there was not enough room in the statement information area to continue. This would never be greater than the number of records provided.
The structure in which to return error information. For the format of the structure, see Error code parameter.
The following table defines the format for the options.
| Offset | Type | Field | |
|---|---|---|---|
| Dec | Hex | ||
| 0 | 0 | BINARY(4) | Number of keys |
| 0 | 0 | BINARY(4) | Key |
| 04 | 04 | BINARY(4) | Length of data |
| 08 | 08 | CHAR(*) | Data |
If the length of character data is longer than the key field's data length, the data will be truncated at the right. No message will be issued.
If the length of character data is smaller than the key field's data length, the data will be padded with blanks at the right. No message will be issued.
If the same key is specified more than once, the last value for the option is used.
Data. The option used by SQL to scan the source and syntax check the SQL statement.
Key. Identifies a field of the options parameters. See Keys for the list of valid keys.
Length of data. The length of the data specified for the option.
Number of keys. The number of keys passed. This specifies the number of key arrays following this field. The arrays contain the key, length of data, and the data.
The following table lists the valid keys and the corresponding option.
| Key | Type | Field |
|---|---|---|
| 1 | CHAR(10) | Naming convention |
| 2 | CHAR(1) | Operation |
| 3 | CHAR(1) | Character for delimited host strings |
| 4 | CHAR(1) | Character for delimited SQL strings |
| 5 | CHAR(1) | Character for the decimal point |
| 6 | BINARY(4) | left margin |
| 7 | BINARY(4) | right margin |
| 8 | BINARY(4) | CCSID |
| 9 | CHAR(10) | Target release |
CCSID. The CCSID to use for the source. The CCSID must be a valid CCSID. If not specified, the job CCSID will be used.
Character for delimited host strings. The character that is to be used to delimit host character strings. This parameter is not valid if the language is C or *NONE, and must be apostrophe if specified for FORTRAN, PL/I and RPG. If not specified for COBOL, the default is the quotation mark. Valid values include the following:
| (') | apostrophe |
| (") | quotation mark |
Character for delimited SQL strings. The character that is to be used to delimit character constants within an SQL statement. If the language is COBOL, either values can be specified and the default is quotation mark. If *NONE is specified for the language, either values can be specified and the default is apostrophe. For other languages, only the apostrophe can be specified. Valid values include the following:
| (') | apostrophe |
| (") | quotation mark |
Character for the decimal point. The character that is to be used for the decimal point. This parameter is valid for all languages. If not specified, the system value (QDECFMT) will be used. Valid values include the following:
| (.) | period |
| (,) | comma |
Left margin. The left margin for the source. This parameter is only valid if language is PL/I or C and the valid values are from 1 to 80. If not specified, the default for PL/I is 2 and the default for C is 1. The left margin for RPG, COBOL, and FORTRAN is defined by the language and cannot be modified.
Naming convention. The naming convention used to qualify table names in the SQL statement. If this parameter is not passed, the default is *NONE. Valid values include the following:
| *NONE | The naming convention is not known. Errors in the qualification of table names are not returned. |
| *SYS | Table names are qualified using the system naming convention in the form library/table. |
| *SQL | Table names are qualified in the SQL naming convention in the form library.table. |
Operation. The operation indicates what operations are to be performed by SQL. For performance, work areas can be reused across calls to the syntax checker, but SQL must be called eventually to terminate. The default is to syntax check the statement and terminate (2). However, for performance it is recommended that operation 0 be used in most cases when more than 1 SQL statement is to be checked. In this case, SQL must be called eventually to terminate. Valid values include the following:
| 0 | Syntax check the statement and do not terminate. If this is specified, SQL must be called again to indicate the syntax check is complete. |
| 1 | Syntax check is complete. This option must be used to inform SQL to terminate when no more SQL statements need to be syntax checked. |
| 2 | Syntax check the statement and terminate. |
Right margin. The right margin for the source. This parameter is only valid if language is PL/I or C and the valid values are from 1 to 80. The right margin must always be greater than the left margin. If not specified, the default for both PL/I and C is 80. The right margin for RPG, COBOL, and FORTRAN is defined by the language and cannot be modified.
Target release. The target release for which the statement should be syntax checked. If the statement cannot be taken back to the release specified, SQL7906 will be returned in the statement information. The default is the current release. The format VxRxMx is used to specify the release, where Vx is the version, Rx is the release, and Mx is the modification level. For example, V5R3M0 is version 5, release 3, modification 0.
| Offset | Type | Field | |
|---|---|---|---|
| Dec | Hex | ||
| Statement information header | |||
| 0 | 0 | CHAR(10) | Message file name |
| 10 | 0A | CHAR(10) | Message file library name |
| 20 | 14 | BINARY(4) | Number of statements processed |
| Statement information returned for statements processed (repeated for each statement processed for HLL): | |||
| 0 | 0 | BINARY(4) | Length of information returned for this statement |
| 4 | 4 | BINARY(4) | Record number of first byte of statement |
| 8 | 8 | BINARY(4) | Column number of first byte of statement |
| 12 | C | BINARY(4) | Record number of last byte of statement |
| 16 | 10 | BINARY(4) | Column number of last byte of statement |
| 20 | 14 | BINARY(4) | Record number of the syntax error |
| 24 | 18 | BINARY(4) | Column number of the syntax error |
| 28 | 1C | CHAR(7) | SQL message ID |
| 35 | 23 | CHAR(5) | SQLSTATE |
| 40 | 28 | BINARY(4) | Length of message replacement text |
| 44 | 2C | CHAR(*) | Message replacement text |
Column number of first byte of statement. The column containing the first byte of the beginning delimiter for the SQL statement. This would be the EXEC SQL in COBOL. This is blank if language is *NONE.
Column number of last byte of statement. The column containing the last byte of the ending delimiter for the SQL statement. This would be the END-EXEC in COBOL. If the record and column number of the first byte of the statement is set and the record and column number of the last byte of the statement is not, then we were processing a statement but did not find the end. No more records would be processed. This is blank if language is *NONE.
Column number of the syntax error. The column containing the syntax error if one was found.
Length of information returned for this statement. The length of the information returned for a single statement. This can be used as a displacement to the next statement.
Length of message replacement text. The length of the replacement text associated with the SQL message ID. If this is 0, then there is no replacement text for the message.
Message file library name. The library containing the SQL message file.
Message file name. The SQL Message file containing the message for the syntax error returned.
Message replacement text. A The replacement text for the message.
Number of statements processed. The number of statements processed. If called with language *NONE, this would always be 1 if enough space was provided for the statement information area.
Record number of first byte of statement. The record containing the first byte beginning delimiter for the SQL statement. This would be the EXEC SQL in COBOL. This is blank if language is *NONE.
Record number of last byte of statement. The record containing the last byte of the ending delimiter for the SQL statement. This would be the END-EXEC in COBOL. This is blank if language is *NONE.
Record number of the syntax error. The record containing the syntax error if one was found. If this is 0, then no error was found. If an error is found when language is *NONE, this value would be 1.
SQL message ID. If an error or warning is found, the message ID is set to th name of the message corresponding to the syntax error that occurred.
SQLSTATE. The SQLSTATE is additional information corresponding to the SQL return code. The SQLSTATEs are common across IBM SQL products for errors. For detailed information on this, see the SQL programming topic collection.
| Message ID | Error Message Text |
|---|---|
| CPF24B4 E | Severe error while addressing parameter list. |
| CPF3C90 E | Literal value cannot be changed. |
| CPF3CF1 E | Error code parameter not valid. |
| SQL0901 E | Record length parameter not valid. |
| SQL5502 E | Number of source records not valid. |
| SQL5503 E | Character for delimited host string not valid. |
| SQL5504 E | Character for delimited SQL string not valid. |
| SQL5505 E | Language not valid. |
| SQL5506 E | Naming convention not valid. |
| SQL5507 E | Margins not valid. |
| SQL5508 E | CCSID not valid. |
| SQL5509 E | Character specified as decimal point not valid. |
| SQL5510 E | Option parameter not valid. |
| SQL5511 E | Key field &1 not valid. |
| SQL5512 E | Number of keys not valid. |
| SQL5513 E | Target release not valid. |
| SQL5514 E | Length of data for key &1 not valid. |
| SQL5515 E | Length of area for statement information not valid. |
[ Back to top | Database and File APIs | APIs by category ]