Solved

IB/FB generators and how do they work ??

Posted on 2004-03-31
8
814 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
  • 3
  • 3
  • 2
8 Comments
 
LVL 10

Accepted Solution

by:
kacor earned 75 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Hi Daniel,

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

Janos
0
 

Author Comment

by:cosmowen
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:cosmowen
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks guys and I look forward to maybe helping you guys one day

-J
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Math Operators with SQL Statements 11 64
ODBC Informix Driver 11 91
UNION query Teradata - error 2 45
What is ISQL? 6 99
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Creating and Managing Databases with phpMyAdmin in cPanel.
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now