Thread: JDBC Array Support, Take 2
Ok, I've re-implemented Array.java in the JDBC2 driver. This time
I think I've read the spec right! :-) This functionality returns
an Array object from ResultSet that allows you to see this data as
either native Java arrays or as a ResultSet. Access methods allow
you to capture slices of the array values.
As before, type maps are not supported in this version. Another constraint
is that only single-dimension arrays are supported. This is because the
JDBC slice parameters (index,count) seem to provide only a single-dimension
support to arrays. [One might also make a strong argument that if you are
using n-dimensional arrays in a table you probably need a better database
design!]
Other behaviors:
-- Retrieving a single-value field (non-Array) field as an Array will
result in an array containing a single element.
-- A null-valued field will return an empty array
Array driver support includes the following datatypes:
bool
int2
int4
int8
float4
float8
numeric
char
varchar
date
time
timestamp
The patchfile was created using this command:
>diff -bBdNrw -U5 OLD NEW > patch.txt
To install the patch:
>cd PATH_TO_PGSQL_SRC/src/interfaces/jdbc/org/postgresql/
>patch -p1 < PATH_TO_PATCH_FILE/patch.txt
>cd ../..
>ant
New postgresql.jar file is now built in the jars dir.
The patch process works for me against the 7.1.2 source release.
If for some reason it doesn't work for you, the changes are very
easy and can be done manually (add one line to error.properties,
create jdbc2/Array.java and a couple small edits to jdbc2/ResultSet.java).
Please send comments and feedback to gzoller@hotmailcom.
Greg
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
Attachment
On Fri, 10 Aug 2001 12:38:43 -0400, Greg Zoller wrote: >patch -p1 < PATH_TO_PATCH_FILE/patch.txt I'm still getting this: $ patch -p1 <patch.txt patching file errors.properties patch: **** malformed patch at line 5: # This is the default errors Any ideas what may be causing this? I'm on Red Hat Linux 7.1 and patch -version says: patch 2.5.4 Copyright 1984-1988 Larry Wall Copyright 1989-1999 Free Software Foundation, Inc. Regards, René Pijlman
The problem is that there is wrapping in the patch. Attached is a cleaned up version. > On Fri, 10 Aug 2001 12:38:43 -0400, Greg Zoller wrote: > >patch -p1 < PATH_TO_PATCH_FILE/patch.txt > > I'm still getting this: > > $ patch -p1 <patch.txt > patching file errors.properties > patch: **** malformed patch at line 5: # This is the default > errors > > Any ideas what may be causing this? I'm on Red Hat Linux 7.1 and > patch -version says: > > patch 2.5.4 > Copyright 1984-1988 Larry Wall > Copyright 1989-1999 Free Software Foundation, Inc. > > Regards, > Ren? Pijlman > > ---------------------------(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 diff -bBdNrw -U5 OLD/errors.properties NEW/errors.properties --- OLD/errors.properties Thu Jan 18 09:37:13 2001 +++ NEW/errors.properties Tue Aug 7 12:54:15 2001 @@ -1,6 +1,7 @@ # This is the default errors +postgresql.arr.range:The array index is out of range. postgresql.drv.version:An internal error has occured. Please recompilethe driver. postgresql.con.auth:The authentication type {0} is not supported. Check that you have configured thepg_hba.conf file to include the client's IP address or Subnet, and that it is using an authentication scheme supportedby the driver. postgresql.con.authfail:An error occured while getting the authentication request. postgresql.con.call:CallableStatements are not supported at this time. postgresql.con.creobj:Failed to create object for{0} {1} diff -bBdNrw -U5 OLD/jdbc2/Array.java NEW/jdbc2/Array.java --- OLD/jdbc2/Array.java Wed Dec 31 16:00:00 1969 +++ NEW/jdbc2/Array.java Fri Aug 10 12:25:22 2001 @@ -0,0 +1,494 @@ +package org.postgresql.jdbc2; + +import java.text.*; +import java.sql.*; +import java.util.*; +import java.math.BigDecimal; +import org.postgresql.Field; +import org.postgresql.util.*; + +/** + * 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. + * + * @see ResultSet#getArray + */ + + +public class Array implements java.sql.Array +{ + public static final int BOOLEAN_ARRAY = 1000; + public static final int CHAR_ARRAY = 1002; + public static final int INT2_ARRAY = 1005; + public static final int INT4_ARRAY = 1007; + public static final int INT8_ARRAY = 1016; + public static final int VARCHAR_ARRAY = 1015; + public static final int FLOAT4_ARRAY = 1021; + public static final int FLOAT8_ARRAY = 1022; + public static final int DATE_ARRAY = 1182; + public static final int TIME_ARRAY = 1183; + public static final int TIMESTAMP_ARRAY = 1185; + public static final int NUMERIC_ARRAY = 1231; + //------------------------------------------ + public static final int BOOLEAN = 16; + public static final int CHAR = 18; + public static final int INT2 = 21; + public static final int INT4 = 23; + public static final int INT8 = 20; + public static final int VARCHAR = 1043; + public static final int FLOAT4 = 700; + public static final int FLOAT8 = 701; + public static final int DATE = 1082; + public static final int TIME = 1083; + public static final int TIMESTAMP = 1184; + public static final int NUMERIC = 1700; + + private org.postgresql.Connection conn = null; + private org.postgresql.Field field = null; + private org.postgresql.jdbc2.ResultSet rs = null; + private int idx = 0; + + /** + * 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 + */ + public Array( org.postgresql.Connection conn, int idx, Field field, org.postgresql.jdbc2.ResultSet rs ) { + this.conn = conn; + this.field = field; + this.rs = rs; + this.idx = idx; + } + + public Object getArray() throws SQLException { + return getArray( 1, 0, null ); + } + + public Object getArray(long index, int count) throws SQLException { + return getArray( index, count, null ); + } + + public Object getArray(Map map) throws SQLException { + return getArray( 1, 0, map ); + } + + public Object getArray(long index, int count, Map map) throws SQLException { + if( map != null ) // For now maps aren't supported. + throw org.postgresql.Driver.notImplemented(); + + if (index < 1) + throw new PSQLException("postgresql.arr.range"); + Object retVal = null; + + ArrayList array = new ArrayList(); + String raw = rs.getFixedString(idx); + if( raw != null ) { + char[] chars = raw.toCharArray(); + StringBuffer sbuf = new StringBuffer(); + boolean foundOpen = false; + boolean insideString = false; + for( int i=0; i<chars.length; i++ ) { + if( chars[i] == '{' ) { + if( foundOpen ) // Only supports 1-D arrays for now + throw org.postgresql.Driver.notImplemented(); + foundOpen = true; + continue; + } + if( chars[i] == '"' ) { + insideString = !insideString; + continue; + } + if( (!insideString && chars[i] == ',') || chars[i] == '}' || i == chars.length-1) { + if( chars[i] != '"' && chars[i] != '}' && chars[i] != ',' ) + sbuf.append(chars[i]); + array.add( sbuf.toString() ); + sbuf = new StringBuffer(); + continue; + } + sbuf.append( chars[i] ); + } + } + String[] arrayContents = (String[]) array.toArray( new String[array.size()] ); + if( count == 0 ) + count = arrayContents.length; + index--; + if( index+count > arrayContents.length ) + throw new PSQLException("postgresql.arr.range"); + + int i = 0; + switch (field.getOID()) + { + case BOOLEAN: + case BOOLEAN_ARRAY: + retVal = new boolean[ count ]; + for( ; count > 0; count-- ) { + String s = arrayContents[(int)index++]; + try + { + char c = s.charAt(0); + ((boolean[])retVal)[i++] = ((c == 't') || (c == 'T')); + } catch (NumberFormatException e) { + throw new PSQLException ("postgresql.res.badbyte",s); + } + } + break; + case INT2: + case INT4: + case INT2_ARRAY: + case INT4_ARRAY: + retVal = new int[ count ]; + for( ; count > 0; count-- ) { + String s = arrayContents[(int)index++]; + try + { + ((int[])retVal)[i++] = Integer.parseInt( s ); + } catch (NumberFormatException e) { + throw new PSQLException ("postgresql.res.badint",s); + } + } + break; + case INT8: + case INT8_ARRAY: + retVal = new long[ count ]; + for( ; count > 0; count-- ) { + String s = arrayContents[(int)index++]; + try + { + ((long[])retVal)[i++] = Long.parseLong(s); + } catch (NumberFormatException e) { + throw new PSQLException ("postgresql.res.badlong",s); + } + } + break; + case NUMERIC: + case NUMERIC_ARRAY: + retVal = new BigDecimal[ count ]; + for( ; count > 0; count-- ) { + String s = arrayContents[(int)index++]; + try + { + ((BigDecimal[])retVal)[i] = new BigDecimal(s); + ((BigDecimal[])retVal)[i++].setScale(0); + } catch (NumberFormatException e) { + throw new PSQLException ("postgresql.res.badbigdec",s); + } catch (ArithmeticException e) { + throw new PSQLException ("postgresql.res.badbigdec",s); + } + } + break; + case FLOAT4: + case FLOAT4_ARRAY: + retVal = new float[ count ]; + for( ; count > 0; count-- ) { + String s = arrayContents[(int)index++]; + try + { + ((float[])retVal)[i++] = Float.parseFloat(s); + } catch (NumberFormatException e) { + throw new PSQLException ("postgresql.res.badfloat",s); + } + } + break; + case FLOAT8: + case FLOAT8_ARRAY: + retVal = new double[ count ]; + for( ; count > 0; count-- ) { + String s = arrayContents[(int)index++]; + try + { + ((double[])retVal)[i++] = Double.parseDouble(s); + } catch (NumberFormatException e) { + throw new PSQLException ("postgresql.res.baddouble",s); + } + } + break; + case CHAR: + case VARCHAR: + case CHAR_ARRAY: + case VARCHAR_ARRAY: + retVal = new String[ count ]; + for( ; count > 0; count-- ) + ((String[])retVal)[i++] = arrayContents[(int)index++]; + break; + case DATE: + case DATE_ARRAY: + retVal = new java.sql.Date[ count ]; + for( ; count > 0; count-- ) { + if( arrayContents[(int)index] == null ) + ((java.sql.Date[])retVal)[i++] = null; + else + ((java.sql.Date[])retVal)[i++] = java.sql.Date.valueOf(arrayContents[(int)index] ); + index++; + } + break; + case TIME: + case TIME_ARRAY: + retVal = new java.sql.Time[ count ]; + for( ; count > 0; count-- ) { + if( arrayContents[(int)index] == null ) + ((java.sql.Time[])retVal)[i++] = null; + else + ((java.sql.Time[])retVal)[i++] = java.sql.Time.valueOf(arrayContents[(int)index] ); + index++; + } + break; + case TIMESTAMP: + case TIMESTAMP_ARRAY: + retVal = new Timestamp[ count ]; + StringBuffer sbuf = null; + for( ; count > 0; count-- ) { + if( arrayContents[(int)index] == null ) { + ((java.sql.Timestamp[])retVal)[i++] = null; + index++; + continue; + } + boolean subsecond = true;; + //if string contains a '.' we have fractional seconds + if (arrayContents[i].indexOf('.') == -1) + subsecond = false; + + //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 Array + // bad practice but possible. Anyhow this is to protect sbuf and + // SimpleDateFormat objects + + // First time? + if(sbuf==null) + sbuf = new StringBuffer(); + + String s = arrayContents[(int)index++]; + sbuf.setLength(0); + sbuf.append(s); + + char sub = sbuf.charAt(sbuf.length()-3); + if (sub == '+' || sub == '-') { + 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 (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 { + ((java.sql.Timestamp[])retVal)[i++] = new Timestamp(df.parse(sbuf.toString()).getTime()); + } catch(ParseException e) { + throw new PSQLException("postgresql.res.badtimestamp",new Integer(e.getErrorOffset()),s); + } + } + } + break; + + // Other datatypes not currently supported. If you are really using other types ask + // yourself if an array of non-trivial data types is really good database design. + default: + throw org.postgresql.Driver.notImplemented(); + } + return retVal; + } + + public int getBaseType() throws SQLException { + switch( field.getOID() ) { + case BOOLEAN_ARRAY: return Field.getSQLType("bool"); + case CHAR_ARRAY: return Field.getSQLType("char"); + case INT2_ARRAY: return Field.getSQLType("int2"); + case INT4_ARRAY: return Field.getSQLType("int4"); + case VARCHAR_ARRAY: return Field.getSQLType("varchar"); + case INT8_ARRAY: return Field.getSQLType("int8"); + case FLOAT4_ARRAY: return Field.getSQLType("float4"); + case FLOAT8_ARRAY: return Field.getSQLType("float8"); + case DATE_ARRAY: return Field.getSQLType("date"); + case TIME_ARRAY: return Field.getSQLType("time"); + case TIMESTAMP_ARRAY: return Field.getSQLType("timestamp"); + case NUMERIC_ARRAY: return Field.getSQLType("numeric"); + default: + throw org.postgresql.Driver.notImplemented(); + } + } + + public String getBaseTypeName() throws SQLException { + switch( field.getOID() ) { + case BOOLEAN_ARRAY: return "bool"; + case CHAR_ARRAY: return "char"; + case INT2_ARRAY: return "int2"; + case INT4_ARRAY: return "int4"; + case VARCHAR_ARRAY: return "varchar"; + case INT8_ARRAY: return "int8"; + case FLOAT4_ARRAY: return "float4"; + case FLOAT8_ARRAY: return "float8"; + case DATE_ARRAY: return "date"; + case TIME_ARRAY: return "time"; + case TIMESTAMP_ARRAY: return "timestamp"; + case NUMERIC_ARRAY: return "numeric"; + default: + throw org.postgresql.Driver.notImplemented(); + } + } + + public java.sql.ResultSet getResultSet() throws SQLException { + return getResultSet( 1, 0, null ); + } + + public java.sql.ResultSet getResultSet(long index, int count) throws SQLException { + return getResultSet( index, count, null ); + } + + public java.sql.ResultSet getResultSet(Map map) throws SQLException { + return getResultSet( 1, 0, map ); + } + + public java.sql.ResultSet getResultSet(long index, int count, java.util.Map map) throws SQLException { + Object array = getArray( index, count, map ); + Vector rows = new Vector(); + Field[] fields = new Field[2]; + fields[0] = new Field(conn, "INDEX", INT2, 2); + switch (field.getOID() ) + { + case BOOLEAN_ARRAY: + boolean[] booleanArray = (boolean[]) array; + fields[1] = new Field(conn, "VALUE", BOOLEAN, 1); + for( int i=0; i<booleanArray.length; i++ ) { + byte[][] tuple = new byte[2][0]; + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index + tuple[1] = (booleanArray[i]?"YES":"NO").getBytes(); // Value + rows.addElement(tuple); + } + case INT2_ARRAY: + fields[1] = new Field(conn, "VALUE", INT2, 2); + case INT4_ARRAY: + int[] intArray = (int[]) array; + if( fields[1] == null ) + fields[1] = new Field(conn, "VALUE", INT4, 4); + for( int i=0; i<intArray.length; i++ ) { + byte[][] tuple = new byte[2][0]; + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index + tuple[1] = Integer.toString(intArray[i]).getBytes(); // Value + rows.addElement(tuple); + } + break; + case INT8_ARRAY: + long[] longArray = (long[]) array; + fields[1] = new Field(conn, "VALUE", INT8, 8); + for( int i=0; i<longArray.length; i++ ) { + byte[][] tuple = new byte[2][0]; + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index + tuple[1] = Long.toString(longArray[i]).getBytes(); // Value + rows.addElement(tuple); + } + break; + case NUMERIC_ARRAY: + BigDecimal[] bdArray = (BigDecimal[]) array; + fields[1] = new Field(conn, "VALUE", NUMERIC, -1); + for( int i=0; i<bdArray.length; i++ ) { + byte[][] tuple = new byte[2][0]; + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index + tuple[1] = bdArray[i].toString().getBytes(); // Value + rows.addElement(tuple); + } + break; + case FLOAT4_ARRAY: + float[] floatArray = (float[]) array; + fields[1] = new Field(conn, "VALUE", FLOAT4, 4); + for( int i=0; i<floatArray.length; i++ ) { + byte[][] tuple = new byte[2][0]; + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index + tuple[1] = Float.toString(floatArray[i]).getBytes(); // Value + rows.addElement(tuple); + } + break; + case FLOAT8_ARRAY: + double[] doubleArray = (double[]) array; + fields[1] = new Field(conn, "VALUE", FLOAT8, 8); + for( int i=0; i<doubleArray.length; i++ ) { + byte[][] tuple = new byte[2][0]; + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index + tuple[1] = Double.toString(doubleArray[i]).getBytes(); // Value + rows.addElement(tuple); + } + break; + case CHAR_ARRAY: + fields[1] = new Field(conn, "VALUE", CHAR, 1); + case VARCHAR_ARRAY: + String[] strArray = (String[]) array; + if( fields[1] == null ) + fields[1] = new Field(conn, "VALUE", VARCHAR, -1); + for( int i=0; i<strArray.length; i++ ) { + byte[][] tuple = new byte[2][0]; + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index + tuple[1] = strArray[i].getBytes(); // Value + rows.addElement(tuple); + } + break; + case DATE_ARRAY: + java.sql.Date[] dateArray = (java.sql.Date[]) array; + fields[1] = new Field(conn, "VALUE", DATE, 4); + for( int i=0; i<dateArray.length; i++ ) { + byte[][] tuple = new byte[2][0]; + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index + tuple[1] = dateArray[i].toString().getBytes(); // Value + rows.addElement(tuple); + } + break; + case TIME_ARRAY: + java.sql.Time[] timeArray = (java.sql.Time[]) array; + fields[1] = new Field(conn, "VALUE", TIME, 8); + for( int i=0; i<timeArray.length; i++ ) { + byte[][] tuple = new byte[2][0]; + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index + tuple[1] = timeArray[i].toString().getBytes(); // Value + rows.addElement(tuple); + } + break; + case TIMESTAMP_ARRAY: + java.sql.Timestamp[] timestampArray = (java.sql.Timestamp[]) array; + fields[1] = new Field(conn, "VALUE", TIMESTAMP, 8); + for( int i=0; i<timestampArray.length; i++ ) { + byte[][] tuple = new byte[2][0]; + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index + tuple[1] = timestampArray[i].toString().getBytes(); // Value + rows.addElement(tuple); + } + break; + + // Other datatypes not currently supported. If you are really using other types ask + // yourself if an array of non-trivial data types is really good database design. + default: + throw org.postgresql.Driver.notImplemented(); + } + return new ResultSet((org.postgresql.jdbc2.Connection)conn, fields, rows, "OK", 1 ); + } +} + diff -bBdNrw -U5 OLD/jdbc2/ResultSet.java NEW/jdbc2/ResultSet.java --- OLD/jdbc2/ResultSet.java Tue May 22 07:46:46 2001 +++ NEW/jdbc2/ResultSet.java Tue Aug 7 12:54:44 2001 @@ -931,18 +931,21 @@ current_row = 0; this_row = (byte [][])rows.elementAt(current_row); return true; } - public Array getArray(String colName) throws SQLException + public java.sql.Array getArray(String colName) throws SQLException { return getArray(findColumn(colName)); } - public Array getArray(int i) throws SQLException + public java.sql.Array getArray(int i) throws SQLException { - throw org.postgresql.Driver.notImplemented(); + if (i < 1 || i > fields.length) + throw new PSQLException("postgresql.res.colrange"); + first(); + return (java.sql.Array) new org.postgresql.jdbc2.Array( connection, i, fields[i-1], this ); } public java.math.BigDecimal getBigDecimal(int columnIndex) throws SQLException { // Now must call BigDecimal with a scale otherwise JBuilder barfs
I also question why you are calling first() in your ResultSet.getArray()
logic? I don't see what purpose this has, and it seems flat out wrong,
to always return the values for only the first row of the result.
thanks,
--Barry
Bruce Momjian wrote:
> The problem is that there is wrapping in the patch. Attached is a
> cleaned up version.
>
>
>
>>On Fri, 10 Aug 2001 12:38:43 -0400, Greg Zoller wrote:
>>
>>>patch -p1 < PATH_TO_PATCH_FILE/patch.txt
>>>
>>I'm still getting this:
>>
>>$ patch -p1 <patch.txt
>>patching file errors.properties
>>patch: **** malformed patch at line 5: # This is the default
>>errors
>>
>>Any ideas what may be causing this? I'm on Red Hat Linux 7.1 and
>>patch -version says:
>>
>>patch 2.5.4
>>Copyright 1984-1988 Larry Wall
>>Copyright 1989-1999 Free Software Foundation, Inc.
>>
>>Regards,
>>Ren? Pijlman
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
>>
>
>
> ------------------------------------------------------------------------
>
> diff -bBdNrw -U5 OLD/errors.properties NEW/errors.properties
> --- OLD/errors.properties Thu Jan 18 09:37:13 2001
> +++ NEW/errors.properties Tue Aug 7 12:54:15 2001
> @@ -1,6 +1,7 @@
> # This is the default errors
> +postgresql.arr.range:The array index is out of range. postgresql.drv.version:An internal error has occured. Please
recompilethe driver. postgresql.con.auth:The authentication type {0} is not supported. Check that you have configured
thepg_hba.conf file to include the client's IP address or Subnet, and that it is using an authentication scheme
supportedby the driver. postgresql.con.authfail:An error occured while getting the authentication request.
postgresql.con.call:CallableStatements are not supported at this time. postgresql.con.creobj:Failed to create object
for{0} {1}
> diff -bBdNrw -U5 OLD/jdbc2/Array.java NEW/jdbc2/Array.java
> --- OLD/jdbc2/Array.java Wed Dec 31 16:00:00 1969
> +++ NEW/jdbc2/Array.java Fri Aug 10 12:25:22 2001
> @@ -0,0 +1,494 @@
> +package org.postgresql.jdbc2;
> +
> +import java.text.*;
> +import java.sql.*;
> +import java.util.*;
> +import java.math.BigDecimal;
> +import org.postgresql.Field;
> +import org.postgresql.util.*;
> +
> +/**
> + * 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.
> + *
> + * @see ResultSet#getArray
> + */
> +
> +
> +public class Array implements java.sql.Array
> +{
> + public static final int BOOLEAN_ARRAY = 1000;
> + public static final int CHAR_ARRAY = 1002;
> + public static final int INT2_ARRAY = 1005;
> + public static final int INT4_ARRAY = 1007;
> + public static final int INT8_ARRAY = 1016;
> + public static final int VARCHAR_ARRAY = 1015;
> + public static final int FLOAT4_ARRAY = 1021;
> + public static final int FLOAT8_ARRAY = 1022;
> + public static final int DATE_ARRAY = 1182;
> + public static final int TIME_ARRAY = 1183;
> + public static final int TIMESTAMP_ARRAY = 1185;
> + public static final int NUMERIC_ARRAY = 1231;
> + //------------------------------------------
> + public static final int BOOLEAN = 16;
> + public static final int CHAR = 18;
> + public static final int INT2 = 21;
> + public static final int INT4 = 23;
> + public static final int INT8 = 20;
> + public static final int VARCHAR = 1043;
> + public static final int FLOAT4 = 700;
> + public static final int FLOAT8 = 701;
> + public static final int DATE = 1082;
> + public static final int TIME = 1083;
> + public static final int TIMESTAMP = 1184;
> + public static final int NUMERIC = 1700;
> +
> + private org.postgresql.Connection conn = null;
> + private org.postgresql.Field field = null;
> + private org.postgresql.jdbc2.ResultSet rs = null;
> + private int idx = 0;
> +
> + /**
> + * 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
> + */
> + public Array( org.postgresql.Connection conn, int idx, Field field, org.postgresql.jdbc2.ResultSet rs ) {
> + this.conn = conn;
> + this.field = field;
> + this.rs = rs;
> + this.idx = idx;
> + }
> +
> + public Object getArray() throws SQLException {
> + return getArray( 1, 0, null );
> + }
> +
> + public Object getArray(long index, int count) throws SQLException {
> + return getArray( index, count, null );
> + }
> +
> + public Object getArray(Map map) throws SQLException {
> + return getArray( 1, 0, map );
> + }
> +
> + public Object getArray(long index, int count, Map map) throws SQLException {
> + if( map != null ) // For now maps aren't supported.
> + throw org.postgresql.Driver.notImplemented();
> +
> + if (index < 1)
> + throw new PSQLException("postgresql.arr.range");
> + Object retVal = null;
> +
> + ArrayList array = new ArrayList();
> + String raw = rs.getFixedString(idx);
> + if( raw != null ) {
> + char[] chars = raw.toCharArray();
> + StringBuffer sbuf = new StringBuffer();
> + boolean foundOpen = false;
> + boolean insideString = false;
> + for( int i=0; i<chars.length; i++ ) {
> + if( chars[i] == '{' ) {
> + if( foundOpen ) // Only supports 1-D arrays for now
> + throw org.postgresql.Driver.notImplemented();
> + foundOpen = true;
> + continue;
> + }
> + if( chars[i] == '"' ) {
> + insideString = !insideString;
> + continue;
> + }
> + if( (!insideString && chars[i] == ',') || chars[i] == '}' || i == chars.length-1) {
> + if( chars[i] != '"' && chars[i] != '}' && chars[i] != ',' )
> + sbuf.append(chars[i]);
> + array.add( sbuf.toString() );
> + sbuf = new StringBuffer();
> + continue;
> + }
> + sbuf.append( chars[i] );
> + }
> + }
> + String[] arrayContents = (String[]) array.toArray( new String[array.size()] );
> + if( count == 0 )
> + count = arrayContents.length;
> + index--;
> + if( index+count > arrayContents.length )
> + throw new PSQLException("postgresql.arr.range");
> +
> + int i = 0;
> + switch (field.getOID())
> + {
> + case BOOLEAN:
> + case BOOLEAN_ARRAY:
> + retVal = new boolean[ count ];
> + for( ; count > 0; count-- ) {
> + String s = arrayContents[(int)index++];
> + try
> + {
> + char c = s.charAt(0);
> + ((boolean[])retVal)[i++] = ((c == 't') || (c == 'T'));
> + } catch (NumberFormatException e) {
> + throw new PSQLException ("postgresql.res.badbyte",s);
> + }
> + }
> + break;
> + case INT2:
> + case INT4:
> + case INT2_ARRAY:
> + case INT4_ARRAY:
> + retVal = new int[ count ];
> + for( ; count > 0; count-- ) {
> + String s = arrayContents[(int)index++];
> + try
> + {
> + ((int[])retVal)[i++] = Integer.parseInt( s );
> + } catch (NumberFormatException e) {
> + throw new PSQLException ("postgresql.res.badint",s);
> + }
> + }
> + break;
> + case INT8:
> + case INT8_ARRAY:
> + retVal = new long[ count ];
> + for( ; count > 0; count-- ) {
> + String s = arrayContents[(int)index++];
> + try
> + {
> + ((long[])retVal)[i++] = Long.parseLong(s);
> + } catch (NumberFormatException e) {
> + throw new PSQLException ("postgresql.res.badlong",s);
> + }
> + }
> + break;
> + case NUMERIC:
> + case NUMERIC_ARRAY:
> + retVal = new BigDecimal[ count ];
> + for( ; count > 0; count-- ) {
> + String s = arrayContents[(int)index++];
> + try
> + {
> + ((BigDecimal[])retVal)[i] = new BigDecimal(s);
> + ((BigDecimal[])retVal)[i++].setScale(0);
> + } catch (NumberFormatException e) {
> + throw new PSQLException ("postgresql.res.badbigdec",s);
> + } catch (ArithmeticException e) {
> + throw new PSQLException ("postgresql.res.badbigdec",s);
> + }
> + }
> + break;
> + case FLOAT4:
> + case FLOAT4_ARRAY:
> + retVal = new float[ count ];
> + for( ; count > 0; count-- ) {
> + String s = arrayContents[(int)index++];
> + try
> + {
> + ((float[])retVal)[i++] = Float.parseFloat(s);
> + } catch (NumberFormatException e) {
> + throw new PSQLException ("postgresql.res.badfloat",s);
> + }
> + }
> + break;
> + case FLOAT8:
> + case FLOAT8_ARRAY:
> + retVal = new double[ count ];
> + for( ; count > 0; count-- ) {
> + String s = arrayContents[(int)index++];
> + try
> + {
> + ((double[])retVal)[i++] = Double.parseDouble(s);
> + } catch (NumberFormatException e) {
> + throw new PSQLException ("postgresql.res.baddouble",s);
> + }
> + }
> + break;
> + case CHAR:
> + case VARCHAR:
> + case CHAR_ARRAY:
> + case VARCHAR_ARRAY:
> + retVal = new String[ count ];
> + for( ; count > 0; count-- )
> + ((String[])retVal)[i++] = arrayContents[(int)index++];
> + break;
> + case DATE:
> + case DATE_ARRAY:
> + retVal = new java.sql.Date[ count ];
> + for( ; count > 0; count-- ) {
> + if( arrayContents[(int)index] == null )
> + ((java.sql.Date[])retVal)[i++] = null;
> + else
> + ((java.sql.Date[])retVal)[i++] = java.sql.Date.valueOf(arrayContents[(int)index] );
> + index++;
> + }
> + break;
> + case TIME:
> + case TIME_ARRAY:
> + retVal = new java.sql.Time[ count ];
> + for( ; count > 0; count-- ) {
> + if( arrayContents[(int)index] == null )
> + ((java.sql.Time[])retVal)[i++] = null;
> + else
> + ((java.sql.Time[])retVal)[i++] = java.sql.Time.valueOf(arrayContents[(int)index] );
> + index++;
> + }
> + break;
> + case TIMESTAMP:
> + case TIMESTAMP_ARRAY:
> + retVal = new Timestamp[ count ];
> + StringBuffer sbuf = null;
> + for( ; count > 0; count-- ) {
> + if( arrayContents[(int)index] == null ) {
> + ((java.sql.Timestamp[])retVal)[i++] = null;
> + index++;
> + continue;
> + }
> + boolean subsecond = true;;
> + //if string contains a '.' we have fractional seconds
> + if (arrayContents[i].indexOf('.') == -1)
> + subsecond = false;
> +
> + //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 Array
> + // bad practice but possible. Anyhow this is to protect sbuf and
> + // SimpleDateFormat objects
> +
> + // First time?
> + if(sbuf==null)
> + sbuf = new StringBuffer();
> +
> + String s = arrayContents[(int)index++];
> + sbuf.setLength(0);
> + sbuf.append(s);
> +
> + char sub = sbuf.charAt(sbuf.length()-3);
> + if (sub == '+' || sub == '-') {
> + 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 (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 {
> + ((java.sql.Timestamp[])retVal)[i++] = new
Timestamp(df.parse(sbuf.toString()).getTime());
> + } catch(ParseException e) {
> + throw new PSQLException("postgresql.res.badtimestamp",new
Integer(e.getErrorOffset()),s);
> + }
> + }
> + }
> + break;
> +
> + // Other datatypes not currently supported. If you are really using other types ask
> + // yourself if an array of non-trivial data types is really good database design.
> + default:
> + throw org.postgresql.Driver.notImplemented();
> + }
> + return retVal;
> + }
> +
> + public int getBaseType() throws SQLException {
> + switch( field.getOID() ) {
> + case BOOLEAN_ARRAY: return Field.getSQLType("bool");
> + case CHAR_ARRAY: return Field.getSQLType("char");
> + case INT2_ARRAY: return Field.getSQLType("int2");
> + case INT4_ARRAY: return Field.getSQLType("int4");
> + case VARCHAR_ARRAY: return Field.getSQLType("varchar");
> + case INT8_ARRAY: return Field.getSQLType("int8");
> + case FLOAT4_ARRAY: return Field.getSQLType("float4");
> + case FLOAT8_ARRAY: return Field.getSQLType("float8");
> + case DATE_ARRAY: return Field.getSQLType("date");
> + case TIME_ARRAY: return Field.getSQLType("time");
> + case TIMESTAMP_ARRAY: return Field.getSQLType("timestamp");
> + case NUMERIC_ARRAY: return Field.getSQLType("numeric");
> + default:
> + throw org.postgresql.Driver.notImplemented();
> + }
> + }
> +
> + public String getBaseTypeName() throws SQLException {
> + switch( field.getOID() ) {
> + case BOOLEAN_ARRAY: return "bool";
> + case CHAR_ARRAY: return "char";
> + case INT2_ARRAY: return "int2";
> + case INT4_ARRAY: return "int4";
> + case VARCHAR_ARRAY: return "varchar";
> + case INT8_ARRAY: return "int8";
> + case FLOAT4_ARRAY: return "float4";
> + case FLOAT8_ARRAY: return "float8";
> + case DATE_ARRAY: return "date";
> + case TIME_ARRAY: return "time";
> + case TIMESTAMP_ARRAY: return "timestamp";
> + case NUMERIC_ARRAY: return "numeric";
> + default:
> + throw org.postgresql.Driver.notImplemented();
> + }
> + }
> +
> + public java.sql.ResultSet getResultSet() throws SQLException {
> + return getResultSet( 1, 0, null );
> + }
> +
> + public java.sql.ResultSet getResultSet(long index, int count) throws SQLException {
> + return getResultSet( index, count, null );
> + }
> +
> + public java.sql.ResultSet getResultSet(Map map) throws SQLException {
> + return getResultSet( 1, 0, map );
> + }
> +
> + public java.sql.ResultSet getResultSet(long index, int count, java.util.Map map) throws SQLException {
> + Object array = getArray( index, count, map );
> + Vector rows = new Vector();
> + Field[] fields = new Field[2];
> + fields[0] = new Field(conn, "INDEX", INT2, 2);
> + switch (field.getOID() )
> + {
> + case BOOLEAN_ARRAY:
> + boolean[] booleanArray = (boolean[]) array;
> + fields[1] = new Field(conn, "VALUE", BOOLEAN, 1);
> + for( int i=0; i<booleanArray.length; i++ ) {
> + byte[][] tuple = new byte[2][0];
> + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index
> + tuple[1] = (booleanArray[i]?"YES":"NO").getBytes(); // Value
> + rows.addElement(tuple);
> + }
> + case INT2_ARRAY:
> + fields[1] = new Field(conn, "VALUE", INT2, 2);
> + case INT4_ARRAY:
> + int[] intArray = (int[]) array;
> + if( fields[1] == null )
> + fields[1] = new Field(conn, "VALUE", INT4, 4);
> + for( int i=0; i<intArray.length; i++ ) {
> + byte[][] tuple = new byte[2][0];
> + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index
> + tuple[1] = Integer.toString(intArray[i]).getBytes(); // Value
> + rows.addElement(tuple);
> + }
> + break;
> + case INT8_ARRAY:
> + long[] longArray = (long[]) array;
> + fields[1] = new Field(conn, "VALUE", INT8, 8);
> + for( int i=0; i<longArray.length; i++ ) {
> + byte[][] tuple = new byte[2][0];
> + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index
> + tuple[1] = Long.toString(longArray[i]).getBytes(); // Value
> + rows.addElement(tuple);
> + }
> + break;
> + case NUMERIC_ARRAY:
> + BigDecimal[] bdArray = (BigDecimal[]) array;
> + fields[1] = new Field(conn, "VALUE", NUMERIC, -1);
> + for( int i=0; i<bdArray.length; i++ ) {
> + byte[][] tuple = new byte[2][0];
> + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index
> + tuple[1] = bdArray[i].toString().getBytes(); // Value
> + rows.addElement(tuple);
> + }
> + break;
> + case FLOAT4_ARRAY:
> + float[] floatArray = (float[]) array;
> + fields[1] = new Field(conn, "VALUE", FLOAT4, 4);
> + for( int i=0; i<floatArray.length; i++ ) {
> + byte[][] tuple = new byte[2][0];
> + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index
> + tuple[1] = Float.toString(floatArray[i]).getBytes(); // Value
> + rows.addElement(tuple);
> + }
> + break;
> + case FLOAT8_ARRAY:
> + double[] doubleArray = (double[]) array;
> + fields[1] = new Field(conn, "VALUE", FLOAT8, 8);
> + for( int i=0; i<doubleArray.length; i++ ) {
> + byte[][] tuple = new byte[2][0];
> + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index
> + tuple[1] = Double.toString(doubleArray[i]).getBytes(); // Value
> + rows.addElement(tuple);
> + }
> + break;
> + case CHAR_ARRAY:
> + fields[1] = new Field(conn, "VALUE", CHAR, 1);
> + case VARCHAR_ARRAY:
> + String[] strArray = (String[]) array;
> + if( fields[1] == null )
> + fields[1] = new Field(conn, "VALUE", VARCHAR, -1);
> + for( int i=0; i<strArray.length; i++ ) {
> + byte[][] tuple = new byte[2][0];
> + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index
> + tuple[1] = strArray[i].getBytes(); // Value
> + rows.addElement(tuple);
> + }
> + break;
> + case DATE_ARRAY:
> + java.sql.Date[] dateArray = (java.sql.Date[]) array;
> + fields[1] = new Field(conn, "VALUE", DATE, 4);
> + for( int i=0; i<dateArray.length; i++ ) {
> + byte[][] tuple = new byte[2][0];
> + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index
> + tuple[1] = dateArray[i].toString().getBytes(); // Value
> + rows.addElement(tuple);
> + }
> + break;
> + case TIME_ARRAY:
> + java.sql.Time[] timeArray = (java.sql.Time[]) array;
> + fields[1] = new Field(conn, "VALUE", TIME, 8);
> + for( int i=0; i<timeArray.length; i++ ) {
> + byte[][] tuple = new byte[2][0];
> + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index
> + tuple[1] = timeArray[i].toString().getBytes(); // Value
> + rows.addElement(tuple);
> + }
> + break;
> + case TIMESTAMP_ARRAY:
> + java.sql.Timestamp[] timestampArray = (java.sql.Timestamp[]) array;
> + fields[1] = new Field(conn, "VALUE", TIMESTAMP, 8);
> + for( int i=0; i<timestampArray.length; i++ ) {
> + byte[][] tuple = new byte[2][0];
> + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index
> + tuple[1] = timestampArray[i].toString().getBytes(); // Value
> + rows.addElement(tuple);
> + }
> + break;
> +
> + // Other datatypes not currently supported. If you are really using other types ask
> + // yourself if an array of non-trivial data types is really good database design.
> + default:
> + throw org.postgresql.Driver.notImplemented();
> + }
> + return new ResultSet((org.postgresql.jdbc2.Connection)conn, fields, rows, "OK", 1 );
> + }
> +}
> +
> diff -bBdNrw -U5 OLD/jdbc2/ResultSet.java NEW/jdbc2/ResultSet.java
> --- OLD/jdbc2/ResultSet.java Tue May 22 07:46:46 2001
> +++ NEW/jdbc2/ResultSet.java Tue Aug 7 12:54:44 2001
> @@ -931,18 +931,21 @@
> current_row = 0;
> this_row = (byte [][])rows.elementAt(current_row);
> return true;
> }
>
> - public Array getArray(String colName) throws SQLException
> + public java.sql.Array getArray(String colName) throws SQLException
> {
> return getArray(findColumn(colName));
> }
>
> - public Array getArray(int i) throws SQLException
> + public java.sql.Array getArray(int i) throws SQLException
> {
> - throw org.postgresql.Driver.notImplemented();
> + if (i < 1 || i > fields.length)
> + throw new PSQLException("postgresql.res.colrange");
> + first();
> + return (java.sql.Array) new org.postgresql.jdbc2.Array( connection, i, fields[i-1], this );
> }
>
> public java.math.BigDecimal getBigDecimal(int columnIndex) throws SQLException
> {
> // Now must call BigDecimal with a scale otherwise JBuilder barfs
>
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
> /bjm/7
>
> Content-Type:
>
> text/plain
> Content-Encoding:
>
> 7bit
>
>
> ------------------------------------------------------------------------
> Part 1.3
>
> Content-Type:
>
> text/plain
> Content-Encoding:
>
> binary
>
>
Greg,
I have a couple of comments on this patch. First off thanks for working
on adding Array support to the jdbc driver.
I think this code would be much better if it didn't end up duplicating
logic that was coded elsewhere. In particular the logic that knows how
to convert the String representation of a particular datatype into the
corresponding object currently exists in ResultSet. It appears that you
have duplicated that logic here in Array. Especially when it comes to
the timestamp logic that is a bad idea, as the timestamp logic has
already changed many times this year, having two copies that need to be
kept in sync is asking for trouble. I think somehow this needs to be
centralized.
I also don't like how you have hardcoded the OID values for that basic
datatypes as constants in Array. The rest of the code goes to great
lengths not to hardcode these values in case they ever change. I think
your code should do likewise. Perhaps expanding the Field object to
include a little more information would solve the problem.
thanks,
--Barry
Bruce Momjian wrote:
> The problem is that there is wrapping in the patch. Attached is a
> cleaned up version.
>
>
>
>>On Fri, 10 Aug 2001 12:38:43 -0400, Greg Zoller wrote:
>>
>>>patch -p1 < PATH_TO_PATCH_FILE/patch.txt
>>>
>>I'm still getting this:
>>
>>$ patch -p1 <patch.txt
>>patching file errors.properties
>>patch: **** malformed patch at line 5: # This is the default
>>errors
>>
>>Any ideas what may be causing this? I'm on Red Hat Linux 7.1 and
>>patch -version says:
>>
>>patch 2.5.4
>>Copyright 1984-1988 Larry Wall
>>Copyright 1989-1999 Free Software Foundation, Inc.
>>
>>Regards,
>>Ren? Pijlman
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
>>
>
>
> ------------------------------------------------------------------------
>
> diff -bBdNrw -U5 OLD/errors.properties NEW/errors.properties
> --- OLD/errors.properties Thu Jan 18 09:37:13 2001
> +++ NEW/errors.properties Tue Aug 7 12:54:15 2001
> @@ -1,6 +1,7 @@
> # This is the default errors
> +postgresql.arr.range:The array index is out of range. postgresql.drv.version:An internal error has occured. Please
recompilethe driver. postgresql.con.auth:The authentication type {0} is not supported. Check that you have configured
thepg_hba.conf file to include the client's IP address or Subnet, and that it is using an authentication scheme
supportedby the driver. postgresql.con.authfail:An error occured while getting the authentication request.
postgresql.con.call:CallableStatements are not supported at this time. postgresql.con.creobj:Failed to create object
for{0} {1}
> diff -bBdNrw -U5 OLD/jdbc2/Array.java NEW/jdbc2/Array.java
> --- OLD/jdbc2/Array.java Wed Dec 31 16:00:00 1969
> +++ NEW/jdbc2/Array.java Fri Aug 10 12:25:22 2001
> @@ -0,0 +1,494 @@
> +package org.postgresql.jdbc2;
> +
> +import java.text.*;
> +import java.sql.*;
> +import java.util.*;
> +import java.math.BigDecimal;
> +import org.postgresql.Field;
> +import org.postgresql.util.*;
> +
> +/**
> + * 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.
> + *
> + * @see ResultSet#getArray
> + */
> +
> +
> +public class Array implements java.sql.Array
> +{
> + public static final int BOOLEAN_ARRAY = 1000;
> + public static final int CHAR_ARRAY = 1002;
> + public static final int INT2_ARRAY = 1005;
> + public static final int INT4_ARRAY = 1007;
> + public static final int INT8_ARRAY = 1016;
> + public static final int VARCHAR_ARRAY = 1015;
> + public static final int FLOAT4_ARRAY = 1021;
> + public static final int FLOAT8_ARRAY = 1022;
> + public static final int DATE_ARRAY = 1182;
> + public static final int TIME_ARRAY = 1183;
> + public static final int TIMESTAMP_ARRAY = 1185;
> + public static final int NUMERIC_ARRAY = 1231;
> + //------------------------------------------
> + public static final int BOOLEAN = 16;
> + public static final int CHAR = 18;
> + public static final int INT2 = 21;
> + public static final int INT4 = 23;
> + public static final int INT8 = 20;
> + public static final int VARCHAR = 1043;
> + public static final int FLOAT4 = 700;
> + public static final int FLOAT8 = 701;
> + public static final int DATE = 1082;
> + public static final int TIME = 1083;
> + public static final int TIMESTAMP = 1184;
> + public static final int NUMERIC = 1700;
> +
> + private org.postgresql.Connection conn = null;
> + private org.postgresql.Field field = null;
> + private org.postgresql.jdbc2.ResultSet rs = null;
> + private int idx = 0;
> +
> + /**
> + * 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
> + */
> + public Array( org.postgresql.Connection conn, int idx, Field field, org.postgresql.jdbc2.ResultSet rs ) {
> + this.conn = conn;
> + this.field = field;
> + this.rs = rs;
> + this.idx = idx;
> + }
> +
> + public Object getArray() throws SQLException {
> + return getArray( 1, 0, null );
> + }
> +
> + public Object getArray(long index, int count) throws SQLException {
> + return getArray( index, count, null );
> + }
> +
> + public Object getArray(Map map) throws SQLException {
> + return getArray( 1, 0, map );
> + }
> +
> + public Object getArray(long index, int count, Map map) throws SQLException {
> + if( map != null ) // For now maps aren't supported.
> + throw org.postgresql.Driver.notImplemented();
> +
> + if (index < 1)
> + throw new PSQLException("postgresql.arr.range");
> + Object retVal = null;
> +
> + ArrayList array = new ArrayList();
> + String raw = rs.getFixedString(idx);
> + if( raw != null ) {
> + char[] chars = raw.toCharArray();
> + StringBuffer sbuf = new StringBuffer();
> + boolean foundOpen = false;
> + boolean insideString = false;
> + for( int i=0; i<chars.length; i++ ) {
> + if( chars[i] == '{' ) {
> + if( foundOpen ) // Only supports 1-D arrays for now
> + throw org.postgresql.Driver.notImplemented();
> + foundOpen = true;
> + continue;
> + }
> + if( chars[i] == '"' ) {
> + insideString = !insideString;
> + continue;
> + }
> + if( (!insideString && chars[i] == ',') || chars[i] == '}' || i == chars.length-1) {
> + if( chars[i] != '"' && chars[i] != '}' && chars[i] != ',' )
> + sbuf.append(chars[i]);
> + array.add( sbuf.toString() );
> + sbuf = new StringBuffer();
> + continue;
> + }
> + sbuf.append( chars[i] );
> + }
> + }
> + String[] arrayContents = (String[]) array.toArray( new String[array.size()] );
> + if( count == 0 )
> + count = arrayContents.length;
> + index--;
> + if( index+count > arrayContents.length )
> + throw new PSQLException("postgresql.arr.range");
> +
> + int i = 0;
> + switch (field.getOID())
> + {
> + case BOOLEAN:
> + case BOOLEAN_ARRAY:
> + retVal = new boolean[ count ];
> + for( ; count > 0; count-- ) {
> + String s = arrayContents[(int)index++];
> + try
> + {
> + char c = s.charAt(0);
> + ((boolean[])retVal)[i++] = ((c == 't') || (c == 'T'));
> + } catch (NumberFormatException e) {
> + throw new PSQLException ("postgresql.res.badbyte",s);
> + }
> + }
> + break;
> + case INT2:
> + case INT4:
> + case INT2_ARRAY:
> + case INT4_ARRAY:
> + retVal = new int[ count ];
> + for( ; count > 0; count-- ) {
> + String s = arrayContents[(int)index++];
> + try
> + {
> + ((int[])retVal)[i++] = Integer.parseInt( s );
> + } catch (NumberFormatException e) {
> + throw new PSQLException ("postgresql.res.badint",s);
> + }
> + }
> + break;
> + case INT8:
> + case INT8_ARRAY:
> + retVal = new long[ count ];
> + for( ; count > 0; count-- ) {
> + String s = arrayContents[(int)index++];
> + try
> + {
> + ((long[])retVal)[i++] = Long.parseLong(s);
> + } catch (NumberFormatException e) {
> + throw new PSQLException ("postgresql.res.badlong",s);
> + }
> + }
> + break;
> + case NUMERIC:
> + case NUMERIC_ARRAY:
> + retVal = new BigDecimal[ count ];
> + for( ; count > 0; count-- ) {
> + String s = arrayContents[(int)index++];
> + try
> + {
> + ((BigDecimal[])retVal)[i] = new BigDecimal(s);
> + ((BigDecimal[])retVal)[i++].setScale(0);
> + } catch (NumberFormatException e) {
> + throw new PSQLException ("postgresql.res.badbigdec",s);
> + } catch (ArithmeticException e) {
> + throw new PSQLException ("postgresql.res.badbigdec",s);
> + }
> + }
> + break;
> + case FLOAT4:
> + case FLOAT4_ARRAY:
> + retVal = new float[ count ];
> + for( ; count > 0; count-- ) {
> + String s = arrayContents[(int)index++];
> + try
> + {
> + ((float[])retVal)[i++] = Float.parseFloat(s);
> + } catch (NumberFormatException e) {
> + throw new PSQLException ("postgresql.res.badfloat",s);
> + }
> + }
> + break;
> + case FLOAT8:
> + case FLOAT8_ARRAY:
> + retVal = new double[ count ];
> + for( ; count > 0; count-- ) {
> + String s = arrayContents[(int)index++];
> + try
> + {
> + ((double[])retVal)[i++] = Double.parseDouble(s);
> + } catch (NumberFormatException e) {
> + throw new PSQLException ("postgresql.res.baddouble",s);
> + }
> + }
> + break;
> + case CHAR:
> + case VARCHAR:
> + case CHAR_ARRAY:
> + case VARCHAR_ARRAY:
> + retVal = new String[ count ];
> + for( ; count > 0; count-- )
> + ((String[])retVal)[i++] = arrayContents[(int)index++];
> + break;
> + case DATE:
> + case DATE_ARRAY:
> + retVal = new java.sql.Date[ count ];
> + for( ; count > 0; count-- ) {
> + if( arrayContents[(int)index] == null )
> + ((java.sql.Date[])retVal)[i++] = null;
> + else
> + ((java.sql.Date[])retVal)[i++] = java.sql.Date.valueOf(arrayContents[(int)index] );
> + index++;
> + }
> + break;
> + case TIME:
> + case TIME_ARRAY:
> + retVal = new java.sql.Time[ count ];
> + for( ; count > 0; count-- ) {
> + if( arrayContents[(int)index] == null )
> + ((java.sql.Time[])retVal)[i++] = null;
> + else
> + ((java.sql.Time[])retVal)[i++] = java.sql.Time.valueOf(arrayContents[(int)index] );
> + index++;
> + }
> + break;
> + case TIMESTAMP:
> + case TIMESTAMP_ARRAY:
> + retVal = new Timestamp[ count ];
> + StringBuffer sbuf = null;
> + for( ; count > 0; count-- ) {
> + if( arrayContents[(int)index] == null ) {
> + ((java.sql.Timestamp[])retVal)[i++] = null;
> + index++;
> + continue;
> + }
> + boolean subsecond = true;;
> + //if string contains a '.' we have fractional seconds
> + if (arrayContents[i].indexOf('.') == -1)
> + subsecond = false;
> +
> + //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 Array
> + // bad practice but possible. Anyhow this is to protect sbuf and
> + // SimpleDateFormat objects
> +
> + // First time?
> + if(sbuf==null)
> + sbuf = new StringBuffer();
> +
> + String s = arrayContents[(int)index++];
> + sbuf.setLength(0);
> + sbuf.append(s);
> +
> + char sub = sbuf.charAt(sbuf.length()-3);
> + if (sub == '+' || sub == '-') {
> + 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 (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 {
> + ((java.sql.Timestamp[])retVal)[i++] = new
Timestamp(df.parse(sbuf.toString()).getTime());
> + } catch(ParseException e) {
> + throw new PSQLException("postgresql.res.badtimestamp",new
Integer(e.getErrorOffset()),s);
> + }
> + }
> + }
> + break;
> +
> + // Other datatypes not currently supported. If you are really using other types ask
> + // yourself if an array of non-trivial data types is really good database design.
> + default:
> + throw org.postgresql.Driver.notImplemented();
> + }
> + return retVal;
> + }
> +
> + public int getBaseType() throws SQLException {
> + switch( field.getOID() ) {
> + case BOOLEAN_ARRAY: return Field.getSQLType("bool");
> + case CHAR_ARRAY: return Field.getSQLType("char");
> + case INT2_ARRAY: return Field.getSQLType("int2");
> + case INT4_ARRAY: return Field.getSQLType("int4");
> + case VARCHAR_ARRAY: return Field.getSQLType("varchar");
> + case INT8_ARRAY: return Field.getSQLType("int8");
> + case FLOAT4_ARRAY: return Field.getSQLType("float4");
> + case FLOAT8_ARRAY: return Field.getSQLType("float8");
> + case DATE_ARRAY: return Field.getSQLType("date");
> + case TIME_ARRAY: return Field.getSQLType("time");
> + case TIMESTAMP_ARRAY: return Field.getSQLType("timestamp");
> + case NUMERIC_ARRAY: return Field.getSQLType("numeric");
> + default:
> + throw org.postgresql.Driver.notImplemented();
> + }
> + }
> +
> + public String getBaseTypeName() throws SQLException {
> + switch( field.getOID() ) {
> + case BOOLEAN_ARRAY: return "bool";
> + case CHAR_ARRAY: return "char";
> + case INT2_ARRAY: return "int2";
> + case INT4_ARRAY: return "int4";
> + case VARCHAR_ARRAY: return "varchar";
> + case INT8_ARRAY: return "int8";
> + case FLOAT4_ARRAY: return "float4";
> + case FLOAT8_ARRAY: return "float8";
> + case DATE_ARRAY: return "date";
> + case TIME_ARRAY: return "time";
> + case TIMESTAMP_ARRAY: return "timestamp";
> + case NUMERIC_ARRAY: return "numeric";
> + default:
> + throw org.postgresql.Driver.notImplemented();
> + }
> + }
> +
> + public java.sql.ResultSet getResultSet() throws SQLException {
> + return getResultSet( 1, 0, null );
> + }
> +
> + public java.sql.ResultSet getResultSet(long index, int count) throws SQLException {
> + return getResultSet( index, count, null );
> + }
> +
> + public java.sql.ResultSet getResultSet(Map map) throws SQLException {
> + return getResultSet( 1, 0, map );
> + }
> +
> + public java.sql.ResultSet getResultSet(long index, int count, java.util.Map map) throws SQLException {
> + Object array = getArray( index, count, map );
> + Vector rows = new Vector();
> + Field[] fields = new Field[2];
> + fields[0] = new Field(conn, "INDEX", INT2, 2);
> + switch (field.getOID() )
> + {
> + case BOOLEAN_ARRAY:
> + boolean[] booleanArray = (boolean[]) array;
> + fields[1] = new Field(conn, "VALUE", BOOLEAN, 1);
> + for( int i=0; i<booleanArray.length; i++ ) {
> + byte[][] tuple = new byte[2][0];
> + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index
> + tuple[1] = (booleanArray[i]?"YES":"NO").getBytes(); // Value
> + rows.addElement(tuple);
> + }
> + case INT2_ARRAY:
> + fields[1] = new Field(conn, "VALUE", INT2, 2);
> + case INT4_ARRAY:
> + int[] intArray = (int[]) array;
> + if( fields[1] == null )
> + fields[1] = new Field(conn, "VALUE", INT4, 4);
> + for( int i=0; i<intArray.length; i++ ) {
> + byte[][] tuple = new byte[2][0];
> + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index
> + tuple[1] = Integer.toString(intArray[i]).getBytes(); // Value
> + rows.addElement(tuple);
> + }
> + break;
> + case INT8_ARRAY:
> + long[] longArray = (long[]) array;
> + fields[1] = new Field(conn, "VALUE", INT8, 8);
> + for( int i=0; i<longArray.length; i++ ) {
> + byte[][] tuple = new byte[2][0];
> + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index
> + tuple[1] = Long.toString(longArray[i]).getBytes(); // Value
> + rows.addElement(tuple);
> + }
> + break;
> + case NUMERIC_ARRAY:
> + BigDecimal[] bdArray = (BigDecimal[]) array;
> + fields[1] = new Field(conn, "VALUE", NUMERIC, -1);
> + for( int i=0; i<bdArray.length; i++ ) {
> + byte[][] tuple = new byte[2][0];
> + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index
> + tuple[1] = bdArray[i].toString().getBytes(); // Value
> + rows.addElement(tuple);
> + }
> + break;
> + case FLOAT4_ARRAY:
> + float[] floatArray = (float[]) array;
> + fields[1] = new Field(conn, "VALUE", FLOAT4, 4);
> + for( int i=0; i<floatArray.length; i++ ) {
> + byte[][] tuple = new byte[2][0];
> + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index
> + tuple[1] = Float.toString(floatArray[i]).getBytes(); // Value
> + rows.addElement(tuple);
> + }
> + break;
> + case FLOAT8_ARRAY:
> + double[] doubleArray = (double[]) array;
> + fields[1] = new Field(conn, "VALUE", FLOAT8, 8);
> + for( int i=0; i<doubleArray.length; i++ ) {
> + byte[][] tuple = new byte[2][0];
> + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index
> + tuple[1] = Double.toString(doubleArray[i]).getBytes(); // Value
> + rows.addElement(tuple);
> + }
> + break;
> + case CHAR_ARRAY:
> + fields[1] = new Field(conn, "VALUE", CHAR, 1);
> + case VARCHAR_ARRAY:
> + String[] strArray = (String[]) array;
> + if( fields[1] == null )
> + fields[1] = new Field(conn, "VALUE", VARCHAR, -1);
> + for( int i=0; i<strArray.length; i++ ) {
> + byte[][] tuple = new byte[2][0];
> + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index
> + tuple[1] = strArray[i].getBytes(); // Value
> + rows.addElement(tuple);
> + }
> + break;
> + case DATE_ARRAY:
> + java.sql.Date[] dateArray = (java.sql.Date[]) array;
> + fields[1] = new Field(conn, "VALUE", DATE, 4);
> + for( int i=0; i<dateArray.length; i++ ) {
> + byte[][] tuple = new byte[2][0];
> + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index
> + tuple[1] = dateArray[i].toString().getBytes(); // Value
> + rows.addElement(tuple);
> + }
> + break;
> + case TIME_ARRAY:
> + java.sql.Time[] timeArray = (java.sql.Time[]) array;
> + fields[1] = new Field(conn, "VALUE", TIME, 8);
> + for( int i=0; i<timeArray.length; i++ ) {
> + byte[][] tuple = new byte[2][0];
> + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index
> + tuple[1] = timeArray[i].toString().getBytes(); // Value
> + rows.addElement(tuple);
> + }
> + break;
> + case TIMESTAMP_ARRAY:
> + java.sql.Timestamp[] timestampArray = (java.sql.Timestamp[]) array;
> + fields[1] = new Field(conn, "VALUE", TIMESTAMP, 8);
> + for( int i=0; i<timestampArray.length; i++ ) {
> + byte[][] tuple = new byte[2][0];
> + tuple[0] = Integer.toString((int)index+i).getBytes(); // Index
> + tuple[1] = timestampArray[i].toString().getBytes(); // Value
> + rows.addElement(tuple);
> + }
> + break;
> +
> + // Other datatypes not currently supported. If you are really using other types ask
> + // yourself if an array of non-trivial data types is really good database design.
> + default:
> + throw org.postgresql.Driver.notImplemented();
> + }
> + return new ResultSet((org.postgresql.jdbc2.Connection)conn, fields, rows, "OK", 1 );
> + }
> +}
> +
> diff -bBdNrw -U5 OLD/jdbc2/ResultSet.java NEW/jdbc2/ResultSet.java
> --- OLD/jdbc2/ResultSet.java Tue May 22 07:46:46 2001
> +++ NEW/jdbc2/ResultSet.java Tue Aug 7 12:54:44 2001
> @@ -931,18 +931,21 @@
> current_row = 0;
> this_row = (byte [][])rows.elementAt(current_row);
> return true;
> }
>
> - public Array getArray(String colName) throws SQLException
> + public java.sql.Array getArray(String colName) throws SQLException
> {
> return getArray(findColumn(colName));
> }
>
> - public Array getArray(int i) throws SQLException
> + public java.sql.Array getArray(int i) throws SQLException
> {
> - throw org.postgresql.Driver.notImplemented();
> + if (i < 1 || i > fields.length)
> + throw new PSQLException("postgresql.res.colrange");
> + first();
> + return (java.sql.Array) new org.postgresql.jdbc2.Array( connection, i, fields[i-1], this );
> }
>
> public java.math.BigDecimal getBigDecimal(int columnIndex) throws SQLException
> {
> // Now must call BigDecimal with a scale otherwise JBuilder barfs
>
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
> /bjm/7
>
> Content-Type:
>
> text/plain
> Content-Encoding:
>
> 7bit
>
>
> ------------------------------------------------------------------------
> Part 1.3
>
> Content-Type:
>
> text/plain
> Content-Encoding:
>
> binary
>
>
Barry... Yes I know there is some code duplication from the ResultSet class. I thought about several options and chose this one. I can easily rework it to one of the following options: 1) Break the formatting code out of ResultSet into a utility class having a bunch of static methods. 2) Create new static methods in ResultSet that format the data. Wasn't sure how this would be received since it added methods to the java.sql.ResultSet interface implementation. (i.e. how "pure" should the ResultSet class be?) As for the OID issue, I followed the DatabaseMetaData.java example in hardcoding OIDs. I did this because the proper SQL type for any array is simply ARRAY (not very useful). I will look into whether the Field object can return the proper '_' prefixed type name with minimal changes (eg. "_int4" is the type name for an array of int4). I could also invent non-standard specific array SQL types (ARRAY_INT4, etc.) but I don't like non-standard. It's really a shame that SQL types aren't more specific with arrays. Your thoughts? Thanks Greg _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp