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.
> "

--