Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.
I have an RPGLE program connecting to an Oracle database and processing records on an iseries.
I am using Scott Klement's service program and binding directory stuff and it works great.
I need to update the Oracle view (Column UPLD to a ‘Y’ so the records fall out)
I need to update each Oracle record but only at the end of the read loop.
I have tried to update two ways: with and without “ around Licplt which is a field being used in the program.
I did it this way to control each row. There should only be one license plate (from_lic) and I made it match my work field.
Below is the Java error and my code.
I don't know if it's Oracle or my syntax causing the error.
So close…
Thanks,
Pete
Display Spooled File
File . . . . . : QPJOBLOG Page/Line 3/21
Control . . . . . W+10 Columns 41 - 118
Find . . . . . .
....+....5....+....6....+....7....+....8....+....9....+....0....+....1....+...
tement . . . . . . . . . : 20
module . . . . . . . . . : JDBCR4
procedure . . . . . . . : JDBC_EXECUPD
tement . . . . . . . . . : 3084
sage . . . . : Java exception received when calling Java method.
se . . . . . : RPG procedure JDBC_EXECU in program PMETSOPULO/JDBCR4
eceived Java exception "java.sql.SQLException: ORA-00904: "licplt": invalid
dentifier " when calling method "executeUpdate" with signature
(Ljava.lang.String;)I" in class "java.sql.Statement". Recovery . . . :
ontact the person responsible for program maintenance to determine the
ause of the problem. Technical description . . . . . . . . : If the
xception indicates that the Java class was not found, ensure the class for
he method is in the class path. If the exception indicates that the Java
ethod was not found, check the method name and signature. If the signature
s not correct, change the RPG prototype for the method, or change the Java
ethod, so that the return type and parameter types match. You can determine
More...
F3=Exit F12=Cancel F19=Left F20=Right F24=More keys
Columns . . . : 6 76 Browse PMETSOPULO/QRPGLESRC
SEU==> JFPWMS08
FMT ** ... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+.
0141.00 rs = JDBC_ExecQry(conn: 'Select ord_no, +
0142.00 from_item, +
0143.00 from_lic, +
0144.00 from_each, +
0145.00 from_exp_dt, +
0146.00 upld +
0147.00 from sonica.v_host_ship_info +
0148.00 order by ord_no, from_item, from_lic');
0149.00
0150.00 dow (jdbc_nextRow(rs));
0151.00 ord_no = jdbc_getCol(rs: 1);
0152.00 from_item = jdbc_getCol(rs: 2);
0153.00 from_lic = jdbc_getCol(rs: 3);
0154.00 from_each = jdbc_getCol(rs: 4);
0155.00 from_exp_dt = jdbc_getCol(rs: 5);
0156.00 upld = jdbc_getCol(rs: 6);
0157.00 eval licplt = from_lic;
0264.00 // Update Oracle on each Read
0265.00
0266.00 count = JDBC_ExecUpd(conn:'update sonica.v_host_ship_info +
0267.00 set upld = "Y" +
0268.00 where from_lic = "licplt"');
0269.00 enddo;
F3=Exit F5=Refresh F9=Retrieve F10=Cursor F11=Toggle F12=Cancel
F16=Repeat find F24=More keys
Focusing on the line
0268.00 where from_lic = "licplt"');
Double quotes are used around an identifier. The line, as written, means where the column named from_lic is equal to the value in a column named licplt. If you omit the quotes, where from_lic = licplt, it means the same thing; it assumes licplt is a column name. You typically only need double quotes when your name has embedded spaces, or other special characters.
If you want to compare to the text literal licplt, you need single quotes, where from_lic = 'licplt'. If you need the single quotes, because it's a literal value, you'll need to double them, because they're embedded in an RPG character literal:
0266.00 count = JDBC_ExecUpd(conn:'update sonica.v_host_ship_info +
0267.00 set upld = "Y" +
0268.00 where from_lic = ''licplt''');
btw, you might also consider adding OPTION(*SRCSTMT) to your H-spec. Your error message would point to line 26600 instead of the mysterious 3084. [Unless you have a compiler listing handy, or you're in the debugger and you've also used DBGVIEW with *LIST, *COPY, or *ALL.]