Table of Contents Previous Next


4 Using Descriptors

Dynamic SQL allows a client application to execute SQL statements that are composed at runtime. This is useful when you don't know the content or form a statement will take when you are writing a client application. ECPGPlus does not allow you to use a host variable in place of an identifier (such as a table name, column name or index name); instead, you should use dynamic SQL statements to build a string that includes the information, and then execute that string. The string is passed between the client and the server in the form of a descriptor. A descriptor is a data structure that contains both the data and the information about the shape of the data.
A client application must use a GET DESCRIPTOR statement to retrieve information from a descriptor. The following steps describe the basic flow of a client application using dynamic SQL:
1.
Use an ALLOCATE DESCRIPTOR statement to allocate a descriptor for the result set (select list).
2.
Use an ALLOCATE DESCRIPTOR statement to allocate a descriptor for the input parameters (bind variables).
4.
Use a PREPARE statement to parse and syntax-check the SQL statement.
5.
Use a DESCRIBE statement to describe the select list into the select-list descriptor.
6.
Use a DESCRIBE statement to describe the input parameters into the bind-variables descriptor.
7.
Prompt the user (if required) for a value for each input parameter. Use a SET DESCRIPTOR statement to assign the values into a descriptor.
8.
Use a DECLARE CURSOR statement to define a cursor for the statement.
9.
Use an OPEN CURSOR statement to open a cursor for the statement.
10.
Use a FETCH statement to fetch each row from the cursor, storing each row in select-list descriptor.
11.
Use a GET DESCRIPTOR command to interrogate the select-list descriptor to find the value of each column in the current row.
12.
Use a CLOSE CURSOR statement to close the cursor and free any cursor resources.
If TYPE is 9:
1 - DATE
2 - TIME
3 - TIMESTAMP
4 - TIME WITH TIMEZONE
5 - TIMESTAMP WITH TIMEZONE
Indicates a NULL or truncated value.
1 - SQL3_CHARACTER
2 - SQL3_NUMERIC
3 - SQL3_DECIMAL
4 - SQL3_INTEGER
5 - SQL3_SMALLINT
6 - SQL3_FLOAT
7 - SQL3_REAL
8 - SQL3_DOUBLE_PRECISION
9 - SQL3_DATE_TIME_TIMESTAMP
10 - SQL3_INTERVAL
12 - SQL3_CHARACTER_VARYING
13 - SQL3_ENUMERATED
14 - SQL3_BIT
15 - SQL3_BIT_VARYING
16 - SQL3_BOOLEAN

4 Using Descriptors

Table of Contents Previous Next