how to get the next sequence number ..

[Akshatha Nayak]

hi guys ,
i have a problem getting the sequence number. i have a table named customer .. where i have want to auto generate the customer id .. so i have used sequence on customer id .. now if i want to insert a new customer from my java program ..first i shud get the sequence number .. how do i get it .. i tried using
int cust_id = statement.executeUpdate("select cust_sequence from dual",RETURN_GENERATED_KEYS );
looks like it isnt working ... although when i print the current value from sql*plus it is incrementing the sequence number ..

OR simply this code is not working .whatz wrong with this .. can anybody pls point out

import java.sql.*;
import java.sql.Statement.*;

public class ConnectionJdbc {

public static Connection con = null;


public Connection connect()throws SQLException{
String dbURI = "jdbc racle:thin:@host:1521:mydb";
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());

con = DriverManager.getConnection(dbURI,"SCOTT","TIGER");
return con;
}


public static void main(String [] args)throws Exception{
int cust_id = 0;
String title = "ms";
String firstname ="Akshatha";
String lastname = "Nayak";

Statement stmt = null;
ResultSet rs = null;
ConnectionJdbc conjdbc = new ConnectionJdbc();
try {
Connection con = conjdbc.connect();

stmt = con.createStatement();
cust_id = stmt.executeUpdate("SELECT CUST_SEQUENCE.NEXTVAL FROM DUAL");
String SQLCommand = "INSERT INTO CUSTOMER (cust_id,title,firstname,lastname) VALUES"+ "("+ cust_id + ",'"+ title +"',"+
"'"+ firstname + "'," + "'" + lastname + "')" ;
rs = stmt.executeUpdate(SQLCommand);
} finally {

// Close connection
if (con != null) {
try {
con.close();
} catch (SQLException ex) {
System.out.println("Error in closing Conection");
ex.printStackTrace();
}
}



}
}
}

[David Harkness]

It's not an update statement, so you'll need to grab the ResultSet and pull the first value from the first row. The following is from memoory as it's been a while since I've had to do much JDBC.

ResultSet rs = stmt.execute("SELECT CUST_SEQUENCE.NEXTVAL FROM DUAL");
if ( rs.next() ) {
  cust_id = rs.getInt(1);
}
else {
  // Uh oh
}

[Nitin Jawarkar]

Hi,

I had faced the same problem in last few days but now it solved, actually David's solution may also correct but my solution is put the simple query in your code which select the latest sequence id on which you have to insert record.
Query is : " select @@identity as "Identity" " where "Identity" is the alies, funny thing is you don't need to give any table name, it automatically select the latest id which is recently insert. Hope it will give some help to you.
Thanx....

[Senthil B Kumar]

Use executeQuery rather than executeUpdate.

[Avi Abrami]

Akshatha,
Oracle JDBC drivers do not support RETURN_GENERATED_KEYS.
Use the following code:

String SQLCommand = "INSERT INTO CUSTOMER " +
                    "(cust_id,title,firstname,lastname) " +
                    "VALUES (CUST_SEQUENCE.NEXTVAL,?,?,?)";
PreparedStatement ps = con.prepareStatement(SQLCommand);
ps.setString(1, title);
ps.setString(2, firstname);
ps.setString(3, lastname);
ps.executeUpdate();

Alternatively, define a trigger on the CUSTOMER table that retrieves the next value from the CUST_SEQUENCE sequence.

David,
Oracle sequence numbers can get much larger than the maximum allowed value for an "int". In fact, Oracle JDBC drivers map the NUMBER data-type to the java.math.BigDecimal class.

Nitin,
The code you have posted is for Micro$oft SQL Server (and Access). Since Akshatha's code clearly indicates he is using an Oracle database, I don't think your code will help him.

Kolkata,
Actually, David's code used the "execute()" method (and not the "executeUpdate()" method).

Akshatha,
The wonderful thing about the Internet is that anyone can write anything they like -- even incorrect, misleading and incomplete statements. This can sometimes be very annoying, no?

Good Luck,
Avi.
[ January 05, 2005: Message edited by: Avi Abrami ]

[Shailesh Chandra]

Akshatha,

you can directly use sequence.nextval in your insert statement, instead of making two database command.

insert statement would be like this

  
String SQLCommand = "INSERT INTO CUSTOMER cust_id,title,firstname,lastname) 
VALUES(CUST_SEQUENCE.NEXTVAL,'" +  title + "','" + firstname + "','" + lastname + "')" ;
 

[ January 05, 2005: Message edited by: Shailesh Chandra ]

[Akshatha Nayak]

Thankx alot Nitin ,Prince, Shailesh,David,Avi for your time & patience .
David,
i made changes as you told me.. but i have used executeQuery() instead of execute ()..coz it gave me a compilation error.. but now its working fine ..

Avi,
Thankx for answering my post .. and about this
"The wonderful thing about the Internet is that anyone can write anything they like -- even incorrect, misleading and incomplete statements. This can sometimes be very annoying, no?"

yes you are right .. but remember that you are in JavaRanch . The main purpose of this site is to help people with their programming problems . if everybody knew everything nobody would have logged into javaranch .
JavaRanch is popular becoz all kinds of programming problems get solved here . Most of the people here try to learn things on their own .. so obviously silly doubts , annoying questions ,incorrect programming do get posted .. but its for the other members of this community to correct them,answer them patiently.. thatz called sharing information..
today if u answer somebody's problem..tomorrow somebody else will answer u .. thatz the way this wolrd works dude ..
i didnt post my program to mislead anybody ... i did it becoz i needed somebody to lead me to correct solution

[David Harkness]

Originally posted by Akshatha Nayak:
i made changes as you told me.. but i have used executeQuery() instead of execute ()..coz it gave me a compilation error.. but now its working fine .

Yes, thus my warning that I was posting code from old memory.

Also, while you can put the sequence access directly into the insert statement, you can't get that ID back as Avi mentioned. You get better (albeit negligible) performance as a trade-off, so if you're merely bulk inserting data or don't need to reference it after the insert via its PK, then you can safely combine them.

i didnt post my program to mislead anybody ...

Actually, I think Avi was implying some of the answers were misleading, but that's just a guess.
[ January 05, 2005: Message edited by: David Harkness ]

[Nitin Jawarkar]

Hello Avi,

I have an objection on your one sentence
"The wonderful thing about the Internet is that anyone can write anything they like -- even incorrect, misleading and incomplete statements. This can sometimes be very annoying, no?" As i take its meaning that would be herted to everybody who trying to solve the problem, nobody have extra time to give wrong, incomplete and misleading solution ok. Everybody is trying to put their own effort. So may be they wrong but its not meaning like that.

[Avi Abrami]

Akshatha,
Let me clarify. I did not mean that your question was misleading, I meant that some of the answers were. For example, if you tried Nitin's code (for Microsoft Access), you would have gotten several Oracle error messages which may have confused you even further. Wouldn't that make you feel more frustrated? I was just trying to warn you that some of the answers people post on "JavaRanch" may be wrong answers.

Personally, I feel that if I'm making an effort to help someone, then I should try my best to make sure I provide correct information. I suppose I should stop hoping that other people also aspire to this goal.

Good Luck,
Avi.

[Akshatha Nayak]

Avi ,
i understand that you were trying to help me.Thankx alot again . but if you see in your post above ,you have already mentioned about Nithin's code ..
i feel its better for all of us to just address the problem and help eachother by correcting eachother when we r wrong .. like how Avi did . Commenting on eachothers suggestion is really misleading coz that does not lead us to the solution... lets not do that..lets correct eachother instead .

[Nitin Jawarkar]

Hello Akshata and Avi,

Lets stop this debet friends, we are seating here to solve the problems of each other but not for battle. So hope both of you understand this. Just forget it and post new queries. OK