permaculture playing cards*
The moose likes JDBC and the fly likes Autoincrement or not, how to ++ Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Autoincrement or not, how to ++" Watch "Autoincrement or not, how to ++" New topic
Author

Autoincrement or not, how to ++

Med Shabe
Ranch Hand

Joined: May 08, 2001
Posts: 84
Yo Ranchers,
I'm dealing with DB2 database where I'm not sure if the field is autoincrement or not. Not even sure if it matters to me. What I know is that the filed is a PK and I need to insert a value based on the last value inserted; perhaps add 1 to it. In other words, how would I look up a last value of a field, increment it and insert a new row with the incremented value?

Thanks much.
Andres Gonzalez
Ranch Hand

Joined: Nov 27, 2001
Posts: 1561
I'd say an autoincrement field will suit your needs, just have a look at the DB2 documentation and you might find useful information.
I'm using the autoincrement in mysql and it does the same of what you want. adds 1 to the last number inserted in the DB
hope it helps


I'm not going to be a Rock Star. I'm going to be a LEGEND! --Freddie Mercury
Graham Thorpe
Ranch Hand

Joined: Mar 25, 2002
Posts: 265
Otherwise u retreve the maximum no of rows from database then u can add +1 then u can get the result.So u can use this code any databases either sql,oracle,mysql,db2 whatever...
Andres Gonzalez
Ranch Hand

Joined: Nov 27, 2001
Posts: 1561
hhmmm... I'm not quite sure about this solution... what happens if you have concurrent users? how does this mechanism behave?
hhmmm..
I'd go with autoincrement...
Graham Thorpe
Ranch Hand

Joined: Mar 25, 2002
Posts: 265
I think every site has unique username is it!!!So thinik about it and do ur best way coding
Dave Vick
Ranch Hand

Joined: May 10, 2001
Posts: 3244
... retreve the maximum no of rows from database then u can add +1 then u can get the result.

This won't work in most DBs that have had a row deleted from them. For example, say you have a DB with 10 rows added to it initially. Your highest auto increment ID is 10 and you have 10 rows. Now, if you delete a row you have only 9 rows but still a highest auto increment field of 10.


Dave
Peter Reinhardt
Ranch Hand

Joined: Aug 02, 2002
Posts: 57
I think I have read somewhere that DB2 does not support auto-increment fields. Anyway the best way to simulate this behaviour is to have a special table for the sequence number (with a column for the name of the sequence and the actual value of the sequence).
this pattern is described in the EJB Pattern book (http://www.theserverside.com). Don't worry about other clients accessing the same table, if you access the table inside a transaction with the proper isolation level you will be save.


SCJP 1.2, SCJD, SCEA, IBM 484, Weblogic 7
Med Shabe
Ranch Hand

Joined: May 08, 2001
Posts: 84
Thank you all for your inputs.
Now I'm a bit more confused than before which is a good thing. It made me think about a much fundamental solution for a seemingly simple problem.
"No problem is a simple problem."
 
Consider Paul's rocket mass heater.
 
subject: Autoincrement or not, how to ++
 
Similar Threads
How to INSERT with an autoincrement column
MySQL field is autoincrememnt - insert?
How to update table with autoincrement column in JSF application
How do I get the AUTOINCREMENT value?
Problem with OJB