What you need to do is

in your client app issue
select GEN_ID(GEN_TABLE1_ID,1) from rdb$database
assign that value to some integer,
then
insert into TABLE1 (IDFIELD, FIELD2) VALUES ( <value from client>,44)
that should do it.
If you wanted a purely SQL, you could combine these into a stored procedure
which would return the ID value for you. I'll explain if you want me to but
it will be fairly long.


-----Original Message-----
From: Bahadir ARMAGAN [mailto:barmagan@...]
Sent: 10 December 2003 10:25
To: 'firebird-support@yahoogroups.com'
Subject: RE: [firebird-support] Re: Auto Incrementing field


:(
Created Generator:
<------------------
CREATE GENERATOR GEN_TABLE1_ID;
------------------>


I have created TABLE and IDFIELD (integer, PrimaryKey), FIELD2 in the table:
<--------------------------
CREATE TABLE TABLE1 (IDFIELD INTEGER NOT NULL, FIELD2 SMALLINT);
ALTER TABLE TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY (IDFIELD);
--------------------------->

note: These are auto generated codes by IBExpertPE. I didn't write them
manually. I WISH I COULD.

Created trigger:
<-------------------------
CREATE TRIGGER TABLE1_BI FOR TABLE1
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.IDFIELD IS NULL) THEN
NEW.IDFIELD = GEN_ID(GEN_TABLE1_ID,1);
END
---------------------------->

When I try to insert a record and return the ID
This statement returns an error:
<--------------------------
insert into TABLE1 (FIELD2) VALUES (44)
select GEN_ID(GEN_TABLE1_ID,1) from rdb$database;
-------------------------->


ERROR is:
<-----
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 2, char 1.
select.
---------->

There is no problem if I don't try to return GEN_ID(GEN_TABLE1_ID,1).

What is GEN_ID(GEN_TABLE1_ID,1)?
What "GEN_ID", "GEN_TABLE1_ID" and "1" stands for?
Can you recommend some readings for this subject? So I don't keep the
maillist busy with me.
Thanks again.

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Wednesday, December 10, 2003 11:13 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Re: Auto Incrementing field


At 10:28 AM 10/12/2003 +0200, you wrote:

>Thank you for your explanation. I did it.
>Now how can I get this auto generated vaule when I insert records? I have

an

>example for mssql server which uses @@IDENTITY for returning this value.
>Does it work for firebird?

That's MSSQL server, this is Firebird.

Beware!
select gen_id(gen_your_id, 0) from rdb$database will give the latest value,
but it's not necessarily the value you just generated. (Someone else might
have been along and got another one).

If you need to *know* the generated value in your app, get it *before* you
insert the new row, using
select gen_id(gen_your_id, 1) from rdb$database

And make sure your trigger is

IF (NEW.YOUR_ID IS NOT NULL) THEN
NEW.YOUR_ID = GEN_ID(GEN_YOUR_ID, 1);

/h



TRANSVARO Elektron Aletleri San. ve Tic. A.S.

Address: Fatih Cad. Dereboyu Sok. No:12
34660 Halkali - Istanbul - Turkey
Phone: +90 (212) 473 0100
Fax: +90 (212) 473 0155
URL: http://www.transvaro.com.tr
E-Mail: mail@...

NOT: Bu elektronik posta mesaji gizli olup, ayni zamanda ayricalikli
olabilir; sartlar ne olursa olsun, gondericinin onceden onayini almaksizin,
baskalarina iletmemeniz, icerigini kopyalamamaniz veya baskalarina
aciklamamaniz gerekmektedir. Bu mesajin muhatabi degilseniz, bu durumu
derhal tarafimiza bildirmenizi rica ederiz. Internet üzerinden yapilan
iletisim guvenli olmayip, verilerin kasten veya tesadufen bozulmasi ve virus
icermesi olasiligi vardir. Ayrica, elektronik posta, resmi olmayan ve
cogunlukla kisaltilmis bir iletisim yontemidir. Dolayisiyla, burada yer alan
bilgi veya tavsiyelere, gonderici ile ayrica gorusmeden guvenmeniz normal
sartlar altinda uygun olmayabilir.

NOTE: This e-mail is confidential and may also be privileged; under no
circumstances should you forward it, or copy or disclose its contents, to
any other person without the prior consent of the sender. If you are not an
intended recipient of this e-mail, please notify us immediately. Internet
communications are not secure and subject to possible data corruption,
either accidentally or on purpose, and may contain viruses. Furthermore
e-mail is an informal and often abbreviated method of communication. For
these reasons, it will normally be inappropriate to rely on any nformation
or advice contained herein without also discussing it with the sender.



[Non-text portions of this message have been removed]



To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/