Re: prepared statements and sequences

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>