Run SQL DELETE statement using RPG2SQL Integrator and return a counter indicating how many records were deleted

From Support

If you have a question or seek clarification, please call Technical Support.

Problem:

I want to run SQL DELETE statement using RPG2SQL Integrator and I'd also like to return a counter indicating how many records were deleted.

Solution:

The following combined SQL statement will run a DELETE SQL query to delete all records in the NameAddr SQL Server table where last name = Jones and return the number of records that were deleted.

RPG Sample Code

 *                  ** Delete all records and return @@ROWCOUNT         
 *                  ** special value from SQL Server. This field        
 *                  ** should tell us how many rows were deleted.       
C                   Eval      Rtn = SQL_RunSQLSel(SQL_Socket:           
C                             'SET NOCOUNT ON ' +                       
C                             'DELETE FROM NameAddr Where Last= ' +
C                             '''Jones'' ' +                 
C                             'SELECT @@ROWCOUNT AS CTR')               
                                                                        
 *                  ** Convert @@RowCount Field into Numeric            
C                   Z-Add     0             RtnCount         30 9       
C                   Eval      RtnCount=SQL_GetFldNum(SQL_Socket:'CTR')  


COBOL Sample Code

 *----------------------------------           
 *** COBOL Working Storage Parameters
 *----------------------------------           
  01 SQL_SQLSEL   PIC  X(4096).    
  01 WORKFLDNAME    PIC  X(256).               
  01 WORKDELRECS  PIC  S9(21)V9(9) COMP-3.     

 *    ----------------------------------                           
 *     ** Delete all records and return @@ROWCOUNT         
 *     ** special value from SQL Server. This field        
 *     ** should tell us how many rows were deleted.       
 *    ----------------------------------                           
      MOVE "SET NOCOUNT ON DELETE FROM NAMEADDR WHERE Last='Jones' 
 -    "SELECT @@ROWCOUNT AS CTR" TO SQL_SQLSEL                     

 *     ** Set field name to return when getting record count
      MOVE "CTR" TO WORKFLDNAME                                    
                                                                   
 *    ----------------------------------                           
 *    ** Run SQL Query to Delete Records and Return Rec Count      
 *    ----------------------------------                           
      CALL PROCEDURE 'SQLRUNSQLSEL'                                
           USING SQL_SOCKET SQL_SQLSEL RETURNING SQL_RTN.          
                                                                   
 *    ----------------------------------                           
 *    ** Retrieve Returned Record Count from SQL Server Recordset  
 *    ** to the WORKDELRECS field. 
 *    ----------------------------------                           
      CALL PROCEDURE 'SQLGETFLDNUM'                                
           USING SQL_SOCKET WORKFLDNAME RETURNING WORKDELRECS.