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@amanue.com
>
>
> ---------------------------(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>