I had to work with a remote Oracle database lately and the choice was made to use Scott's wrapper around the Java JDBC API.

The solution worked. Everything is fine. Is it? No it isn't.

I had to insert some thousand records into the Oracle database. What JDBCR4 supports is something like

1
2
3
4
5
6
statement = JDBC_PrepStmt(connection : 'INSERT INTO ...');

for i = 1 to numberRecords;
  JDBC_setString(statement : 1 : data.name);
  JDBC_ExecPrepUpd(statement);
endfor;

The data is processed record by record. That would be the same as using embedded SQL to fetch a set of data but fetching the data row by row instead of using a multi row fetch to get a whole block of records with one call.

In Java you can do the same by batching SQL statements. You need some more prototypes for that.

1
2
3
4
5
dcl-c JDBC_STATEMENT_SUCCESS_NO_INFO -2;

dcl-pr PreparedStatement_addBatch extproc(*java : 'java.sql.PreparedStatement' : 'addBatch') end-pr;
dcl-pr PreparedStatement_executeBatch int(10) dim(999)
           extproc(*java : 'java.sql.PreparedStatement' : 'executeBatch') end-pr;

Now the workflow looks like this:

1
2
3
4
5
6
7
8
statement = JDBC_PrepStmt(connection : 'INSERT INTO ...');

for i = 1 to numberRecords;
  JDBC_setString(statement : 1 : data.name);
  PreparedStatement_addBatch(statement);
endfor;

resultData = PreparedStatement_executeBatch(statement);

If this really speeds up execution depends on the JDBC driver (and probably the database).

Happy batching!

Mihael