Thread: Postgres 8.0 + JDBC

Johann Robette wrote:

>                 java.sql.SQLException: ERROR: cannot cast type text to
> text[]

setArray has never worked particularly well; you're seeing another
manifestation here.

The underlying problem here is that:

- setArray() is claiming that the type it is setting is a VARCHAR
- the parameter is being passed as a typed parameter via the V3
protocol, with type = 'text'
- apparently you can't cast directly from text -> text[] (although you
can interpret an untyped literal as text[], which is why it worked before).

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'll try to do this soon, but I'm a bit busy so it may be a few days.

A longer term fix is to properly implement array support in setArray()..
I've submitted patches in the past to do this but they've never made it
into the official driver.

-O

On Wed, 6 Oct 2004, 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.
>

Wouldn't it be simpler to change setArray to call setString with 0 as the
type oid allowing the backend to figure out what to do with it?  Perhaps
it would have trouble determining the type if the underlying function was
overloaded, but other than that I don't see a problem.

Kris Jurka

Kris Jurka wrote:
>
> On Wed, 6 Oct 2004, 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.
>>
>
>
> Wouldn't it be simpler to change setArray to call setString with 0 as the
> type oid allowing the backend to figure out what to do with it?  Perhaps
> it would have trouble determining the type if the underlying function was
> overloaded, but other than that I don't see a problem.

Using the unknown oid seems like it'd introduce more problems:
overloading, and the possibility you actually pass your array as some
other type unexpectedly.

I am wondering if the right way to find the array OID is to prepend "_"
and search on pg_type.typname, or to look for pg_type.typinput = (oid of
array_in) and pg_type.typelem = (oid of underlying type). Is there a
'standard' way of finding an array type OID?

-O

Oliver Jowett <oliver@opencloud.com> writes:
> I am wondering if the right way to find the array OID is to prepend "_"
> and search on pg_type.typname, or to look for pg_type.typinput = (oid of
> array_in) and pg_type.typelem = (oid of underlying type). Is there a
> 'standard' way of finding an array type OID?

The backend does it by name, ie prepend '_' and lookup by typname (and
typnamespace).  This is a mite unclean but it hasn't seemed worth
fixing.  If you like you can use the typelem as an additional check that
you found the right thing.

            regards, tom lane

OK Thanks a lot for your answer.
But as I'm a newbie in this field, could you give me an example of how
to do that.

I've already checked the array typename and it is "_text".

You said :
"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."

How to do that?

Thanks

JR

-----Message d'origine-----
De : Oliver Jowett [mailto:oliver@opencloud.com]
Envoyé : mardi 5 octobre 2004 22:11
À : Johann Robette
Cc : pgsql-jdbc@postgresql.org
Objet : Re: [JDBC] Postgres 8.0 + JDBC

Johann Robette wrote:

>                 java.sql.SQLException: ERROR: cannot cast type text to

> text[]

setArray has never worked particularly well; you're seeing another
manifestation here.

The underlying problem here is that:

- setArray() is claiming that the type it is setting is a VARCHAR
- the parameter is being passed as a typed parameter via the V3
protocol, with type = 'text'
- apparently you can't cast directly from text -> text[] (although you
can interpret an untyped literal as text[], which is why it worked
before).

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'll try to do this soon, but I'm a bit busy so it may be a few days.

A longer term fix is to properly implement array support in setArray()..

I've submitted patches in the past to do this but they've never made it
into the official driver.

-O

On Wed, 6 Oct 2004, Johann Robette wrote:

> But as I'm a newbie in this field, could you give me an example of how
> to do that.

It's not something you can do in your code.  It will require changes to
the driver itself.  Oliver or I will do this at some point, but I couldn't
say when.  If you're desperate to get a fix immediately I've attached a
patch implementing the inferior solution I suggested that made a simple
test work for me.

Kris Jurka

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)

-O

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]);
+     }
  }

Oliver Jowett <oliver@opencloud.com> writes:
> !         // 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);

Er, what about schema-qualified type names?  Also, are you sure that
getBaseTypeName will return the correct internal type name, eg "int8"
not "bigint"?

            regards, tom lane

Tom Lane wrote:
> Oliver Jowett <oliver@opencloud.com> writes:
>
>>!         // 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);
>
>
> Er, what about schema-qualified type names?

We don't really support those anywhere else in the driver yet.
getPGType() certainly doesn't know how to handle them.

> Also, are you sure that
> getBaseTypeName will return the correct internal type name, eg "int8"
> not "bigint"?

It'll be correct for Array implementations generated by the driver. I
didn't see an easy way to get at the list of type aliases -- they appear
to only exist in the backend's query parser. A type alias view or
something similar would be nice to have..

If you're using your own Array implementation, you're playing with fire
anyway, as the current setArray() implementation is limited and fragile.
This patch is really just to get things back to the same level of
support as before the V3 protocol rewrite, not to fix all the other
problems.

-O

On Fri, 8 Oct 2004, Oliver Jowett wrote:

> 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 applied this.  It's not a great solution, but it's low impact and
does fix the problem.  An idea I had when looking at it is that if the
lookup by getBaseTypeName fails we could convert getBaseType to a pg type
name by the first match in the connection's jdbcXTypes and retry looking
for an array type with that.  Although considering our existing dependency
on Array.toString() returning a properly formatted array this seems like a
waste of effort.

Kris Jurka

On Wed, 6 Oct 2004, Kris Jurka wrote:

> On Wed, 6 Oct 2004, Johann Robette wrote:
>
> > But as I'm a newbie in this field, could you give me an example of how
> > to do that.
>
> It's not something you can do in your code.  It will require changes to
> the driver itself.  Oliver or I will do this at some point, but I couldn't
> say when.  If you're desperate to get a fix immediately I've attached a
> patch implementing the inferior solution I suggested that made a simple
> test work for me.
>

Just wanted to follow up with you here in case you weren't carefully
following the mailing list.  Oliver has provided a patch to make this
happen and it is now in the cvs repository.

It does require some changes to the PostgresArray class you sent.  Notably
the datatype name needs to match up exactly with the pg data type (aliases
aren't allowed).  So for example the create(long[] array) method currently
creates does this at the end:

return new PostgresArray(sb.toString(), Types.BIT, "bigint");

this now needs to use "int8" as the type name.  The Types.BIT is also
bogus, but that currently isn't a problem.

Kris Jurka