embedded sql update
I have a prog that should update all KY cust master records with the new code AR.
I run the program but nothing happens. The file remains unchanged.
I am new to embedded sql so what am I doing wrong ??
D OLDWH S 2 INZ('KY') D NEWWH S 2 INZ('AR') * Get all records with 'KY' C/EXEC SQL C+ UPDATE TRNLIB/CMP C+ SET CMWH = :NEWWH C+ WHERE CMWH = :OLDWH C/END-EXEC C EVAL *INLR = '1'
Software/Hardware used:
I run the program but nothing happens. The file remains unchanged.
I am new to embedded sql so what am I doing wrong ??
D OLDWH S 2 INZ('KY') D NEWWH S 2 INZ('AR') * Get all records with 'KY' C/EXEC SQL C+ UPDATE TRNLIB/CMP C+ SET CMWH = :NEWWH C+ WHERE CMWH = :OLDWH C/END-EXEC C EVAL *INLR = '1'
Software/Hardware used:
ASKED:
December 14, 2009 2:55 PM
UPDATED:
December 15, 2009 1:34 AM
Answer Wiki:
A couple of thoughts.
1. do you need to use COMMIT(NC). It could be changing the data but because of no commit, it does a rollback.
2. If you run the statement with STRSAL does it change any data/
To see all answers submitted to the Answer Wiki: View Answer History.
Thanks for the info..
I poked around a lot more and found this example code.
Put this in before my previous logic and all is well now.
C/EXEC SQL
C+ Set Option
C+ Commit = *NONE,
C+ CloSqlCsr = *ENDMOD
C/END-EXEC
Better would be to start commitment control, run the UPDATE, and then commit the updates. Simple and allows recovery if something unexpected happens in the middle. (Power fails, disk drive dies, etc.)
Tom
p.s. I tend to to run WITH ‘NC’ myself even though I know better.