Thread: sequences in JDBC

I'm trying to use a sequence via JDBC, but I'm having a problem.

The prepared statement I'm using looks like this:

final String address_lineInsert = "insert into address_line( address_id,
address_line_id, address_line_text) values (?,?,?)";

I set the values like so:

insertAddressLine.setString(1,"nextval('address_id_seq')");
insertAddressLine.setString(2,addressLine.getAttribute("Number").getValue())
;
insertAddressLine.setString(3,addressLine.getText());

When I do the executeUpdate on the prepared statement, I get this error:

java.sql.SQLException: ERROR:  pg_atoi: error in
"nextval('address_id_seq')": can't parse "nextval('address_id_seq')"

This makes sense to me, and I think I could get around it by locking the
table, max value and then inserting my row... but I wonder if there is a
more graceful way to use sequences in JDBC that I'm missing.

Has someone else already solved this problem? Any ideas?

Thanks!

-Nick



---------------------------------------------------------------------
Nick Fankhauser

    nickf@doxpop.com  Phone 1.765.965.7363  Fax 1.765.962.9788
doxpop - Court records at your fingertips - http://www.doxpop.com/

At 01:47 PM 9/26/2001, Nick Fankhauser wrote:
>I'm trying to use a sequence via JDBC, but I'm having a problem.
>
>The prepared statement I'm using looks like this:
>
>final String address_lineInsert = "insert into address_line( address_id,
>address_line_id, address_line_text) values (?,?,?)";

The problem is that it is treating "nextval..." as a literal string and
then trying to convert it to a number (as that is the column's type). You
need to change it to this:

final String address_lineInsert = "insert into address_line( address_id,
address_line_id, address_line_text) values (?, nextval('address_id_seq'), ?)";

This way Postgres will execute the nextval function as you wanted.

Peace,
Dave

Thanks Dave! That's exactly what I needed to know. -Nick

> The problem is that it is treating "nextval..." as a literal string and
> then trying to convert it to a number (as that is the column's type). You
> need to change it to this:
>
> final String address_lineInsert = "insert into address_line( address_id,
> address_line_id, address_line_text) values (?,
> nextval('address_id_seq'), ?)";
>
> This way Postgres will execute the nextval function as you wanted.
>
> Peace,
> Dave
>