Re: moveToInsertRow

Dennis,

I have to admit I have never worked with updateable result sets so, I
could be way off.

but my understanding is that you would select a bunch of rows from the
db and then modifiy them and then save them back

if this is what you are doing then at least notionally it is correct.
Except that postgres doesn't support it at the moment

if you are trying to insert rows using the updateable result set, then I
have no idea how that would work

Dave

-----Original Message-----
From: Dennis R. Gesker [mailto:dennis(at)gesker(dot)com]
Sent: Wednesday, March 06, 2002 7:47 PM
To: Dave(at)micro-automation(dot)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(at)gesker(dot)com]
Sent: Wednesday, March 06, 2002 4:36 PM
To: Dave(at)micro-automation(dot)net
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] moveToInsertRow

I actually only

need 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 SQL

statements.

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(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Dennis R. Gesker
Sent: Wednesday, March 06, 2002 3:32 PM
To: pgsql-jdbc(at)postgresql(dot)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(at)gesker(dot)com Fax: 413.740.4653

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