Guys checkout this simple code
RowSetFactory myRowSetFactory = null;
JdbcRowSet jdbcRs = null;
myRowSetFactory = RowSetProvider.newFactory();
jdbcRs = myRowSetFactory.createJdbcRowSet();
jdbcRs.setUrl(connString);
jdbcRs.setUsername(user);
jdbcRs.setPassword(pass);
//Note: if "table1.*" is not used, the jdbc driver reports "ResultSet is not updatable" error for some bizzaire reasons...
jdbcRs.setCommand("SELECT table1.* from table1 where name1='john'");
jdbcRs.execute();
String refobj = "namae";
while(jdbcRs.next()) {
for(int i=1;i<=2;i++) {//just consider two columns
jdbcRs.updateObject(i, refobj);
//jdbcRs.updateObject(i, refobj, JDBCType.valueOf(coltype2)); //"method not supported/implemented" exception from ojdbc driver implementation
//jdbcRs.updateNString(i, refobj);//didn't work... same error
}
jdbcRs.updateRow();//edited
}
jdbcRs.updateRow();
jdbcRs.close();
Consider a simple table in Oracle(>= 11g release 2)
Tablename:table1 Column1: ColumnName:name1 Type:NVARCHAR2 length 100
Column2: ColumnName:name2 Type:NVARCHAR2 length 100
with sample entries/rows
name1 | name2
john | doe
jane | doe
This results in invalid column index error while executing jdbcRs.updateRow();
Unexpected exception
java.lang.RuntimeException: Invalid column index
at oracle.jdbc.driver.OraclePreparedStatement.setFormOfUse(OraclePreparedStatement.java:10435)
at oracle.jdbc.driver.UpdatableResultSet.prepareUpdateRowBinds(UpdatableResultSet.java:1040)
at oracle.jdbc.driver.UpdatableResultSet.updateRow(UpdatableResultSet.java:570)
at com.sun.rowset.JdbcRowSetImpl.updateRow(JdbcRowSetImpl.java:3017)
So I've included System.setProperty("oracle.jdbc.defaultNChar", "true"); at the top and linked the "orai18n.jar" file hoping that it'll handle the NLS support and take care of NVARCHAR2 but it was all in vain...
Extra info: When I was using OracleWebRowSet and acceptChanges(), it(ojdbc) reported a "invalid column type" error...
Inrealtime, I've been using OracleWebRowset and it stores the resultset in an xml and while reading XML, it automatically provides me corresponding objects related to the column types... I must use updateObject coz, the columns could be any type and I could mess up the code if i use column specific updateNString,UpdateInt, ... and so on... but even if I use updateNString(), it throws the same error
I'm in a jam... something so simple such as this should work just fine and oracle is reputed product but why... I've tried all ojdbc drivers from 11.2.x.x to 12.2.x.x but to no avail...
I couldn't find any Source package for OJDBC and debugging the ojdbc is not an option...
Any Oracle Gurus care to direct me in THE right path?
Message was edited by: 851890