updateRow with Array

Hello,
I have a table with a column "categories" of type integer[]

I wish to update it like this (simplified):
ResultSet rs = st.executeQuery(query);
while(rs.next()) {

   rs.updateArray("categories", <array>);
   rs.updateRow();
}

I know this has been asked before, but none of the answers worked for
me.


I have tried many different things, but none seem to work.
I used "?protocolVersion=2" and updateStatement.setString(1, "{1,2,3}");
and it worked with prepared statements but I can not get it to work
with updateRow.

I also tried creating an implementation of java.sql.Array as
suggested by someone (see below), but i get:
org.postgresql.util.PSQLException: Method
org.postgresql.jdbc3.Jdbc3ResultSet.updateArray(String,Array) is not
yet implemented.

Any help would be great! I really need to solve this quickly.

Thanks!


The sql.Array impl. several things are hard coded for testing:

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.Map;

/*
* Array is used collect one column of query result data.
*
* <p>Read a field of type Array into either a natively-typed
* Java array object or a ResultSet.  Accessor methods provide
* the ability to capture array slices.
*
* <p>Other than the constructor all methods are direct implementations
* of those specified for java.sql.Array.  Please refer to the javadoc
* for java.sql.Array for detailed descriptions of the functionality
* and parameters of the methods of this class.
*
* <b>This class stolen from postgresql 7.2.1's source tree!!!</b>
*
* @see ResultSet#getArray
*
*/
public class PostgresArray implements Array {

     private String rawString = null;
     private String typeName = null;
     private int baseType = -1;
     private static final String jdbc2Types[] = {
         "int2",
         "int4", "oid",
         "int8",
         "cash", "money",
         "numeric",
         "float4",
         "float8",
         "bpchar", "char", "char2", "char4", "char8", "char16",
         "varchar", "text", "name", "filename",
         "bytea",
         "bool",
         "date",
         "time",
         "abstime", "timestamp", "timestamptz",
         "_bool", "_char", "_int2", "_int4", "_text", "_integer",
         "_oid", "_varchar", "_int8", "_float4", "_float8",
         "_abstime", "_date", "_time", "_timestamp", "_numeric",
         "_bytea"
     };

     /*
      * This table holds the JDBC type for each entry above.
      *
      * Note: This must be in the same order as above
      *
      * Tip: keep these grouped together by the Types. value
      */
     private static final int jdbc2Typei[] = {
         Types.SMALLINT,
         Types.INTEGER, Types.INTEGER,
         Types.BIGINT,
         Types.DOUBLE, Types.DOUBLE,
         Types.NUMERIC,
         Types.REAL,
         Types.DOUBLE,
         Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR,
Types.CHAR,
         Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
         Types.BINARY,
         Types.BIT,
         Types.DATE,
         Types.TIME,
         Types.TIMESTAMP, Types.TIMESTAMP, Types.TIMESTAMP,
         Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY,
Types.ARRAY, Types.ARRAY,
         Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY,
Types.ARRAY,
         Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY,
Types.ARRAY,
         Types.ARRAY
     };


     /*
     * Create a new Array
     *
     * @param conn a database connection
     * @param idx 1-based index of the query field to load into this Array
     * @param field the Field descriptor for the field to load into this
Array
     * @param rs the ResultSet from which to get the data for this Array
     */
     PostgresArray(String rawString, int baseType, String typeName)
throws SQLException {
       System.out.println("[PostgresArray] - constructor");
         this.rawString = rawString;
         this.baseType = baseType;
         this.typeName = typeName;
         if (this.typeName.startsWith("_")) {
             this.typeName = this.typeName.substring(1);
         }
     }
     /*
     public static Array create(Object[] array) throws SQLException {
         throw new SQLException("Not Implemented");
     }
     */

     public static Array create(int[] array) throws SQLException {
       System.out.println("[PostgresArray] - create(int[] array)");
         if (array == null)
             return new PostgresArray(null, -1, null);

         StringBuffer sb = new StringBuffer("{");
         for (int x = 0; x < array.length; x++) {
             if (x > 0)
                 sb.append(",");
             sb.append("\"").append(array[x]).append("\"");
         }
         sb.append("}");

         return new PostgresArray(sb.toString(), Types.INTEGER, "int");
     }
      public Object getArray() throws SQLException {
       System.out.println("[PostgresArray] - getArray()");
         return getArray(1, 0, null);
     }

     public Object getArray(long index, int count) throws SQLException {
       System.out.println("[PostgresArray] - getArray(long index, int
count)");
         return getArray(index, count, null);
     }

     public Object getArray(Map map) throws SQLException {
       System.out.println("[PostgresArray] - getArray(Map map)");
         return getArray(1, 0, map);
     }

     public Object getArray(long index, int count, Map map) throws
SQLException {
       System.out.println("[PostgresArray] - getArray(long index, int
count, Map map)");
         if (map != null) // For now maps aren't supported.
             throw new SQLException("Maps are not supported in
Array.getArray");

         if (index < 1)
             throw new SQLException("index < 1");

         Object retVal = null;

         return retVal;
     }

     public int getBaseType() throws SQLException {
       System.out.println("[PostgresArray] - getBaseType() ");
         int sqlType = Types.OTHER; // default value
         for (int i = 0; i < jdbc2Types.length; i++) {
             if (this.typeName.equals(jdbc2Types[i])) {
                 sqlType = jdbc2Typei[i];
                 break;
             }
         }
         return sqlType;
     }


     public String getBaseTypeName() throws SQLException {
       System.out.println("[PostgresArray] - getBaseTypeName() ");
         return this.typeName;
     }

     public java.sql.ResultSet getResultSet() throws SQLException {
       System.out.println("[PostgresArray] - getResultSet() ");
         return getResultSet(1, 0, null);
     }

     public java.sql.ResultSet getResultSet(long index, int count)
throws SQLException {
       System.out.println("[PostgresArray] - getResultSet(long index,
int count) ");
         return getResultSet(index, count, null);
     }

     public java.sql.ResultSet getResultSet(Map map) throws
SQLException {
       System.out.println("[PostgresArray] - getResultSet(Map map) ");
         return getResultSet(1, 0, map);
     }

     public java.sql.ResultSet getResultSet(long index, int count,
Map map) throws SQLException {
       System.out.println("[PostgresArray] - getResultSet(long index,
int count, Map map)");
         throw new SQLException("Array.getResultSet() not supported");
     }

     public String toString() {
       System.out.println("[PostgresArray] - toString() ");
         return "{123}";
         //return rawString;
     }
}