× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



<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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.