• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Autoincrement or not, how to ++

 
Med Shabe
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1561
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Graham Thorpe
Ranch Hand
Posts: 265
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1561
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 265
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think every site has unique username is it!!!So thinik about it and do ur best way coding
 
Dave Vick
Ranch Hand
Posts: 3244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
... 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.
 
Peter Reinhardt
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Med Shabe
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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."
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic