<snip>
If you have access to any of the Oracle clients then you most likely
have
the JDBC drivers already.
If not, You can download them from
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html
You place the JDBC driver in your classpath on the iSeries, You can
create
a java pgm to retreive data from the Oracle
tables, you can either call the java pgm from a rpg pgm or insert the
data
into a db2 table.
Below is a simple java pgm that retrieves data from a oracle database
and
inserts the data into a db2 table.
</snip>
I didn't include the Java program source here.
My only recommendation beyond the obvious of converting the program to a
class and passing parameters rather than any form of input operations
would be to write the data access in the Oracle Stored Procedure
Language.
Since you are accessing an Oracle database, they should be someone on
the Oracle side who writes their stored procedures.
Do all the data manipulation there and only return a simple result set
to the java class. All the complexity is hidden on their end.
You might do the same thing on the AS/400 side. Write a stored procedure
in SPL (Stored Procedure Language) and just pass the fields as
parameters.
Do the inserts and whatever in the stored Procedure. Again reducing the
complexity of the code.
Virtually all your code would to convert this statement to a load stored
procedure parameters.
// prepare parameters for insert statement to insert row into DB2
database table
DB2insert.setBigDecimal(1,Oraclerset.getBigDecimal(1));
DB2insert.setBigDecimal(2,Oraclerset.getBigDecimal(2));
DB2insert.setBigDecimal(3,Oraclerset.getBigDecimal(3));
DB2insert.setBigDecimal(4,Oraclerset.getBigDecimal(4));
DB2insert.setBigDecimal(5,Oraclerset.getBigDecimal(5));
DB2insert.setBigDecimal(6,Oraclerset.getBigDecimal(6));
DB2insert.setBigDecimal(7,Oraclerset.getBigDecimal(7));
DB2insert.setBigDecimal(8,Oraclerset.getBigDecimal(8));
DB2insert.setBigDecimal(9,Oraclerset.getBigDecimal(9));
DB2insert.setBigDecimal(10,Oraclerset.getBigDecimal(10));
DB2insert.setBigDecimal(11,Oraclerset.getBigDecimal(11));
DB2insert.setBigDecimal(12,Oraclerset.getBigDecimal(12));
DB2insert.setString(13,Oraclerset.getString(13));
DB2insert.setDate(14,Oraclerset.getDate(14));
DB2insert.setBigDecimal(15,Oraclerset.getBigDecimal(15));
DB2insert.setBigDecimal(16,Oraclerset.getBigDecimal(16));
DB2insert.setBigDecimal(17,Oraclerset.getBigDecimal(17));
DB2insert.setBigDecimal(18,Oraclerset.getBigDecimal(18));
DB2insert.setBigDecimal(19,Oraclerset.getBigDecimal(19));
DB2insert.setBigDecimal(20,Oraclerset.getBigDecimal(20));
DB2insert.setString(21,Oraclerset.getString(21));
DB2insert.setString(22,Oraclerset.getString(22));
The input to getBigDecimal is a string. You might not even convert to a
Decimal. You might just pass the string directly to the stored procedure
and do the conversion there. Depends on what the data looks like coming
in from Oracle.
Again, I don't know if anybody is noticing, but this could be written
generically.
Pass a stored procedure string or a select statement, execute the string
and return number of results sets to caller.
Call a second method once for each result passing the number of columns.
Loop for the number of columns and build a string for return:
ReturnString = ReturnString + '~' + FieldAsString(ColumnNumber)
The caller takes care of the parsing.
You now have a generic class that be used to return any result set. The
caller is the only one who knows what the data looks like.
Now the interesting thing here is that you could write the code in
RPG/ILE (ODBC is ODBC) and have one method that receives the string, the
number of columns and a procedure address for a callback routine.
Run the string, loop through the result set and for each column call the
callback routine passing the string, the result number and the column
number.
The callback is responsible for formatting the return, loading a data
structure, writing the record when the result number changes.
I think I will try this. Might be interesting.
midrange.com
