Re: Odd behavior with 'currval'

On Fri, 9 Feb 2018, Adrian Klaver wrote:

> I remember seeing it, so I went back to look at the message. Turns out you
> sent it to me only. Unfortunately I am not a Java programmer so I did not
> catch the error. For the record:

Ah, blast... Apologies to everyone concerned. I need to understand why
my mail reader (Alpine on Linux) insists on defaulting to the original
poster's e-mail address when I hit 'Reply' rather than the group list.
It's not doing that with any other mail list I subscribe to.

I was trying to be careful and changed the address manually for all
messages except this one.

Thanks, Adrian! I was sure that I sent it - just not to the right place
as it turns out.

> "
> Here is the JDBC code:
>
> try {
> conn.setAutoCommit(false);
> PreparedStatement sth = null;
> ResultSet rs = null;
>
> // Insert new
> sth = conn.prepareStatement(addAssetType);
> sth.setString(1, name);
> sth.execute();
>
> sth = conn.prepareStatement(lastTypeId);
> rs = sth.executeQuery();
> if (rs.next()) {
> long id = rs.getLong(1);
> result.put("id", id);
> result.put("name", name);
> }
> else {
> throw new WebApplicationException(buildError(BAD_REQUEST,
> "Lookup of most recent sequence failed"));
> }
> conn.commit();
> }
> catch (SQLException e) {
> conn.rollback();
> throw new WebApplicationException(buildError(BAD_REQUEST,
> e.getMessage()));
> }
>
>
> Where:
>
> private final static String addAssetType =
> "INSERT INTO udm_asset_type_definition (def_name) "
> + "VALUES (?)";
>
> private final static String lastTypeId = "SELECT currval(
> pg_get_serial_sequence('udm_asset_type_definition', 'def_id'))";
>
> Code with this exact same structure (but different SQL, obviously) is working
> just fine with five other tables using sequences. The above code snippet
> always produces '0' for the id. If I use the 'INSERT .. RETURNING..'
> approach, it gives the right answer.
> "

--