Firebird SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesFirebird SQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread » Featured Thread Featured Thread  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old July 11th, 2003, 11:43 AM
jpenn's Avatar
jpenn jpenn is offline
Contributing User
Dev Shed Frequenter (2500 - 2999 posts)
 
Join Date: Jun 2002
Location: Washington, DC
Posts: 2,693 jpenn User rank is Sergeant (500 - 2000 Reputation Level)jpenn User rank is Sergeant (500 - 2000 Reputation Level)jpenn User rank is Sergeant (500 - 2000 Reputation Level)jpenn User rank is Sergeant (500 - 2000 Reputation Level)jpenn User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 5 h 41 m 10 sec
Reputation Power: 16
Easiest way to create an auto incrementing column?

Using FB - is there a built in way to create an auto incrementing column? If not, what is the easiest way to do this? I am guessing using a Generator - is this correct?

Thanks
__________________
~ Joe Penn

Reply With Quote
  #2  
Old July 11th, 2003, 12:10 PM
dcaillouet's Avatar
dcaillouet dcaillouet is offline
Big Endian
Dev Shed Beginner (1000 - 1499 posts)
 
Join Date: May 2001
Location: Fly-over country
Posts: 1,173 dcaillouet User rank is Sergeant (500 - 2000 Reputation Level)dcaillouet User rank is Sergeant (500 - 2000 Reputation Level)dcaillouet User rank is Sergeant (500 - 2000 Reputation Level)dcaillouet User rank is Sergeant (500 - 2000 Reputation Level)dcaillouet User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 16 h 29 m 5 sec
Reputation Power: 24
Yes you use a Generator.

For example:
Code:
INSERT INTO SALES (PO_NUMBER) VALUES (GEN_ID(generator_name, 1));
Or if you need the generator client-side before the insert, write a stored procedure which returns a generated number you can use in your program.
Code:
/* This stored procedure returns a generated integer */
CREATE PROCEDURE GEN_BATCH_ID returns (ID_VALUE Integer)
AS
BEGIN
  ID_VALUE = gen_id(BATCH_ID_GEN, 1);
  SUSPEND;
END
Or you can insert the record using a stored procedure and return the generated value after the insert.
Code:
/* Send this procedure an Employee's name. Get back the Employee's ID number. */
CREATE PROCEDURE EMPLOYEE_INSERT (LAST_NAME VARCHAR(25), FIRST_NAME VARCHAR(25)) returns (EMPLOYEE_ID Integer)
AS
BEGIN
  EMPLOYEE_ID = GEN_ID(EMPLOYEE_ID_GEN, 1);

  INSERT INTO EMPLOYEE (
    EMPLOYEE_ID,
    LAST_NAME,
    FIRST_NAME)
  VALUES (
    :EMPLOYEE_ID,
    :LAST_NAME,
    :FIRST_NAME);
END

Last edited by dcaillouet : July 11th, 2003 at 02:19 PM.

Reply With Quote
  #3  
Old July 13th, 2003, 02:41 AM
jpenn's Avatar
jpenn jpenn is offline
Contributing User
Dev Shed Frequenter (2500 - 2999 posts)
 
Join Date: Jun 2002
Location: Washington, DC
Posts: 2,693 jpenn User rank is Sergeant (500 - 2000 Reputation Level)jpenn User rank is Sergeant (500 - 2000 Reputation Level)jpenn User rank is Sergeant (500 - 2000 Reputation Level)jpenn User rank is Sergeant (500 - 2000 Reputation Level)jpenn User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 5 h 41 m 10 sec
Reputation Power: 16
Great - thanks dman- i thought it would be by means of the generator - just wasn't sure if there was already something built in to the package or not...

PS - appreciate the great examples

Reply With Quote
  #4  
Old December 29th, 2006, 01:37 AM
hafidz hafidz is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Dec 2006
Posts: 7 hafidz User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 h 2 m 47 sec
Reputation Power: 0
??

Quote:
Originally Posted by dcaillouet

For example:
Code:
INSERT INTO SALES (PO_NUMBER) VALUES (GEN_ID(generator_name, 1));


so how do we get the generator_name?? Please help..

Reply With Quote
  #5  
Old December 29th, 2006, 03:22 AM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 6th Plane (7500 - 7999 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 7,917 pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level) 
Time spent in forums: 2 Months 3 Weeks 3 Days 14 h 16 m 7 sec
Reputation Power: 279
You create it, so you know that name.
Note that you can put a trigger to do it for you, i.e. you insert nulls and it adds the value taken from the generator for you.
Find more infos in this document

Reply With Quote
  #6  
Old December 29th, 2006, 03:43 AM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 6th Plane (7500 - 7999 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 7,917 pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level) 
Time spent in forums: 2 Months 3 Weeks 3 Days 14 h 16 m 7 sec
Reputation Power: 279
Posting here as a reference for others:
Say you have a table like this
sql Code:
Original - sql Code
  1. CREATE TABLE new_one
  2. (
  3. col1 integer NOT NULL,
  4. col2 CHAR(10),
  5. PRIMARY KEY (field1)
  6. );
You want col1 to behave like an autoincrement/identity field.
You create a generator
sql Code:
Original - sql Code
  1. CREATE GENERATOR my_gen_id;
(you could set more options).
Now you want it to kick in automatically, just like an autoincrement, no need to explictly call it,
you can achieve this through a trigger:
sql Code:
Original - sql Code
  1. CREATE TRIGGER autoincrementor_id FOR new_one
  2. ACTIVE BEFORE INSERT POSITION 0
  3. AS
  4. BEGIN
  5. IF (NEW.col1 IS NULL) THEN
  6. NEW.col1 = GEN_ID(my_gen_id,1);
  7. END
Comments on this post
shammat agrees: Basically the same technique as with Oracle's sequences

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesFirebird SQL Development > Easiest way to create an auto incrementing column?


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 6 hosted by Hostway
Stay green...Green IT