Fix setArray() when using the v3 protocol (was Re: Postgres 8.0 + JDBC) - Mailing list pgsql-jdbc

Oliver Jowett wrote:
> Oliver Jowett wrote:
>
>> It seems possible to fix the driver to handle this case by making
>> setArray() derive a proper array type name i.e. ("_" +
>> Array.getBaseType()), and using that rather than 'text' as the
>> parameter type.
>
>
> I've done this (plus tests) and it seems to work OK. I can't generate a
> patch right now as gborg seems to be having some problems (I get 70%
> packet loss on the last hop, which pretty much hoses CVS access)

Here is the patch.

I made it fail if the array type can't be found, rather than risking the
backend interpreting the value in an unpredictable way.

-O
Index: org/postgresql/errors.properties
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/errors.properties,v
retrieving revision 1.37
diff -u -c -r1.37 errors.properties
*** org/postgresql/errors.properties    6 Oct 2004 18:40:19 -0000    1.37
--- org/postgresql/errors.properties    8 Oct 2004 02:10:58 -0000
***************
*** 71,76 ****
--- 71,77 ----
  postgresql.prep.param:No value specified for parameter {0}
  postgresql.prep.range:Parameter index out of range.
  postgresql.prep.type:Unknown Types value.
+ postgresql.prep.typenotfound:Unknown type {0}.
  postgresql.prep.zeroinstring:Zero bytes may not occur in string parameters.
  postgresql.res.badbigdec:Bad BigDecimal {0}
  postgresql.res.badbyte:Bad Byte {0}
Index: org/postgresql/core/Oid.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/core/Oid.java,v
retrieving revision 1.1
diff -u -c -r1.1 Oid.java
*** org/postgresql/core/Oid.java    29 Jun 2004 06:43:25 -0000    1.1
--- org/postgresql/core/Oid.java    8 Oct 2004 02:10:58 -0000
***************
*** 5,10 ****
--- 5,11 ----
   * use.
   */
  public class Oid {
+     public static final int INVALID = 0;
      public static final int INT2 = 21;
      public static final int INT4 = 23;
      public static final int INT8 = 20;
Index: org/postgresql/jdbc2/AbstractJdbc2Connection.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Connection.java,v
retrieving revision 1.16
diff -u -c -r1.16 AbstractJdbc2Connection.java
*** org/postgresql/jdbc2/AbstractJdbc2Connection.java    20 Sep 2004 08:36:50 -0000    1.16
--- org/postgresql/jdbc2/AbstractJdbc2Connection.java    8 Oct 2004 02:10:58 -0000
***************
*** 871,877 ****
      public int getPGType(String typeName) throws SQLException
      {
          if (typeName == null)
!             return 0;

          synchronized (this) {
              Integer oidValue = (Integer) typeOidCache.get(typeName);
--- 871,877 ----
      public int getPGType(String typeName) throws SQLException
      {
          if (typeName == null)
!             return Oid.INVALID;

          synchronized (this) {
              Integer oidValue = (Integer) typeOidCache.get(typeName);
***************
*** 879,885 ****
                  return oidValue.intValue();

              // it's not in the cache, so perform a query, and add the result to the cache
!             int oid = 0;

              PreparedStatement query;
              if (haveMinimumServerVersion("7.3"))
--- 879,885 ----
                  return oidValue.intValue();

              // it's not in the cache, so perform a query, and add the result to the cache
!             int oid = Oid.INVALID;

              PreparedStatement query;
              if (haveMinimumServerVersion("7.3"))
***************
*** 912,918 ****
       */
      public String getPGType(int oid) throws SQLException
      {
!         if (oid == 0)
              return null;

          synchronized (this) {
--- 912,918 ----
       */
      public String getPGType(int oid) throws SQLException
      {
!         if (oid == Oid.INVALID)
              return null;

          synchronized (this) {
Index: org/postgresql/jdbc2/AbstractJdbc2Statement.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java,v
retrieving revision 1.31
diff -u -c -r1.31 AbstractJdbc2Statement.java
*** org/postgresql/jdbc2/AbstractJdbc2Statement.java    21 Sep 2004 01:14:06 -0000    1.31
--- org/postgresql/jdbc2/AbstractJdbc2Statement.java    8 Oct 2004 02:10:59 -0000
***************
*** 2443,2449 ****
      public void setArray(int i, java.sql.Array x) throws SQLException
      {
          checkClosed();
!         setString(i, x.toString());
      }

      public void setBlob(int i, Blob x) throws SQLException
--- 2443,2462 ----
      public void setArray(int i, java.sql.Array x) throws SQLException
      {
          checkClosed();
!
!         // This only works for Array implementations that return a valid array
!         // literal from Array.toString(), such as the implementation we return
!         // from ResultSet.getArray(). Eventually we need a proper implementation
!         // here that works for any Array implementation.
!
!         // Use a typename that is "_" plus the base type; this matches how the
!         // backend looks for array types.
!         String typename = "_" + x.getBaseTypeName();
!         int oid = connection.getPGType(typename);
!         if (oid == Oid.INVALID)
!             throw new PSQLException("postgresql.prep.typenotfound", PSQLState.INVALID_PARAMETER_TYPE, typename);
!
!         setString(i, x.toString(), oid);
      }

      public void setBlob(int i, Blob x) throws SQLException
Index: org/postgresql/test/jdbc2/ArrayTest.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/test/jdbc2/ArrayTest.java,v
retrieving revision 1.2
diff -u -c -r1.2 ArrayTest.java
*** org/postgresql/test/jdbc2/ArrayTest.java    20 Jul 2004 00:24:37 -0000    1.2
--- org/postgresql/test/jdbc2/ArrayTest.java    8 Oct 2004 02:10:59 -0000
***************
*** 3,14 ****
  import org.postgresql.test.TestUtil;
  import java.sql.*;
  import java.math.BigDecimal;

  import junit.framework.TestCase;

  public class ArrayTest extends TestCase
  {
-
      private Connection conn;

      public ArrayTest(String name)
--- 3,14 ----
  import org.postgresql.test.TestUtil;
  import java.sql.*;
  import java.math.BigDecimal;
+ import java.util.Map;

  import junit.framework.TestCase;

  public class ArrayTest extends TestCase
  {
      private Connection conn;

      public ArrayTest(String name)
***************
*** 19,30 ****
      protected void setUp() throws SQLException
      {
          conn = TestUtil.openDB();
-
          TestUtil.createTable(conn, "arrtest", "intarr int[], decarr decimal(2,1)[], strarr text[]");
-         Statement stmt = conn.createStatement();
-         // you need a lot of backslashes to get a double quote in.
-         stmt.executeUpdate("INSERT INTO arrtest VALUES ('{1,2,3}','{3.1,1.4}', '{abc,f''a,\"fa\\\\\"b\",def}')");
-         stmt.close();
      }

      protected void tearDown() throws SQLException
--- 19,25 ----
***************
*** 35,40 ****
--- 30,39 ----

      public void testRetrieveArrays() throws SQLException {
          Statement stmt = conn.createStatement();
+
+         // you need a lot of backslashes to get a double quote in.
+         stmt.executeUpdate("INSERT INTO arrtest VALUES ('{1,2,3}','{3.1,1.4}', '{abc,f''a,\"fa\\\\\"b\",def}')");
+
          ResultSet rs = stmt.executeQuery("SELECT intarr, decarr, strarr FROM arrtest");
          assertTrue(rs.next());

***************
*** 66,71 ****
--- 65,74 ----

      public void testRetrieveResultSets() throws SQLException {
          Statement stmt = conn.createStatement();
+
+         // you need a lot of backslashes to get a double quote in.
+         stmt.executeUpdate("INSERT INTO arrtest VALUES ('{1,2,3}','{3.1,1.4}', '{abc,f''a,\"fa\\\\\"b\",def}')");
+
          ResultSet rs = stmt.executeQuery("SELECT intarr, decarr, strarr FROM arrtest");
          assertTrue(rs.next());

***************
*** 113,117 ****
--- 116,166 ----
          stmt.close();
      }

+     private static class SimpleArray implements Array {
+         SimpleArray(int baseType, String baseTypeName, String value) {
+             this.baseType = baseType;
+             this.baseTypeName = baseTypeName;
+             this.value = value;
+         }
+
+         public Object getArray() { throw new UnsupportedOperationException(); }
+         public Object getArray(long index, int count) { throw new UnsupportedOperationException(); }
+         public Object getArray(long index, int count, Map map) { throw new UnsupportedOperationException(); }
+         public Object getArray(Map map) { throw new UnsupportedOperationException(); }
+         public ResultSet getResultSet() { throw new UnsupportedOperationException(); }
+         public ResultSet getResultSet(long index, int count) { throw new UnsupportedOperationException(); }
+         public ResultSet getResultSet(long index, int count, Map map) { throw new UnsupportedOperationException(); }
+         public ResultSet getResultSet(Map map) { throw new UnsupportedOperationException(); }
+
+         public int getBaseType() { return baseType; }
+         public String getBaseTypeName() { return baseTypeName; }
+
+         public String toString() { return value; }
+
+         private final int baseType;
+         private final String baseTypeName;
+         private final String value;
+     }
+
+
+     public void testSetArray() throws SQLException {
+         PreparedStatement stmt = conn.prepareStatement("INSERT INTO arrtest(intarr) VALUES (?)");
+         stmt.setArray(1, new SimpleArray(Types.INTEGER, "int4", "{1,2,3}"));
+         stmt.executeUpdate();
+
+         Statement select = conn.createStatement();
+         ResultSet rs = select.executeQuery("SELECT intarr FROM arrtest");
+         assertTrue(rs.next());
+
+         Array result = rs.getArray(1);
+         assertEquals(Types.INTEGER, result.getBaseType());
+         assertEquals("int4", result.getBaseTypeName());
+
+         int intarr[] = (int[])result.getArray();
+         assertEquals(3,intarr.length);
+         assertEquals(1,intarr[0]);
+         assertEquals(2,intarr[1]);
+         assertEquals(3,intarr[2]);
+     }
  }