Re: prepared statements and sequences

See below, comments on sequence, I will have to look at the date problem

On Wed, 2003-04-02 at 10:52, Ryan Wexler wrote:
> Thanks for replying much appreicated my comments are inserted
>
>
> On 2 Apr 2003, Dave Cramer wrote:
>
> >
> > 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??
> > >
>
> Here is the method I am calling. It is throwing the error on:
> pStatement.setDate(4, new java.sql.Date(new java.util.Date().getTime()))
>
> The connection broker i created uses the "org.postgresql.Driver" driver
> and has autocommit set to true.
>
>
> public void insertOrder()
> {
> try
> {
> String query = "insert into customerorder (customer_id, address_id,
> payment_id, createdate, ordertotal, tax, shipping, subtotal) " +
> "values (?, ?, ?, ?, ?, ?, ?, ?)" ;
> System.err.println(query);
>
> PreparedStatement pStatement= postgres.getPreparedStatement(query);
> pStatement.setInt(1, 1);
> pStatement.setInt(2, 1);
> pStatement.setInt(3, 1);
> pStatement.setDate(4, new java.sql.Date(new java.util.Date().getTime()));
> pStatement.setBigDecimal(5, (new BigDecimal(123)).setScale(2,
> BigDecimal.ROUND_HALF_UP));
> pStatement.setBigDecimal(6, (new BigDecimal(123)).setScale(2,
> BigDecimal.ROUND_HALF_UP));
> pStatement.setBigDecimal(7, (new BigDecimal(123)).setScale(2,
> BigDecimal.ROUND_HALF_UP));
> pStatement.setBigDecimal(8, (new BigDecimal(123)).setScale(2,
> BigDecimal.ROUND_HALF_UP));
> int i = pStatement.executeUpdate(query);
> System.err.println("i: " + i);
>
> }
> catch (Exception x)
> {
> System.err.println("Exception: " + x);
> x.printStackTrace();
> }
>
> }
>
> > >
> > >
> > >
> > >
> > > 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.
> >
>
> ???
> 1)If you use the nextval('sequence') method then can I assume when you
> create your table you don't auto set it to be connected to the sequence
> like:
> CREATE TABLE "customerorder"
> (
> order_id integer DEFAULT nextval('order_id') UNIQUE not null,
> customer_id integer not null,
> address_id integer not null,
> payment_id integer not null,
> createdate date not null,
> ordertotal numeric not null,
> tax numeric not null,
> shipping numeric not null,
> subtotal numeric not null
> );
>
Create the table just like above and use nextval, the sequence is only
autoincremented when you omit the column on insert, or insert DEFAULT.

> ???
>
> 2)How can you guarantee that between the select currval('sequence')
> and the insertion that another value hasn't been inserted?
The server does this for you.
>
>
>
>
> -Ryan
>
>
> > Dave
> > >
> > >
> > > thanks a ton
> > > ryan
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
> > --
> > Dave Cramer <Dave(at)micro-automation(dot)net>
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
--
Dave Cramer <Dave(at)micro-automation(dot)net>