Re: moveToInsertRow SQL Exception "No Primary Keys"

Jim,

So without a primary key, what would you think the update statement
should be

update coffees set sup_id=?, price=?, sales=? total=? where something=?

what should the something be ?

send me the output of pg_dump -t coffees -s

Thanks,

Dave

On Wed, 2003-02-05 at 14:33, Jim Rosenberg wrote:
> I am fairly new to PostgreSQL and completely new to JDBC. I am trying to
> follow along with the White et al _JDBC API Tutorial and Reference_, 2nd
> edition, from Addison Wesley, and cannot get moveToInsertRow to work.
>
> A thread from a few weeks ago discussed this issue; the poster was told
> that tables need a primary key for updatable result sets. We can argue
> about whether the designers of JDBC intended this (I believe they didn't
> ...) but on the other hand I don't have a problem with the idea that
> every table should have a primary key in my databases. When I put a
> primary key in the table coffees below I am still getting a SQL
> Exception "No Primary Keys" from moveToInsertRow.
>
> Here is the schema:
>
> CREATE TABLE coffees (
> cof_name character varying(32) NOT NULL,
> sup_id integer,
> price double precision,
> sales integer,
> total integer
> );
>
>
> --
> -- TOC entry 3 (OID 355664)
> -- Name: pi_coffees; Type: CONSTRAINT; Schema: public; Owner: jr
> --
>
> ALTER TABLE ONLY coffees
> ADD CONSTRAINT pi_coffees PRIMARY KEY (cof_name);
>
>
> And below is the code. It doesn't get any farther than printing out Boo1:
>
> Beagle.local. 25% java InsertRow
> Boo1
> SQLException: No Primary Keys
>
> My JDBC driver says it's 7.3 -- I'm not sure what build. PostgreSQL
> version is 7.3, OS is Mac OS 10.2.3.
>
> I don't see why moveToInsertRow is failing here, any help would be
> welcome. If I recall correctly, the White et al book is linked on the
> PostgreSQL JDBC web site; one would think from this that the code from
> the book would work ...
>
> -Thanks, Jim
>
> /*
> * @(#)Graph.java 1.7 98/07/17
> *
> * Copyright 1997, 1998, 1999 Sun Microsystems, Inc. All Rights Reserved.
> *
> * Sun grants you ("Licensee") a non-exclusive, royalty free, license to use,
> * modify and redistribute this software in source and binary code form,
> * provided that i) this copyright notice and license appear on all copies of
> * the software; and ii) Licensee does not utilize the software in a manner
> * which is disparaging to Sun.
> *
> * This software is provided "AS IS," without a warranty of any kind. ALL
> * EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING ANY
> * IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR
> * NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN AND ITS LICENSORS SHALL NOT BE
> * LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING
> * OR DISTRIBUTING THE SOFTWARE OR ITS DERIVATIVES. IN NO EVENT WILL SUN OR ITS
> * LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT,
> * INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER
> * CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF
> * OR INABILITY TO USE SOFTWARE, EVEN IF SUN HAS BEEN ADVISED OF THE
> * POSSIBILITY OF SUCH DAMAGES.
> *
> * This software is not designed or intended for use in on-line control of
> * aircraft, air traffic, aircraft navigation or aircraft communications; or in
> * the design, construction, operation or maintenance of any nuclear
> * facility. Licensee represents and warrants that it will not use or
> * redistribute the Software for such purposes.
> */
>
> import java.sql.*;
>
> public class InsertRow {
>
> public static void main(String args[]) {
>
> String url = "jdbc:postgresql://localhost/jdbc_book";
> Connection con;
> Statement stmt;
> String query = "select COF_NAME, PRICE from COFFEES";
>
> try {
>
> Class.forName("org.postgresql.Driver");
>
> } catch(java.lang.ClassNotFoundException e) {
> System.err.print("Hey, fluffy, ClassNotFoundException: ");
> System.err.println(e.getMessage());
> }
>
> try {
>
> con = DriverManager.getConnection(url,
> "jr", "");
>
> stmt = con.createStatement(
> ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
> ResultSet uprs = stmt.executeQuery(
> "SELECT * FROM COFFEES");
>
> System.out.println("Boo1");
> uprs.moveToInsertRow();
> System.out.println("Boo2");
>
> uprs.updateString("COF_NAME", "Kona");
> uprs.updateInt("SUP_ID", 150);
> uprs.updateFloat("PRICE", 10.99f);
> uprs.updateInt("SALES", 0);
> uprs.updateInt("TOTAL", 0);
>
> uprs.insertRow();
> uprs.beforeFirst();
>
> System.out.println("Table COFFEES after insertion:");
> while (uprs.next()) {
> String s = uprs.getString("COF_NAME");
> int sup = uprs.getInt("SUP_ID");
> float f = uprs.getFloat("PRICE");
> int sales = uprs.getInt("SALES");
> int t = uprs.getInt("TOTAL");
> System.out.print(s + " " + sup + " " + f + " ");
> System.out.println(sales + " " + t);
> }
>
> uprs.close();
> stmt.close();
> con.close();
>
> } catch(SQLException ex) {
> System.err.println("SQLException: " + ex.getMessage());
> }
> }
> }
>
> ---
> Jim Rosenberg http://www.well.com/user/jer/
> WELL: jer
> Internet: jr(at)amanue(dot)com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
--
Dave Cramer <Dave(at)micro-automation(dot)net>