jQuery in Action, 2nd edition*
The moose likes JDBC and the fly likes sequence creation Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "sequence creation" Watch "sequence creation" New topic
Author

sequence creation

Sri In
Greenhorn

Joined: May 08, 2009
Posts: 5
Hi,
Can anybody please help me how to generate sequence in
my sql 5.1.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31079
    
163

Does MySql support sequences? I thought it didn't. In which case you will need to create a table to hold the sequence value.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Vamsi Mohan
Ranch Hand

Joined: Jun 20, 2009
Posts: 80
Please find the sequence syntax:

CREATE SEQUENCE <sequence name> START WITH <start no.> MAXVALUE <max no.> INCREMENT BY <value> CACHE value;

Please use triggers, while inserting the records in the database. So automatically trigger will be triggered before inserting the record in the table.


VAMSI MOHAN.V
(SCJP5.0, IBM Rational Solution Designer, IBM Rational Architect, IBM RUP Designer)
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

Vamsi Mohan.Technology wrote:Please find the sequence syntax:

CREATE SEQUENCE <sequence name> START WITH <start no.> MAXVALUE <max no.> INCREMENT BY <value> CACHE value;

Please use triggers, while inserting the records in the database. So automatically trigger will be triggered before inserting the record in the table.
Does MySql 5.1 support that? I could not find it in the reference.


OCUP UML fundamental and ITIL foundation
youtube channel
ujjwal soni
Ranch Hand

Joined: Mar 28, 2007
Posts: 403
Hi,

Use the auto_increment feature in mysql. See the create table documentation - http://dev.mysql.com/doc/refman/5.1/en/create-table.html

create a real sequence as follows:
"""




now when you want to get the next number in the sequence do:
"""


the key here is that the sequence table is myisam, and so does not respect transactions. if you use a table that respects transactions, you will block when there are multiple transactions trying to get a sequence, whereas what you want is to just get a sequence.

Cheers!!!
Ujjwal B Soni <baroda, gujarat, india> <+919909981973>
"Helping hands are better than praying lips......"
Vamsi Mohan
Ranch Hand

Joined: Jun 20, 2009
Posts: 80
Yes, Mysql supports sequences. Please use appropriate syntax for creating sequences.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Does it? Is this new functionality? The documentation seems to suggest otherwise.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
ujjwal soni
Ranch Hand

Joined: Mar 28, 2007
Posts: 403
Hi,

Mysql does not have any specific keyword for sequences....
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: sequence creation