take a look at the primary key generation design pattern on theserverside.com. it presents a very nice db-independent solution to the primary key generation problem.
the document can be d/led directly from: http://www.theserverside.com/resources/review/ejbpatterns-primarykeystrategies-sept3.zip but u need to register (free) in order to d/l their articles... At 13:17 01/10/19 -0400, David Jencks wrote: >not to quibble, but sequences only work if they are (implemented in the >rdbms) to be independent of transaction context. I think the important >point you are making is to get the id before you try to insert the row. > >david jencks > >On 2001.10.19 11:47:11 -0400 Edward Q. Bridges wrote: > > > > actually, you _don't_ want to use OID's in any form, they are internal > > housekeeping numbers that the RDBMS uses, they're really not intended for > > application work. besides, you then bind your code to postgres, because > > it's > > platform dependent (analogous to the rowid in oracle). it's also not > > guaranteed to be unique: if you move the database to a new installation, > > OID's start from the beginning. if you really really must make your code > > postgres-dependent, then use the "serial" datatype (which creates an > > implicit sequence). > > > > in any event the most platform independent approach is to do the select > > from > > the sequence in the same transaction as the insert. > > > > > > On 19 Oct 2001 08:34:47 -0200, Marcus Brito wrote: > > > > >> As an alternative to fetching the parameters from the original > > definition > > >> as above, you can use > > >> SELECT last_value FROM seqname; > > >> to obtain the last value allocated by any backend. > > > > > >You can't trust this. Perhaps some other process (other bean, other > > >application, other thread) has alredy modified the sequence. > > > > > >Suppose you have a table named MYTABLE, with a column named TBL_ID, > > >whose value is obtained from the SEQ_TBL_ID sequence. > > > > > >If you do something like: > > > > > >PreparedStatement stmt =3D dbConn.prepareStatement("INSERT INTO > > mytable(tbl= > > >_id) VALUES (nextval('seq_tbl_id'))"); > > >stmt.executeUpdate(); > > > > > >you should do the following (in JBoss) to get the value just inserted > > > > > >int oid =3D ((org.postgresql.Statement)(((org.jboss.pool.jdbc.PreparedState= > > >mentInPool)stmt).getUnderlyingPreparedStatement())).getInsertedOID(); > > > > > >and then > > > > > >stmt =3D dbConn.prepareStatement("SELECT tbl_id FROM mytable WHERE oid > > =3D = > > >?"); > > >stmt.setInt(1, oid); > > >ResultSet rs =3D stmt.executeQuery(); > > >rs.next(); > > >int tbl_id =3D rs.getInt("tbl_id"); > > > > > >This is pretty ugly, specilly the line to obtain the inserted OID. So > > >many typecasts. Also, this is not portable across application servers. > > >But is the only way to make sure you obtain the last value you inserted. > > >If I'm wrong, please someone correct me. > > > > > >--=20 > > >Ja ne, > > > Pazu > > > mailto: [EMAIL PROTECTED] > > > > > >Anime Gaiden: de f=E3s para f=E3s, sempre. > > > > -------------------------------------------- > > <argo_tec gmbh> > > ed.q.bridges > > tel. 089-368179.552 > > fax 089-368179.79 > > osterwaldstra$B!,(Be 10 > > (haus F eingang 21) > > 80805 m$B—O(Bchen > > </argo_tec gmbh> > > -------------------------------------------- > > > > > > > > _______________________________________________ > > JBoss-user mailing list > > [EMAIL PROTECTED] > > https://lists.sourceforge.net/lists/listinfo/jboss-user > > > > > >_______________________________________________ >JBoss-user mailing list >[EMAIL PROTECTED] >https://lists.sourceforge.net/lists/listinfo/jboss-user _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
