Thread: prepared statements and sequences

prepared statements and sequences

From

Ryan Wexler

Date:

02 April 2003, 03:51:12

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?






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?


thanks a ton
ryan

Re: prepared statements and sequences

From

Dave Cramer

Date:

02 April 2003, 07:36:55

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@micro-automation.net>

Re: prepared statements and sequences

From

Ryan Wexler

Date:

02 April 2003, 14:04:32

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
>

Re: prepared statements and sequences

From

Dave Cramer

Date:

02 April 2003, 15:09:13

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@micro-automation.net>
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
--
Dave Cramer <Dave@micro-automation.net>

Re: prepared statements and sequences

From

Ryan Wexler

Date:

02 April 2003, 17:23:13

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@micro-automation.net>
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
> > >
> --
> Dave Cramer <Dave@micro-automation.net>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Re: prepared statements and sequences

From

Dave Cramer

Date:

02 April 2003, 17:30:08

Ryan,

Scroll Way down :)


On Wed, 2003-04-02 at 14:10, Ryan Wexler wrote:
> 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?

no, omit the column on the insert

insert (customer_id) values (1) will auto increment the serial, as will
insert (order_id, customer_id) values (DEFAULT, 1);

> >
> > > ???
> > >
> > > 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?
when nextval is called it remembers the value.

try it. open two windows with psql do a select nextval( 'sequence' ) in
each, then do  select curval('sequence') in each


>
> Any thoughts on the date thing?
I need some time to debug, and I am working on something else at the
moment, will get to it before tomorrow.

> thanks
> ryan
>
>
>
> > >
> > >
> > >
> > >
> > > -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
> > > >
> > --
> > Dave Cramer <Dave@micro-automation.net>
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
--
Dave Cramer <Dave@micro-automation.net>

Re: prepared statements and sequences

From

Ryan Wexler

Date:

02 April 2003, 17:39:07

Dave the scroll is getting longer....

On 2 Apr 2003, Dave Cramer wrote:

> Ryan,
>
> Scroll Way down :)
>
>
> On Wed, 2003-04-02 at 14:10, Ryan Wexler wrote:
> > 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?
>
> no, omit the column on the insert
>
> insert (customer_id) values (1) will auto increment the serial, as will
> insert (order_id, customer_id) values (DEFAULT, 1);

Ahhh now I see this will work perfect for me....

>
> > >
> > > > ???
> > > >
> > > > 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?
> when nextval is called it remembers the value.
>
> try it. open two windows with psql do a select nextval( 'sequence' ) in
> each, then do  select curval('sequence') in each
>
>

I believe you and I am going to try this, but there must be some basis.
Like do you have to use the same statment or connection or something like
that...

> >
> > Any thoughts on the date thing?
> I need some time to debug, and I am working on something else at the
> moment, will get to it before tomorrow.


Awesome dave this is much appreciated so I am using 7.3.1 if that helps

>
> > thanks
> > ryan
> >
> >
> >
> > > >
> > > >
> > > >
> > > >
> > > > -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
> > > > >
> > > --
> > > Dave Cramer <Dave@micro-automation.net>
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > >
> --
> Dave Cramer <Dave@micro-automation.net>
>
>

Re: prepared statements and sequences

From

Dave Cramer

Date:

02 April 2003, 17:47:37

Ryan,

Keep scrolling

On Wed, 2003-04-02 at 14:26, Ryan Wexler wrote:
> Dave the scroll is getting longer....
>
> On 2 Apr 2003, Dave Cramer wrote:
>
> > Ryan,
> >
> > Scroll Way down :)
> >
> >
> > On Wed, 2003-04-02 at 14:10, Ryan Wexler wrote:
> > > 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?
> >
> > no, omit the column on the insert
> >
> > insert (customer_id) values (1) will auto increment the serial, as will
> > insert (order_id, customer_id) values (DEFAULT, 1);
>
> Ahhh now I see this will work perfect for me....
>
> >
> > > >
> > > > > ???
> > > > >
> > > > > 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?
> > when nextval is called it remembers the value.
> >
> > try it. open two windows with psql do a select nextval( 'sequence' ) in
> > each, then do  select curval('sequence') in each
> >
> >
>
> I believe you and I am going to try this, but there must be some basis.
> Like do you have to use the same statment or connection or something like
> that...
You must use the same connection, sorry I guess I made an assumption.
>
> > >
> > > Any thoughts on the date thing?
> > I need some time to debug, and I am working on something else at the
> > moment, will get to it before tomorrow.
>
>
> Awesome dave this is much appreciated so I am using 7.3.1 if that helps
Have you tried the latest driver?
>
> >
> > > thanks
> > > ryan
> > >
> > >
> > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > -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
> > > > > >
> > > > --
> > > > Dave Cramer <Dave@micro-automation.net>
> > > >
> > > >
> > > > ---------------------------(end of broadcast)---------------------------
> > > > TIP 2: you can get off all lists at once with the unregister command
> > > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > > >
> > --
> > Dave Cramer <Dave@micro-automation.net>
> >
> >
--
Dave Cramer <Dave@micro-automation.net>

Re: prepared statements and sequences

From

Ryan Wexler

Date:

02 April 2003, 18:04:21

Dave I just downloaded the pg73jdbc3.jar driver.  But i can't seem to
connect using this driver, i  get:
java.sql.SQLException: Connection refused
java.sql.SQLException: Connection refused
        at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:230)
        at
oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:110)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:148)
        at java.sql.DriverManager.getConnection(DriverManager.java:512)
        at java.sql.DriverManager.getConnection(DriverManager.java:171)
        at movedaSQLException: Connection refused
        at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:230)
        at
oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:110)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:148)
        at java.sql.DriverManager.getConnection(DriverManager.java:512)
        at java.sql.DriverManager.getConnection(DriverManager.java:171)


I am refering to the class as "org.postgresql.Driver" then my connect
string is :
jdbc:postgresql://127.0.0.1/pgdb
I am using j2sdk1.4.1_01

Is this not right?

ryan


On 2 Apr 2003, Dave Cramer wrote:

> Ryan,
>
> Keep scrolling
>
> On Wed, 2003-04-02 at 14:26, Ryan Wexler wrote:
> > Dave the scroll is getting longer....
> >
> > On 2 Apr 2003, Dave Cramer wrote:
> >
> > > Ryan,
> > >
> > > Scroll Way down :)
> > >
> > >
> > > On Wed, 2003-04-02 at 14:10, Ryan Wexler wrote:
> > > > 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?
> > >
> > > no, omit the column on the insert
> > >
> > > insert (customer_id) values (1) will auto increment the serial, as will
> > > insert (order_id, customer_id) values (DEFAULT, 1);
> >
> > Ahhh now I see this will work perfect for me....
> >
> > >
> > > > >
> > > > > > ???
> > > > > >
> > > > > > 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?
> > > when nextval is called it remembers the value.
> > >
> > > try it. open two windows with psql do a select nextval( 'sequence' ) in
> > > each, then do  select curval('sequence') in each
> > >
> > >
> >
> > I believe you and I am going to try this, but there must be some basis.
> > Like do you have to use the same statment or connection or something like
> > that...
> You must use the same connection, sorry I guess I made an assumption.
> >
> > > >
> > > > Any thoughts on the date thing?
> > > I need some time to debug, and I am working on something else at the
> > > moment, will get to it before tomorrow.
> >
> >
> > Awesome dave this is much appreciated so I am using 7.3.1 if that helps
> Have you tried the latest driver?
> >
> > >
> > > > thanks
> > > > ryan
> > > >
> > > >
> > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > -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
> > > > > > >
> > > > > --
> > > > > Dave Cramer <Dave@micro-automation.net>
> > > > >
> > > > >
> > > > > ---------------------------(end of broadcast)---------------------------
> > > > > TIP 2: you can get off all lists at once with the unregister command
> > > > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > > > >
> > > --
> > > Dave Cramer <Dave@micro-automation.net>
> > >
> > >
> --
> Dave Cramer <Dave@micro-automation.net>
>
>

Re: prepared statements and sequences

From

Dave Cramer

Date:

02 April 2003, 18:06:47

Ryan,

Yes this is correct, it should be exactly the same as the old driver

Dave
On Wed, 2003-04-02 at 14:51, Ryan Wexler wrote:
> Dave I just downloaded the pg73jdbc3.jar driver.  But i can't seem to
> connect using this driver, i  get:
> java.sql.SQLException: Connection refused
> java.sql.SQLException: Connection refused
>         at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:230)
>         at
> oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:110)
>         at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:148)
>         at java.sql.DriverManager.getConnection(DriverManager.java:512)
>         at java.sql.DriverManager.getConnection(DriverManager.java:171)
>         at movedaSQLException: Connection refused
>         at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:230)
>         at
> oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:110)
>         at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:148)
>         at java.sql.DriverManager.getConnection(DriverManager.java:512)
>         at java.sql.DriverManager.getConnection(DriverManager.java:171)
>
>
> I am refering to the class as "org.postgresql.Driver" then my connect
> string is :
> jdbc:postgresql://127.0.0.1/pgdb
> I am using j2sdk1.4.1_01
>
> Is this not right?
>
> ryan
>
>
> On 2 Apr 2003, Dave Cramer wrote:
>
> > Ryan,
> >
> > Keep scrolling
> >
> > On Wed, 2003-04-02 at 14:26, Ryan Wexler wrote:
> > > Dave the scroll is getting longer....
> > >
> > > On 2 Apr 2003, Dave Cramer wrote:
> > >
> > > > Ryan,
> > > >
> > > > Scroll Way down :)
> > > >
> > > >
> > > > On Wed, 2003-04-02 at 14:10, Ryan Wexler wrote:
> > > > > 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?
> > > >
> > > > no, omit the column on the insert
> > > >
> > > > insert (customer_id) values (1) will auto increment the serial, as will
> > > > insert (order_id, customer_id) values (DEFAULT, 1);
> > >
> > > Ahhh now I see this will work perfect for me....
> > >
> > > >
> > > > > >
> > > > > > > ???
> > > > > > >
> > > > > > > 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?
> > > > when nextval is called it remembers the value.
> > > >
> > > > try it. open two windows with psql do a select nextval( 'sequence' ) in
> > > > each, then do  select curval('sequence') in each
> > > >
> > > >
> > >
> > > I believe you and I am going to try this, but there must be some basis.
> > > Like do you have to use the same statment or connection or something like
> > > that...
> > You must use the same connection, sorry I guess I made an assumption.
> > >
> > > > >
> > > > > Any thoughts on the date thing?
> > > > I need some time to debug, and I am working on something else at the
> > > > moment, will get to it before tomorrow.
> > >
> > >
> > > Awesome dave this is much appreciated so I am using 7.3.1 if that helps
> > Have you tried the latest driver?
> > >
> > > >
> > > > > thanks
> > > > > ryan
> > > > >
> > > > >
> > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > -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
> > > > > > > >
> > > > > > --
> > > > > > Dave Cramer <Dave@micro-automation.net>
> > > > > >
> > > > > >
> > > > > > ---------------------------(end of broadcast)---------------------------
> > > > > > TIP 2: you can get off all lists at once with the unregister command
> > > > > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > > > > >
> > > > --
> > > > Dave Cramer <Dave@micro-automation.net>
> > > >
> > > >
> > --
> > Dave Cramer <Dave@micro-automation.net>
> >
> >
--
Dave Cramer <Dave@micro-automation.net>

Re: prepared statements and sequences

From

Ryan Wexler

Date:

02 April 2003, 18:13:35

Dave I am being a space cadet,  the errors were for an oracle connection
I was also making. (I am migrating data to a postgresql db from and oracle
one)

That new driver works fine but I get the same error:


//The out statement of the query below before I set the variables
insert into customerorder (customer_id, address_id, payment_id,
createdate, ordertotal, tax, shipping, subtotal) values (?, ?, ?, ?, ?, ?,
?, ?)
Exception: java.sql.SQLException: ERROR:  parser: parse error at or near
"," at character 123

java.sql.SQLException: ERROR:  parser: parse error at or near "," at
character 123

        at
org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
        at
org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)
        at
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
        at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:197)
        at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:183)
        at movedata.MoveData.insertOrder(MoveData.java:51)


On 2 Apr 2003, Dave Cramer wrote:

> Ryan,
>
> Yes this is correct, it should be exactly the same as the old driver
>
> Dave
> On Wed, 2003-04-02 at 14:51, Ryan Wexler wrote:
> > Dave I just downloaded the pg73jdbc3.jar driver.  But i can't seem to
> > connect using this driver, i  get:
> > java.sql.SQLException: Connection refused
> > java.sql.SQLException: Connection refused
> >         at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:230)
> >         at
> > oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:110)
> >         at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:148)
> >         at java.sql.DriverManager.getConnection(DriverManager.java:512)
> >         at java.sql.DriverManager.getConnection(DriverManager.java:171)
> >         at movedaSQLException: Connection refused
> >         at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:230)
> >         at
> > oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:110)
> >         at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:148)
> >         at java.sql.DriverManager.getConnection(DriverManager.java:512)
> >         at java.sql.DriverManager.getConnection(DriverManager.java:171)
> >
> >
> > I am refering to the class as "org.postgresql.Driver" then my connect
> > string is :
> > jdbc:postgresql://127.0.0.1/pgdb
> > I am using j2sdk1.4.1_01
> >
> > Is this not right?
> >
> > ryan
> >
> >
> > On 2 Apr 2003, Dave Cramer wrote:
> >
> > > Ryan,
> > >
> > > Keep scrolling
> > >
> > > On Wed, 2003-04-02 at 14:26, Ryan Wexler wrote:
> > > > Dave the scroll is getting longer....
> > > >
> > > > On 2 Apr 2003, Dave Cramer wrote:
> > > >
> > > > > Ryan,
> > > > >
> > > > > Scroll Way down :)
> > > > >
> > > > >
> > > > > On Wed, 2003-04-02 at 14:10, Ryan Wexler wrote:
> > > > > > 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?
> > > > >
> > > > > no, omit the column on the insert
> > > > >
> > > > > insert (customer_id) values (1) will auto increment the serial, as will
> > > > > insert (order_id, customer_id) values (DEFAULT, 1);
> > > >
> > > > Ahhh now I see this will work perfect for me....
> > > >
> > > > >
> > > > > > >
> > > > > > > > ???
> > > > > > > >
> > > > > > > > 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?
> > > > > when nextval is called it remembers the value.
> > > > >
> > > > > try it. open two windows with psql do a select nextval( 'sequence' ) in
> > > > > each, then do  select curval('sequence') in each
> > > > >
> > > > >
> > > >
> > > > I believe you and I am going to try this, but there must be some basis.
> > > > Like do you have to use the same statment or connection or something like
> > > > that...
> > > You must use the same connection, sorry I guess I made an assumption.
> > > >
> > > > > >
> > > > > > Any thoughts on the date thing?
> > > > > I need some time to debug, and I am working on something else at the
> > > > > moment, will get to it before tomorrow.
> > > >
> > > >
> > > > Awesome dave this is much appreciated so I am using 7.3.1 if that helps
> > > Have you tried the latest driver?
> > > >
> > > > >
> > > > > > thanks
> > > > > > ryan
> > > > > >
> > > > > >
> > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > -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
> > > > > > > > >
> > > > > > > --
> > > > > > > Dave Cramer <Dave@micro-automation.net>
> > > > > > >
> > > > > > >
> > > > > > > ---------------------------(end of broadcast)---------------------------
> > > > > > > TIP 2: you can get off all lists at once with the unregister command
> > > > > > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > > > > > >
> > > > > --
> > > > > Dave Cramer <Dave@micro-automation.net>
> > > > >
> > > > >
> > > --
> > > Dave Cramer <Dave@micro-automation.net>
> > >
> > >
> --
> Dave Cramer <Dave@micro-automation.net>
>
>

Re: prepared statements and sequences

From

Dave Cramer

Date:

02 April 2003, 20:22:09

Ryan,

Just tried to replicate, and couldn't ???

after you set the parameters what does

System.out.println(pstmt.toString()) show you?

Dave
On Wed, 2003-04-02 at 15:00, Ryan Wexler wrote:
> Dave I am being a space cadet,  the errors were for an oracle connection
> I was also making. (I am migrating data to a postgresql db from and oracle
> one)
>
> That new driver works fine but I get the same error:
>
>
> //The out statement of the query below before I set the variables
> insert into customerorder (customer_id, address_id, payment_id,
> createdate, ordertotal, tax, shipping, subtotal) values (?, ?, ?, ?, ?, ?,
> ?, ?)
> Exception: java.sql.SQLException: ERROR:  parser: parse error at or near
> "," at character 123
>
> java.sql.SQLException: ERROR:  parser: parse error at or near "," at
> character 123
>
>         at
> org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
>         at
> org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)
>         at
> org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
>         at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
>         at
> org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:197)
>         at
> org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:183)
>         at movedata.MoveData.insertOrder(MoveData.java:51)
>
>
> On 2 Apr 2003, Dave Cramer wrote:
>
> > Ryan,
> >
> > Yes this is correct, it should be exactly the same as the old driver
> >
> > Dave
> > On Wed, 2003-04-02 at 14:51, Ryan Wexler wrote:
> > > Dave I just downloaded the pg73jdbc3.jar driver.  But i can't seem to
> > > connect using this driver, i  get:
> > > java.sql.SQLException: Connection refused
> > > java.sql.SQLException: Connection refused
> > >         at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:230)
> > >         at
> > > oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:110)
> > >         at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:148)
> > >         at java.sql.DriverManager.getConnection(DriverManager.java:512)
> > >         at java.sql.DriverManager.getConnection(DriverManager.java:171)
> > >         at movedaSQLException: Connection refused
> > >         at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:230)
> > >         at
> > > oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:110)
> > >         at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:148)
> > >         at java.sql.DriverManager.getConnection(DriverManager.java:512)
> > >         at java.sql.DriverManager.getConnection(DriverManager.java:171)
> > >
> > >
> > > I am refering to the class as "org.postgresql.Driver" then my connect
> > > string is :
> > > jdbc:postgresql://127.0.0.1/pgdb
> > > I am using j2sdk1.4.1_01
> > >
> > > Is this not right?
> > >
> > > ryan
> > >
> > >
> > > On 2 Apr 2003, Dave Cramer wrote:
> > >
> > > > Ryan,
> > > >
> > > > Keep scrolling
> > > >
> > > > On Wed, 2003-04-02 at 14:26, Ryan Wexler wrote:
> > > > > Dave the scroll is getting longer....
> > > > >
> > > > > On 2 Apr 2003, Dave Cramer wrote:
> > > > >
> > > > > > Ryan,
> > > > > >
> > > > > > Scroll Way down :)
> > > > > >
> > > > > >
> > > > > > On Wed, 2003-04-02 at 14:10, Ryan Wexler wrote:
> > > > > > > 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?
> > > > > >
> > > > > > no, omit the column on the insert
> > > > > >
> > > > > > insert (customer_id) values (1) will auto increment the serial, as will
> > > > > > insert (order_id, customer_id) values (DEFAULT, 1);
> > > > >
> > > > > Ahhh now I see this will work perfect for me....
> > > > >
> > > > > >
> > > > > > > >
> > > > > > > > > ???
> > > > > > > > >
> > > > > > > > > 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?
> > > > > > when nextval is called it remembers the value.
> > > > > >
> > > > > > try it. open two windows with psql do a select nextval( 'sequence' ) in
> > > > > > each, then do  select curval('sequence') in each
> > > > > >
> > > > > >
> > > > >
> > > > > I believe you and I am going to try this, but there must be some basis.
> > > > > Like do you have to use the same statment or connection or something like
> > > > > that...
> > > > You must use the same connection, sorry I guess I made an assumption.
> > > > >
> > > > > > >
> > > > > > > Any thoughts on the date thing?
> > > > > > I need some time to debug, and I am working on something else at the
> > > > > > moment, will get to it before tomorrow.
> > > > >
> > > > >
> > > > > Awesome dave this is much appreciated so I am using 7.3.1 if that helps
> > > > Have you tried the latest driver?
> > > > >
> > > > > >
> > > > > > > thanks
> > > > > > > ryan
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > -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
> > > > > > > > > >
> > > > > > > > --
> > > > > > > > Dave Cramer <Dave@micro-automation.net>
> > > > > > > >
> > > > > > > >
> > > > > > > > ---------------------------(end of broadcast)---------------------------
> > > > > > > > TIP 2: you can get off all lists at once with the unregister command
> > > > > > > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > > > > > > >
> > > > > > --
> > > > > > Dave Cramer <Dave@micro-automation.net>
> > > > > >
> > > > > >
> > > > --
> > > > Dave Cramer <Dave@micro-automation.net>
> > > >
> > > >
> > --
> > Dave Cramer <Dave@micro-automation.net>
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Dave Cramer <Dave@micro-automation.net>

Re: prepared statements and sequences

From

Ryan Wexler

Date:

02 April 2003, 20:47:22

Dave this is what I get

insert into customerorder (customer_id, address_id, payment_id,
createdate, ordertotal, tax, shipping, subtotal) values (1, 1, 1,
'2003-04-02', 123.00, 123.00, 123.00, 123.00)


The funny thing is if i insert this exact statement into psql it inserts
the row no problem.  The error I get is:
java.sql.SQLException: ERROR:  parser: parse error at or near "," at
character 123
Which indicates the comma after the first value.

ryan

On 2 Apr 2003, Dave Cramer wrote:

> Ryan,
>
> Just tried to replicate, and couldn't ???
>
> after you set the parameters what does
>
> System.out.println(pstmt.toString()) show you?
>
> Dave
> On Wed, 2003-04-02 at 15:00, Ryan Wexler wrote:
> > Dave I am being a space cadet,  the errors were for an oracle connection
> > I was also making. (I am migrating data to a postgresql db from and oracle
> > one)
> >
> > That new driver works fine but I get the same error:
> >
> >
> > //The out statement of the query below before I set the variables
> > insert into customerorder (customer_id, address_id, payment_id,
> > createdate, ordertotal, tax, shipping, subtotal) values (?, ?, ?, ?, ?, ?,
> > ?, ?)
> > Exception: java.sql.SQLException: ERROR:  parser: parse error at or near
> > "," at character 123
> >
> > java.sql.SQLException: ERROR:  parser: parse error at or near "," at
> > character 123
> >
> >         at
> > org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
> >         at
> > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)
> >         at
> > org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
> >         at
> > org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
> >         at
> > org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:197)
> >         at
> > org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:183)
> >         at movedata.MoveData.insertOrder(MoveData.java:51)
> >
> >
> > On 2 Apr 2003, Dave Cramer wrote:
> >
> > > Ryan,
> > >
> > > Yes this is correct, it should be exactly the same as the old driver
> > >
> > > Dave
> > > On Wed, 2003-04-02 at 14:51, Ryan Wexler wrote:
> > > > Dave I just downloaded the pg73jdbc3.jar driver.  But i can't seem to
> > > > connect using this driver, i  get:
> > > > java.sql.SQLException: Connection refused
> > > > java.sql.SQLException: Connection refused
> > > >         at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:230)
> > > >         at
> > > > oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:110)
> > > >         at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:148)
> > > >         at java.sql.DriverManager.getConnection(DriverManager.java:512)
> > > >         at java.sql.DriverManager.getConnection(DriverManager.java:171)
> > > >         at movedaSQLException: Connection refused
> > > >         at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:230)
> > > >         at
> > > > oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:110)
> > > >         at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:148)
> > > >         at java.sql.DriverManager.getConnection(DriverManager.java:512)
> > > >         at java.sql.DriverManager.getConnection(DriverManager.java:171)
> > > >
> > > >
> > > > I am refering to the class as "org.postgresql.Driver" then my connect
> > > > string is :
> > > > jdbc:postgresql://127.0.0.1/pgdb
> > > > I am using j2sdk1.4.1_01
> > > >
> > > > Is this not right?
> > > >
> > > > ryan
> > > >
> > > >
> > > > On 2 Apr 2003, Dave Cramer wrote:
> > > >
> > > > > Ryan,
> > > > >
> > > > > Keep scrolling
> > > > >
> > > > > On Wed, 2003-04-02 at 14:26, Ryan Wexler wrote:
> > > > > > Dave the scroll is getting longer....
> > > > > >
> > > > > > On 2 Apr 2003, Dave Cramer wrote:
> > > > > >
> > > > > > > Ryan,
> > > > > > >
> > > > > > > Scroll Way down :)
> > > > > > >
> > > > > > >
> > > > > > > On Wed, 2003-04-02 at 14:10, Ryan Wexler wrote:
> > > > > > > > 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?
> > > > > > >
> > > > > > > no, omit the column on the insert
> > > > > > >
> > > > > > > insert (customer_id) values (1) will auto increment the serial, as will
> > > > > > > insert (order_id, customer_id) values (DEFAULT, 1);
> > > > > >
> > > > > > Ahhh now I see this will work perfect for me....
> > > > > >
> > > > > > >
> > > > > > > > >
> > > > > > > > > > ???
> > > > > > > > > >
> > > > > > > > > > 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?
> > > > > > > when nextval is called it remembers the value.
> > > > > > >
> > > > > > > try it. open two windows with psql do a select nextval( 'sequence' ) in
> > > > > > > each, then do  select curval('sequence') in each
> > > > > > >
> > > > > > >
> > > > > >
> > > > > > I believe you and I am going to try this, but there must be some basis.
> > > > > > Like do you have to use the same statment or connection or something like
> > > > > > that...
> > > > > You must use the same connection, sorry I guess I made an assumption.
> > > > > >
> > > > > > > >
> > > > > > > > Any thoughts on the date thing?
> > > > > > > I need some time to debug, and I am working on something else at the
> > > > > > > moment, will get to it before tomorrow.
> > > > > >
> > > > > >
> > > > > > Awesome dave this is much appreciated so I am using 7.3.1 if that helps
> > > > > Have you tried the latest driver?
> > > > > >
> > > > > > >
> > > > > > > > thanks
> > > > > > > > ryan
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > -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
> > > > > > > > > > >
> > > > > > > > > --
> > > > > > > > > Dave Cramer <Dave@micro-automation.net>
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > ---------------------------(end of broadcast)---------------------------
> > > > > > > > > TIP 2: you can get off all lists at once with the unregister command
> > > > > > > > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > > > > > > > >
> > > > > > > --
> > > > > > > Dave Cramer <Dave@micro-automation.net>
> > > > > > >
> > > > > > >
> > > > > --
> > > > > Dave Cramer <Dave@micro-automation.net>
> > > > >
> > > > >
> > > --
> > > Dave Cramer <Dave@micro-automation.net>
> > >
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> --
> Dave Cramer <Dave@micro-automation.net>
>
>