RPGLE & SQL

pts.
Tags:
RPG
RPGLE
I am writing my first SQl in an RPG The statement I am using is: UPDATE AQUA20/XNMSGX SET ISTS = 'd', ISTATE = 'a' WHERE ISTS='C' and ISTATE='C'and idate<20010501 1. Does the statement look okay 2. Can I, and how would I, use a variable in the statement - the date I am checking against will change daily

Answer Wiki

Thanks. We'll let you know when a new response is added.

A “Program Described Variable” coded in an “Embedded SQL Statement” is called a “Host Variable”. A Host Variable is ALWAYS coded with a Colon ( : ) in front of the host variable. An Example being like this.
(idate < :test_date)

Hope that Helps,
Mark Schroeder

Discuss This Question: 17  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Raddy59
    Would this work: EVAL SqlStmStr = 'UPDATE inmsp + 'SET ists ="D" AND ' + 'SET istate ="A" ' + 'WHERE ists = "U" AND + 'istate = "C" AND ' + 'ircdte
    0 pointsBadges:
    report
  • SteveCCNJ
    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
    0 pointsBadges:
    report
  • SteveCCNJ
    Sorry for the typos - each of the lines starting with C= should actually be C+ (It was before I had caffeine)
    0 pointsBadges:
    report
  • astradyne
    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
    370 pointsBadges:
    report
  • Raddy59
    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
    0 pointsBadges:
    report
  • astradyne
    Are both date fields in YYYYMMDD or YYMMDD order?
    370 pointsBadges:
    report
  • Raddy59
    both dates are YYYMMDD
    0 pointsBadges:
    report
  • Raddy59
    I mean YYYYMMDD I think it looks okay.
    0 pointsBadges:
    report
  • SteveCCNJ
    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.
    0 pointsBadges:
    report
  • TimRac
    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?
    25 pointsBadges:
    report
  • astradyne
    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
    370 pointsBadges:
    report
  • Raddy59
    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.
    0 pointsBadges:
    report
  • TimRac
    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?
    25 pointsBadges:
    report
  • astradyne
    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
    370 pointsBadges:
    report
  • Raddy59
    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
    0 pointsBadges:
    report
  • Mrs9693nj
    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
    0 pointsBadges:
    report
  • astradyne
    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
    370 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following