Progress
Embedded SQL-92
Guide and Reference


4DynSel.pc

This section provides the complete code listing of an ESQL-92 program that processes a dynamic SQL-92 SELECT statement.

4DynSel.pc
/*********************************************************************** 
** File Name: 4DynSel.pc                                              ** 
**                                                                    ** 
** Purpose: Illustrate processing of a dynamic SELECT statement.      ** 
**                                                                    ** 
** Overview:                                                          ** 
**   - Requires 2 input parameters:                                   ** 
**    (1) connect_string                                              ** 
**    (2) SELECT SQL-92 statement enclosed in double quotes,          ** 
**         naming 3 columns of datatypes: integer, integer, varchar.  ** 
**   - As written, you can use this program to verify results         ** 
**         from sample program 3DynUpd.pc.                            ** 
**                                                                    ** 
** SQL Steps, in logical order:                                       ** 
** ============================                                       ** 
**   main()                                                           ** 
**     1. DECLARE variable needed for connecting to a database.       ** 
**     2. Name WHENEVER routine to handle SQLERROR condition.         ** 
**     3. CONNECT to database specified in connect_string.            ** 
**                                                                    ** 
**    15. After SELECT operation, DISCONNECT from database.           ** 
**                                                                    ** 
**   DynSel()                                                         ** 
**     4. DECLARE variables needed in SELECT statement.               ** 
**     5. Name WHENEVER routine to handle SQLERROR.                   ** 
**     6. PREPARE the dynamic SELECT statement.                       ** 
**     7. DECLARE cursor for the prepared SELECT statement.           ** 
**     8. OPEN the declared cursor.                                   ** 
**     9. Name WHENEVER routine for NOT FOUND condition.              ** 
**    10. FETCH a row and report output until no more rows.           ** 
**    11. CLOSE cursor.                                               ** 
**    12. COMMIT WORK to release locks.                               ** 
**    13. Direct processing to CONTINUE after SQLERROR.               ** 
**    14. If SQLERROR, ROLLBACK WORK to release locks.                ** 
**                                                                    ** 
**                                                                    ** 
** Requirements for building executable program:                      ** 
** =============================================                      ** 
**   - Precompile, compile, and link,                                 ** 
**     creating *.exe                                                 ** 
**                                                                    ** 
**   - EXAMPLE for building in 1 step:                                ** 
**     > esqlc 4DynSel.pc -o DynSel                                   ** 
**                                                                    ** 
** Requirements for executing:                                        ** 
** ===========================                                        ** 
**   - Copy of SPORTS2000 database, OR                                ** 
**     Database containing PUB.InventoryTrans table, OR               ** 
**     Database with table containing integer and varchar columns.    ** 
**   - Database running                                               ** 
**   - Invoker must have authority to access table in FROM clause.    ** 
**   - To invoke from command line:                                   ** 
**     > 4DynSel {connect_string} {"SELECT statement in quotes"}      ** 
**   - INPUTS (positional)                                            ** 
**       (1) connect_string, in URL form (required).                  ** 
**       (2) SELECT statement enclosed in double quotes (required).   ** 
**           Statement should name 2 columns of datatype integer,     ** 
**           and 1 column of datatype varchar.                        ** 
**                                                                    ** 
**           These are the integer and varchar columns in the         ** 
**           PUB.InventoryTrans table:                                ** 
**                                                                    ** 
**           column name                datatype                      ** 
**           -----------                --------                      ** 
**           BinNum                     integer                       ** 
**           InvTransNum                integer                       ** 
**           Itemnum                    integer                       ** 
**           Ordernum                   integer                       ** 
**           PONum                      integer                       ** 
**           Qty                        integer                       ** 
**           WarehouseNum               integer                       ** 
**           InvType                    varchar                       ** 
**           Transtime                  varchar                       ** 
**                                                                    ** 
**   - OUTPUTS                                                        ** 
**       (1) Message to stdout, reporting success, NOT FOUND,         ** 
**       (2) 3 columns from every row in the table.                   ** 
**                                                                    ** 
**   - EXAMPLES of valid queries that fit the model of the            ** 
**      sample programs:                                              ** 
**      "Select BinNum, InvTransNum, InvType from Pub.InventoryTrans" ** 
**      "Select Itemnum, Ordernum, Transtime from Pub.InventoryTrans" ** 
**                                                                    ** 
** Side effects:                                                      ** 
** =============                                                      ** 
**   - Returns 3 columns from all rows in the specified table.        ** 
**   - Holds lock on the table while the transaction is active.       ** 
**                                                                    ** 
************************************************************************ 
**                                                                    ** 
** copyright (c) Progress Software Corporation, Bedford MA, 1999      ** 
**                    All rights reserved.                            ** 
**                                                                    ** 
** Revision History:                                                  ** 
**                                                                    ** 
** Date      By              Revision                                 ** 
** ====      ==              ========                                 ** 
** 10/1999   DB Doc          ESQL Sample Program for V9.1A            ** 
**                                                                    ** 
***********************************************************************/ 
static void DynSel(char *sel_stmt) ; 
static void usage_err() ; 
struct  sqlca sqlca; 
dh_i32_t    SQLCODE; 
main (int argc, char **argv) 
{ 
/* 
**    1. DECLARE variable needed for connecting to a database  
*/      
      EXEC SQL BEGIN DECLARE SECTION ; 
      char    connect_string_v[120] ; 
      EXEC SQL END DECLARE SECTION ; 
      char  sel_stmt[255] ; 
       
      if (argc != 3)  
          { 
          usage_err () ; 
          } 
/* 
** Save input parameters: 
**    - 1st param => connect_string_v 
**    - 2nd param => sel_stmt 
*/  
      strcpy (connect_string_v, argv[1]) ; 
      strcpy (sel_stmt, argv[2]) ;       
/* 
**  2. Define WHENEVER statement to handle SQLERROR condition. 
**  3. CONNECT to the database named in connect_string. 
**   
** 14. After SELECT operation, DISCONNECT from the database. 
*/ 
      EXEC SQL WHENEVER SQLERROR GOTO mainerr ; 
      EXEC SQL CONNECT TO :connect_string_v AS 'conn1' ; 
      DynSel(sel_stmt) ; 
      EXEC SQL DISCONNECT 'conn1' ; 
       
/* 
** If control passes here, SELECT operation completed with no SQL errors. 
*/ 
      exit (0) ; 
/* 
** Error Handling; control passes here 
** WHENEVER there is an SQLERROR in main() 
*/ 
mainerr: 
      if (sqlca.sqlcode) 
          { 
          printf ("SQL Error (%ld) %s", sqlca.sqlcode, sqlca.sqlerrm) ; 
          exit (1) ; 
          }  
} /* end main() */ 
/********************************************************************** 
** 
** DynSel(): process a dynamic SELECT statement.  
**  
**********************************************************************/ 
static void DynSel (char *sel_stmt) 
{ 
/* 
**    4. DECLARE variables needed in SELECT statement.  
**       NOTE 1: By program convention, host variables end with "_v". 
**       NOTE 2: Variables with datatype "int" are invalid 
**               in the DECLARE SECTION.  
*/ 
      char  errmesg[80] ; 
      int   i,j ; 
      EXEC SQL BEGIN DECLARE SECTION ; 
      char  sel_stmt_v[255] ;     /* INPUT SELECT stmt     */ 
      short int_p1_v ;            /* OUTPUT 1st int column */  
      short int_p2_v ;            /* OUTPUT 2nd int column */ 
      char  char_p_v[100] ;       /* OUTPUT CHAR column    */ 
      EXEC SQL END DECLARE SECTION ; 
  
/* 
**    5.  Name WHENEVER routine to handle SQLERROR. 
**    6.  PREPARE the dynamic SELECT statement. 
**    7.  DECLARE cursor for the prepared SELECT statement. 
**        NOTE: You must set input parameter values before OPEN CURSOR. 
**        If your query has input parameters, you must define them in 
**        the DECLARE SECTION. 
**    8.  OPEN the declared cursor. 
**        NOTE: For static statements, if a DECLARE CURSOR 
**              statement contains references to automatic variables, 
**              the OPEN CURSOR statement must be in the same C function. 
** 
**    9.  Name WHENEVER routine for NOT FOUND condition. 
**   10.  FETCH a row and print results until no more rows.      
*/ 
   
      strcpy(sel_stmt_v, sel_stmt) ; 
      
      EXEC SQL WHENEVER SQLERROR GOTO selerr ; 
      EXEC SQL PREPARE stmtid from :sel_stmt_v ; 
      EXEC SQL DECLARE dyncur CURSOR FOR stmtid ;              
      EXEC SQL OPEN dyncur ;             
      EXEC SQL WHENEVER NOT FOUND GOTO seldone ; 
/* 
**  One way to limit the number of rows returned is to 
**  set a new value for "j" here. As supplied in the SPORTS200 database, 
**  the PUB.InventoryTrans table contains 75 rows. 
*/  
      j = 100; 
      for (i = 0; i < j; i++) 
           {  
           EXEC SQL FETCH dyncur INTO 
             :int_p1_v, :int_p2_v, :char_p_v ; 
           if (i == 0) 
               { 
               printf (" 1st col  2nd col  3rd col"); 
               printf (" -------  -------  --------"); 
               }   
           printf (" %d  %d  %s ",  
                   int_p1_v, int_p2_v, char_p_v) ;  
           } 
  
seldone: 
/* 
**   NOTE: The SQLCA is available for examination only while the  
**         cursor is open. 
** 
**   11.  CLOSE the cursor. 
**   12.  COMMIT the transaction to release locks. 
*/    
  
       printf ("Dynamic SELECT statement executed successfully.") ; 
       printf ("Number of rows returned = %ld ", sqlca.sqlerrd[2]) ; 
        
       EXEC SQL CLOSE dyncur ; 
       EXEC SQL COMMIT WORK ; 
       return ; 
selerr: 
/* 
**   13.  Direct processing to CONTINUE after SQLERROR. 
**   14.  If SQLERROR, ROLLBACK WORK to release locks.     
*/ 
      if (sqlca.sqlcode < 0) 
      { 
           strncpy (errmesg, sqlca.sqlerrm, sqlca.sqlerrml); 
           errmesg[sqlca.sqlerrml] = '' ; 
           printf ("SQL Error : %s", errmesg); 
      } 
      EXEC SQL WHENEVER SQLERROR CONTINUE; 
      EXEC SQL ROLLBACK WORK ; 
      exit (1); 
} /* end 4DynSel() */ 
static void usage_err ()  
{ 
    printf ("Usage: 4DynSel <connect_string> <SELECT statement>") ; 
    printf ("   connect_string (required) ") ; 
    printf ("   SELECT stmt listing 3 columns in double quotes (required) ") ; 
    printf ("   Additional parameters are invalid ") ; 
    exit (1) ; 
     
} /* end usage_err() */ 
  /* end 4DynSel.pc  */ 


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