For reference I do something like this:

public class IdentityGenerator
{
private static final IdentityGenerator sm_instance = new
IdentityGenerator();

public static IdentityGenerator getInstance()
{
return sm_instance;
}


private long m_next = Long.MIN_VALUE;
private long m_max = Long.MIN_VALUE;

private static final int BLOCK_SIZE = 5000;

private static final String IDENTITY_QUERY =
"SELECT GEN_ID(IdGen,"
+ BLOCK_SIZE
+ ") FROM rdb$database;";

public synchronized long createUniqueId( final Connection con )
throws Exception
{
if ( m_next == m_max )
{
m_next = generateUnique( con );
m_max = ( m_next + BLOCK_SIZE );
}

return m_next++;
}

private long generateUnique( final Connection con )
throws Exception
{
final PreparedStatement ps = con.prepareStatement(
IDENTITY_QUERY );

try
{
final ResultSet rset = ps.executeQuery();
try
{
rset.next();
return ( rset.getLong( 1 ) - BLOCK_SIZE );
}
finally
{
rset.close();
}
}
finally
{
ps.close();
}
}
}


-----Original Message-----
From: Rick Fincher [mailto:rnf@...]
Sent: Monday, January 12, 2004 12:09 PM
To: Firebird-Java@yahoogroups.com
Subject: Re: [Firebird-Java] return ID after insert??


One other helpful idea from Roman: If you have a lot of inserts to do,
create a pool of ID's (primary keys) to use, then use them one by one as
needed.

Rick

----- Original Message -----

> Hi Richard,
>
> I had this same problem, the answer is below. I'll add this to the
FAQ
> since it keeps coming up.
>
> You do have to do a two step process with a generator to get the
Primary
Key
> (Pk below).
>
> To get a new primary key to insert with:
>
> SELECT gen_id(my_generator, 1) FROM RDB$DATABASE
>
> This fires your generator and returns the new key. The following sets
up
> the trigger so that it will auto create the primary key if you don't
pass
it
> one, but will use one that you give it (generated above). So this
works
> both ways.
>
> Rick
>
> >
> > The only way to get the value of a generated key into your app in a
way
> > that will allow you to subsequently locate the row you just inserted
is
to
> > generate the number FIRST and pass it into your insertsql. If you
have
an
> > "autoinc trigger" on this PK, then you must modify the trigger so
that
it
> > does a null test before firing, viz.
> >
> > create trigger...
> > active before insert
> > as
> > begin
> > if (new.YourPk is null) then
> > new.YourPk = gen_id(YourGenerator, 1);
> > end
> >
> > Trigger generation is outside of transaction control (the only thing
that
> > is!) so it is guaranteed to be 100% atomic.
> >
> > Helen
>
> ----- Original Message -----
> From: "Richard Drent" <richard@...>
> To: <Firebird-Java@yahoogroups.com>
> Sent: Sunday, January 11, 2004 7:33 AM
> Subject: [Firebird-Java] return ID after insert??
>
>
> >
> > Hi guy's and girl's,...
> >
> > Is there a way to get back the id of a record that just has been
> > inserted,... with java..??
> >
> > gteerz
> >
> >
> >
> > Richard Drent


Yahoo! Groups Sponsor
ADVERTISEMENT






Yahoo! Groups Links

To visit your group on the web, go to:
http://groups.yahoo.com/group/Firebird-Java/

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

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.