Dynamic Cursor SELECT Using Progress/ESQL

These are typical steps you take to execute a dynamic cursor SELECT using Progress/ESQL:

  1. Declare a pointer to an instance of the ESQL SQLDA_T structure, as shown in the following example:
  2. struct SQLDA_T *psqlda=(struct SQLDA_T *) 0; 
    

    For detailed information on the SQLDA, see the "SQL Descriptor Area" section.

  3. Prepare the SQL statement as shown in this code fragment:
  4. char *prequest = "SELECT * FROM customer FOR READ ONLY";
    EXEC SQL PREPARE DEMOSTMT FROM prequest; 
    

    NOTE: You specify any lock criteria (FOR READ ONLY or FOR UPDATE) directly in the request string, along with the cursor specification. DEMOSTMT is an arbitrary statement identifier, like a cursor name.

    The variable prequest is a pointer to the request string. It ultimately maps to a request handle in the C code.

    NOTE: The scope of a statement identifier (used, for example, in PREPARE, DESCRIBE and DECLARE CURSOR statements) extends to the entire ESQL source file. In the example, this means that any reference to this statement identifier in any function within the file references the prepared statement in prequest.

  5. Determine the number of columns in the result set by first calling the sqlgetrqhd2() ESQL-LIB function to obtain the request handle for the prepared cursor SELECT, and then calling the sqldynncols() function to return the number of columns:
  6. EXEC SQL BEGIN DECLARE SECTION;
    int    ncols;
    EXEC SQL END DECLARE SECTION;
    SQLRQHDL *shandle;
                .
                .
                .
    sqlgetrqhdl("DEMOSTMT", &shandle);
    sqldynncols(shandle, &ncols); 
    

    You can also select a number of columns known to be greater or equal to the columns in the result set. However, the following steps assume you use these ESQL-LIB functions to return the exact number in the host language variable ncols.

  7. Allocate storage for the SQLDA_T and SQLVAR_T structures using your own memory allocation routines, the sqlald() ESQL-LIB function, or the ALLOCATE DESCRIPTOR statement that this function supports. The following example shows function sqlald() allocating space for structures that can handle ncols result columns as determined in Step 3 (The equivalent ALLOCATE DESCRIPTOR statement is shown in the comment.):
  8. /* EXEC SQL ALLOCATE DESCRIPTOR psqlda WITH MAX :ncols; */
    
    psqlda = sqlald((int) ncols);
    if (psqlda == (struct SQLDA_T *) 0)
    {
        printf("Couldn’t alloc memory for SQLDA.\n");
        break;
    } 
    

  9. Initialize each SQLVAR_T structure with the attributes of the columns in the result set, including data type and size information. You can do this by executing the DESCRIBE statement, calling the sqldyndesc() ESQL-LIB function directly, or providing the information from your knowledge of the table. This example executes the DESCRIBE statement:
  10. EXEC SQL DESCRIBE DEMOSTMT USING SQL DESCRIPTOR psqlda; 
    

  11. Based on the data type and size of each column, use your own memory allocation routines or the sqlalloc() ESQL-LIB function to allocate storage for the data in the columns. As described in the previous section, the data type for each column is contained in the TYPE members of the SQLVAR_T structures (each column or array column element in the result has its own SQLVAR_T instance). The DATA member of the SQLVAR_T structure points to the allocated storage for each column.
  12. Also, if you can set the data type to a NULL value (the NULLABLE member of the SQLVAR_T structure is non-zero (TRUE), also allocate storage (again using sqlalloc() or the equivalent) for the indicator variable (a long type) to which the INDICATOR member of the SQLVAR_T structure points.

    NOTE: See the alloc_disp_cols() function in the dyndemo.cc example for a comprehensive example of this step. This function uses sqllalloc() to allocate storage for an arbitrary number of columns with arbitrary data types, and displays the names of the columns.

  13. Declare the cursor for the SELECT using the DECLARE CURSOR statement, as shown in this example:
  14. EXEC SQL DECLARE C1 CURSOR FOR DEMOSTMT; 
    

    DEMOSTMT is the statement identifier used in the PREPARE statement in Step 2. You do not specify lock criteria in the dynamic DECLARE CURSOR statement, as you do in the static DECLARE CURSOR statement. Instead, you place lock criteria directly in the request string along with the cursor specification (see Step 2).

  15. Open the cursor you just declared, as shown in this example:
  16. EXEC SQL OPEN C1; 
    

    When you open a cursor, the SELECT statement you defined in your PREPARE statement (in Step 2) executes.

    NOTE: If the SELECT statement to which the cursor name refers contains dynamic parameter markers, values for the parameter markers are supplied in the OPEN statement. For more information, see the "Using Dynamic Parameter Markers" section in this chapter.

  17. Use the FETCH statement to access the results that the cursor SELECT generates, as shown in the following example:
  18. EXEC SQL FETCH C1 INTO SQL DESCRIPTOR psqlda; 
    

    The FETCH statement retrieves one row of data into the data areas allocated in Step 6. You must access these data areas to access the column data.

    NOTE: See the disp_data() function in the installed dyndemo.cc example for a comprehensive example of this step. This function reads and displays the data for an arbitrary number of columns with arbitrary data types.

  19. When you are done with the SELECT statement, use the CLOSE statement to close the cursor, as shown in the following example:
  20. EXEC SQL CLOSE C1; 
    

  21. Deallocate the storage you allocated in Step 4 and Step 6. Use sqlfree() to free any memory allocated using sqlalloc(). The sqldld() does not call sqlfree() to do this for you.

CAUTION: If you call sqldld() before deallocating memory previously allocated with sqlalloc(), the pointers to that memory are lost and the memory cannot be recovered until your application terminates.

See the dealloc_cols() function in the installed dyndemo.cc example for a comprehensive example of this step.

The following section describes how to use dynamic parameter markers in the cursor SELECT statement and any other dynamic ESQL request, using prepared execution. For more information on the dynamic ESQL statements used for prepared execution, see the "Dynamic ESQL Statements for Prepared Execution" section in this chapter.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095