Query Remote Database Tables from the iSeries Using SQL and Java

[MCWebsite.Staff]

** This thread discusses the article: Query Remote Database Tables from the iSeries Using SQL and Java **
** This thread discusses the Content article: Query Remote Database Tables from the iSeries Using SQL and Java0

[vengoal@ddsc.com.tw]

Query Remote Database Tables from the iSeries Using SQL and Java

** This thread discusses the article: Query Remote Database Tables from the iSeries Using SQL and Java **
When I download the ExtDB.java program, I got following message: The page you are looking for has been moved. Please visit our home page to find it. For product support, visit mcpressonline.com/updates.

Where can I download the code ? Best regards, Vengoal

[gcostagliola@tin.it]

Query Remote Database Tables from the iSeries Using SQL and Java

** This thread discusses the article: Query Remote Database Tables from the iSeries Using SQL and Java **
Strange things happen with the JVM. After you ran the sql statement, the jvm remains on. Then if you try to run some rpg+java with different classpath it doesn't work and it seems that you have to destroy the current jvm and restart another one. But if you run any rpg+sql and leave the jvm on, you can't run any sql against the java udtf. You get the error JVAB55C with reason code 15 (that is not on the 2nd level message). Apart from this, everything runs fine.

[gcostagliola@tin.it]

Query Remote Database Tables from the iSeries Using SQL and Java

** This thread discusses the article: Query Remote Database Tables from the iSeries Using SQL and Java **
Btw, if we register all the jar's used by rpg+java with the install_jar, both sql and rpg seem to work well together. Giuseppe.

[Guest.Visitor]

Query Remote Database Tables from the iSeries Using SQL and Java

** This thread discusses the article: Query Remote Database Tables from the iSeries Using SQL and Java **
When I execute the SQL to return orders data I receive the following message below. (The message text is somewhat jumbled but that's the way it was received.) We're new to Java so any help you can provide is appreciated. Message ID . . . . . . : CPF503E Severity . . . . . . : 30 Message type . . . . . : Diagnostic Date sent . . . . . . : 05/04/04 Time sent. . . . . . : 09:26:25 Message . . : User-defined function error on member QSQPTABL. Cause . . . : An error occurred while invoking user-defined function EXT_ORDERS in library QGPL. The error occurred while invoking the associated external program or service program rs in library ExtDB.orde, program entry point or external name . The error occurred on member QSQPTABL file QSQPTABL in library QSYS2. The error code is 1. The error codes and their meanings follow: 1 -- The external program or service program returned SQLSTATE 38501. The text message returned from the program is: QGPL.EXT_ORDERSEXT_ORDERSj.

[M.Sansoterra]

Query Remote Database Tables from the iSeries Using SQL and Java

** This thread discusses the article: Query Remote Database Tables from the iSeries Using SQL and Java **
Hi Phil Unfortunately, this SQL state is vague (as you know.) My experience with a 38501 has been that there is something internally wrong with the Java code (i.e. the Java code throws an exception.) The first thing I would verify is that you're actually connecting to SQL Server (you should be able to do this by looking in Enterprise Manager.) If you find a process id from your AS/400 then double click to view the last SQL statement submitted. At least this would let you know you're connecting. If you don't connect, review the installation and registration (SQLJ.INSTALL_JAR) of the SQL Server .JARs, etc. as outlined in the article. Next, verify that each portion of the Java code is "bullet proofed" by making sure everything is in a try/catch block. If an exception is caught, make the table function gracefully end by returning an SQL State of the form 38Ixx. Each section of code should pass back a unique SQL state so that you can tell which portion of code is having the problem. This way if a problem occurs, you'll get your own SQL state returned rathn than the generic 38501. In the article sample code (ExtDB.java), the "case SQLUDF_TF_FETCH:" is not bullet proofed and would be my first area of concern for possible problems. Hope that helps

[mruble@wisesnacks.com]

Query Remote Database Tables from the iSeries Using SQL and Java

** This thread discusses the article: Query Remote Database Tables from the iSeries Using SQL and Java **
I had the same problem running a SQL select from an interactive job. But, when I ran the same SQL select from the Run SQL Scripts of iSeries naviagtor it worked! Go figure...

[M.Sansoterra]

Query Remote Database Tables from the iSeries Using SQL and Java

** This thread discusses the article: Query Remote Database Tables from the iSeries Using SQL and Java **
If your UDTF will run in iSeries Navigator but not in an interactive STRSQL or QMQRY, check to see if your job's CCSID is 65535. If it is 65535, try changing the job's CCSID to your local CCSID (for example, CCSID 37 is US EBCDIC): CHGJOB CCSID(37)

[Guest.Visitor]

Query Remote Database Tables from the iSeries Using SQL and Java

** This thread discusses the article: Query Remote Database Tables from the iSeries Using SQL and Java **
I put try catches around all the code, but it never hits any or returns any of my error msgs. I keep getting the following error when I run the Select for EXT_ORDERS: [SQL4302] Java stored procedure or user-defined function QGPL.EXT_ORDERS, specific name EXT_ORDERS aborted with an exception "java.lang.NullPointerException". Its says to turn on SQJAVA component trace, where do I do that?

[M.Sansoterra]

Query Remote Database Tables from the iSeries Using SQL and Java

** This thread discusses the article: Query Remote Database Tables from the iSeries Using SQL and Java **
Sorry it took me so long to see this one (I prefer e-mail! -- I didn't notice that little orange "new message" indicator until today!) To run the trace: Start an interactive session 1 ADDENVVAR ENVVAR(QIBM_COMPONENT_TRACE_LEVEL) VALUE('SQJAVA,VERBOSE') 2 Run your SQL statement in STRSQL then get back to the command line after it fails 3 DMPUSRTRC (will create a trace file: qtemp/qap0zdmp) 4 CPYF QTEMP/QAP0ZDMP *PRINT (to create a spool file) Good luck reading this thing. Let me know if you need help.

[orozco_josel]

Query Remote Database Tables from the iSeries Using SQL and Java

** This thread discusses the article: Query Remote Database Tables from the iSeries Using SQL and Java **
Hello Phil I know this is an old article, but I'm just getting started on accessing SQL Server data from the ISeries. I got this same error message when I tried the program in this article. I was wondering if you fixed the problem. If so, could you share the solution. You can email me at jorozco@cameroncad.org. Thanks in advance. Joe Orozco

[Guest.Visitor]

Query Remote Database Tables from the iSeries Using SQL and Java

** This thread discusses the article: Query Remote Database Tables from the iSeries Using SQL and Java **
Does any one have the ExtDB.java program they can send me. It is not available on mcpressonline site.


Query Remote Database Tables from the iSeries Using SQL and Java
Apr 05 2004 05:32:00
When I download the ExtDB.java program, I got following message: The page you are looking for has been moved. Please visit our home page to find it. For product support, visit mcpressonline.com/updates.

[MatthewNeill]

Ditto on the availability of the ExtDB.Java program.

** This thread discusses the article: Query Remote Database Tables from the iSeries Using SQL and Java **
The link to the downloadable ExtDB.Java program still appears broken.

Has anyone been able to get it downloaded? If so can you send me a copy to MNeill@republicServices.com.


Regards...

[V.Mack]

Downloadable Code

** This thread discusses the article: Query Remote Database Tables from the iSeries Using SQL and Java **
Please accept our apology for the difficulties with the downloadable code. It's attached here for your convenience.

Victoria Mack
Executive Editor
MC Press Online

[MatthewNeill]

Thanks!

** This thread discusses the article: Query Remote Database Tables from the iSeries Using SQL and Java **
Thanks Victoria!