How to use Oracle Sequence for auto increment column
While reading through this thread http://forum.sun.com/jive/thread.jspa?threadID=99306
I had to check out how to invoke sequence.nextval for new row being appended.
Here's how i did achieve this:
1. Created a Sequence and a Table:
CREATE SEQUENCE userid_seq MINVALUE 1 START WITH 1 INCREMENT BY 1 NOCACHE;
create table user_seq_test (
seq_col int not null primary key,
char_col char(10) );
2. Creating a Page to append rows into this table:
Add a datadource (say ora9i) to access this database table in Servers Window.
Create a New Project, drag and drop user_seq_test table onto page. Dropa text field and bind it to user_seq_test.char_col. Add a button for "Append Row". Add a message group to be able to see the error messages on the page, when it happens.
In the prerender, append a row to the dataprovider and get the sequence value for the new row and set it:
try {
RowKey rk = user_seq_testDataProvider.appendRow();
user_seq_testDataProvider.setCursorRow(rk);
Connection conn = null ;
Statement sqlStatement = null ;
ResultSet rs = null ;
javax.naming.Context ctx = new javax.naming.InitialContext() ;
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/ora9i") ;
conn = ds.getConnection() ;
// setup the connection
conn.setAutoCommit(true) ;
// execute the query
sqlStatement = conn.createStatement() ;
rs = sqlStatement.executeQuery("select userid_seq.NEXTVAL from dual" ) ;
rs.next() ;
int nextvalue = rs.getInt(1) ;
user_seq_testDataProvider.setValue("seq_col", new Integer(nextvalue));
rs.close();
sqlStatement.close();
conn.close();
} catch (Exception ex) {
error(ex.getMessage());
log("Error Description", ex);
}
In the append Row Button action:
try {
user_seq_testDataProvider.commitChanges();
user_seq_testDataProvider.refresh();
} catch (Exception ex) {
log("Error Description", ex);
error(ex.getMessage());
}
So, when we want to use Sequence.NEXTVAL, you need to fetch the value and set this value on the auto-increment column in the appended row.
We could use similar technique for calling functions and stored procedures.
Posted at 04:01PM Jun 23, 2006
by sakthi in Sun |
