Progress
Embedded SQL-92
Guide and Reference


1StatUpd.pc

This section contains the complete code listing of an ESQL-92 program with an embedded static SQL-92 UPDATE statement.

1StatUpd.pc
/*********************************************************************** 
** File Name: 1StatUpd.pc                                             ** 
**                                                                    ** 
** Purpose: Illustrate processing of a static UPDATE statement.       ** 
**                                                                    ** 
**          Other static non-SELECT statements require similar        ** 
**          processing.                                               ** 
**                                                                    ** 
** Overview:                                                          ** 
**   - Accepts connect_string, and optionally a transaction number.   ** 
**   - Updates the Qty column in the PUB.InventoryTrans table,        ** 
**     incrementing by 1.                                             ** 
**   - As supplied, you can use sample program 2StatSel.pc to verify  ** 
**     results after executing this program.                          ** 
**   - You can modify this program, changing the statement to modify  ** 
**     different column(s) in a different table.                      ** 
**                                                                    ** 
** 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.            ** 
**                                                                    ** 
**    11. After UPDATE operation, DISCONNECT from the database.       ** 
**                                                                    ** 
**   StatUpd()                                                        ** 
**     4. DECLARE variable(s) needed in UPDATE query.                 ** 
**     5. Name WHENEVER routine to handle NOT FOUND and SQLERROR.     ** 
**     6. EXECUTE the UPDATE statement.                               ** 
**     7. COMMIT if UPDATE successful.                                ** 
**     8. Examine sqlca.sqlcode for exception conditions.             ** 
**     9. Direct processing to CONTINUE after SQLERROR.               ** 
**    10. If SQLERROR, ROLLBACK transaction.                          ** 
**                                                                    ** 
** Requirements for building executable program:                      ** 
** =============================================                      ** 
**   - Precompile, compile, and link,                                 ** 
**     creating *.exe                                                 ** 
**                                                                    ** 
**   - EXAMPLE for building in 1 step:                                ** 
**     > esqlc 1StatUpd.pc -o 1StatUpd                                ** 
**                                                                    ** 
** Requirements for executing:                                        ** 
** ===========================                                        ** 
**   - Copy of SPORTS2000 database, OR                                ** 
**     Database containing InventoryTrans table, owner PUB            ** 
**     InventoryTrans table includes columns:                         ** 
**        InvTransNum (datatype short)                                ** 
**        Qty (datatype short)                                        ** 
**   - Database running.                                              ** 
**   - Invoker must have authority to UPDATE PUB.InventoryTrans.      ** 
**   - To invoke from command line:                                   ** 
**     > 1statupd {connect_string} [Transaction_number]               ** 
**   - INPUTS (positional)                                            ** 
**       connect_string, in URL form (required).                      ** 
**       Transaction number, to match InvTransNum column (optional).  ** 
**       As supplied, the InvTransNum column in the                   ** 
**       Pub.InventoryTrans table has values ranging from 4 to 106.   ** 
**   - OUTPUTS                                                        ** 
**       Message to stdout, reporting success, NOT FOUND,             ** 
**       or exception condition.                                      ** 
**   - EXAMPLES for invoking from command line:                       ** 
**     (1) 1StatUpd progress:T:localhost:1024:y2ksports 55            ** 
**     (1) 1StatUpd progress:T:localhost:800:newsports 71             ** 
** Side effects:                                                      ** 
** =============                                                      ** 
**   - Successful execution updates the Qty column in the             ** 
**     PUB.InventoryTrans table.                                      ** 
**   - Locks held while 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 StatUpd(int TransNum) ; 
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 ; 
       
      int     TransNum ; 
  
      if ((argc != 2) && (argc != 3)) 
          { 
          usage_err () ; 
          } 
  
/* 
** Save input parameters: 
**       - connect_string 
**       - if supplied, save transaction_number, 
**         else default to transaction number 81. 
*/ 
  
      strcpy (connect_string_v, argv[1]) ; 
      if (argc == 3) 
          { 
          TransNum = atoi(argv[2]) ; 
          } 
      else 
          { 
          TransNum = 81 ; 
          } 
/* 
**  2. Name WHENEVER routine to handle SQLERROR condition. 
**  3. CONNECT to the database named in connect_string. 
**   
** 11. After UPDATE operation, DISCONNECT from the database. 
*/ 
  
      EXEC SQL WHENEVER SQLERROR GOTO mainerr ; 
      EXEC SQL CONNECT TO :connect_string_v AS 'conn1' ; 
      StatUpd(TransNum) ; 
      EXEC SQL DISCONNECT 'conn1' ;/* 
** If control passes here, UPDATE 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() */ 
  
/********************************************************************** 
** 
** StatUpd(TransNum): process a static UPDATE statement.  
**  
**********************************************************************/ 
  
static void StatUpd (int TransNum) 
{ 
      char  errmesg[80];  
/* 
**    4. DECLARE variable needed in UPDATE statement.  
*/ 
      EXEC SQL BEGIN DECLARE SECTION ; 
      short InvTransNum_v ; 
      EXEC SQL END DECLARE SECTION ; 
/* 
**    Set inventory transaction number. 
** 
**    5. Define WHENEVER routine to handle NOT FOUND and SQLERROR 
*/ 
      InvTransNum_v = TransNum ; 
       EXEC SQL WHENEVER SQLERROR GOTO noupdate ; 
      EXEC SQL WHENEVER NOT FOUND GOTO noupdate ; 
/* 
** Update Qty in Inventory Transaction (PUB.InventoryTrans) table 
**     6. Execute the UPDATE statement 
**     7. COMMIT if UPDATE successful 
*/ 
  
      EXEC SQL 
          UPDATE PUB.InventoryTrans 
          SET Qty = Qty + 1 
          WHERE InvTransNum = :InvTransNum_v ; 
      EXEC SQL COMMIT WORK ; 
       
      printf ("Static UPDATE statement executed successfully.") ; 
      printf ("For InvTransNum %d, Qty value incremented by 1.", 
               InvTransNum_v) ;           
      return ; 
  
      noupdate:  
/*            
**     8. Examine sqlca.sqlcode for exception conditions. 
**     9. Direct processing to CONTINUE after SQLERROR. 
**    10. If SQLERROR, ROLLBACK transaction. 
*/ 
      if (sqlca.sqlcode == SQL_NOT_FOUND) 
          { 
          printf ("InvTransNum %d not found in InventoryTrans table ", 
          InvTransNum_v); 
          } 
            
      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 1StatUpd() */ 
static void usage_err ()  
{ 
    printf ("Usage: 1StatUpd <connect_string> [InvTransNum] ") ; 
    printf ("   connect_string (required) ") ; 
    printf ("   InvTransNum (optional) ") ; 
    printf ("   Additional parameters are invalid ") ; 
    exit (1) ; 
     
} /* end usage_err() */ 
  /* end 1StatUpd.pc */ 


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