Thread: Insert Row to ResultSet problem....java.sql.SQLException: No Primary Keys

I sent this through google groups, but then found I could join the mailing list directly sorry about the repost, but I
addedthe table information to this one..... 

I'm migrating data from a poorly modeled MS Access 2000 DB to newly
developed PosgreSQL 7.4 DB. I'm connecting to the Access database
using a jdbc-odbc bridge driver. I'm connectin the postgreSQL DB
using the pg74jdbc3.jar driver.

public static void main(String[] args) {
String[] schools; // Array of SchoolCodes

MigrationUtil migr = new MigrationUtil();
// Connect to the Access Database
migr.connectToAccess("Access", "C:\\Working\\Access\\Access.mdb");
// Connect to the PostgreSQL Database
migr.connectToPostgreSQL("PostgreSQL_Test", "cimTux.cim.internal",
"5432");
// Get Schools List
System.out.println("Getting List of Schools from access.mdb");
schools = migr.resultSetToStringArray(migr.queryStatement(migr.ODBCConnection,
"SELECT ftno FROM tblmst WHERE ftcode ='SC'"));
// Start the Main Loop
for(int i = 0; i < schools.length; i++)
{
String currentSchool = schools[i]; // current School Code
ResultSet rs = null; // reusable ResultSet variable
String sql = null; // reusable SQL Statement String
int schoolContactID = 0; // current School ContactID
int schoolAddressID = 0; // current School AddressID
int eduOrgID = 0; // current EduOrganization ID
System.out.println("Current School: " + currentSchool);
// Get School information
sql = "SELECT FtDes, LenderAddress1, LenderCity, LenderState,
LenderZip, " + "LenderAddress2, fsch_code, EINNo FROM tblmst WHERE
ftcode = 'SC' AND ftno = '" + currentSchool + "';";
rs = migr.queryStatement(migr.ODBCConnection, sql);
// Update school address information
System.out.println("Migrating School Address Info");
try{
ResultSet addressInfo = null; // ResultSet for Address Info
sql = "SELECT * FROM address;";
addressInfo = migr.queryStatement(migr.postgreSQLConnection, sql);
addressInfo.moveToInsertRow();
..... this is where I get this exception
java.sql.SQLException: No Primary Keys
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.isUpdateable(AbstractJdbc2ResultSet.java:1363)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.moveToInsertRow(AbstractJdbc2ResultSet.java:697)
at com.cimconsultants.EFRMigration.MigrationUtil.main(MigrationUtil.java:62)

Here is the queryStatement(Connection, String) Method

private ResultSet queryStatement(Connection con, String SQLStatement)
{
ResultSet rs = null;
try {
System.out.println("Query: " + SQLStatement);
Statement stmt =
con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(SQLStatement);
}catch(SQLException ex) {
System.out.println("Query SQL Exception");
ex.printStackTrace();
System.exit(0);
}
return rs;
}

Here is the table from psql:
PostgreSQL_Test=# \d address
                                          Table "public.address"
     Column      |         Type          |                           Modifiers
-----------------+-----------------------+----------------------------------------------------------------
 addressid       | bigint                | not null default nextval('public.address_addressid_seq'::text)
 address1        | character varying(50) | not null
 address2        | character varying(50) |
 address3        | character varying(50) |
 city            | character varying(50) | not null
 stateprovidence | character varying(10) | not null
 postalcode      | character varying(12) | not null
 country         | character varying(50) | not null
Indexes:
    "address_pkey" primary key, btree (addressid)


I'm experienced with Java, but very new to JDBC and PostgreSQL. I'm
using the JDK 1.4, PostgreSQL 7.4, and the binary pg74jdbc3.jar
driver.

Thanks in advance,

Jason L. van Brackel

On Tue, 16 Dec 2003, Jason L. van Brackel wrote:

> ResultSet addressInfo = null; // ResultSet for Address Info
> sql = "SELECT * FROM address;";
> addressInfo = migr.queryStatement(migr.postgreSQLConnection, sql);
> addressInfo.moveToInsertRow();
> ..... this is where I get this exception
> java.sql.SQLException: No Primary Keys
> at org.postgresql.jdbc2.AbstractJdbc2ResultSet.isUpdateable(AbstractJdbc2ResultSet.java:1363)
> at org.postgresql.jdbc2.AbstractJdbc2ResultSet.moveToInsertRow(AbstractJdbc2ResultSet.java:697)
> at com.cimconsultants.EFRMigration.MigrationUtil.main(MigrationUtil.java:62)

I think the problem is the semicolon after address.  It is confusing
the driver into trying to update a table named "address;".  Either
remove the semicolon or put a space between it and the table name.

Kris Jurka