Progress
Embedded SQL-92
Guide and Reference


2StatSel.pc

This section provides the complete code listing of an ESQL-92 program that contains an embedded static SQL-92 SELECT statement.

2StatSel.pc
/*********************************************************************** 
** File Name: 2StatSel.pc                                             ** 
**                                                                    ** 
** Purpose: Illustrate processing of a static SELECT statement.       ** 
**                                                                    ** 
** Overview:                                                          ** 
**   - Accepts a connect_string.                                      ** 
**   - Issues a SELECT statement, selecting the InvTransNum, Qty,     ** 
**     and OrderNum columns for all rows in the PUB.InventoryTrans    ** 
**     table.                                                         ** 
**   - As written, you can use this program to verify results from    ** 
**     the 1StatUpd.pc sample program.                                ** 
**   - You can modify this program to issue a query against a         ** 
**     different database and table.                                  ** 
**                                                                    ** 
** SQL Steps, in logical order:                                       ** 
** ============================                                       ** 
**   main()                                                           ** 
**     1. DECLARE variable(s) needed for connecting to a database.    ** 
**     2. Name WHENEVER routine to handle SQLERROR condition.         ** 
**     3. CONNECT to database specified in connect_string.            ** 
**                                                                    ** 
**    14. After SELECT operation, DISCONNECT from database.           ** 
**                                                                    ** 
**   StatSel()                                                        ** 
**     4. DECLARE output variables needed in SELECT statement.        ** 
**     5. Name WHENEVER routine to handle SQLERROR.                   ** 
**     6. DECLARE cursor for the SELECT statement.                    ** 
**     7. OPEN the declared cursor.                                   ** 
**     8. Name WHENEVER routine for NOT FOUND condition.              ** 
**     9. FETCH a row, report results until no more rows.             ** 
**    10. CLOSE the cursor.                                           ** 
**    11. If success, COMMIT WORK to release locks.                   ** 
**    12. Direct processing to CONTINUE after SQLERROR.               ** 
**    13. If SQLERROR, ROLLBACK transaction to release locks.         ** 
**                                                                    ** 
** Requirements for building executable program:                      ** 
** =============================================                      ** 
**   - Precompile, compile, and link,                                 ** 
**     creating *.exe                                                 ** 
**                                                                    ** 
**   - EXAMPLE for building in 1 step:                                ** 
**     > esqlc 2StatSel.pc -o 2StatSel.exe                            ** 
**                                                                    ** 
** Requirements for executing:                                        ** 
** ===========================                                        ** 
**   - Copy of SPORTS2000 database, or                                ** 
**     Database containing InventoryTrans table, owner PUB            ** 
**     Table must contain columns:                                    ** 
**        InvTransNum (short)                                         ** 
**        Qty (short)                                                 ** 
**        OrderNum (short)                                            ** 
**   - Database running                                               ** 
**   - Invoker must have authority to access PUB.InventoryTrans       ** 
**   - To invoke from command line:                                   ** 
**     > 2StatSel {connect_string}                                    ** 
**   - INPUT                                                          ** 
**       connect_string, in URL form (required)                       ** 
**   - OUTPUT                                                         ** 
**       Message to stdout, reporting success, NOT FOUND,             ** 
**       or exception condition.                                      ** 
**       One display row for each table row retrieved in the query.   ** 
**                                                                    ** 
** Side effects:                                                      ** 
** =============                                                      ** 
**   - Returns 3 columns from all rows in the PUB.InventoryTrans      ** 
**     table.                                                         ** 
**   - Holds lock on the table while transaction is active.           ** 
**                                                                    ** 
************************************************************************ 
**                                                                    ** 
** copyright (c) Progress Software Corporation, Bedford MA, 1999,2000 ** 
**                    All rights reserved.                            ** 
**                                                                    ** 
** Revision History:                                                  ** 
**                                                                    ** 
** Date      By              Revision                                 ** 
** ====      ==              ========                                 ** 
** 10/1999   DB Doc          ESQL Sample Program for V9.1A            ** 
**                                                                    ** 
***********************************************************************/ 
static void StatSel() ; 
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 ; 
       
      if (argc != 2)  
          { 
          usage_err () ; 
          } 
/* 
** Save connect_string input parameter. 
*/  
      strcpy (connect_string_v, argv[1]) ; 
  
/* 
**  2. Name WHENEVER routine 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' ; 
      StatSel() ; 
      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() */ 
/********************************************************************** 
** 
** StatSel(): process a static SELECT statement.  
**  
**********************************************************************/ 
static void StatSel () 
{ 
/* 
**    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 a DECLARE SECTION.  
*/ 
      char  errmesg[80] ; 
      int   i,j ; 
      EXEC SQL BEGIN DECLARE SECTION ; 
      short InvTransNum_v ; 
      short Qty_v ; 
      short OrderNum_v ; 
      EXEC SQL END DECLARE SECTION ; 
/* 
**    5.  Name WHENEVER routine to handle SQLERROR. 
** 
**    6.  DECLARE cursor for the SELECT statement. 
**        NOTE: You must set input parameter values before OPEN CURSOR. 
**        The static query in this program does not have input parameters. 
** 
**    7.  OPEN the 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. 
** 
**    8.  Name WHENEVER routine to handle NOT FOUND condition. 
**    9.  Retrieve a row with a FETCH operation until no more rows.      
*/ 
      
      EXEC SQL WHENEVER SQLERROR GOTO selerr ; 
      EXEC SQL DECLARE stcur CURSOR FOR   
            SELECT InvTransNum, Qty, 
            OrderNum FROM PUB.InventoryTrans ;             
      EXEC SQL OPEN stcur ;       
      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 stcur INTO 
             :InvTransNum_v, :Qty_v, :OrderNum_v ; 
           if (i == 0) 
               { 
               printf ("      InvTransNum   Qty     OrderNum"); 
               printf ("      -----------   ---     --------"); 
               }   
           printf (" %d     %d       %d ",  
                   InvTransNum_v, Qty_v, OrderNum_v) ;  
           } 
seldone: 
/* 
**   NOTE: The SQLCA is available for examination only while the  
**         cursor is open. 
** 
**   10.  CLOSE cursor. 
**   11.  COMMIT WORK to release locks. 
**        A SELECT statement does not modify the database.     
*/    
       printf ("Static SELECT statement executed successfully.") ; 
       printf ("Number of rows returned = %ld ", sqlca.sqlerrd[2]) ; 
        
       EXEC SQL CLOSE stcur ; 
       EXEC SQL COMMIT WORK ; 
  
       return ; 
selerr: 
/* 
**   12.  Direct processing to CONTINUE after error. 
**   13.  If SQLERROR, ROLLBACK transaction 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 2StatSel() */ 
  
static void usage_err ()  
{ 
    printf ("Usage: 2StatSel <connect_string> ") ; 
    printf ("   connect_string (required) ") ; 
    printf ("   Additional parameters are invalid ") ; 
    exit (1) ; 
     
} /* end usage_err() */ 
  /* end 2StatSel.pc */ 


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