Unable to Update file After SQL insert SQLRPGLE

Question

Good Day all,

I am writing a program to build a report where I build the SQL statement to insert selected records into a file and after that insert I would like to do a simple update on the file to change a select fields in some records.

The problem is that after the insert is run, anytime I try to update the file I get a Record or File in use error.

I have tried updating it programatically with sqlrpgle and with I/O read and set functions and I have even tried just updating the file in STRSQL after I run the program and I all come to the same error.

I suspect I am not closing something properly but I am not sure what.

Code is as follows

// Assign SQL Query

    sqlstmt = 'insert into jallib/orhsrpt ('+
              'oacctd, oacmp, oaord, oacust, o8type, ' +
              'o8text, o8date, o8time ) ' +
              'select oacctd, oacmp, oaord, oacust, ' +
              'o8type, o8text, o8date, o8time ' +
              'from r50files.vcohead ' +
              'join r50files.vcopkct ' +
              'on oacmp = o8cmp and oaord = o8ord ' +
              'where oacmp = 1 ' +
              'and o8type not in (' +
              '''C'',''a'',''H'',''E'',''F'', '+
              '''A'',''1'',''N'',''M'') ' +
              'and oacctd = ' + curdate +
              ' order by oaord, o8time ';

    // Prepare for multiple sql statements 

    exec sql
        Set Option Commit = *NONE; 

    // Clear output file before executing SQL

    exec sql
        Delete from jallib/orhsrpt;

    if sqlcode < *zeros; 
        errmsg = 'Delete of file failed'; 
    endif; 

    // Execute SQL Insert statement

    exec sql prepare sqlsel from :sqlstmt;
    exec sql execute sqlsel;

    if sqlcode < *zeros; 
        errmsg = 'Insert of file failed'; 
    endif;

    // Update file data

    exec sql
        Set Option clossqlcsr = *ENDMOD; 

    exec sql  
        Update jallib/orhsrpt 
        set o8text = 'Order Invoiced' 
        where o8type = 'I' 

The error from STRSQL is as follows

Row or object ORHSRPT in JALLIB type *FILE in use.

Answer 1

The quick answer is that the insert is not closed because your module hasn't ended according to the Set Option you specified. However, the correct answer here is that there is no reason for you to be using dynamic SQL statements at all. They are slower and more error prone in general and you run into issues like this one. You should instead use a regular embedded SQL statement as below:

exec sql
    set option commit = *NONE; 

// Clear output file before executing SQL

exec sql
    delete from jallib/orhsrpt;

if sqlstate <> *zeros; 
    errmsg = 'Delete of file failed'; 
endif; 

exec sql
    insert into jallib/orhsrpt (
        oacctd, oacmp, oaord, oacust,
        o8type, o8text, o8date, o8time )
    select oacctd, oacmp, oaord, oacust, o8type,
           o8text, o8date, o8time
    from r50files.vcohead join r50files.vcopkct
        on oacmp = o8cmp and oaord = o8ord
    where oacmp = 1 and o8type not in (
              'C','a','H','E','F', 'A','1','N','M') and
          oacctd = :curdate
    order by oaord, o8time;

exec sql  
    update jallib/orhsrpt 
    set o8text = 'Order Invoiced' 
    where o8type = 'I' 

This is better practice and should solve your issue.