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
|