Thread: moveToInsertRow SQL Exception "No Primary Keys"
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
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>
Jim, Using the current cvs HEAD and creating the table as follows create table coffees (cof_name char(32) not null primary key, sup_id int, price double precision, sales int, total int); your code works fine. the output is Boo1 Boo2 Table COFFEES after insertion: Kona 150 10.99 0 0 Dave -- Dave Cramer <Dave@micro-automation.net>
Further more, creating the table exactly as you have it, also works Dave On Wed, 2003-02-05 at 19:26, Dave Cramer wrote: > Jim, > > Using the current cvs HEAD and creating the table as follows > > > create table coffees (cof_name char(32) not null primary key, sup_id > int, price double precision, sales int, total int); > > > your code works fine. the output is > > > Boo1 > Boo2 > Table COFFEES after insertion: > Kona 150 10.99 0 0 > > Dave -- Dave Cramer <Dave@micro-automation.net>
> Further more, creating the table exactly as you have it, also works Oh joy. So, my problem is platform-dependent, it seems. *SOMETHING* is going wrong on my system with the ability of the JDBC driver to detect the primary key. Do you have any suggestions for what I can do to try to trace this problem? There is an interesting JDBC client called SQuirrel SQL. When I aim this at the database in question and inspect the table coffees, under the Primarary Key tab it does show my key as being there. The view has a column called table_cat, and the value showing there is <Other> -- I have no idea if this is normal or not. Under table_schem it shows public, it shows the right table name and column name, under key_seq it shows 1, under pk_name it shows pi_coffees. I sure would like to get this fixed. I really wanna be using updatable result sets, but at the moment this problem has me stopped cold. Any suggestions for things I can try to diagnose the problem would be welcome. I'm using the Mac OS X PostgreSQL distribution from www.entropy.ch, which is one of the more commonly used OS X packages I think. -Thanks, Jim --- Jim Rosenberg http://www.well.com/user/jer/ WELL: jer Internet: jr@amanue.com
> Let me know how this works Bingo! A thousand thanks, your postgresql.jar works fine. The one I had been using was pg73jdbc2.jar which I downloaded from the PostgreSQL JDBC site just in the last couple of days. Thanks for the ref in the White et al book on the primary key issue. It looks to me like they didn't follow their own advice in making examples for their book!! As I said earlier, I don't really have a problem with the idea that every table should have a primary key -- it's just good database design. (Though I have to say that I have seen cases where one is stuck with e.g. an ERP system that has a table in which the designer got it wrong and the "primary index" is actually not unique -- ugh!) Thanks again! --- Jim Rosenberg http://www.well.com/user/jer/ WELL: jer Internet: jr@amanue.com
Jim, As Dave said in his email, he tested against current CVS. And since there have been some bug fixes in this area recently it could be that the bug is already fixed and that is why Dave can't reproduce. Have you tried pulling the current sources from CVS and tried testing with those? thanks, --Barry Jim Rosenberg wrote: >>Further more, creating the table exactly as you have it, also works > > > Oh joy. So, my problem is platform-dependent, it seems. > > *SOMETHING* is going wrong on my system with the ability of the JDBC > driver to detect the primary key. Do you have any suggestions for what I > can do to try to trace this problem? > > There is an interesting JDBC client called SQuirrel SQL. When I aim > this at the database in question and inspect the table coffees, under > the Primarary Key tab it does show my key as being there. The view > has a column called table_cat, and the value showing there is > > <Other> > > -- I have no idea if this is normal or not. Under table_schem it > shows public, it shows the right table name and column name, under > key_seq it shows 1, under pk_name it shows pi_coffees. > > I sure would like to get this fixed. I really wanna be using > updatable result sets, but at the moment this problem has me stopped > cold. Any suggestions for things I can try to diagnose the problem > would be welcome. I'm using the Mac OS X PostgreSQL distribution from > www.entropy.ch, which is one of the more commonly used OS X packages > I think. > > -Thanks, Jim > > --- > Jim Rosenberg http://www.well.com/user/jer/ > WELL: jer > Internet: jr@amanue.com > > ---------------------------(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 >
http://sixgirls.org/~adler/pgcopy/ You can download a readymade jar with copy support and compile the example class to experiment with the API. Unfortunately, the major re-tooling of the source tree today has invalidated the patches I've posted. The prebuilt jar will suffice until I updated them accordingly. Barry and Chris - Why no response to my various patches over the last month? -best Mike Adler