Many thanks for the example, Fred.

Seems to be several ways to do this, but still struggling to find a
way that allows me to migrate my code easily. At the moment I have
a single DatabaseBean which handles all interation with the
database, and programmers are able to call methods on that bean that
pass in a SQL statement to execute.

In the insert case, they call the method with, for example:

INSERT INTO table1 (col2, col3) VALUES (col2Val, col3Val)

and the call returns the id of the newly created row.

To migrate easily, therefore, I'd like to find a way that allows me
to still issue that call into the database, and get the id back. So
far, I don't think any of the solutions proposed allow me to do so
(but I could be wrong!!).

Is one possiblity locking the table for writing? That would mean
the trigger couldn't get called before I call the

SELECT GEN_ID(GEN_ATABLE, 0) FROM RDB$DATABASE;

immediately after the insert, and then release the lock?

Does anyone know of any problems with this approach, performance
issues etc?

Thanks very much!!

David



--- In ib-support@yahoogroups.com, "Wilson, Fred" <fred.wilson@b...>
wrote:

> Here's an example of a "insert" stored procedure, similiar to one

of many

> that we use..
> The USER and PASSWORD, of course aren't 'XXXX', but are the real

strings..

> This is just an example of how to get the ID back.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> CONNECT "hubba:d:\intrbase\5.8.0\ga"
> USER "xxxx" PASSWORD "xxxxx";
>
> SET TERM !!;
> CREATE PROCEDURE sp_AddtMachineRecord
> (
> NAME ARCHAR (32),
> DESCRIPTION VARCHAR (60),
> feederCount integer,
> MachineTypeID INTEGER,
> miltsID INTEGER,
> SerialNumber VARCHAR (32),
> GUITypeID INTEGER ,
> AIMCount INTEGER
> )
> RETURNS
> (
> machineID INTEGER
> )
>
> AS
> BEGIN
>
>
> machineid = gen_id( machineid_gen, 1 );
>
> INSERT INTO MACHINE
> (
> machineID,
> name,
> description,
> feederCount,
> machineTypeID,
> miltsID,
> serialnumber ,
> monitor ,
> GUITypeID ,
> AIMCount
> )
> VALUES
> (
> :machineid,
> :NAME,
> :DESCRIPTION,
> :feederCount,
> :machineTypeID,
> :miltsID,
> :serialnumber ,
> 1,
> :GUITypeID ,
> :AIMCount
> );
>
> SUSPEND;
>
> END !!
> SET TERM ; !!
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> -----Original Message-----
> From: hay77772000 [mailto:david.hay@e...]
> Sent: Monday, April 28, 2003 1:27 PM
> To: ib-support@yahoogroups.com
> Subject: [ib-support] Re: Getting id of last inserted record
>
>
> Okay....how would I do that?! Never created stored procedure
> before! Also, would I have to use that in the insert statement?
> With triggers, the programmer never has to worry about whether

it's

> autoincrement or not.
>
> Thanks very much,
>
> David
>
>
> --- In ib-support@yahoogroups.com, "Robert DiFalco"

<rdifalco@t...>

> wrote:
> > What about using a stored procedure instead of a trigger? The
> stored
> > procedure could return the generated value that was used to set

the

> > identity column.
> >
> > -----Original Message-----
> > From: hay77772000 [mailto:david.hay@e...]
> > Sent: Monday, April 28, 2003 1:22 PM
> > To: ib-support@yahoogroups.com
> > Subject: [ib-support] Getting id of last inserted record
> >
> >
> > Hi. Am still continuing to port our system from mySQL to
> > Firebird.
> >
> > Have set up the database correctly, along with generators
> and
> > triggers that simulate an autoincrement field.
> >
> > However, I still need a way to get the id back of the

record

> > just
> > inserted, in a distributed, multiple-user environment.
> >
> > Can anyone let me know how to do so, please? I understand

I

> > could
> > use SELECT GEN_ID(GEN_ATABLE, 0) FROM RDB$DATABASE; but

that

> > this
> > cannot be relied on with other transactions etc..
> >
> > Thanks,
> >
> > David
> >
> >
> >
> >
> >
> > Yahoo! Groups Sponsor
> >
> >
> <

http://rd.yahoo.com/M=229633.3212141.4526654.2595810/D=egroupweb/S=1

>

<http://rd.yahoo.com/M=229633.3212141.4526654.2595810/D=egroupweb/S=1

>
> 705
> > 115386:HM/A=1556779/R=0/* http://shop.store.yahoo.com/cgi-

bin/clink?

> <http://shop.store.yahoo.com/cgi-bin/clink?>
> ftd2+
> >
>

shopping:dmad/M=229633.3212141.4526654.2595810/D=egroupweb/S=17051153

> 86:
> >
> HM/A=1556779/R=1/1051561355+

http://us.rmi.yahoo.com/rmi/http://www.ft

> <http://us.rmi.yahoo.com/rmi/http://www.ft>
> d.c
> > om/rmi-framed-url/ http://www.ftd.com/yahoo36

<http://www.ftd.com/yahoo36>

> >
> >
> > < http://us.adserver.yahoo.com/l?

<http://us.adserver.yahoo.com/l?>

> M=229633.3212141.4526654.2595810/D=egrou
> > pmail/S=:HM/A=1556779/rand=309980560>
> >
> > To unsubscribe from this group, send an email to:
> > ib-support-unsubscribe@egroups.com
> >
> >
> >
> > Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> > Service < http://docs.yahoo.com/info/terms/
> <http://docs.yahoo.com/info/terms/> > .
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
>
>
>
> Yahoo! Groups Sponsor
>
>
> <http://us.adserver.yahoo.com/l?

M=229633.3212141.4526654.2595810/D=egroupmai

> l/S=:HM/A=1556779/rand=375633351>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
> <http://docs.yahoo.com/info/terms/> .
>
>
>
>
> [Non-text portions of this message have been removed]