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.

Comments:

Post a Comment:
Comments are closed for this entry.
About

sakthi

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today