How to get generated id using node-jdbc?

Question

I am using node-jdbc and the oracle driver "ojdbc6_g.jar" to connect to the Oracle database. I want to do some insertion of queries.

Problem:

How to get generated id when inserting row in oracle using node-jdbc?

I tried calling statement.getGeneratedKeys() , but it returns the following error in the 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 the ROWID of the new inserted row. The ROWID is the unique key that oracle uses for each row of the table and is different from the auto increament identifier. In fact, the oracle maps each row to a unique ROWID.

Having a ROWID, we can SELECT the inserted row and get all its columns.

select * from 'table_name', where rowid = 'received rowid'

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

Here is my modification of the jdbc node module to get the 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); } } }); } }); }; 

the result is an array of objects of the type:

 [ { ROWID: 'AAAVTcAAEAAAADzAAK' } ] 

We wish it to be useful.

Answer 2

Many people misunderstand and use PreparedStatement # executeUpdate (arg). The Java doc says that this method with an argument cannot be called on PreparedStatement or CallableStatement. This means that we should use executeUpdate () without arguments, although the executeUpdate (arg) method can be inherited in the PreparedStatement class, but we do not need to use it, otherwise we will get SQLException.