I successfully use stored procedures with output parameters to retrieve
a generated primary key after an insert. Works great with the new
driver. Let me know if you want a code snipet.

-----Original Message-----
From: Richard Bair [mailto:richardallenbair@...]
Sent: Wednesday, August 07, 2002 12:02 AM
To: Firebird-Java@yahoogroups.com
Subject: Re: [Firebird-Java] Re: Getting a reference after insert

Rick,

Another approach might be to have a stored procedure for generating the
key.
Call the stored proc in the first sql, get the new Id, and then use
that
id when you insert into the table.

I faced the same basic issue years ago when I first started working with

interbase, and got around the issue via triggers and stored procedures.
I
basically had a before insert trigger that would get the new id and put
it
into the table, then a stored procedure that would return that latest
key.

So basically, I would do an insert statement and then a select statement

(from the stored proc) to get the new Id. That particular approach
wouldn't
work in a multiuser environment with frequent inserts, however. You
could
still do the "get the id and then insert" routine, which works fairly
well
(and is currently my preferred approach).


Just my 2 cents,
Rich

>From: "Rick Fincher" <rnf@...>
>Reply-To: Firebird-Java@yahoogroups.com
>To: <Firebird-Java@yahoogroups.com>
>Subject: Re: [Firebird-Java] Re: Getting a reference after insert
>Date: Tue, 6 Aug 2002 22:07:06 -0400
>
>Thanks for the input David, AND for all the work you have already done

on

>this project.
>
>I can work around this fairly easily in the particular app I'm doing.
>Certain fields are never empty so I can just insert an empty record

and

>the
>generator puts in a new key. Then I can search for the record with the

>null
>field, read the key, and update the record with the data. Then display

the

>key as a confirmation number for the user for future reference.
>
>A little kludgy but it will work, and the speed shouldn't be too bad.
>Fortunately this server insn't heavily loaded. I just have to insure

that

>no blank records get inserted if something breaks in mid-process, but
>that's
>easy enough to scan for periodically.
>
>Thanks,
>
>Rick
>
>
>----- Original Message -----
>
> > There's support for this stuff in jdbc 3. I think there is some way

to

>get
> > this info from firebird also, but it may require modifying the dsql
>parser.
> > I think I remember Ann Harrison talking about it. Oh to have

enough

>time
> > to take a look!
> >
> > david jencks
> >
> > On 2002.08.06 17:28:29 -0400 rrokytskyy wrote:
> > > Hi,
> > >
> > > > Forgive my ignorance about SQL, but is there a way to get the
>primary
> > > > key back of a record that is inserted? I know I can do a search

on

>the
> > > > same record data, but it is not guaranteed to be distinct. Do

any

>of
> > > > the driver call return this to the calling java program?
> > >
> > > Since you've used magic word "java" you're not bounced to

IB-Support

> > > list :)
> > >
> > > As far as I know there is no chance to get value of an ID

generated

> > > during execution of INSERT ... statement. You can either select

next

> > > ID directly before executing INSERT with
> > >
> > > SELECT gen_id(my_generator, 1) FROM RDB$DATABASE
> > >
> > > or you can get a block of IDs (for example 100) using
> > >
> > > SELECT gen_id(my_generator, 100) FROM RDB$DATABASE
> > >
> > > and then set them directly in your INSERT statement ("select ...

from

> > > rdb$database" returns you exactly one row). If you have other
> > > applications that assume that ID is generated automatically, you

can

> > > modify your trigger so it does not generate new ID if your primary

key

> > > is not null.
> > >
> > > But in general, such questions should be posted to IB-Support

group,

> > > you would get better feedback there.
> > >
> > > Best regards,
> > > Roman Rokytskyy
>
>

_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx



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



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