Re: prepared statements and sequences

Ryan,

See my comments below

On Wed, 2003-04-02 at 00:43, Ryan Wexler wrote:
> I am interfacing a postgresql db with jdbc using jdk1.4x on a linux box.
> I have two questions/problems that I need help with.
>
> 1)Prepared Statments
> I am trying to use a prepared statement and am successful except for date
> fields. I get a parse error when using the preparedStatement.setDate(x,
> java.sql.Date); Is this a postgres thing or a personal problem? Is there
> a workaround?
>
Can you reproduce this in a small file? There was a similar question
yesterday??
>
>
>
>
>
> 2)Sequences-
> I am using sequences as unique identifiers, or rather I should say I would
> like to use sequences. I have successfully set up several sequences and
> every time i insert an new row it automatically increments itself. My
> problem is whenever I insert a row I need to know
> what the sequence is that was associated with the row inserted. I
> can't rely on doing a
> max(sequenceid) kind of query because there maybe 10 rows inserted in that
> time. My method of inserting rows is just using a prepared statement and
> in my insert statement i don't reference the sequence. Is there a way to
> get it to return the sequence id say when you call executeUpdate() on the
> prepared statement? Or what is the proper way to do this?

There is no way to get it to return the sequence. However you have two
options here

1) get the sequence before the insert and insert it with the data.

select nextval('sequence')

2) get the sequence after the insert

select currval('sequence')

Both of these methods are multi-connection safe, in other words if two
connections are creating sequences at the same time, you will get the
right data.

Dave
>
>
> thanks a ton
> ryan
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Dave Cramer <Dave(at)micro-automation(dot)net>