Avatar of cosmowen
cosmowen asked on

IB/FB generators and how do they work ??

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
Databases

Avatar of undefined
Last Comment
cosmowen

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
kacor

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
idt

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.
kacor

Hi Daniel,

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

Janos
ASKER
cosmowen

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
cosmowen

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
SOLUTION
idt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
kacor

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
ASKER
cosmowen

Thanks guys and I look forward to maybe helping you guys one day

-J
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.