RE: Pass ResultSet / Array of Strings from Java to AS400StoredProcedure

Ok, that makes things clearer.

But the answer is still no, you can't pass a JDBC result set to a stored 
procedure.


Besides the temporary table and DTAQ idea, you could also I suppose pass a byte 
array to an RPG stored procedure.  But you'd have to make multiple calls due to 
the limits on the size of a parameter.

Note sure I'm recommend that method, but it is a solution.

When you compare DTAQ vs. temp file, the DTAQ would be faster, but the temp 
file would be more robust.

HTH,

Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: java400-l-bounces@xxxxxxxxxxxx
> [mailto:java400-l-bounces@xxxxxxxxxxxx]On Behalf Of Szabolcs Pall
> Sent: Tuesday, March 22, 2005 4:25 PM
> To: java400-l@xxxxxxxxxxxx
> Subject: Re: Pass ResultSet / Array of Strings from Java to
> AS400StoredProcedure
> 
> 
> 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
> -- 
> This is the Java Programming on and around the iSeries / 
> AS400 (JAVA400-L) mailing list
> To post a message email: JAVA400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.
> 
>