Re: moveToInsertRow - Mailing list pgsql-jdbc
From | Dave Cramer |
---|---|
Subject | Re: moveToInsertRow |
Date | |
Msg-id | 004f01c1c57d$b7a4cfa0$c201a8c0@inspiron Whole thread Raw |
In response to | Re: moveToInsertRow ("Dennis R. Gesker" <dennis@gesker.com>) |
List | pgsql-jdbc |
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-----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:
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
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'sonly 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(UpdateableResultSet.java:98)
Is there a stable version of this driver available that implements the
moveToInsertRow capabilities on an updatable result set? If not is thismethod 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
pgsql-jdbc by date: