Subject: Re: Insert Row to ResultSet
From: Dave Cramer <pg@fastcrypt.com>
Reply-To: pg@fastcrypt.com
To: "Jason L. van Brackel" <jasonvanbrackel@speakeasy.net>
Cc: "pgsql-jdbc@postgresql.org" <pgsql-jdbc@postgresql.org>
In-Reply-To: <621ddc9b.0312160724.6c6955db@posting.google.com>
References: <621ddc9b.0312160724.6c6955db@posting.google.com>
Content-Type: text/plain
Organization: Cramer Consulting
Message-Id: <1072121539.1634.84.camel@localhost.localdomain>
Mime-Version: 1.0
Date: 22 Dec 2003 14:32:19 -0500
Content-Transfer-Encoding: 7bit

Jason,

A couple of things, 
1) can you send us the table definitions,
2) there was a similar post about 2 weeks ago, search the archives
3) I have migrated a db using just access and the postgres odbc driver,
you can do a cross db query in access ie sth like insert into postgres
table, select from access table.

Dave

On Tue, 2003-12-16 at 10:24, Jason L. van Brackel wrote:
> 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;
> }
> 
> 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 
>