#docslug#/ecob/ecob/V1.1.6/ansi-dynamic-sql OceanBase Embedded SQL in C (ECOB) supports dynamic SQL statements that are compatible with American National Standards Institute (ANSI) standards. ECOB currently supports the following SQL statements: ALLOCATE DESCRIPTOR, DEALLOCATE DESCRIPTOR, DESCRIBE INPUT DESCRIPTOR, DESCRIBE OUTPUT DESCRIPTOR, GET DESCRIPTOR, SET DESCRIPTOR, OPEN USING DESCRIPTOR, and FETCH INTO DESCRIPTOR. When you are not sure about the number of input or output host variables of an SQL statement, you can use ANSI dynamic SQL statements.
SQL statements description
ALLOCATE DESCRIPTOR
You can execute the ALLOCATE DESCRIPTOR statement to allocate the SQL description area.
Syntax:
EXEC SQL ALLOCATE DESCRIPTOR {:desc_name | string} [ WITH MAX occurrences];
The desc_name parameter specifies the host variable of the description area name.
The string parameter specifies the string constant of the description area.
The occurrences parameter specifies the maximum number of hosts that can be included in the description area. Default value: 100.
DEALLOCATE DESCRIPTOR
You can execute the DEALLOCATE DESCRIPTOR statement to release the SQL description area.
Syntax:
EXEC SQL DEALLOCATE DESCRIPTOR {:desc_name | string} ;
The desc_name parameter specifies the host variable of the description area name.
The string parameter specifies the string constant of the description area.
DESCRIBE INPUT DESCRIPTOR
You can execute the DESCRIBE INPUT DESCRIPTOR statement to bind variable information.
Syntax:
EXEC SQL DESCRIBE INPUT stat_id USING [SQL] DESCRIPTOR {:desc_name | string} ;
DESCRIBE OUTPUT DESCRIPTOR
You can execute the DESCRIBE OUTPUT DESCRIPTOR statement to query the output column information.
Syntax:
EXEC SQL DESCRIBE [OUTPUT] stat_id USING [SQL] DESCRIPTOR {:desc_name | string} ;
GET DESCRIPTOR
You can execute the GET DESCRIPTOR statement to query the item information of the SQL description area.
Syntax:
EXEC SQL GET DESCRIPTOR {:desc_name | string} {:host_variable = COUNT | VALUE occur_idx [, :host_variable=ITEMs];
The COUNT parameter returns the total number of bound variables.
The VALUE occur_idx parameter specifies the positions of host variables in the SQL description area.
The ITEMs parameter includes the following elements:
TYPE: The code of a data type. The following table lists the data type codes of general internal data types.
Internal data type Data type code VARCHAR2 1 NUMBER 2 DATE 12 CHAR 96 BINARY_FLOAT 100 BINARY_DOUBLE 101 LENGTH: The data length of the column.
SCALE: The number of digits after the decimal point.
PRECISION: The total number of digits of a number.
NULLABLE: Specifies whether to allow values of a column to be NULL. At present, the value is fixed to 1.
DATA: Specifies to get data.
NAME: Specifies to get the column name.
INDICATOR: Specifies to get the value of the indicator variable.
SET DESCRIPTOR
You can execute the SET DESCRIPTOR statement to set the item information of the SQL description area.
Syntax:
EXEC SQL SET DESCRIPTOR {:desc_name | string} {COUNT = :host_variable | VALUE occur_idx [, ITEMs=:host_variable];
The COUNT parameter specifies the total number of input variables or output variables. The VALUE occur_idx parameter specifies the positions of the host variables in the SQL description area.
The ITEMs parameter includes the following elements:
TYPE: The code of a data type.
LENGTH: The data length of the column.
DATA: Specifies to get data.
INDICATOR: Specifies to get the value of the indicator variable.
OPEN USING DESCRIPTOR
You can execute the OPEN USING DESCRIPTOR statement to open the cursor variable in the ANSI dynamic SQL statement.
Syntax:
EXEC SQL OPEN {cur_name} USING DESCRIPTOR {:desc_name | string} ;
The cur_name parameter specifies the cursor name.
The desc_name parameter specifies the host variable of the description area name.
The string parameter specifies the string constant of the description area.
FETCH INTO DESCRIPTOR
You can execute the FETCH INTO DESCRIPTOR statement to obtain the result set stored by the cursor variable.
Syntax:
EXEC SQL FETCH {cur_name} INTO DESCRIPTOR {:desc_name | string};
The cur_name parameter specifies the cursor name. The desc_name parameter specifies the host variable of the description area name. The string parameter specifies the string constant of the description area.
Example
Example 1
int type = 2;
int len = 4;
int val1 = 12;
int val2 = 0;
EXEC SQL ALLOCATE DESCRIPTOR in_desc;
EXEC SQL CREATE TABLE t1(c1 int, c2 int);
EXEC SQL INSERT INTO t1 VALUES(12, 24);
EXEC SQL PREPARE stmt1 FROM "select c2 from t1 where c1 = :val";
EXEC SQL DESCRIBE INPUT stmt1 USING SQL DESCRIPTOR in_desc;
EXEC SQL SET DESCRIPTOR in_desc VALUE 1 TYPE = :type;
EXEC SQL SET DESCRIPTOR in_desc VALUE 1 LENGTH = :len, DATA = :val1;
EXEC SQL DECLARE d_cursor CURSOR FOR stmt1;
EXEC SQL OPEN d_cursor USING DESCRIPTOR in_desc;
EXEC SQL FETCH d_cursor into :val2;
EXEC SQL DEALLOCATE DESCRIPTOR in_desc;
EXEC SQL DROP TABLE descriptor_1;
Example 2
int count;
int length;
int len1 = 100;
int len2 = 0;
int val = 0;
char * desc_name="out_desc";
EXEC SQL ALLOCATE DESCRIPTOR :desc_name;
EXEC SQL CREATE TABLE t1(c1 int, c2 int);
EXEC SQL PREPARE stmt1 FROM "select c1, c2 from t1";
EXEC SQL DESCRIBE OUTPUT stmt1 USING SQL DESCRIPTOR :desc_name;
EXEC SQL GET DESCRIPTOR :desc_name VALUE 2 :length = LENGTH;
EXEC SQL GET DESCRIPTOR :desc_name :count = COUNT;
EXEC SQL SET DESCRIPTOR :desc_name VALUE 1 LENGTH = :len1;
EXEC SQL GET DESCRIPTOR :desc_name VALUE 1 :len2 = LENGTH;
EXEC SQL INSERT INTO t1 VALUES(1, 2);
EXEC SQL DECLARE d_cursor CURSOR FOR stmt1;
EXEC SQL OPEN d_cursor;
EXEC SQL FETCH d_cursor into DESCRIPTOR :desc_name;
EXEC SQL GET DESCRIPTOR :desc_name VALUE 1 :val = DATA;
EXEC SQL DEALLOCATE DESCRIPTOR :desc_name;
EXEC SQL DROP TABLE t1;