SQLRPGLE Update not working 2

[tdacquisto]

Hi all,
I've written an RPGLE pgm and wanted to put a simple SQL statement into it, so I changed the source type to SQLRPGLE and imbedded the following code into my pgm:

C/EXEC SQL

C+ UPDATE EDIINHL1 SET CO = 'PRT' WHERE HDRKEY = :HDR_key
C/END-EXEC
The pgm compiles normally and when I run it, it doesn't crash. My file does not get updated, however and when I look in the joblog, here's what I see:

Query options retrieved file QAQQINI in library QUSRSYS.
**** Starting optimizer debug message for query .
The OS/400 Query access plan has been rebuilt.
Query options used to build the OS/400 query access plan.
All access paths were considered for file EDIINH.
Access path of file EDIINHL1 was used by query.
Query options retrieved file QAQQINI in library QUSRSYS.
Member EDIINH not journaled to journal *N.
EDIINH in EDI not valid for operation.

I can interactively start SQL and run the update and there is not a problem. What does journaling of my file have to do with the SQL statement? The file is not journaled.

Any ideas?

Tom.

 

[iSeriesCodePoet]

I don't know why it needs to be journaled, but I had to do this at one point myself. Let me know if you need the instructions on how to enable it.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[iSeriesCodePoet]

... on second thought I will provide it here anyways for others to look at.

HOW-TO: Journal Files

CRTJRNRCV JRNRCV(MYLIB/MYRECV)

CRTJRN JRN(MYLIB/MYJRN) JRNRCV(MYLIB/MYRECV)

STRJRNPF FILE(List all files here) JRN(MYLIB/MYJRN) IMAGES(*BOTH)

ENDJRNPF FILE(*ALL) JRN(MYJRN)

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[jack1955]

The COMMIT parm of the CRTSQLRPG) defaults to *CHG- change it to *NONE.

 

[tdacquisto]

Hey Jack,

Thanks Bud, that totally worked!



Tom.

 

[iSeriesCodePoet]

Hmm.. I never looked at the parms before. Thanks! I will have to remember that.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[cjkoontz]

See this old article from MC:

http://mcpressonline.com/mc/.5bfa43f1!sectionID=.5bfbaeeb

The CRTSQLRPG command will compile SQLRPG[le] source members. Be aware that the COMMIT parameter defaults to *CHG. Unless you are journaling your files, you will want to change the COMMIT parameter to a value of *NONE; using *CHG requires journaling to be active. If you don't use journaling, you can create your own version of CRTSQLRPG in a library higher than QSYS in the library list and change the default value of the COMMIT parameter to *NONE.

 

[jools1971]

Hi

I use embedded SQL all the time and for various customers / machines. The problem with changing compile options is that some customers don't want you to! Or they may recompile something and forget..... The solution is

Place the following code somewhere in your SQLRPGLE source

C/EXEC SQL
C+ Set Option
C+ Commit = *NONE,
C+ CloSqlCsr = *ENDMOD
C/END-EXEC

It does not have to go anywhere in particular, its picked up by the pre-compiler, but I put it my my initialisation routine for clarity.

You notice the CloSqlCsr option here is another thing I used which can sometimes cause records to to appear until a job ends. The alternative is *ENDACTGRP which means that the records can be sat pending for ages, especially if run interactively.

This methods removes the "minor change", "quick re-compile", "scratching head over a minor mod that should have just worked" totally forgetting the compile options.

Sorry for the late reply

Julian