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.

Did you tried this with explicit commit statement after the insert? — user7392562
– user7392562, Commented Oct 24, 2018 at 15:54

Player1st · Accepted Answer · 2018-10-24 22:43:09Z

8

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.

edited Oct 24, 2018 at 22:43

answered Oct 24, 2018 at 17:42

Player1st

1,60511 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Javed Ali Over a year ago

Great ! Thanks this works. I initially did it dynamically anticipating the request to be modified by the users. But I will just work it around with subsequent update statements

Player1st Over a year ago

As some additional input, if they are possibly filtering on different fields, I often use something like the following: where (:curdate = '0001-01-01' or oacctd = :curdate) and .... It's a nice non-dynamic way to filter on different possilbe selections.

Collectives™ on Stack Overflow

Unable to Update file After SQL insert SQLRPGLE

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related