Neil的备忘录

just do it
posts - 66, comments - 8, trackbacks - 0, articles - 0

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.


Author:  Sakthi

Link  :  http://blogs.sun.com/sakthi/entry/a_scenario_with_oracle_sequence


只有注册用户登录后才能发表评论。


网站导航: