Thread: MoveToInsertRow on a table without a primary key
Hello all, I have a table in a database that does not have a primary key. In the process of migrating data from Oracle to Postgres,I am using ResultSets to populate the Postgres tables. Basically, all is working well except for tables that do not have a primary key. When I reach the "movetoinsertrow" instructionin my code, the jdbc driver raises an exception about the missing primary key. The workaround would be to add a temporary primary key to such table, but I want to avoid that. I did the same test withHSQLDB and it works. To me, it seems like a bug. I googled around for the solution, but found nothing. Any tips?
Pretty sure a primary key is a pre-requisite for an updatable result set. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Wed, Mar 14, 2012 at 9:03 AM, <patrick.balleux@gmail.com> wrote: > Hello all, > > I have a table in a database that does not have a primary key. In the process of migrating data from Oracle to Postgres,I am using ResultSets to populate the Postgres tables. > > Basically, all is working well except for tables that do not have a primary key. When I reach the "movetoinsertrow" instructionin my code, the jdbc driver raises an exception about the missing primary key. > > The workaround would be to add a temporary primary key to such table, but I want to avoid that. I did the same test withHSQLDB and it works. To me, it seems like a bug. > > I googled around for the solution, but found nothing. > > Any tips? > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc
Not sure if this helps but:- 1. Examination of the JBC4 spec makes no mention of a PK being a pre-requisite for an updatable ResultSet. It implies "cursors" or similar are employed. 2. MS SQLServer 2008 and IBM DB2 for Linux/Unix/Windows also have a similar restriction. Anyway, a table with no PK is bad design of your database tables anyway ;-) Andrew On 14/03/12 14:05, Dave Cramer wrote: > Pretty sure a primary key is a pre-requisite for an updatable result set. > > Dave Cramer > > dave.cramer(at)credativ(dot)ca > http://www.credativ.ca > > > > On Wed, Mar 14, 2012 at 9:03 AM,<patrick.balleux@gmail.com> wrote: >> Hello all, >> >> I have a table in a database that does not have a primary key. In the process of migrating data from Oracle to Postgres,I am using ResultSets to populate the Postgres tables. >> >> Basically, all is working well except for tables that do not have a primary key. When I reach the "movetoinsertrow" instructionin my code, the jdbc driver raises an exception about the missing primary key. >> >> The workaround would be to add a temporary primary key to such table, but I want to avoid that. I did the same test withHSQLDB and it works. To me, it seems like a bug. >> >> I googled around for the solution, but found nothing. >> >> Any tips? >> >> -- >> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-jdbc
Unfortunately the API docs are not particularly great. This http://docs.oracle.com/javase/1.3/docs/guide/jdbc/getstart/resultset.html gives a strong suggestion to use a primary key Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Wed, Mar 14, 2012 at 10:46 AM, Andrew Hastie <andrew@ahastie.net> wrote: > Not sure if this helps but:- > 1. Examination of the JBC4 spec makes no mention of a PK being a > pre-requisite for an updatable ResultSet. It implies "cursors" or similar > are employed. > 2. MS SQLServer 2008 and IBM DB2 for Linux/Unix/Windows also have a similar > restriction. > > Anyway, a table with no PK is bad design of your database tables anyway ;-) > > Andrew > > > On 14/03/12 14:05, Dave Cramer wrote: >> >> Pretty sure a primary key is a pre-requisite for an updatable result set. >> >> Dave Cramer >> >> dave.cramer(at)credativ(dot)ca >> http://www.credativ.ca >> >> >> >> On Wed, Mar 14, 2012 at 9:03 AM,<patrick.balleux@gmail.com> wrote: >>> >>> Hello all, >>> >>> I have a table in a database that does not have a primary key. In the >>> process of migrating data from Oracle to Postgres, I am using ResultSets to >>> populate the Postgres tables. >>> >>> Basically, all is working well except for tables that do not have a >>> primary key. When I reach the "movetoinsertrow" instruction in my code, the >>> jdbc driver raises an exception about the missing primary key. >>> >>> The workaround would be to add a temporary primary key to such table, but >>> I want to avoid that. I did the same test with HSQLDB and it works. To me, >>> it seems like a bug. >>> >>> I googled around for the solution, but found nothing. >>> >>> Any tips? >>> >>> -- >>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-jdbc > > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc