Thanks, David.
I found a way around the problem (without needing another connection) by locally caching and closing result sets thus no result set is ever pending when another takes place. It intelligently caches the data in-memory or in a temporary file. The code is at
if anyone is interested. The actual caching occurs in Cursor.cacheAllRecords() and Cursor.nextCachedRecord().
On Fri, Mar 11, 2022 at 11:16 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
>
> On Fri, Mar 11, 2022 at 8:47 PM Blake McBride <
blake1024@gmail.com> wrote:
>>
>>
>> conn.setAutoCommit(false)
>> PreparedStatement pstat = conn.prepareStatement(sql)
>> pstat.setFetchSize(50)
>> select all records in a large file
>> for each record:
>> read one field from the record
>> through a second statement, add a record to a different table - XXX
>> execute the second statement - YYY
>> commit the change - ZZZ
>> loop
>>
>> It seems I can do one or the other but not both - or can I?
>>
>
> Correct. What you are trying to do requires that you open two sessions to the database (conn_read, conn_write) and execute the INSERT command on the conn_write connection while looping through data on the conn_read one.
>
> David J.
>