How to get generated Id using node-jdbc?

Question

I'm using node-jdbc and oracle driver "ojdbc6_g.jar" to connect to an Oracle database. I want to execute some insert queries.

The problem:

How to get generated Id when inserting a row into oracle using node-jdbc?

I tried to call statement.getGeneratedKeys() but it returns the following error in callback:

> { [Error: Error running instance method java.sql.SQLException:
> operation not allowed
>         at oracle.jdbc.driver.OracleStatement.getGeneratedKeys(OracleStatement.java:8425)
>         at oracle.jdbc.driver.OracleStatementWrapper.getGeneratedKeys(OracleStatementWrapper.java:1106)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>         at java.lang.reflect.Method.invoke(Method.java:483) ] cause: {} }

Answer 1

I tested some solutions and finally I managed to get ROWID of the new inserted row. ROWID is a unique key which oracle uses for each row of a table and is different from auto increament ID. In fact, oracle maps each row to a unique ROWID.

Having ROWID we can SELECT the inserted row and get every columns of it.

select * from 'table_name' where rowid = 'obtained rowid'

To get generated keys we should call statement.executeUpdate() with parameter "1", so we can use statement.getGeneratedKeys().

Here is my modification to jdbc node module to get ROWID:

JDBCConn.prototype.executeInsert = function (sql, callback, getGeneratedKeys) {
    var self = this;

    self._conn.createStatement(function (err, statement) {
        if (err) {
            return callback(err);
        }
        else {
            // calling `statement.executeUpdate()` with parameter 1
            statement.executeUpdate(sql, 1, function (err, rowcount) {
                if (err) {
                    return callback(err);
                }
                else {
                    if (getGeneratedKeys) {
                        statement.getGeneratedKeys(function (err, resultset) {
                            resultset.getMetaData(function (err, rsmd) {
                                if (err) {
                                    return callback(err);
                                } else {
                                    var results = [];
                                    var cc = rsmd.getColumnCountSync();
                                    var columns = [''];
                                    for (var i = 1; i <= cc; i++) {
                                        var colname = rsmd.getColumnNameSync(i);
                                        columns.push(colname);
                                    }
                                    var next = resultset.nextSync();
                                    var processRow = function (next) {
                                        if (next) {
                                            setImmediate(function () {
                                                var row = {};
                                                for (var a = 1; a <= cc; a++) {
                                                    row[columns[a]] = trim1(resultset.getStringSync(a));
                                                }
                                                results.push(row);
                                                next = resultset.nextSync();
                                                processRow(next);
                                            });
                                        } else {
                                            callback(null, rowcount, results);
                                        }
                                    };
                                    processRow(next);
                                }
                            });
                        });
                    }
                    else {
                        callback(null, rowcount);
                    }
                }
            });
        }
    });
};

results is an array of objects like:

[ { ROWID: 'AAAVTcAAEAAAADzAAK' } ]

Wish it is helpful.

Answer 2

Many people misunderstand and use PreparedStatement#executeUpdate(arg) . Java doc says This method with argument cannot be called on a PreparedStatement or CallableStatement. It means we have to use executeUpdate() without argument even though executeUpdate(arg) method can be inherited in PreparedStatement class but we don't have to use it otherwise we will get SQLException.