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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Daniel,
of course every referenced object must exist before referencing on it. Sorry I've forget it. Thanks for your comment
Janos
of course every referenced object must exist before referencing on it. Sorry I've forget it. Thanks for your comment
Janos
ASKER
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
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
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
Thanks guys and I look forward to maybe helping you guys one day
-J
-J
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.