JDBC for AS/400

[Shanmugam Perumal]

How to use AS/400 as back end for my Java application. How to set System DSN.......
Thanks,
SHAN.

[Ray Marsh]

Shan,
I am familiar with the AS/400, however, I don't have an answer to your question.
If you could provide some more specifics, I'll see what resources I can find.
Ray

[Todd Bush]

Can't one just odbc to it?

[Ray Marsh]

You CAN connect via ODBC that's why I asked for more info.
I'm not clear on how Shan is trying to connect.

[Todd Bush]

Ok Ray, just simmer down.
Bring it down a notch.

[Ray Marsh]

Ok, I'll try too avoid the use of too many capital letters, don't wanna scare the young-uns.
Does anyone know if you need the JDBC if you are running a Java App on an AS/400 using db2?
This may sound odd too many of you, but I've never had to connect to a db2 database EVER! Sorry, forgot about the caps .
Connecting to a database is a foreign concept to me.
On the AS/400 you merely declare the file in a program read or chain to your hearts content. In RPG you don't even need to open and close the file, the OS handles that for you.
So, my question is: do you even need to connect to a db2 file (or table for you Oracle people) in OS/400?
I'm trying to find someone who has done some Java on the AS/400.
If anyone knows such a person point them here, please.
Thanks.

[Ajay Kumar]

Hi,
I have tinkered with Java and AS/400 as the backend. Here is an example of using the JDBC for connecting to AS/400.
</pre>
import java.sql.*;
import java.math.*;
public class JDBCExample01 {
public static void main (String[] args) {
Connection con = null;
try {
// Load the JDBC driver
DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
// Connect to the database
con = DriverManager.getConnection (
"jdbc:as400://sysname","username","password"); // substitue UR values here
// Run an SQL SELECT statement
Statement stmt = con.createStatement ();
ResultSet rs = stmt.executeQuery (
"SELECT * " +
" FROM QIWS.QCUSTCDT ORDER BY LSTNAM");
// Display each row (record) retrieved by the SQL statement
while (rs.next ()) {
String cusnum = rs.getString(1);
String cusnam = rs.getString("LSTNAM");
BigDecimal baldue = rs.getBigDecimal(10, 2);
System.out.println (cusnum + " " + cusnam + " " + baldue);
}
rs.close ();
stmt.close ();
}
catch (Exception e) {
System.out.println ("\nERROR: " + e.getMessage());
}
finally {
try {
con.close ();
}
catch (SQLException e) {
}
}
System.exit (0);
}
}

Substitute UR system name, the USERID and the password in the space indicated. I am using the QCUSTCDT file in the QIWS library which is shipped with every AS/400 .
I would suggest U can go the www.zappie.net for a comprehensive explanation about how to use java and AS/400. Pls feel free to contact me via email.
Thnx
Ajay Kumar

[This message has been edited by Ajay Kumar (edited April 28, 2000).]
[This message has been edited by Ajay Kumar (edited April 28, 2000).]
[This message has been edited by Ajay Kumar (edited April 28, 2000).]

[Ray Marsh]

I'm getting the following errors attempting to compile Ajay's source.
If anyone has suggestions on any or all of them, they would be much appreciated.
C:\Java\Java Source>javac -deprecation JDBCExample01.java
JDBCExample01.java:9: Class com.ibm.as400.access.AS400JDBCDriver not found.
DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
^
JDBCExample01.java:22: Note: The method java.math.BigDecimal getBigDecimal(int,
int) in interface java.sql.ResultSet has been deprecated.
BigDecimal baldue = rs.getBigDecimal(10, 2);
^
Note: JDBCExample01.java uses or overrides a deprecated API. Please consult the
documentation for a better alternative.
1 error, 1 warning
C:\Java\Java Source>

[Ajay Kumar]

Hi Ray,
One of the first things I did before experimenting was to follow the instructions from the Website www.zappie.net for installing the toolbox for AS/400. I took some of the example programs from the website and tested it before I started experimenting on my own. I did not have any problems since then. Maybe U could try it.
I am presently working on a project on JBA and I do not get much time tinker around with java.
The exact link for the AS/400 page on the above mentioned website is http://www.zappie.net/Java/Javatorium/installi.htm
Hope this helps.
Thanks
Ajay

[Mike Broad]

I have done quite a bit of work with JDBC connections to an AS/400 back end. In regards to the driver error you must look at two things. IBM provides 2 drivers for DB2 on the AS/400. The one that you are trying to use above com.ibm.as400.access.AS400JDBCDriver is the toolbox driver and requires installation of the toolkit on the AS/400.
There is a second driver which is the native AS/400 driver is the following: com.ibm.db2.jdbc.app.DB2Driver
This should be included on the system if you are at V4R4 (and possibly earlier releases, I don't know).
Note: the native driver can not be used if you are not running the code on the 400 itself.
Once you have one of these two drives you can perform JDBC functions very easily on the AS/400.
-Mike

[Ray Marsh]

Thanks Mike.
Your response, however, elicits a question. If the program must be on the 400, how would one compile and run an Java applications on some other platform and access DB2/400 files?

[Mike Broad]

You can access the 400 from other platforms using the toolbox driver:
com.ibm.as400.access.AS400JDBCDriver
You can only use the native driver for code that is running on the 400:
com.ibm.db2.jdbc.app.DB2Driver
What I have done in the past is create a helper class in my applications that stores the driver name string as a member. For code on other platforms(i.e. windows etc...) I set this member to the toolbox driver. For code running on the 400 I set this string value to that of the native driver (which I believe is the best option for code on the 400 itself, or so IBM says).
Hope this helps,
Mike

[Ray Marsh]

Originally posted by Mike:
You can access the 400 from other platforms using the toolbox driver:
com.ibm.as400.access.AS400JDBCDriver
You can only use the native driver for code that is running on the 400:
com.ibm.db2.jdbc.app.DB2Driver
What I have done in the past is create a helper class in my applications that stores the driver name string as a member. For code on other platforms(i.e. windows etc...) I set this member to the toolbox driver. For code running on the 400 I set this string value to that of the native driver (which I believe is the best option for code on the 400 itself, or so IBM says).
Hope this helps,
Mike

Yes it does help. Is the reason you use the native driver name on the 400 code, for efficiancy? It appears your approach limits portability.
I'm not challenging your approach. I'm just trying to understand the methodology and the rational behind it.
Thanks,
Ray

[Sam Malhotra]

Has any one tried calling RPG Stored Procedures from Java? I am having trouble sending resultset back to java from a SQLRPGLE program.