Re: prepared statements and sequences - Mailing list pgsql-jdbc

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
  );

???

2)How can you guarantee that between the select currval('sequence')
and the insertion that another value hasn't been inserted?




-Ryan


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