Re: moveToInsertRow

-----Original Message-----
From: Dennis R. Gesker [mailto:dennis@gesker.com]
Sent: Wednesday, March 06, 2002 7:47 PM
To: Dave@micro-automation.net; psql
Subject: Re: [JDBC] moveToInsertRow

There is actually two resultsets involved. I've written an object that maps to a row in the first resultset as well as the target in the pg database. The sequence of events is similar to the following:

retrive data into result set from the source MS-SQL database: ms_resultset
also create updatable result set from the PG database: pg_resultset

Begin loop
copy contents of row from ms_resultset into object acting as a holding place: temp.object
copy temp.object into updatable pg_result set row.

ms_resultset -> temp.object -> pg_resultset -> move to nextrow in ms_resultset

Continue to loop until all the data is extracted from the first database and inserted into second database


Dave Cramer wrote:

Dennis,

My understanding of an updateable result set is that you would first
have to get the result set from the db and then change something in it,
then update it.

In your case this won't work, I don't think there is any way you could
switch databases on it half way through the update.

The driver does handle batches, but I would be leary doing it this way,
my preference would probably be just to read a row, and  then write a
row.

Dave

-----Original Message-----
From: Dennis R. Gesker [mailto:dennis@gesker.com] 
Sent: Wednesday, March 06, 2002 4:36 PM
To: Dave@micro-automation.net
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] moveToInsertRow


I actually onlyneed to be able to update a single table. I have some 
tables which reside in a MS-SQL database. I basically am writing some 
routines that will pull the appropriate information from the MS-SQL 
database and place this data into my Pg database. I was planning to pull

the information I need from the MS-SQL database (located in several 
tables via a join) as a single resultSet containing only what I need 
then loop through this result set writing into the appropriate table of 
the Pg database via an updatable result set from the Pg connection.

Maybe there is a better (more efficient?) way to go about this. Since 
using an updatable rs on the Pg side dosen't seem to be an option  I 
wonder if looping throught the MS-SQL rs creating a big batch of insert 
statments then executing the batch at once into Pg would be the way to 
go. I'll have to look into the documentation of the Pg JDBC driver to 
see if it can handle a batches of SQLstatements.

I would certainly welcome any ideas or advice as I'm still working my 
way up the learning curve.

Dennis


Dave Cramer wrote:

There are some people looking at implementing the UpdateableResultSet 
methods, but I have no idea when.

I do have a question though. How do you expect them to work? AFAIK It's

only possible to update a result set that is from a single table.

The driver would still end up issuing an update sql statement.

Dave

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org 
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Dennis R. Gesker
Sent: Wednesday, March 06, 2002 3:32 PM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] moveToInsertRow


Hello:

I'm currently getting the following error using the moveToInsertRow
method with an updatable result set using what (I think but am not 
entirely sure) is the current version of the JDBC driver for use with 
PostgreSQL. I found the jar containing this driver at
jdbc.postgresql.
org.

This method is not yet implemented.
     at org.postgresql.Driver.notImplemented(Driver.java:440)
     at
org.postgresql.jdbc2.UpdateableResultSet.moveToInsertRow(UpdateableResu

l

tSet.java:98) 


Is there a stable version of this driver available that implements the
moveToInsertRow capabilities on an updatable result set? If not is this

method planned for sometime in the future?

-- 
Dennis Roman Gesker		ICQ: 194047
dennis@gesker.com		Fax: 413.740.4653

Seek freedom and become captive to your desires. Seek discipline and find your liberty. -- F.H. 1985