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

[Loyd Goodbar <loyd@xxxxxxxxxxxxxx>]

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/