Re: prepared statements and sequences

Dave I couple more comments on the sequence

On 2 Apr 2003, Dave Cramer wrote:

> 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.
This pretty much clears me up, except when you say omit the DEFAULT
setting do you mean on the create table call, or in the insert?
>
> > ???
> >
> > 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.

How can the server know this? What does it base it on?

Any thoughts on the date thing?
thanks
ryan

> >
> >
> >
> >
> > -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>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>