AutoInc with Interbase?

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 ;-)

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

Wow.  What a pain in the bum!  OK.  I'll check that out.  Thank's Meikl.

Thanks SBSen.  I'll check that out too.  What you have posted seems a little easier to do.  Cheers!

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 ;-)

meikl,
exactly!
-----
Igor.

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 ;-)

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.

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 ;-)

appendix

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

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

:-))

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

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 ;-)

anyway,
if stuart wants the bahaviour of an autoinc,
then sbsen is nearest

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.

about replication, i've never thought ;-)

hope you will beware of using  triggers by this way too :-)

stu, any results?

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.

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.

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

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