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: {} }
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With