Midrange News for the IBM i Community


Posted by: petemets
RPGLE , Java Oracle problem
has no ratings.
Published: 07 May 2012
Revised: 23 Jan 2013 - 1553 days ago
Last viewed on: 25 Apr 2017 (3481 views) 

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.

RPGLE , Java Oracle problem Published by: petemets on 07 May 2012 view comments(1)

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                                          

                                                                                

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: DaleB
Premium member *
Reading, PA
Comment on: RPGLE , Java Oracle problem
Posted: 4 years 11 months 19 days 5 hours 37 minutes ago

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.]