Returned SQLCODE Values with EXECUTE

If a prepared statement fails to access any rows when it executes, the database server returns the SQLCODE value of zero (0).

For a multistatement prepared object, however, if any statement in the following list fails to access rows, the returned SQLCODE value is SQLNOTFOUND ( = 100):
  • INSERT INTO table SELECT ...; WHERE
  • SELECT...;WHERE ...; INTO TEMP
  • DELETE...; WHERE
  • UPDATE ... WHERE

In an ANSI-compliant database, if you prepare and execute any of the statements in the preceding list, and no rows are returned, the returned SQLCODE value is SQLNOTFOUND ( = 100).

USING Clause

Use the USING clause to specify the values that are to replace question-mark ( ? ) placeholders in the prepared statement. Providing values in the EXECUTE statement that replace the question-mark ( ? ) placeholders in the prepared statement is sometimes called parameterizing the prepared statement. 
(1)
USING Clause

1  USING
2.1 + , parameter_var? 
2.2.1 1  : 
2.2.1 INDICATOR
2.1 indicator_var
2.1  SQL DESCRIPTOR
2.2.1 descriptor_var
2.2.1 'descriptor'
2.1  DESCRIPTOR sqlda_pointer
Notes:
  • 1 HCL OneDB™ extension
Element Description Restrictions Syntax
descriptor Quoted string that identifies a system-descriptor area System-descriptor area must already be allocated. Use single ( ' ) quotation marks. Quoted String
descriptor_var Host variable that identifies a system-descriptor area System-descriptor area must already be allocated Language specific
indicator_var Host variable that receives a return code if corresponding parameter_var is NULL value, or if truncation occurs Cannot be DATETIME or INTERVAL data type Language specific
parameter_var Host variable whose contents replace a question-mark ( ? ) placeholder in a prepared statement Must be a character data type Language specific
sqlda_pointer Pointer to an sqlda structure that defines data type and memory location of values to replace question-mark ( ? ) placeholder in a prepared object Cannot begin with a dollar sign ( $ ) or a colon ( : ). An sqlda structure is required with dynamic SQL DESCRIBE INPUT statement

This closely resembles the syntax of the INTO Clause.

If you know the number of parameters to be supplied at runtime and their data types, you can define the parameters that are needed by the EXECUTE statement as host variables in your program.

If you do not know the number of parameters to be supplied at runtime or their data types, you can associate input values from a system-descriptor area or an sqlda structure. Both of these descriptor structures describe the data type and memory location of one or more values to replace question-mark ( ? ) placeholders.