Progress
Embedded SQL-92
Guide and Reference


3DynUpd.pc

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

3DynUpd.pc
/*********************************************************************** 
** File Name: 3DynUpd.pc                                              ** 
**                                                                    ** 
** Purpose: Illustrate processing of a dynamic non-SELECT statement.  ** 
**                                                                    ** 
**          All dynamic non-SELECT statements require similar         ** 
**          processing. To add complexity, add accepting and          ** 
**          processing of INPUT variables.                            ** 
**                                                                    ** 
** Overview:                                                          ** 
**   - Requires 2 input parameters:                                   ** 
**    (1) connect_string                                              ** 
**    (2) non-SELECT SQL-92 statement, requiring no INPUT variables;  ** 
**         constants permitted.                                       ** 
**   - As supplied, you can use sample program 4DynSel.pc to verify   ** 
**     results after executing this program.                          ** 
**                                                                    ** 
** 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.            ** 
**                                                                    ** 
**    12. After non-SELECT operation, DISCONNECT from database.       ** 
**                                                                    ** 
**   DynUpd()                                                         ** 
**     4. DECLARE variable for dynamic non-SELECT statement string.   ** 
**     5. Name WHENEVER routine to handle NOT FOUND and SQLERROR.     ** 
**     6. PREPARE the statement.                                      ** 
**     7. EXECUTE the prepared statement.                             ** 
**     8. COMMIT transaction if operation successful.                 ** 
**     9. Examine sqlca.sqlcode for exception conditions.             ** 
**    10. Direct processing to CONTINUE after SQLERROR.               ** 
**    11. If SQLERROR, ROLLBACK transaction.                          ** 
**                                                                    ** 
** Requirements for building executable program:                      ** 
** =============================================                      ** 
**   - Precompile, compile, and link,                                 ** 
**     creating *.exe                                                 ** 
**                                                                    ** 
**   - EXAMPLE for building in 1 step:                                **  
**     > esqlc 3DynUpd.pc -o DynUpd                                   ** 
**                                                                    ** 
** Requirements for executing:                                        ** 
** ===========================                                        ** 
**   - Your database running.                                         ** 
**     Can use copy of SPORTS2000 db, and issue UPDATE,               ** 
**        INSERT, and DELETE statements against these columns in      ** 
**        PUB.InventoryTrans table:                                   ** 
**            InvTransNum (datatype integer)                          ** 
**            Qty (datatype integer)                                  ** 
**            OrderNum (datatype integer)                             ** 
**   - Invoker must have authority to UPDATE target database.         ** 
**   - To invoke from command line:                                   ** 
**     > 3DynUpd {connect_string} {dynamic non-SELECT statement}      ** 
**   - INPUTS (positional)                                            ** 
**       connect_string, in URL form (required)                       ** 
**       dynamic non-SELECT statement, in double quotes (required)    ** 
**   - OUTPUTS                                                        ** 
**       Message to stdout, reporting success, NOT FOUND,             ** 
**       or exception condition                                       ** 
**   - EXAMPLES of dynamic non-SELECT statements that fit             ** 
**       the model of the sample programs:                            ** 
**     (1) "UPDATE PUB.InventoryTrans set Qty = 5                     ** 
**          WHERE InvTransNum = 81"                                   ** 
**     (2) "DELETE FROM PUB.InventoryTrans WHERE InvTransNum = 81"    ** 
**     (3) "INSERT INTO PUB.InventoryTrans                            ** 
**          (InvTransNum, Qty, OrderNum) values (81, 99, 185)"        ** 
** Side effects:                                                      ** 
** =============                                                      ** 
**   - Successful execution modifies the target database.             ** 
**   - Table is locked 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-92 Sample Program for V9.1A         ** 
**                                                                    ** 
***********************************************************************/ 
static void DynUpd(char *input_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     input_stmt[255] ; 
      if (argc != 3) 
          { 
          usage_err () ; 
          } 
     
/* 
** Save input parameters: 
**       - 1st param => connect_string_v 
**       - 2nd param => input_stmt 
*/ 
  
      strcpy (connect_string_v, argv[1]) ; 
      strcpy (input_stmt, argv[2]) ; 
/* 
**  2. Name WHENEVER routine to handle SQLERROR condition. 
**  3. CONNECT to the database named in 1st param. 
**   
** 12. After non-SELECT operation, DISCONNECT from the database. 
*/ 
      EXEC SQL WHENEVER SQLERROR GOTO mainerr ; 
      EXEC SQL CONNECT TO :connect_string_v AS 'conn1' ; 
      DynUpd(input_stmt) ; 
      EXEC SQL DISCONNECT 'conn1' ; 
/* 
** If control passes here, dynamic 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() */ 
  
/********************************************************************** 
** 
** DynUpd(char *input_stmt): process a dynamic non-SELECT statement.  
**  
**********************************************************************/ 
static void DynUpd (char *input_stmt) 
{ 
      char  errmesg[80];  
/* 
**   4. DECLARE variable for dynamic non-SELECT statement string.  
*/ 
      EXEC SQL BEGIN DECLARE SECTION ; 
      char sql_stmt_v[255] ; 
      EXEC SQL END DECLARE SECTION ; 
     strcpy(sql_stmt_v, input_stmt) ;  
/* 
** 
**    5. Name WHENEVER routine to handle NOT FOUND and SQLERROR. 
*/ 
  
      EXEC SQL WHENEVER SQLERROR GOTO nodyn ; 
      EXEC SQL WHENEVER NOT FOUND GOTO nodyn ; 
/* 
** Process the non-SELECT input statement 
**    6. PREPARE the statement 
**    7. EXECUTE the prepared statement 
**    8. COMMIT WORK 
*/ 
      EXEC SQL PREPARE dynstmt FROM :sql_stmt_v ;                 
      EXEC SQL EXECUTE dynstmt ; 
      EXEC SQL COMMIT WORK ; 
      printf ("Dynamic non-SELECT statement executed successfully.") ; 
         
      return ; 
/* 
** 
**    5. Name WHENEVER routine to handle NOT FOUND and SQLERROR. 
*/ 
  
      EXEC SQL WHENEVER SQLERROR GOTO nodyn ; 
      EXEC SQL WHENEVER NOT FOUND GOTO nodyn ; 
/* 
** Process the non-SELECT input statement 
**    6. PREPARE the statement 
**    7. EXECUTE the prepared statement 
**    8. COMMIT WORK 
*/ 
      EXEC SQL PREPARE dynstmt FROM :sql_stmt_v ;  
      EXEC SQL EXECUTE dynstmt ; 
      EXEC SQL COMMIT WORK ; 
      printf ("Dynamic non-SELECT statement executed successfully.") ; 
         
      return ; 
 nodyn:  
/*            
**   9. Examine sqlca.sqlcode for exception conditions. 
**  10. Direct processing to CONTINUE after SQLERROR. 
**  11. If SQLERROR, ROLLBACK transaction. 
*/ 
printf ("DEBUG, entering nodyn ") ; 
      if (sqlca.sqlcode == SQL_NOT_FOUND) 
          { 
          printf ("Requested row not found ") ; 
          } 
            
      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 3DynUpd() */ 
static void usage_err ()  
{ 
    printf ("Usage: 3DynUpd <connect string> <SQL-92 non-SELECT stmt> ") ; 
    printf ("   connect_string (required) ") ; 
    printf ("   SQL-92 non_SELECT statement in quotes (required) ") ; 
    printf ("   Additional parameters are invalid ") ; 
    exit (1) ; 
     
} /* end usage_err() */ 
  /* end 3DynUpd.pc  */ 


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