Re: (beginner) Java/JDBC program to pull SQL data?

I can give this one a try.

>> 1. What do I need, LPP-wise, to do this from the AS/400?
You need licensed program 5769JV1.  Various options of the LP load
different levels of the JVM on your iSeries.  For example, option 4 is
1.1.8, option 5 is 1.3.  If a JVM level is made available after the release
GAed then you load the option by applying PTFs.  For example, JVM level 1.3
was probably released after v4r5 GAed, so you can't load 1.3 using your
v4r5 install tapes.  You get the option by installing PTFs.

>> 2. Does the LPP come with the requisite driver to talk to MSSQL server
(as a client)?
No, you need to get the MSSQL JDBC driver from the MSSQL package and copy
it to the iSeries.  The iSeries only comes with JDBC drivers that access
the AS/400 database.

>> 3. Setup of said driver for SQL server connection?
You do whatever the MSSQL doc says to do.

>> 4. Do I need to do anything special on the MSSQL server side, or can I
just use the TCP/IP
>>  address and port 1433?
My guess is you would access it via IP.  The advantage of Java is it runs
the same everywhere so if you have it working from a PC client it will work
the same when the iSeries is the client.

>> 5. How would a Java program communicate with CL/RPG?
Lots of options, some of them discussed last week on this forum.  (1) You
can create a Java CL command to start your Java program.  Data you need to
pass to Java is passed on as a command parameter.  (2) RPG has a way via
JNI to directly call Java classes.  I don't have any experience with this
but appends to the forum say the RPG book has some examples.  (3) Have the
Java program run as a deamon process.  Your RPG program tells it to do
something via some sort of inter-process communication.  Data queues works
well.  Your RPG program puts an entry on the Q.  The Java program is
waiting for work by listening to the Q, reads the entry, does the work,
then posts results back to the queue.

>> 6. Locking/access issues if I allocate an object in CL and Java tries to
write to it?
Depends on how you do 5.  The java program can easily be another process so
you would have locking considerations.

>> 7. Robustness/error checking capabilities in Java?
As good as other languages.  This is probably a function of the programmer
as much as the language.  If the programmer wants to do error checking,
Java provides the facilities to do so.

>> 8. If I wanted to make a parameters file (holding server addresses or
SQL
>> statements to run), where is the best place to put it to access from
Java?
>> AS/400 db vs IFS file? (assuming little editing or modification)
The question may be more what you are comfortable with.  Many iSeries folks
like Data Queues (if the data is small) or DQ + User Space if the data is
larger.  The DQ holds the offset and length of the data in the user space.
If the programmer is more familiar with DB programming then you can pass
data by sticking it in the database.  Java has built-in classes to
read/write stream (IFS) files in the java.io package.  That makes storing
config data in files easy to do.

>> 9. Is this a relatively simple program? Examples of writing to AS/400
>> databases, getting/sending messages (?) data queues (?) in Java?
The downloads link on the Toolbox home page
(http://www.ibm.com/eserver/iseries/toolbox/) has labs from the Spring 2002
COMMON conference.  Two labs of interest to you may be the Intro to Java
lab (basic Java information) and the Toolbox lab (JDBC, ProgramCall, etc.).
The KeyedDataQueue page off the Toolbox programmer's guide has DQ examples.


Hope some of this helps,

David Wall
Toolbox for Java
iSeries ODBC Driver for Linux




                      Loyd Goodbar
                      <loyd@blackrobes.        To:       
midrange-l@midrange.com, java400-l@midrange.com
                      net>                     cc:
                      Sent by:                 Subject:  (beginner) Java/JDBC 
program to pull SQL data?
                      java400-l-admin@m
                      idrange.com


                      05/31/2002 08:26
                      PM
                      Please respond to
                      java400-l





Before I start, let me say I only know how to spell Java. I haven't used it
at
all, but read some articles. I am hoping this is a fairly simple project to
"jump into" Java. I am proficient with RPG/IV and basic ILE concepts.

Data is stored in a Microsoft SQL server that I would access from the
AS/400..
I wish to run a SQL statement (prepared at run-time based on user input)
against the SQL server, retrieve the data and populate an AS/400 database
(single table only).

The assumptions I make: we are pulling some personnel data (name, clock
number, shift, rate, job code, etc) from our HR system running on SQL
server.
The data will go into our shop floor module on AS/400-based MRP package.
Only
green-screen access is required. (The MRP package is green screen; this
would
be integrated into the menu structure.)

Program structure: 1) AS/400 command accepts SQL user name, SQL password,
and
other parameters. 2) Call a CL or RPG program to communicate (?) with the
Java
program that does the work. The CL would do such things as submit itself to
batch, allocate a work file and clear it. 3) The Java program would accept
the
user, password, and other parms and build a dynamic SQL statement to be
executed. Execute the statement, and write the data to the work file. If
any
errors occur (user/password invalid, could not connect to database, no
records
found, SQL error) it would communicate those back to the green screen.
Successful status would also go back to the green screen. 4) The CL/RPG
program accepts status messages and relays them to the user (if only as a
error report spool file).

My questions:
1. What do I need, LPP-wise, to do this from the AS/400?
2. Does the LPP come with the requisite driver to talk to MSSQL server (as
a
client)?
3. Setup of said driver for SQL server connection?
4. Do I need to do anything special on the MSSQL server side, or can I just
use the TCP/IP address and port 1433?
5. How would a Java program communicate with CL/RPG?
6. Locking/access issues if I allocate an object in CL and Java tries to
write
to it?
7. Robustness/error checking capabilities in Java?
8. If I wanted to make a parameters file (holding server addresses or SQL
statements to run), where is the best place to put it to access from Java?
AS/400 db vs IFS file? (assuming little editing or modification)
9. Is this a relatively simple program? Examples of writing to AS/400
databases, getting/sending messages (?) data queues (?) in Java?

We're on V4R5.

Thanks,
Loyd

--
"Why, you can even hear yourself think." --Hobbes
"This is making me nervous. Let's go in." --Calvin
loyd@blackrobes.net  ICQ#504581  http://www.blackrobes.net/
_______________________________________________
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l
or email: JAVA400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.