wood burning stoves*
The moose likes JDBC and the fly likes how to get the next sequence number .. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to get the next sequence number .." Watch "how to get the next sequence number .." New topic
Author

how to get the next sequence number ..

Akshatha Nayak
Ranch Hand

Joined: Jul 15, 2004
Posts: 53
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();
}
}



}
}
}


Only those who will risk going too far, Can possibly find out how far one can go! <br />happiness is journey and not destination<br /> <br />A Nayak<br />----------------------------------<br />SCJP 1.4<br />SCWCD 1.3<br />SCBCD 1.3
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
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.
Nitin Jawarkar
Ranch Hand

Joined: Dec 18, 2004
Posts: 79
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....


Cheers<br />Nitin
Senthil B Kumar
Ranch Hand

Joined: Feb 09, 2004
Posts: 140
Use executeQuery rather than executeUpdate.


Work like you don't need the money. Love like you've never been hated. Dance like nobody's watching. Sing like nobody's listening. Live like it's Heaven on Earth.
Currently I Reside Here WEBlog
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

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

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
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Akshatha,

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

insert statement would be like this


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

Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Akshatha Nayak
Ranch Hand

Joined: Jul 15, 2004
Posts: 53
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
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
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
Ranch Hand

Joined: Dec 18, 2004
Posts: 79
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
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

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
Ranch Hand

Joined: Jul 15, 2004
Posts: 53
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
Ranch Hand

Joined: Dec 18, 2004
Posts: 79
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

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to get the next sequence number ..
 
Similar Threads
Access database using JDBC error
Resultset not Open?
data mismatch error
Returning a 0 value error
jsp and jdbc