Re: JDBC Array Support, Take 2

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(at)postgresql(dot)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 recompile the driver. postgresql.con.auth:The authentication type {0} is not supported. Check that you have configured the pg_hba.conf file to include 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. postgresql.con.call:Callable Statements 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
>
>