db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Getting an attribute (the rid) of the affected tuples in an update
Date Mon, 15 Mar 2010 14:35:36 GMT
Gabriele Kahlout <gabriele@mysimpatico.com> writes:

> Hello,
>
> In my code, I often need to add a tuple  to another relation, so that
> I update another in another relation. Examine this code:
>
> private static int rescheduleObject(final String[] expDef, int batch,
> final Statement st, ResultSet rs) throws SQLException, Exception {
> 		final int objectId = rs.getInt(SqlWrapper.index); //assume rs has
> already been nexted.
> 		try{
> 			rescheduleObject(st, batch, objectId);
> 		} catch(SQLException e){ //batch is full
> 			final int res = st.executeUpdate(SqlWrapper.insert(batTable));
> 			assert(res == 1);
> 			batch = getMaxBatch(st);
> 			rescheduleObject(st, batch, objectId);
> 		}
> 		return objectId;
> 	}
>
> 	private static void rescheduleObject(final Statement st, int batch,
> final int objectId) throws SQLException {
> 		final int res = st.executeUpdate(SqlWrapper.update(objTable,
> batColumn, batch, objTable.getIdColumn(), objectId));
> 		assert(res == 1);
> 	}
>
> I'm not happy having to use batch = getMaxBatch(st), but would like to
> get exactly the batch (primary key of batTable) inserted in the 2
> lines above it. Are there ways to get it?

Hi Gabriele,

If you have declared the primary key as GENERATED ALWAYS AS IDENTITY or
as GENERATED BY DEFAULT AS IDENTITY, you could either

a) use the IDENTITY_VAL_LOCAL function
http://db.apache.org/derby/docs/10.5/ref/rrefidentityvallocal.html

or

b) change the executeUpdate() call to
   executeUpdate(SqlWrapper..., Statement.RETURN_GENERATED_KEYS) and
   then get the generated primary key by calling st.getGeneratedKeys()

Hope this helps,

-- 
Knut Anders

Mime
View raw message