Solved

IB/FB generators and how do they work ??

Posted on 2004-03-31
8
847 Views
Last Modified: 2013-12-09
Hey experts, I have a table and I need to generate a unique ID all the way throught the table ie( PK_INDEX, USER, TRANS ) lets say that those are my fields in my Table.  Now in my program (C++) i need to create new users without every seeing the DB.  I need a generator that will automatically create a new  almost blank entry in my table, (by the almost blank, I mean I need a new unique index with no user or transaction so that when I:
insert into POSTRANS (PK_INDEX, USER, TRANS) values ( %d, %s, %d ), index, user_name, transaction_number;
what am I doing wrong in this call. I have my generator set up as:
 AS
  BEGIN
     NEW.PK_INDEX = GEN_ID(POSTRANS_GEN,1)
END

this is a type "BEFORE INSERT" .  What am I doing wrong, is it the SQL statement in my C++ code or am I missing something in the vein of a stored procedure or what.  This is beyond my understanding of DB's so PLease help..

Thanks in advance
0
Comment
Question by:cosmowen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 10

Accepted Solution

by:
kacor earned 75 total points
ID: 10729977
Hi cosmowen,

first you have to create a trigger which fires when the event occurs. If you use InterBase 7.0 or newer you haven't to use the SET TERM statement (but you can).

The following trigger, SET_NEW_USER, uses a generator to create unique user identification numbers
when a new user record is inserted in the USERS table:

SET TERM !! ;
CREATE TRIGGER SET_NEW_USER FOR USERS
AFTER INSERT AS
BEGIN
   NEW.USER_ID = GEN_ID(GEN_USER_ID , 1);
END !!
SET TERM ; !!

Your principle was right otherwise. If you need more explanations please tell us.

with best regards

Janos
0
 
LVL 3

Expert Comment

by:idt
ID: 10730824
Janos is correct with his assessment of the trigger creation, how it occurred to me that you did not create your generator before you tried to use it.  If this assumption is incorrect, I defer to Janos.

To create the generator:
CREATE GENERATOR YOUR_GENERATOR;
CREATE GENERATOR MY_GENERATOR;

To set a generator to a specific value:
SET GENERATOR YOUR_GENERATOR TO 100;

To use a (current value) generator in a query:
SELECT GEN_ID(MY_GENERATOR,0) FROM RDB$DATABASE;

To use in a trigger or stored procedure , refer to previous posting.

As a final note,  Generators are outside of the scope of transactions.  This means that if you start a transaction, cause a generator to be incremented or decremented, and then rollback, the generator does NOT return to its previous value.

Daniel P.
0
 
LVL 10

Expert Comment

by:kacor
ID: 10730868
Hi Daniel,

of course every referenced object must exist before referencing on it. Sorry I've forget it. Thanks for your comment

Janos
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:cosmowen
ID: 10733736
ok I am using Firebird 1.5  and IBAccess to modify the DB.  
Now the transactions that I am adding to the DB will not go away, they will be reconciled against a Master DB at the end of the day.  What I need to do is create the table and then add to the table every time a user makes a transaction.  then at the end of the day the DB in the POS station will reconcile with a master server and be reduced to an empty table and reset the generator.  This allows me to not have a headache of messing with losing transactions, since their boss wants to know everytime they open their till and every transaction they made.  

The PK_INDEX is the index that I need to keep unique, the USERID should be the same on most to all transactions so this is expected.  THat way when it reconciles with the MDB (and there will be multiple POS stations doing this) we will have a record of each user's transactiona and what they did.  Now I just gave you the premise so maybe this will clear up exactly what i am doing and maybe you can help me decide exactly what the generator should look like.  I just need each new index to be 1 higher than the previous.

So Janos: are you sure that it will be AFTER INSERT ?? cause I believe that it should be before so that no old data is overwritten.  also I entered what you typed and it keep giving me SQL -104 error and I tried to modify it a little bit and to no avail.

idt: I can't find my generator now but i inserted and modified it to work (so i thought) but Janos' code  wouldn't seem to work either.  please help I am so lost now.  However I am eager to learn so please instruct me.

-J
0
 

Author Comment

by:cosmowen
ID: 10733898
ok I think I might have it here is my trigger
AS
 BEGIN
NEW.PK_INDEX = GEN_ID(POSTRANS_GEN,1);
END

with POSTRANS_GEN being my generator.  now the only thing I need to know is what do I do in my C++ code when i am declaring an INSERT

INSERT into POSTRANS (PK_INDEX, USER, TRANS) values ( %d, %s, %d ), index, user_name, transaction_number;

should the index be left out or what.  this is now my only stumbling block please help.  THanks to janos and idt so far.  

-J
0
 
LVL 3

Assisted Solution

by:idt
idt earned 75 total points
ID: 10734231
Error 104 is datatype unknown/token unknown.  So if the generator had not been created, it could result in that error.

When inserting into the table do not include the PK_INDEX column or any other column that is set by your trigger.

INSERT INTO POSTRANS (USER, TRANS) values (%s, %d ),user_name, transaction_number;

since the value WILL be provided by a BEFORE INSERT trigger


Hope this helps

Daniel
0
 
LVL 10

Expert Comment

by:kacor
ID: 10734326
cosmowen,

sorry, you have right, this is a BEFORE INSERT activity, i tried to use the right syntactic and I made this mistake. I hope you repaired it

Janos
0
 

Author Comment

by:cosmowen
ID: 10734858
Thanks guys and I look forward to maybe helping you guys one day

-J
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question