We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

IB/FB generators and how do they work ??

cosmowen
cosmowen asked
on
Medium Priority
881 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
Comment
Watch Question

retired
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
idt

Commented:
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.
kacorretired

Commented:
Hi Daniel,

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

Janos

Author

Commented:
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

Author

Commented:
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
idt
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
kacorretired

Commented:
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

Author

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

-J
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.