1 DESCRIBE Writes information about a prepared statement to the SQL Descriptor Area (SQLDA). The DESCRIBE statement is a dynamic SQL statement. Dynamic SQL lets programs accept or generate SQL statements at run time, in contrast to SQL statements that are part of the source code for precompiled programs or SQL module language procedures. Unlike precompiled SQL or SQL module language statements, such dynamically executed SQL statements are not part of a program's source code but are generated while the program runs. Dynamic SQL is useful when you cannot predict the type of SQL statement your program needs to process. The SQLDA is a collection of host language variables used only in dynamic SQL programs. To use the SQLDA, host languages must support pointer variables that provide indirect access to storage by storing the address of data instead of directly storing data in the host language variable. The languages supported by the SQL precompiler that also support pointer variables are Ada, C, and PL/I. Any other language that supports pointer variables can use the SQLDA, but must call SQL module procedures that contain SQL statements instead of embedding the SQL statements directly in source code. The SQLDA provides information about dynamic SQL statements to the program and information about memory allocated by the program to SQL. The DESCRIBE statement is how SQL writes information that the program uses to the SQLDA. Specifically, the DESCRIBE statement stores in the SQLDA the number and data types of any select list items or parameter markers in a prepared statement. The Oracle Rdb SQL Reference Manual describes in more detail the specific fields of the SQLDA and how programs use it to communicate about select list items and parameter markers in prepared statements. 2 Environment You can use the DESCRIBE statement: o Embedded in host language programs to be precompiled o As part of a procedure in an SQL module 2 Format DESCRIBE -+-> ------------+----+ +-> ----+ | +---------------------- <----------------------+ +--+----------------+-----> INTO --> +-> SELECT LIST -+ +-> OUTPUT ------+ +-> MARKERS -----+ +-> INPUT -------+ 2 Arguments 3 INPUT Specifies that the DESCRIBE statement writes information about input parameter markers to the SQLDA. The MARKERS or INPUT clause specifies that the DESCRIBE statement writes information about the number and data types of any input parameter markers in the prepared statement to the SQLDA. Input parameter markers in a prepared statement serve the same purpose as host language variables in nondynamic, embedded SQL statements. The program can use that information in the SQLDA to allocate storage. The program must supply values in that allocated storage. SQL substitutes these values for the parameter markers when it dynamically executes the prepared statement. 3 INTO_descriptor_name Specifies the name of a structure declared in the host language program as an SQLDA to which SQL writes information about select list items, or input or output parameter markers. Precompiled programs can use the embedded SQL statement INCLUDE SQLDA to automatically insert a declaration of an SQLDA structure, called SQLDA, in the program when it precompiles the program. Programs that use the SQL module language must explicitly declare an SQLDA. Either precompiled or SQL module language programs can explicitly declare additional SQLDAs but must declare them with unique names. For sample declarations of SQLDA structures, see the Oracle Rdb SQL Reference Manual. 3 MARKERS Specifies that the DESCRIBE statement writes information about input parameter markers to the SQLDA. The MARKERS or INPUT clause specifies that the DESCRIBE statement writes information about the number and data types of any input parameter markers in the prepared statement to the SQLDA. Input parameter markers in a prepared statement serve the same purpose as host language variables in nondynamic, embedded SQL statements. The program can use that information in the SQLDA to allocate storage. The program must supply values in that allocated storage. SQL substitutes these values for the parameter markers when it dynamically executes the prepared statement. 3 OUTPUT Specifies that the DESCRIBE statement writes information about returned values in a prepared statement to the SQLDA. If you use this clause, the DESCRIBE statement writes information about the number and data types of any returned values in the prepared statement to the SQLDA. The program uses that information to allocate storage for the returned values. The storage allocated by the program then receives the returned values. The following statements or clauses return values to the DESCRIBE statement: o Select list items in a SELECT statement o The following statements within multistatement procedures: - Singleton SELECT statement - INSERT . . . RETURNING and UPDATE . . . RETURNING statements - SET assignment statement o CALL statement (invoking a stored procedure) o Dynamic singleton SELECT statement The default is SELECT LIST (or OUTPUT). 3 SELECT_LIST Specifies that the DESCRIBE statement writes information about returned values in a prepared statement to the SQLDA. If you use this clause, the DESCRIBE statement writes information about the number and data types of any returned values in the prepared statement to the SQLDA. The program uses that information to allocate storage for the returned values. The storage allocated by the program then receives the returned values. The following statements or clauses return values to the DESCRIBE statement: o Select list items in a SELECT statement o The following statements within multistatement procedures: - Singleton SELECT statement - INSERT . . . RETURNING and UPDATE . . . RETURNING statements - SET assignment statement o CALL statement (invoking a stored procedure) o Dynamic singleton SELECT statement The default is SELECT LIST (or OUTPUT). 3 statement-name Specifies the name of a prepared statement. If the PREPARE statement for the dynamically executed statement specifies a parameter, use the same parameter in the DESCRIBE statement instead of an explicit statement name. You can supply either a parameter or a compile-time statement name. Specifying a parameter lets SQL supply identifiers to programs at run time. Use an integer parameter to contain the statement identifier returned by SQL or a character string parameter to contain the name of the statement that you pass to SQL. See the PREPARE statement and the DECLARE Dynamic_CURSOR statement for more details. 3 statement-id-parameter The name of a prepared statement. If the PREPARE statement for the dynamically executed statement specifies a parameter, use the same parameter in the DESCRIBE statement instead of an explicit statement name. You can supply either a parameter or a compile-time statement name. Specifying a parameter lets SQL supply identifiers to programs at run time. Use an integer parameter to contain the statement identifier returned by SQL or a character string parameter to contain the name of the statement that you pass to SQL. See the PREPARE statement and the DECLARE Dynamic_CURSOR statement for more details. 2 Examples Example 1: Using the DESCRIBE . . . OUTPUT statement with a prepared SELECT statement This PL/I program illustrates using the DESCRIBE . . . OUTPUT statement to write information to the SQLDA about the select list items of a prepared SELECT statement. There are no parameter markers in this particular prepared SELECT statement. After issuing the DESCRIBE statement, the program stores in the SQLDA the addresses of host language variables that will receive values from columns of the result table during FETCH statements. To shorten the example, this PL/I program is simplified: o The program includes the SELECT statement to be dynamically executed as part of the source code directly in the PREPARE statement. A program with such coded SQL statements does not need to use dynamic SQL at all, but can simply embed the SELECT statement in a DECLARE CURSOR statement. (A program that must process SQL statements generated as it executes is the only type that requires dynamic SQL.) o The program declares host language variables for select list items without checking the SQLDA for a description of those items. Typically, an application needs to look in the SQLDA to determine the number and data type of select list items generated by a prepared SELECT statement before allocating storage. o The program does not use the DESCRIBE . . . INPUT statement to determine if there are any parameter markers in this dynamically executed SELECT statement. In this example, because the SELECT statement is coded in the program, it is clear that there is no need for a DESCRIBE . . . INPUT statement. However, if the SELECT statement is generated at run time, the program may have to determine there if are parameter markers by issuing a DESCRIBE . . . INPUT statement and looking at the value of the SQLD field in the SQLDA. CURSOR_EX : PROCEDURE OPTIONS (MAIN); /* * Illustrate the DESCRIBE...SELECT LIST statement using a * dynamic SELECT statement: * * Use a dynamic SELECT statement as the basis for * a cursor that displays a join of the EMPLOYEES * and SALARY_HISTORY tables on the screen. */ declare sys$putmsg external entry (any, any value, any value, any value); /* Declare SQL Communications Area: */ EXEC SQL INCLUDE SQLCA; /* Declare SQL Descriptor Area: */ EXEC SQL INCLUDE SQLDA; /* Declare the alias: */ EXEC SQL DECLARE ALIAS FILENAME 'SQL$DATABASE'; /* * Branch to ERR_HANDLER if the SQLCODE field * of the SQLCA is greater than 0: */ EXEC SQL WHENEVER SQLERROR GOTO ERR_HANDLER; /* * Declare a cursor named EMP that uses the * prepared statement DYN_SELECT: */ EXEC SQL DECLARE EMP CURSOR FOR DYN_SELECT; /* Declare a host structure to receive * the results of FETCH statements: */ DCL 1 P_REC, 2 EMPLOYEE_ID CHAR(5), 2 FIRST_NAME CHAR(10), 2 LAST_NAME CHAR(14), 2 SALARY_AMOUNT FIXED BINARY(31); /* Allocate memory for the SQLDA and * set the value of its SQLN field: */ SQLSIZE = 10; ALLOCATE SQLDA SET (SQLDAPTR); SQLN = 10; /* Prepare the SELECT statement * for dynamic execution directly * from a statement string: */ EXEC SQL PREPARE DYN_SELECT FROM 'SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, S.SALARY_AMOUNT FROM EMPLOYEES E, SALARY_HISTORY S WHERE E.EMPLOYEE_ID = S.EMPLOYEE_ID AND S.SALARY_END IS NULL'; /* Write information about the * columns of the result table * of DYN_SELECT into the SQLDA: */ EXEC SQL DESCRIBE DYN_SELECT OUTPUT INTO SQLDA; /* * Assign the addresses of the host language * variables that will receive the values of the * fetched row to the SQLDATA field * of the SQLDA: */ SQLDATA(1) = ADDR( EMPLOYEE_ID ); SQLDATA(2) = ADDR( FIRST_NAME); SQLDATA(3) = ADDR( LAST_NAME ); SQLDATA(4) = ADDR( SALARY_AMOUNT); /* Open the cursor: */ EXEC SQL OPEN EMP; /* Fetch the first row of the result table. * SQL uses the addresses in the SQLDA * to store values from the table into * the host language variables. */ EXEC SQL FETCH EMP USING DESCRIPTOR SQLDA; PUT EDIT ('Current Salaries of Employees: ') (SKIP, A, SKIP(2)); /* While the SQLCODE field of the * SQLCA is not 100 (NOT_FOUND error): */ DO WHILE (SQLCA.SQLCODE = 0); /* Display the values from the host language variables: */ PUT SKIP EDIT (EMPLOYEE_ID, ' ', FIRST_NAME, ' ', LAST_NAME, ' ', SALARY_AMOUNT) (A, A, A, A, A, A, F(9)); /* Fetch another row of the result table: */ EXEC SQL FETCH EMP USING DESCRIPTOR SQLDA; END; /* Close the cursor: */ EXEC SQL CLOSE EMP; RETURN; ERR_HANDLER: PUT EDIT ('Unexpected error, SQLCODE is: ', SQLCA.SQLCODE) (skip, a, f(9)); CALL SYS$PUTMSG(RDB$MESSAGE_VECTOR, 0, 0, 0); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK; RETURN; END CURSOR_EX; See also Example 2 in DECLARE Dynamic_CURSOR.