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
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.
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:
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).