RE: Pass ResultSet / Array of Strings from Java toAS400StoredProcedure

Those 10,000 updates have to be done somewhere! Since you would have to scan
through your Oracle result set anyway to either write to a temporary table
or send to a data queue, why not try executing your PreparedStatement in
that loop. I would take a look at executing a batch of SQL statements using
the addBatch and executeBatch methods.

Check out http://javaalmanac.com/egs/java.sql/BatchUpdate.html for an
example. That shows how to "accumulate" your updates and then send them to
the database in one shot.

Hope this helps!
Richard Casey


-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx
[mailto:java400-l-bounces@xxxxxxxxxxxx]On Behalf Of Wilt, Charles
Sent: Tuesday, March 22, 2005 5:07 PM
To: Java Programming on and around the iSeries / AS400
Subject: RE: Pass ResultSet / Array of Strings from Java
toAS400StoredProcedure


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