Solved

AutoInc with Interbase?

Posted on 2001-07-11
23
396 Views
Last Modified: 2010-04-06
Hi,

Does Interbase have a field type of AutoInc, or do I need to setup a generator to do it?  If so, can someone show me how to do this, as the example I worked from in the help file didn't work ;(

Ta.

Stu.
0
Comment
Question by:Stuart_Johnson
  • 10
  • 6
  • 4
  • +1
23 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6275661
hi,

you need a generator

i've setup the generator in interbase like

CREATE GENERATOR CVP_ID_GEN;

COMMIT WORK;

/*Procedures*/
SET AUTODDL OFF;
SET TERM !! ;

CREATE PROCEDURE CVP_NEXT_ID
RETURNS (ID INTEGER)
AS
BEGIN
  ID = GEN_ID(CVP_ID_GEN,1);
END !!

SET TERM ; !!
SET AUTODDL ON;

COMMIT WORK;

and use in delphi a stored procedure and a function like

Function TF_PResource.Get_Next_ID(ProcName : String) : Integer;
Begin
  StoredProc1.StoredProcName := ProcName;
  Try
    StoredProc1.ExecProc;
    Result := StoredProc1.ParamByName('ID').AsInteger;
  Except
    Raise;
  end;
end;

and use the beforepost event

procedure TF_PResource.Tbl_PRGBeforePost(DataSet: TDataSet);
begin
  if fr_prg1.Tbl_PRG.State = dsInsert then
    fr_prg1.Tbl_PRG.FieldByName('PRG_ID').AsInteger := Get_Next_ID('PRG_NEXT_ID');
end;

on the id field must the required property set to false

you can use also a trigger on the database,
but i myself want more control about this

meikl ;-)
0
 
LVL 1

Expert Comment

by:SBSen
ID: 6275934
Hi,

u can create a generator and write a trigger BEFORE INSERT
so it will ack as a AUTOINC Field.

regards
senthil

CREATE GENERATOR EMP_NO_GEN;

CREATE TRIGGER SET_EMP_NO FOR EMPLOYEE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    new.emp_no = gen_id(emp_no_gen, 1);
END
0
 
LVL 6

Author Comment

by:Stuart_Johnson
ID: 6276015
Wow.  What a pain in the bum!  OK.  I'll check that out.  Thank's Meikl.
0
 
LVL 6

Author Comment

by:Stuart_Johnson
ID: 6276043
Thanks SBSen.  I'll check that out too.  What you have posted seems a little easier to do.  Cheers!
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6276061
hi,

well sbsen's triggerlogic does this what an autoinc really does and i've mantioned that you can do so

just for myself, i want more control for the given number,
because with a trigger the given number is unknown to the app and the record must be refetched to get knowledge about the given number to the app

you may get messages like record has changed by another user, if u use a trigger-logic, when u try to update the just inserted record, without refreshing your dataset.

meikl ;-)
0
 
LVL 9

Expert Comment

by:ITugay
ID: 6276357
meikl,
exactly!
-----
Igor.



0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6276400
hi igor,

a friend of me says, if you use a trigger,
then you have forgotton something in your coding-concept.

(well, i think that can't be said in general)

meikl ;-)
0
 
LVL 1

Expert Comment

by:SBSen
ID: 6276466
Hi meikl,


>you may get messages like record has changed by another >user, if u use a trigger-logic, when u try to
>update the just inserted record, without refreshing your >dataset.

While inserting a record to a Table how can it say is there
is any logic;-)
>record has changed by another user
while update it can say this :-(

Triggers are very faster they fire automatically

There are some parts of application if we don't use triggers it will become around head process.

consider
sbsen.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6276508
sbsen,
just reread
>when u try to >> UPDATE << the just inserted record

well,
>Triggers are very faster they fire automatically
but they are avoiding the transparency of coding and versioning, and specially in delphi, its more work to
handle the unknown keyfield in a further process,
also the time-issue is so minimal->forgetable in a dialogfrontend

well, you see i don't like triggers :-)

but sure it can be done with triggers as you shown

meikl ;-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6276516
appendix

just to say,
the refetching of a record costs more time
then to get a next id from a storedproc
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6276555
>There are some parts of application if we don't use
>triggers it will become around head process

if you use a trigger,
then you have forgotton something in your coding-concept.

:-))
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 9

Expert Comment

by:ITugay
ID: 6276713
Just my experience in primary keys generation :-)


I found another way to generate primary keys. This way is best for me and I use it last few years in all DB applications.

All databases has table USERS
ID_USER int not null primary key
USER_NAME varchar(50)
....

ID_USER values 0..1023
USER_NAME - login name

Once user connected to database, application extracts unique user's  ID.  I have TQuery descendant component that able automatically generate primary key depending from user ID. Let say for user ID=23, generated primary keys will be from 23,000,000 to 23,999,999 (user's cluster). BeforePost event extracts maximum value of primary key that lie in user's cluster and increment value. Last used value for the table stored in application's primary keys cache, so, it isn't necessary next time to make DB call to generate new primary key. Regarding all users has different ID's it is not possible to generate duplicate key's for different users.

Advantages of this way:

 - you always know value of new generated key (easy way to refetch new record)
 - fast key generation without triggers
 - works for all database types
 - record can be associated with the user (e.g. it is possible to prevent user's from modification of records created by another user)

Disadvantages:

 - key can be reused. Let say user deleted last created record and in next session the same key will be generated again. I have solution to prevent such things, but it doesn't look like easy. Anyway, usually no any problems with key reusing. Even in replication processes.

-----
Igor
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6276751
thats a way, igor,
which i would use, if the database doesn't support such as generators (interbase) or sequences (oracle) (you can easily wrap the different syntax into a stored function, so that the interface to your coding language (delphi for ex.) is allways the same).

i use such as your suggestion for generating user-depending document-keys like orders or invoices,
but also wrapped into a database-depended procedure with
a table on the back, to avoid such reusing, also with an unified interface to the coding language (delphi for ex.).

meikl ;-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6276766
anyway,
if stuart wants the bahaviour of an autoinc,
then sbsen is nearest
0
 
LVL 9

Expert Comment

by:ITugay
ID: 6276842
it's also the way :-)

The main reason why I avoid of using triggers is necessity to replicate few databases (e.g. via e-mail messages once or few times per day). Every database has different users (once ID_USER used in one database it never apears in other). Customers add data in all databases in the same time. Can you guess what is the problem will be with triggers in this way? Yes, it is possible to produce duplicate primary keys in different databases and replication process will be not possible (about not possible by easy way ;-).

Igor.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6276870
about replication, i've never thought ;-)
0
 
LVL 9

Expert Comment

by:ITugay
ID: 6276883
hope you will beware of using  triggers by this way too :-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6279996
stu, any results?
0
 
LVL 6

Author Comment

by:Stuart_Johnson
ID: 6284317
Sorry guys, just checking it right now.  I've been away from work for three days.

Be back with a comment in a coupld of minutes.

Stu.
0
 
LVL 6

Author Comment

by:Stuart_Johnson
ID: 6284346
OK, I think I'm doing something wrong here.  I'm new to this - I'm used to my DB's creating their own autoinc fields.

Meikl: I have read your comments above, and although I agree with what you are saying, I don't actually need to know what the new ID number is at the time I am writing to the DB.  Therefore, I think that sbsen's proposal maybe more what I am looking for.

Therefore, I guess my next question is pointed at SBSen.

I am just using good old IBConsole to create my tables and then I tried adding the generator you posted.  I could run the first line OK (CREATE GENERATOR EMP_NO_GEN;), but the subsequent lines produced an error.  What I ran was this:

CREATE TRIGGER SET_EMP_NO FOR EMPLOYEE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
new.emp_no = gen_id(emp_no_gen, 1);
END

Is this supposed be broken down further (say, a semicolon at the end of the first line)?  The error I get is:

Dynamic SQL Error
SQL error code = -104
Unexpected end of command
Statement: CREATE TRIGGER SET_EMP_NO FOR EMPLOYEE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
new.emp_no = gen_id(emp_no_gen, 1)

Can you help me out with this?  I created a table using this script just for testing purposes:

CREATE TABLE EMPLOYEE (
EMP_NO INTEGER,
FULLNAME VARCHAR(100))

Am I missing anything?

Cheers,


Stu.
0
 
LVL 6

Author Comment

by:Stuart_Johnson
ID: 6495377
Hi SBSen,

Do you have anything else you can add to help out with this?  I'm not actually doing this anymore, but I would still be curious as how to get it working.

If not, I'll split the points between you and Meikl.

Stu
0
 
LVL 1

Accepted Solution

by:
SBSen earned 100 total points
ID: 6495584
Hi,

It Should be like this add "SET TERM" It will work

regards
sbsen.

SET TERM !! ;
CREATE TRIGGER SET_EMP_NO FOR EMPLOYEE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
new.emp_no = gen_id(emp_no_gen, 1);
END !!
SET TERM ; !!

0
 
LVL 6

Author Comment

by:Stuart_Johnson
ID: 6658194
Thanks for you help.  Sorry it's taken so long to get back too you, but EE hasn't been working everytime I've tried to log in.

Meikl, I'll post you a question you can answer to grab your points.

Thanks guys.

Stu
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

744 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

13 Experts available now in Live!

Get 1:1 Help Now