Re: Pass ResultSet / Array of Strings from Java to AS400 StoredProcedure

Hi,

sorry for the confusion...

I've written java code that will run on the iSeries v5r2 (but I'm 
currently testing on my PC). I want to update 'mylibr/itmstr' on the AS400 
with some data from oracle that I've fetched into my java ResultSet. At 
this point, Oracle is out of the picture...

I now want to use this data that I got from Oracle, to update our item 
master, but don't know how to go about doing it. I find that executing 
PreparedStatements against the AS400 (using this driver: 
com.ibm.as400.access.AS400JDBCDriver) is too slow (I have about 10,000 
records I want to update).

// -- This is how I do my update, so if OraRS has 10,000 records, I could 
be executing the PreparedStatement 10K times... doesn't seem practical.
PreparedStatement ps = conn.prepareStatement("UPDATE mylibr.itmstr SET 
IMLDTM = ? WHERE IMPN = ?");

while(OraRS.next()) {
ps.setString (1, OraRS.getString(1));
ps.setString (2, OraRS.getString(2));

ps.executeUpdate();
}

Ideally, I would like to create an SQL stored procedure on the iSeries and 
call it once using the CallableStatement (this is the way to call stored 
procedures from Java, isn't it?), passing my OraRS Resultset as the input 
parameter. Or if not a ResultSet, then some sort of other container that 
has my data from Oracle.

All my connections have been tested, and what's got me searching for a 
better solution is that the performance (executing potentially 10,000 
PreparedStatements) is too slow. This is why I thought of passing my 
ResultSet to an as400 stored procedure once, and have the SP do the 
updates. I have not yet taken the code to the AS400 and run it through 
it's optimizer.

Joe Sam: I will try your suggestion, thanks.

Hope this is clearer, I apologize if it's not. And sorry if I didn't 
follow proper posting ettiquete.

Thanks again,

Szabolcs