Would this work:

EVAL SqlStmStr = ‘UPDATE inmsp
+ ‘SET ists =”D” AND ‘
+ ‘SET istate =”A” ‘
+ ‘WHERE ists = “U” AND
+ ‘istate = “C” AND ‘
+ ‘ircdte

You don’t need to compose a SQL String for this. Just do as below – it is far more self evident. (I wouldn’t specify the library if it were me.)

C/EXEC SQL
C+ UPDATE XNMSGX
C+ SET ISTS = ‘d,
C= ISTATE = ‘a’
C+ WHERE ISTS = ‘C’
C+ AND ISTATE = ‘C
C= AND IDate = :YestDate
C/END-EXEC

Sorry for the typos – each of the lines starting with C= should actually be C+ (It was before I had caffeine)

Whichever method you use, the important thing to note is that the host (program) variables are prefixed with a colon (:) in the SQL statement. So “:YestDate” (my quotes) is a program variable, while “YestDate” is a file/table variable.

Jonathan

I tried this, and it doesn’t seem to be doing the date thing. Any ideas?
C/EXEC SQL
C+ UPDATE aqua20data/INMSGP
C+ SET ISTS=’D',
C+ ISTATE=’A’
C+ WHERE ISTS=’U’
C+ AND ISTATE=’C’
C+ AND Ircdte

Are both date fields in YYYYMMDD or YYMMDD order?

both dates are YYYMMDD

I mean YYYYMMDD

I think it looks okay.

I would stick the pgm into debug and step it through, checking the variables before and after it executes the SQL. There’s nothing wrong with the SQL itself that I can see.

Is the assumption that both the program variable and the field from the file both dates? Not just number or character fields in date format?

Is the file being journalled? To use SQL to update the file, the file needs to be journalled.

You can add the file to a journal using:

STRJRNPF FILE(EXAMPLES/MYFILE) JRN(QSQJRN)

and then rerun your program.

Jonathan

I actually put this in the code, and it seems to have done the trick
C/EXEC SQL
C+ Set Option
C+ Commit = *NONE,
C+ CloSqlCsr = *ENDMOD
C/END-EXEC

Thanks everyone
P.

I would like to know why this worked. Is it because the file is journaled and needs a commit statement to make any changes effective?

Hi Tim

The default for SQL is for commitment control to be used when updating records in the table – just in case the job is intrrupted midway through a mass update.

The “Set Option” SQL statement acts in a similar manner to the RPG “H” spec in that it allows you to set compile/program attributes for the SQL pre-processor.

The posted code:

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

is turning commitment control and forcing the SQL cursor to be closed when the module ends. I have to admit that I slapped my head when I read the post on the Set Option – I was too quick to reply to the problem without giving it enough thought. Ouch.

Jonathan

I asked the question, and I am just pleased it is resolved. I just found a colleague who had experienced the same problem earlier and hey presto!

One thing – I actually had the program written in RPG in about 5 minutes, and it worked fine my (manager wanted it in SQL). SQL statements are a bit (a lot) of a black box.

thanks all once again

Your code solution is neat & correct, yet IMHO it doesn’t pertain to “actual logic” which might cause confusion?
C/EXEC SQL
C+ Set Option
C+ Commit = *NONE,
C+ CloSqlCsr = *ENDMOD
C/END-EXEC

Instead, (my own preference) you can accomplish the same thru compile parms, like example below…

Create SQL ILE RPG Object (CRTSQLRPGI)

Type choices, press Enter.

Object . . . . . . OBJ > WEEKOFYEAR
Library . . . . > PROG14
Source file . . . SRCFILE > QRPGLESAV
Library . . . . > PROG14
Source member .. . SRCMBR > WEEKOFYEAR
Commitment control .COMMIT > *NONE

(cut-out some for sake of space)

Close SQL cursor . CLOSQLCSR > *ENDMOD

(cut-out some for sake of space)

Debugging view . .. DBGVIEW > *SOURCE (optional, allows source to be stepped thru during debug)

Glad you were able to resolve your issue.

Mark Schroeder

Hi Mark

Yes, the “Set Options” is designed to allow you to override the compiler options in the same way that the H-spec keywords do.

The benefit of using Set Options (and H-Spec keywords for that matter) is that anybody maintaining the source a) doesn’t need to remember compile time options and b) can identify anything that might not be the “norm” in the program (for example specific date formats, binding directories, activation groups, etc)

Jonathan