Re: JDBC Array Support, Take III - Mailing list pgsql-patches

Patch applied.  I have attached a non-gzip version for review.

> Greetings again.
>
> Thanks for your feedback (and patience).  Enclosed is my third
> attempt at a patch to 7.1.2 to support Array.
>
> [I think I've solved the mangled patch problem.  Hotmail seems to
> try to format the text file, so gzipping it should solve this
> problem.]
>
> In this patch I've incorporated Barry's feedback.  Specifically:
>
> 1)  OIDs are no longer hard-coded into Array.java.  In order to
>     support this change I added a getOID(String) method to Field.java
>     which receives a PostgreSQL field type and returns a value from
>     java.sql.Types.  I couldn't get away from using OIDs altogether
>     because the JDBC spec for Array specifies that some methods return
>     a ResultSet.  This requires I construct Field objects,
>     which means I need OIDs.  At least this approach doesn't hard
>     code these values.  A Hashtable cache has been added to Field
>     so that an SQL lookup isn't necessary (following the model already
>     in Field.java).
>
> 2)  Rewired the base formatting code in ResultSet.java to use 'to'
>     methods, which are then exposed as static methods in ResultSet.
>     These methods are used in Array to format the data without
>     duplications in the code.
>
> 3)  Artifact call to first() in ResultSet.getArray() removed.
>
> Patching as before: (gunzip first this time)
>
> >cd <PGSQL_SRC_PATH>/src/interfaces/jdbc/org/postgresql
> >patch -p1 < $PATH_TO_PATCH/patch
> >cd <PGSQL_SRC_PATH>
> >make
>
> Let me know if I'm getting closer to something useful.
>
> Take care.
> Greg
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
Index: src/interfaces/jdbc/org/postgresql/Field.java
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/Field.java,v
retrieving revision 1.4
diff -c -r1.4 Field.java
*** src/interfaces/jdbc/org/postgresql/Field.java    2001/02/13 16:39:00    1.4
--- src/interfaces/jdbc/org/postgresql/Field.java    2001/08/17 14:43:48
***************
*** 22,27 ****
--- 22,29 ----
    public int sql_type = -1;    // The entry in java.sql.Types for this field
    public String type_name = null;// The sql type name

+   private static Hashtable oidCache = new Hashtable();
+
    /**
     * Construct a field based on the information fed to it.
     *
***************
*** 105,110 ****
--- 107,139 ----
    }

    /**
+    * This returns the oid for a field of a given data type
+    * @param type_name PostgreSQL type name
+    * @return PostgreSQL oid value for a field of this type
+    */
+   public int getOID( String type_name ) throws SQLException
+   {
+     int oid = -1;
+     if(type_name != null) {
+         Integer oidValue = (Integer) oidCache.get( type_name );
+         if( oidValue != null )
+             oid = oidValue.intValue();
+         else {
+             // it's not in the cache, so perform a query, and add the result to the cache
+             ResultSet result = (org.postgresql.ResultSet)conn.ExecSQL("select oid from pg_type where typname='"
+                 + type_name + "'");
+             if (result.getColumnCount() != 1 || result.getTupleCount() != 1)
+                 throw new PSQLException("postgresql.unexpected");
+             result.next();
+             oid = Integer.parseInt(result.getString(1));
+             oidCache.put( type_name, new Integer(oid) );
+             result.close();
+         }
+     }
+     return oid;
+   }
+
+   /**
     * This table holds the org.postgresql names for the types supported.
     * Any types that map to Types.OTHER (eg POINT) don't go into this table.
     * They default automatically to Types.OTHER
***************
*** 126,132 ****
      "bool",
      "date",
      "time",
!     "abstime","timestamp"
    };

    /**
--- 155,163 ----
      "bool",
      "date",
      "time",
!     "abstime","timestamp",
!     "_bool", "_char", "_int2", "_int4", "_text", "_oid", "_varchar", "_int8",
!     "_float4", "_float8", "_abstime", "_date", "_time", "_timestamp", "_numeric"
    };

    /**
***************
*** 149,155 ****
      Types.BIT,
      Types.DATE,
      Types.TIME,
!     Types.TIMESTAMP,Types.TIMESTAMP
    };

    /**
--- 180,188 ----
      Types.BIT,
      Types.DATE,
      Types.TIME,
!     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
    };

    /**
Index: src/interfaces/jdbc/org/postgresql/errors.properties
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/errors.properties,v
retrieving revision 1.4
diff -c -r1.4 errors.properties
*** src/interfaces/jdbc/org/postgresql/errors.properties    2001/01/18 17:37:13    1.4
--- src/interfaces/jdbc/org/postgresql/errors.properties    2001/08/17 14:43:48
***************
*** 1,4 ****
--- 1,5 ----
  # This is the default errors
+ postgresql.arr.range:The array index is out of range.
  postgresql.drv.version:An internal error has occured. Please recompile the driver.
  postgresql.con.auth:The authentication type {0} is not supported. Check that you have configured the pg_hba.conf file
toinclude the client's IP address or Subnet, and that it is using an authentication scheme supported by the driver. 
  postgresql.con.authfail:An error occured while getting the authentication request.
Index: src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java,v
retrieving revision 1.28
diff -c -r1.28 ResultSet.java
*** src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java    2001/07/21 18:52:11    1.28
--- src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java    2001/08/17 14:43:49
***************
*** 61,70 ****
  {
    protected org.postgresql.jdbc2.Statement statement;

!   /**
!    * StringBuffer used by getTimestamp
!    */
!   private StringBuffer sbuf;

    /**
     * Create a new ResultSet - Note that we create ResultSets to
--- 61,67 ----
  {
    protected org.postgresql.jdbc2.Statement statement;

!   private StringBuffer sbuf = null;

    /**
     * Create a new ResultSet - Note that we create ResultSets to
***************
*** 185,198 ****
     */
    public boolean getBoolean(int columnIndex) throws SQLException
    {
!     String s = getString(columnIndex);
!
!     if (s != null)
!       {
!     int c = s.charAt(0);
!     return ((c == 't') || (c == 'T') || (c == '1'));
!       }
!     return false;        // SQL NULL
    }

    /**
--- 182,188 ----
     */
    public boolean getBoolean(int columnIndex) throws SQLException
    {
!     return toBoolean( getString(columnIndex) );
    }

    /**
***************
*** 250,267 ****
     */
    public int getInt(int columnIndex) throws SQLException
    {
!     String s = getFixedString(columnIndex);
!
!     if (s != null)
!       {
!     try
!       {
!         return Integer.parseInt(s);
!       } catch (NumberFormatException e) {
!         throw new PSQLException ("postgresql.res.badint",s);
!       }
!       }
!     return 0;        // SQL NULL
    }

    /**
--- 240,246 ----
     */
    public int getInt(int columnIndex) throws SQLException
    {
!     return toInt( getFixedString(columnIndex) );
    }

    /**
***************
*** 273,290 ****
     */
    public long getLong(int columnIndex) throws SQLException
    {
!     String s = getFixedString(columnIndex);
!
!     if (s != null)
!       {
!     try
!       {
!         return Long.parseLong(s);
!       } catch (NumberFormatException e) {
!         throw new PSQLException ("postgresql.res.badlong",s);
!       }
!       }
!     return 0;        // SQL NULL
    }

    /**
--- 252,258 ----
     */
    public long getLong(int columnIndex) throws SQLException
    {
!     return toLong( getFixedString(columnIndex) );
    }

    /**
***************
*** 296,313 ****
     */
    public float getFloat(int columnIndex) throws SQLException
    {
!     String s = getFixedString(columnIndex);
!
!     if (s != null)
!       {
!     try
!       {
!         return Float.valueOf(s).floatValue();
!       } catch (NumberFormatException e) {
!         throw new PSQLException ("postgresql.res.badfloat",s);
!       }
!       }
!     return 0;        // SQL NULL
    }

    /**
--- 264,270 ----
     */
    public float getFloat(int columnIndex) throws SQLException
    {
!     return toFloat( getFixedString(columnIndex) );
    }

    /**
***************
*** 319,336 ****
     */
    public double getDouble(int columnIndex) throws SQLException
    {
!     String s = getFixedString(columnIndex);
!
!     if (s != null)
!       {
!     try
!       {
!         return Double.valueOf(s).doubleValue();
!       } catch (NumberFormatException e) {
!         throw new PSQLException ("postgresql.res.baddouble",s);
!       }
!       }
!     return 0;        // SQL NULL
    }

    /**
--- 276,282 ----
     */
    public double getDouble(int columnIndex) throws SQLException
    {
!     return toDouble( getFixedString(columnIndex) );
    }

    /**
***************
*** 345,371 ****
     */
    public BigDecimal getBigDecimal(int columnIndex, int scale) throws SQLException
    {
!     String s = getFixedString(columnIndex);
!     BigDecimal val;
!
!     if (s != null)
!       {
!
!         try
!           {
!         val = new BigDecimal(s);
!       } catch (NumberFormatException e) {
!         throw new PSQLException ("postgresql.res.badbigdec",s);
!       }
!     if (scale==-1) return val;
!       try
!         {
!           return val.setScale(scale);
!         } catch (ArithmeticException e) {
!           throw new PSQLException ("postgresql.res.badbigdec",s);
!         }
!       }
!     return null;        // SQL NULL
    }

    /**
--- 291,297 ----
     */
    public BigDecimal getBigDecimal(int columnIndex, int scale) throws SQLException
    {
!     return toBigDecimal( getFixedString(columnIndex), scale );
    }

    /**
***************
*** 412,427 ****
     */
    public java.sql.Date getDate(int columnIndex) throws SQLException
    {
!     String s = getString(columnIndex);
!     if(s==null)
!       return null;
!     // length == 10: SQL Date
!     // length >  10: SQL Timestamp, assumes PGDATESTYLE=ISO
!     try {
!       return java.sql.Date.valueOf((s.length() == 10) ? s : s.substring(0,10));
!     } catch (NumberFormatException e) {
!       throw new PSQLException("postgresql.res.baddate", s);
!     }
    }

    /**
--- 338,344 ----
     */
    public java.sql.Date getDate(int columnIndex) throws SQLException
    {
!     return toDate( getString(columnIndex) );
    }

    /**
***************
*** 434,450 ****
     */
    public Time getTime(int columnIndex) throws SQLException
    {
!     String s = getString(columnIndex);
!
!     if(s==null)
!       return null; // SQL NULL
!     // length == 8: SQL Time
!     // length >  8: SQL Timestamp
!     try {
!       return java.sql.Time.valueOf((s.length() == 8) ? s : s.substring(11,19));
!     } catch (NumberFormatException e) {
!       throw new PSQLException("postgresql.res.badtime",s);
!     }
    }

    /**
--- 351,357 ----
     */
    public Time getTime(int columnIndex) throws SQLException
    {
!     return toTime( getString(columnIndex) );
    }

    /**
***************
*** 457,546 ****
     */
    public Timestamp getTimestamp(int columnIndex) throws SQLException
    {
!     String s = getString(columnIndex);
!     if(s==null)
!     return null;
!
!     boolean subsecond;
!     //if string contains a '.' we have fractional seconds
!     if (s.indexOf('.') == -1) {
!       subsecond = false;
!     } else {
!       subsecond = true;
!     }
!
!     //here we are modifying the string from ISO format to a format java can understand
!     //java expects timezone info as 'GMT-08:00' instead of '-08' in postgres ISO format
!     //and java expects three digits if fractional seconds are present instead of two for postgres
!     //so this code strips off timezone info and adds on the GMT+/-...
!     //as well as adds a third digit for partial seconds if necessary
!     synchronized(this) {
!       // We must be synchronized here incase more theads access the ResultSet
!       // bad practice but possible. Anyhow this is to protect sbuf and
!       // SimpleDateFormat objects
!
!       // First time?
!       if(sbuf==null)
!         sbuf = new StringBuffer();
!
!       sbuf.setLength(0);
!       sbuf.append(s);
!
!       //we are looking to see if the backend has appended on a timezone.
!       //currently postgresql will return +/-HH:MM or +/-HH for timezone offset
!       //(i.e. -06, or +06:30, note the expectation of the leading zero for the
!       //hours, and the use of the : for delimiter between hours and minutes)
!       //if the backend ISO format changes in the future this code will
!       //need to be changed as well
!       char sub = sbuf.charAt(sbuf.length()-3);
!       if (sub == '+' || sub == '-') {
!         //we have found timezone info of format +/-HH
!         sbuf.setLength(sbuf.length()-3);
!         if (subsecond)  {
!           sbuf.append('0').append("GMT").append(s.substring(s.length()-3)).append(":00");
!         } else {
!           sbuf.append("GMT").append(s.substring(s.length()-3)).append(":00");
!         }
!       } else if (sub == ':') {
!         //we may have found timezone info of format +/-HH:MM, or there is no
!         //timezone info at all and this is the : preceding the seconds
!         char sub2 = sbuf.charAt(sbuf.length()-5);
!         if (sub2 == '+' || sub2 == '-') {
!           //we have found timezone info of format +/-HH:MM
!           sbuf.setLength(sbuf.length()-5);
!           if (subsecond)  {
!             sbuf.append('0').append("GMT").append(s.substring(s.length()-5));
!           } else {
!             sbuf.append("GMT").append(s.substring(s.length()-5));
!           }
!         } else if (subsecond) {
!           sbuf.append('0');
!         }
!       } else if (subsecond) {
!         sbuf.append('0');
!       }
!
!       // could optimize this a tad to remove too many object creations...
!       SimpleDateFormat df = null;
!
!       if (sbuf.length()>23 && subsecond) {
!         df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSzzzzzzzzz");
!       } else if (sbuf.length()>23 && !subsecond) {
!         df = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz");
!       } else if (sbuf.length()>10 && subsecond) {
!         df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
!       } else if (sbuf.length()>10 && !subsecond) {
!         df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
!       } else {
!         df = new SimpleDateFormat("yyyy-MM-dd");
!       }
!
!       try {
!           return new Timestamp(df.parse(sbuf.toString()).getTime());
!       } catch(ParseException e) {
!           throw new PSQLException("postgresql.res.badtimestamp",new Integer(e.getErrorOffset()),s);
!       }
!     }
    }

    /**
--- 364,370 ----
     */
    public Timestamp getTimestamp(int columnIndex) throws SQLException
    {
!     return toTimestamp( getString(columnIndex), this );
    }

    /**
***************
*** 960,973 ****
      return true;
      }

!     public Array getArray(String colName) throws SQLException
      {
      return getArray(findColumn(colName));
      }

!     public Array getArray(int i) throws SQLException
      {
!     throw org.postgresql.Driver.notImplemented();
      }

      public java.math.BigDecimal getBigDecimal(int columnIndex) throws SQLException
--- 784,799 ----
      return true;
      }

!     public java.sql.Array getArray(String colName) throws SQLException
      {
      return getArray(findColumn(colName));
      }

!     public java.sql.Array getArray(int i) throws SQLException
      {
!         if (i < 1 || i > fields.length)
!               throw new PSQLException("postgresql.res.colrange");
!         return (java.sql.Array) new org.postgresql.jdbc2.Array( connection, i, fields[i-1], this );
      }

      public java.math.BigDecimal getBigDecimal(int columnIndex) throws SQLException
***************
*** 1486,1490 ****
--- 1312,1484 ----
        this.statement=statement;
      }

+     //----------------- Formatting Methods -------------------
+
+     public static boolean toBoolean(String s)
+     {
+         if (s != null)
+         {
+             int c = s.charAt(0);
+             return ((c == 't') || (c == 'T'));
+         }
+         return false;        // SQL NULL
+     }
+
+     public static int toInt(String s) throws SQLException
+     {
+         if (s != null)
+         {
+             try
+             {
+                 return Integer.parseInt(s);
+             } catch (NumberFormatException e) {
+                 throw new PSQLException ("postgresql.res.badint",s);
+             }
+         }
+         return 0;        // SQL NULL
+     }
+
+     public static long toLong(String s) throws SQLException
+     {
+         if (s != null)
+         {
+             try
+             {
+                 return Long.parseLong(s);
+             } catch (NumberFormatException e) {
+                 throw new PSQLException ("postgresql.res.badlong",s);
+             }
+         }
+         return 0;        // SQL NULL
+     }
+
+     public static BigDecimal toBigDecimal(String s, int scale) throws SQLException
+     {
+         BigDecimal val;
+         if (s != null)
+         {
+             try
+             {
+                 val = new BigDecimal(s);
+             } catch (NumberFormatException e) {
+                 throw new PSQLException ("postgresql.res.badbigdec",s);
+             }
+             if (scale==-1) return val;
+             try
+             {
+                 return val.setScale(scale);
+             } catch (ArithmeticException e) {
+                 throw new PSQLException ("postgresql.res.badbigdec",s);
+             }
+         }
+         return null;        // SQL NULL
+     }
+
+     public static float toFloat(String s) throws SQLException
+     {
+         if (s != null)
+         {
+             try
+             {
+                 return Float.valueOf(s).floatValue();
+             } catch (NumberFormatException e) {
+                 throw new PSQLException ("postgresql.res.badfloat",s);
+             }
+         }
+         return 0;        // SQL NULL
+     }
+
+     public static double toDouble(String s) throws SQLException
+     {
+         if (s != null)
+         {
+             try
+             {
+                 return Double.valueOf(s).doubleValue();
+             } catch (NumberFormatException e) {
+                 throw new PSQLException ("postgresql.res.baddouble",s);
+             }
+         }
+         return 0;        // SQL NULL
+     }
+
+     public static java.sql.Date toDate(String s) throws SQLException
+     {
+         if(s==null)
+             return null;
+         return java.sql.Date.valueOf(s);
+     }
+
+     public static Time toTime(String s) throws SQLException
+     {
+         if(s==null)
+             return null; // SQL NULL
+         return java.sql.Time.valueOf(s);
+     }
+
+     public static Timestamp toTimestamp(String s, ResultSet resultSet) throws SQLException
+     {
+         if(s==null)
+             return null;
+
+         boolean subsecond;
+         //if string contains a '.' we have fractional seconds
+         if (s.indexOf('.') == -1) {
+             subsecond = false;
+         } else {
+             subsecond = true;
+         }
+
+         //here we are modifying the string from ISO format to a format java can understand
+         //java expects timezone info as 'GMT-08:00' instead of '-08' in postgres ISO format
+         //and java expects three digits if fractional seconds are present instead of two for postgres
+         //so this code strips off timezone info and adds on the GMT+/-...
+         //as well as adds a third digit for partial seconds if necessary
+         synchronized(resultSet) {
+             // We must be synchronized here incase more theads access the ResultSet
+             // bad practice but possible. Anyhow this is to protect sbuf and
+             // SimpleDateFormat objects
+
+             // First time?
+             if(resultSet.sbuf==null)
+                 resultSet.sbuf = new StringBuffer();
+
+             resultSet.sbuf.setLength(0);
+             resultSet.sbuf.append(s);
+
+             char sub = resultSet.sbuf.charAt(resultSet.sbuf.length()-3);
+             if (sub == '+' || sub == '-') {
+                 resultSet.sbuf.setLength(resultSet.sbuf.length()-3);
+                 if (subsecond)  {
+                     resultSet.sbuf.append('0').append("GMT").append(s.substring(s.length()-3)).append(":00");
+                 } else {
+                     resultSet.sbuf.append("GMT").append(s.substring(s.length()-3)).append(":00");
+                 }
+             } else if (subsecond) {
+                 resultSet.sbuf.append('0');
+             }
+
+             // could optimize this a tad to remove too many object creations...
+             SimpleDateFormat df = null;
+
+             if (resultSet.sbuf.length()>23 && subsecond) {
+                 df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSzzzzzzzzz");
+             } else if (resultSet.sbuf.length()>23 && !subsecond) {
+                 df = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz");
+             } else if (resultSet.sbuf.length()>10 && subsecond) {
+                 df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
+             } else if (resultSet.sbuf.length()>10 && !subsecond) {
+                 df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+             } else {
+                 df = new SimpleDateFormat("yyyy-MM-dd");
+             }
+
+             try {
+                 return new Timestamp(df.parse(resultSet.sbuf.toString()).getTime());
+             } catch(ParseException e) {
+                 throw new PSQLException("postgresql.res.badtimestamp",new Integer(e.getErrorOffset()),s);
+             }
+         }
+     }
  }