mirth connect use of executeUpdateAndGetGeneratedKeys with Oracle

I am using Mirth Connect 3.5.0.8232. I have created a persisted connection to an Oracle database and using it throughout my source and destination connectors. One of the methods Mirth provides for talking with the database is executeUpdateAndGetGeneratedKeys. It would be quite useful for insert statements that would return the primary keys for the inserted rows.

My question is - how do you specify WHICH columns to return? Running the provided function works, but returns ROWID in the CachedRowSet, which is not what I want.

As far as I understood, which columns to return depends on the type of the database, and every database behaves differently. I am interested in Oracle specifically.

Thank you.

Solution

The executeUpdateAndGetGeneratedKeys method uses the Statement.RETURN_GENERATED_KEYS flag to signal to the driver that auto-generated keys should be returned. However, from the Oracle docs:

If key columns are not explicitly indicated, then Oracle JDBC drivers cannot identify which columns need to be retrieved. When a column name or column index array is used, Oracle JDBC drivers can identify which columns contain auto-generated keys that you want to retrieve. However, when the Statement.RETURN_GENERATED_KEYS integer flag is used, Oracle JDBC drivers cannot identify these columns. When the integer flag is used to indicate that auto-generated keys are to be returned, the ROWID pseudo column is returned as key. The ROWID can be then fetched from the ResultSet object and can be used to retrieved other columns.

So instead, try using their suggestion of passing in a column name array to prepareStatement:

var dbConn;

try {
    dbConn = DatabaseConnectionFactory.createDatabaseConnection('oracle.jdbc.driver.OracleDriver','jdbc:oracle:thin:@localhost:1521:DBNAME','user','pass');

    // Create a Java String array directly
    var keyColumns = java.lang.reflect.Array.newInstance(java.lang.String, 1);
    keyColumns[0] = 'id';
    var ps = dbConn.getConnection().prepareStatement('INSERT INTO tablename (columnname) VALUES (?)', keyColumns);

    try {
        // Set variables here
        ps.setObject(1, 'test');
        ps.executeUpdate();

        var result = ps.getGeneratedKeys();
        result.next();
        var generatedKey = result.getObject(1);

        logger.info(generatedKey);
    } finally {
        ps.close();
    }
} finally {
    if (dbConn) {
        dbConn.close();
    }
}