Re: How to add data with ResultSet object? - Mailing list pgsql-jdbc

vasylenko@uksatse.org.ua wrote:
>  >
>  >This is a known driver bug.  The parser to determine the base table of an
>  >updatable result set is *very* easily confused.  Aside from fixing the
>  >driver the only way to fix this would be to use a view to hide the ONLY
>  >from the driver.  CREATE VIEW myonlyview AS SELECT * FROM ONLY forcehol
>  >and then writing appropriate insert rules on it so that you can have the
>  >driver select from and insert into the view.
>  >
>
> Kris, thanks a lot for an advice!
> That what  I have done in my DB.
>
>
> I succeed with update,insert and delete calls from pgsql consol.
> But using jdbc driver I'v got another exception attempting to insert,
> update or delete a row : "No primary key found for table "adminhol_view".
>

Oops, sorry for pointing you down the wrong path.

> I think it is more easy to change a bit   parseQuery() from
>
>
> ->        //I add a couple of code string to aviod this problem
> ->                    if(tableName.toLowerCase().equals("only"))
> ->                        tableName = st.nextToken();
>

It needs a little bit more than that.  You must use ONLY when issuing
delete or update statements because primary keys do not inherit and you
could end up updating or deleting child table data.  I've applied the
attached patch to CVS for 8.0, 8.1, 8.2, and HEAD.

Kris Jurka
? .only.txt.swp
? ChangeLog
? only.patch
Index: org/postgresql/jdbc2/AbstractJdbc2ResultSet.java
===================================================================
RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java,v
retrieving revision 1.98
diff -c -r1.98 AbstractJdbc2ResultSet.java
*** org/postgresql/jdbc2/AbstractJdbc2ResultSet.java    27 Jul 2007 22:55:28 -0000    1.98
--- org/postgresql/jdbc2/AbstractJdbc2ResultSet.java    27 Nov 2007 19:26:22 -0000
***************
*** 43,48 ****
--- 43,49 ----
      private boolean usingOID = false; // are we using the OID for the primary key?
      private Vector primaryKeys;    // list of primary keys
      private boolean singleTable = false;
+     private String onlyTable = "";
      private String tableName = null;
      private PreparedStatement updateStatement = null;
      private PreparedStatement insertStatement = null;
***************
*** 748,754 ****
          {


!             StringBuffer deleteSQL = new StringBuffer("DELETE FROM " ).append(tableName).append(" where " );

              for ( int i = 0; i < numKeys; i++ )
              {
--- 749,755 ----
          {


!             StringBuffer deleteSQL = new StringBuffer("DELETE FROM " ).append(onlyTable).append(tableName).append("
where" ); 

              for ( int i = 0; i < numKeys; i++ )
              {
***************
*** 1183,1189 ****
              }

          }
!         selectSQL.append(" from " ).append(tableName).append(" where ");

          int numKeys = primaryKeys.size();

--- 1184,1190 ----
              }

          }
!         selectSQL.append(" from " ).append(onlyTable).append(tableName).append(" where ");

          int numKeys = primaryKeys.size();

***************
*** 1247,1253 ****
          if (!doingUpdates)
              return; // No work pending.

!         StringBuffer updateSQL = new StringBuffer("UPDATE " + tableName + " SET  ");

          int numColumns = updateValues.size();
          Iterator columns = updateValues.keySet().iterator();
--- 1248,1254 ----
          if (!doingUpdates)
              return; // No work pending.

!         StringBuffer updateSQL = new StringBuffer("UPDATE " + onlyTable + tableName + " SET  ");

          int numColumns = updateValues.size();
          Iterator columns = updateValues.keySet().iterator();
***************
*** 1642,1647 ****
--- 1643,1652 ----
                  if (name.toLowerCase().equals("from"))
                  {
                      tableName = st.nextToken();
+                     if (tableName.toLowerCase().equals("only")) {
+                         tableName = st.nextToken();
+                         onlyTable = "ONLY ";
+                     }
                      tableFound = true;
                  }
              }
Index: org/postgresql/test/jdbc2/UpdateableResultTest.java
===================================================================
RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/test/jdbc2/UpdateableResultTest.java,v
retrieving revision 1.27
diff -c -r1.27 UpdateableResultTest.java
*** org/postgresql/test/jdbc2/UpdateableResultTest.java    14 Nov 2007 03:17:50 -0000    1.27
--- org/postgresql/test/jdbc2/UpdateableResultTest.java    27 Nov 2007 19:26:22 -0000
***************
*** 428,434 ****
          rs.moveToInsertRow();
          rs.close();
          st.close();
!     }

      public void testUpdateReadOnlyResultSet() throws Exception
      {
--- 428,445 ----
          rs.moveToInsertRow();
          rs.close();
          st.close();
!     }
!
!     public void testUpdateSelectOnly() throws Exception
!     {
!         Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
!                                            ResultSet.CONCUR_UPDATABLE);
!
!         ResultSet rs = st.executeQuery( "select * from only second");
!         assertTrue(rs.next());
!         rs.updateInt(1, 2);
!         rs.updateRow();
!     }

      public void testUpdateReadOnlyResultSet() throws Exception
      {