Thread: patch - support for multi-dimensional arrays and NULL values
Hello Kris and hello group,
in the attachment you can find AbstractJdbc2Array class with two fixed
features: null value as element of an array and... multi-dimensional
arrays support. There are two things related with this two changes:
- all arrays are objects arrays (which means that e.g. boolean[] is
represented as Boolean[])
- when multi-dimensional array is processed then Object[] is returned by
getArray() - it's because we can't declare in Java an array of unknown
dimension (so if PostgreSQL returns char[][] then jdbc will return
Object[], that contains String[] elements)
Sorry that I didn't send patch file, but I didn't have time to install
cvs client (I'm using svn in everyday work).
In case of questions or any other discussion write me an email.
Best regards,
Marek Lewczuk
/*-------------------------------------------------------------------------
*
* Copyright (c) 2004-2005, PostgreSQL Global Development Group
*
* IDENTIFICATION
* $PostgreSQL: pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Array.java,v 1.18 2005/12/04 21:40:33 jurka Exp $
*
*-------------------------------------------------------------------------
*/
package org.postgresql.jdbc2;
import org.postgresql.core.*;
import org.postgresql.util.PSQLException;
import org.postgresql.util.PSQLState;
import org.postgresql.util.GT;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Map;
import java.util.Vector;
import java.util.GregorianCalendar;
/**
* 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 AbstractJdbc2Array
{
private BaseConnection conn = null;
private Field field = null;
private BaseResultSet rs;
private int idx = 0;
private String rawString = null;
/**
* 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 AbstractJdbc2Array(BaseConnection conn, int idx, Field field, BaseResultSet rs )
throws SQLException
{
this.conn = conn;
this.field = field;
this.rs = rs;
this.idx = idx;
this.rawString = rs.getFixedString(idx);
}
public Object getArray() throws SQLException
{
return getArrayImpl( 1, 0, null );
}
public Object getArray(long index, int count) throws SQLException
{
return getArrayImpl( index, count, null );
}
public Object getArrayImpl(Map map) throws SQLException
{
return getArrayImpl( 1, 0, map );
}
public Object getArrayImpl(long index, int count, Map map) throws SQLException
{
if ( map != null && !map.isEmpty()) // For now maps aren't supported.
throw org.postgresql.Driver.notImplemented(this.getClass(), "getArrayImpl(long,int,Map)");
if (index < 1)
throw new PSQLException(GT.tr("The array index is out of range: {0}", new Long(index)),
PSQLState.DATA_ERROR);
Object retVal = null;
ArrayList array = new ArrayList();
/* Check if the String is also not an empty array
* otherwise there will be an exception thrown below
* in the ResultSet.toX with an empty string.
* -- Doug Fields <dfields-pg-jdbc@pexicom.com> Feb 20, 2002 */
if ( rawString != null && !rawString.equals("{}") )
{
char[] chars = rawString.toCharArray();
StringBuffer sbuf = new StringBuffer();
boolean insideString = false;
boolean wasInsideString = false; // needed for checking if NULL value occured
Vector dims = new Vector(); // array dimension arrays
ArrayList curArray = array; // currently processed array
/**
* Starting with 8.0 non-standard (beginning index
* isn't 1) bounds the dimensions are returned in the
* data formatted like so "[0:3]={0,1,2,3,4}".
* Older versions simply do not return the bounds.
*
* Right now we ignore these bounds, but we could
* consider allowing these index values to be used
* even though the JDBC spec says 1 is the first
* index. I'm not sure what a client would like
* to see, so we just retain the old behavior.
*/
int startOffset = 0;
if (chars[0] == '[')
{
while (chars[startOffset] != '=')
{
startOffset++;
}
startOffset++; // skip =
}
for ( int i = startOffset; i < chars.length; i++ )
{
if ( chars[i] == '\\' )
//escape character that we need to skip
i++;
else if (!insideString && chars[i] == '{' )
{
if (dims.size() == 0) dims.add(array);
else {
ArrayList a = new ArrayList();
((ArrayList) dims.lastElement()).add(a);
dims.add(a);
}
curArray = (ArrayList) dims.lastElement();
sbuf = new StringBuffer();
continue;
}
else if (chars[i] == '"')
{
insideString = !insideString;
wasInsideString = true;
continue;
}
else if (!insideString && (chars[i] == ',' || chars[i] == '}') ||
i == chars.length - 1)
{
if ( chars[i] != '"' && chars[i] != '}' && chars[i] != ',' && sbuf != null)
sbuf.append(chars[i]);
String b = sbuf == null ? null : sbuf.toString();
if (b != null) curArray.add(wasInsideString == false && b.equals("NULL") ? null : b);
wasInsideString = false;
sbuf = new StringBuffer();
if (chars[i] == '}') {
dims.remove(dims.size() - 1);
if (dims.size() > 0) curArray = (ArrayList) dims.lastElement();
sbuf = null;
}
continue;
}
if (sbuf != null) sbuf.append( chars[i] );
}
}
// convert
if ( count == 0 )
count = array.size();
index--;
if ( index + count > array.size() )
throw new PSQLException(GT.tr("The array index is out of range: {0}, number of elements: {1}.", new
Object[]{newLong(index + count), new Long(array.size())}), PSQLState.DATA_ERROR);
return buildArray(array, (int) index, count);
}
/**
* Convert ArrayList to array.
*/
private Object[] buildArray (ArrayList _input, int index, int count) throws SQLException {
int i = 0;
if (count == -1) count = _input.size();
Object[] retVal = null;
boolean multi = false;
for (int z = 0; z < count; z++) {
Object x = _input.get(z);
if (x == null) continue;
else if (x instanceof ArrayList) multi = true;
break;
}
switch ( getBaseType() )
{
case Types.BIT:
retVal = multi ? new Object[count] : new Boolean[count];
for ( ; count > 0; count-- ) {
Object v = _input.get(index++);
retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) :
AbstractJdbc2ResultSet.toBoolean((String)v);
}
break;
case Types.SMALLINT:
case Types.INTEGER:
retVal = multi ? new Object[count] : new Integer[count];
for ( ; count > 0; count-- ) {
Object v = _input.get(index++);
retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) :
AbstractJdbc2ResultSet.toInt((String)v);
}
break;
case Types.BIGINT:
retVal = multi ? new Object[count] : new Long[count];
for ( ; count > 0; count-- ) {
Object v = _input.get(index++);
retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) :
AbstractJdbc2ResultSet.toLong((String)v);
}
break;
case Types.NUMERIC:
retVal = multi ? new Object[count] : new BigDecimal[count];
for ( ; count > 0; count-- ) {
Object v = _input.get(index++);
retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) :
AbstractJdbc2ResultSet.toBigDecimal((String)v, -1);
}
break;
case Types.REAL:
retVal = multi ? new Object[count] : new Float[count];
for ( ; count > 0; count-- ) {
Object v = _input.get(index++);
retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) :
AbstractJdbc2ResultSet.toFloat((String)v);
}
break;
case Types.DOUBLE:
retVal = multi ? new Object[count] : new Double[count];
for ( ; count > 0; count-- ) {
Object v = _input.get(index++);
retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) :
AbstractJdbc2ResultSet.toDouble((String)v);
}
break;
case Types.CHAR:
case Types.VARCHAR:
retVal = multi ? new Object[count] : new String[count];
for ( ; count > 0; count-- ) {
Object v = _input.get(index++);
retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) : v;
}
break;
case Types.DATE:
retVal = multi ? new Object[count] : new java.sql.Date[count];
for ( ; count > 0; count-- ) {
Object v = _input.get(index++);
retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) :
conn.getTimestampUtils().toDate(null,(String) v);
}
break;
case Types.TIME:
retVal = multi ? new Object[count] : new java.sql.Time[count];
for ( ; count > 0; count-- ) {
Object v = _input.get(index++);
retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) :
conn.getTimestampUtils().toTime(null,(String) v);
}
break;
case Types.TIMESTAMP:
retVal = multi ? new Object[count] : new java.sql.Timestamp[count];
for ( ; count > 0; count-- ) {
Object v = _input.get(index++);
retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) :
conn.getTimestampUtils().toTimestamp(null,(String) v);
}
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:
if (conn.getLogger().logDebug())
conn.getLogger().debug("getArrayImpl(long,int,Map) with "+getBaseTypeName());
throw org.postgresql.Driver.notImplemented(this.getClass(), "getArrayImpl(long,int,Map)");
}
return retVal;
}
public int getBaseType() throws SQLException
{
return conn.getSQLType(getBaseTypeName());
}
public String getBaseTypeName() throws SQLException
{
String fType = conn.getPGType(field.getOID());
if ( fType.charAt(0) == '_' )
fType = fType.substring(1);
return fType;
}
public java.sql.ResultSet getResultSet() throws SQLException
{
return getResultSetImpl( 1, 0, null );
}
public java.sql.ResultSet getResultSet(long index, int count) throws SQLException
{
return getResultSetImpl( index, count, null );
}
public java.sql.ResultSet getResultSetImpl(Map map) throws SQLException
{
return getResultSetImpl( 1, 0, map );
}
private void fillIntegerResultSet(long index, int[] intArray, Vector rows) throws SQLException
{
for ( int i = 0; i < intArray.length; i++ )
{
byte[][] tuple = new byte[2][0];
tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
tuple[1] = conn.encodeString( Integer.toString(intArray[i]) ); // Value
rows.addElement(tuple);
}
}
private void fillStringResultSet(long index, String[] strArray, Vector rows) throws SQLException
{
for ( int i = 0; i < strArray.length; i++ )
{
byte[][] tuple = new byte[2][0];
tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
tuple[1] = conn.encodeString( strArray[i] ); // Value
rows.addElement(tuple);
}
}
public java.sql.ResultSet getResultSetImpl(long index, int count, java.util.Map map) throws SQLException
{
Object array = getArrayImpl( index, count, map );
Vector rows = new Vector();
Field[] fields = new Field[2];
fields[0] = new Field("INDEX", Oid.INT2);
switch ( getBaseType() )
{
case Types.BIT:
boolean[] booleanArray = (boolean[]) array;
fields[1] = new Field("VALUE", Oid.BOOL);
for ( int i = 0; i < booleanArray.length; i++ )
{
byte[][] tuple = new byte[2][0];
tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
tuple[1] = conn.encodeString( (booleanArray[i] ? "YES" : "NO") ); // Value
rows.addElement(tuple);
}
break;
case Types.SMALLINT:
fields[1] = new Field("VALUE", Oid.INT2);
fillIntegerResultSet(index, (int[])array, rows);
break;
case Types.INTEGER:
fields[1] = new Field("VALUE", Oid.INT4);
fillIntegerResultSet(index, (int[])array, rows);
break;
case Types.BIGINT:
long[] longArray = (long[]) array;
fields[1] = new Field("VALUE", Oid.INT8);
for ( int i = 0; i < longArray.length; i++ )
{
byte[][] tuple = new byte[2][0];
tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
tuple[1] = conn.encodeString( Long.toString(longArray[i]) ); // Value
rows.addElement(tuple);
}
break;
case Types.NUMERIC:
BigDecimal[] bdArray = (BigDecimal[]) array;
fields[1] = new Field("VALUE", Oid.NUMERIC);
for ( int i = 0; i < bdArray.length; i++ )
{
byte[][] tuple = new byte[2][0];
tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
tuple[1] = conn.encodeString( bdArray[i].toString() ); // Value
rows.addElement(tuple);
}
break;
case Types.REAL:
float[] floatArray = (float[]) array;
fields[1] = new Field("VALUE", Oid.FLOAT4);
for ( int i = 0; i < floatArray.length; i++ )
{
byte[][] tuple = new byte[2][0];
tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
tuple[1] = conn.encodeString( Float.toString(floatArray[i]) ); // Value
rows.addElement(tuple);
}
break;
case Types.DOUBLE:
double[] doubleArray = (double[]) array;
fields[1] = new Field("VALUE", Oid.FLOAT8);
for ( int i = 0; i < doubleArray.length; i++ )
{
byte[][] tuple = new byte[2][0];
tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
tuple[1] = conn.encodeString( Double.toString(doubleArray[i]) ); // Value
rows.addElement(tuple);
}
break;
case Types.CHAR:
fields[1] = new Field("VALUE", Oid.BPCHAR);
fillStringResultSet(index, (String[])array, rows);
break;
case Types.VARCHAR:
fields[1] = new Field("VALUE", Oid.VARCHAR);
fillStringResultSet(index, (String[])array, rows);
break;
case Types.DATE:
java.sql.Date[] dateArray = (java.sql.Date[]) array;
fields[1] = new Field("VALUE", Oid.DATE);
for ( int i = 0; i < dateArray.length; i++ )
{
byte[][] tuple = new byte[2][0];
tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
tuple[1] = conn.encodeString( conn.getTimestampUtils().toString(null, dateArray[i]) ); // Value
rows.addElement(tuple);
}
break;
case Types.TIME:
java.sql.Time[] timeArray = (java.sql.Time[]) array;
fields[1] = new Field("VALUE", Oid.TIME);
for ( int i = 0; i < timeArray.length; i++ )
{
byte[][] tuple = new byte[2][0];
tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
tuple[1] = conn.encodeString( conn.getTimestampUtils().toString(null, timeArray[i]) ); // Value
rows.addElement(tuple);
}
break;
case Types.TIMESTAMP:
java.sql.Timestamp[] timestampArray = (java.sql.Timestamp[]) array;
fields[1] = new Field("VALUE", Oid.TIMESTAMPTZ);
for ( int i = 0; i < timestampArray.length; i++ )
{
byte[][] tuple = new byte[2][0];
tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
tuple[1] = conn.encodeString( conn.getTimestampUtils().toString(null, timestampArray[i]) ); // 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:
if (conn.getLogger().logDebug())
conn.getLogger().debug("getResultSetImpl(long,int,Map) with "+getBaseTypeName());
throw org.postgresql.Driver.notImplemented(this.getClass(), "getResultSetImpl(long,int,Map)");
}
BaseStatement stat = (BaseStatement) conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
return (ResultSet) stat.createDriverResultSet(fields, rows);
}
public String toString()
{
return rawString;
}
}
Marek Lewczuk pisze:
> Hello Kris and hello group,
> in the attachment you can find AbstractJdbc2Array class with two fixed
> features: null value as element of an array and... multi-dimensional
> arrays support. There are two things related with this two changes:
> - all arrays are objects arrays (which means that e.g. boolean[] is
> represented as Boolean[])
> - when multi-dimensional array is processed then Object[] is returned by
> getArray() - it's because we can't declare in Java an array of unknown
> dimension (so if PostgreSQL returns char[][] then jdbc will return
> Object[], that contains String[] elements)
>
> Sorry that I didn't send patch file, but I didn't have time to install
> cvs client (I'm using svn in everyday work).
>
> In case of questions or any other discussion write me an email.
>
> Best regards,
> Marek Lewczuk
Small correction to my previous post - I forgot to include null support
when bulding final arrays. In the attachment corrected file.
/*-------------------------------------------------------------------------
*
* Copyright (c) 2004-2005, PostgreSQL Global Development Group
*
* IDENTIFICATION
* $PostgreSQL: pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Array.java,v 1.18 2005/12/04 21:40:33 jurka Exp $
*
*-------------------------------------------------------------------------
*/
package org.postgresql.jdbc2;
import org.postgresql.core.*;
import org.postgresql.util.PSQLException;
import org.postgresql.util.PSQLState;
import org.postgresql.util.GT;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Map;
import java.util.Vector;
import java.util.GregorianCalendar;
/**
* 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 AbstractJdbc2Array
{
private BaseConnection conn = null;
private Field field = null;
private BaseResultSet rs;
private int idx = 0;
private String rawString = null;
/**
* 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 AbstractJdbc2Array(BaseConnection conn, int idx, Field field, BaseResultSet rs )
throws SQLException
{
this.conn = conn;
this.field = field;
this.rs = rs;
this.idx = idx;
this.rawString = rs.getFixedString(idx);
}
public Object getArray() throws SQLException
{
return getArrayImpl( 1, 0, null );
}
public Object getArray(long index, int count) throws SQLException
{
return getArrayImpl( index, count, null );
}
public Object getArrayImpl(Map map) throws SQLException
{
return getArrayImpl( 1, 0, map );
}
public Object getArrayImpl(long index, int count, Map map) throws SQLException
{
if ( map != null && !map.isEmpty()) // For now maps aren't supported.
throw org.postgresql.Driver.notImplemented(this.getClass(), "getArrayImpl(long,int,Map)");
if (index < 1)
throw new PSQLException(GT.tr("The array index is out of range: {0}", new Long(index)),
PSQLState.DATA_ERROR);
Object retVal = null;
ArrayList array = new ArrayList();
/* Check if the String is also not an empty array
* otherwise there will be an exception thrown below
* in the ResultSet.toX with an empty string.
* -- Doug Fields <dfields-pg-jdbc@pexicom.com> Feb 20, 2002 */
if ( rawString != null && !rawString.equals("{}") )
{
char[] chars = rawString.toCharArray();
StringBuffer sbuf = new StringBuffer();
boolean insideString = false;
boolean wasInsideString = false; // needed for checking if NULL value occured
Vector dims = new Vector(); // array dimension arrays
ArrayList curArray = array; // currently processed array
/**
* Starting with 8.0 non-standard (beginning index
* isn't 1) bounds the dimensions are returned in the
* data formatted like so "[0:3]={0,1,2,3,4}".
* Older versions simply do not return the bounds.
*
* Right now we ignore these bounds, but we could
* consider allowing these index values to be used
* even though the JDBC spec says 1 is the first
* index. I'm not sure what a client would like
* to see, so we just retain the old behavior.
*/
int startOffset = 0;
if (chars[0] == '[')
{
while (chars[startOffset] != '=')
{
startOffset++;
}
startOffset++; // skip =
}
for ( int i = startOffset; i < chars.length; i++ )
{
if ( chars[i] == '\\' )
//escape character that we need to skip
i++;
else if (!insideString && chars[i] == '{' )
{
if (dims.size() == 0) dims.add(array);
else {
ArrayList a = new ArrayList();
((ArrayList) dims.lastElement()).add(a);
dims.add(a);
}
curArray = (ArrayList) dims.lastElement();
sbuf = new StringBuffer();
continue;
}
else if (chars[i] == '"')
{
insideString = !insideString;
wasInsideString = true;
continue;
}
else if (!insideString && (chars[i] == ',' || chars[i] == '}') ||
i == chars.length - 1)
{
if ( chars[i] != '"' && chars[i] != '}' && chars[i] != ',' && sbuf != null)
sbuf.append(chars[i]);
String b = sbuf == null ? null : sbuf.toString();
if (b != null) curArray.add(wasInsideString == false && b.equals("NULL") ? null : b);
wasInsideString = false;
sbuf = new StringBuffer();
if (chars[i] == '}') {
dims.remove(dims.size() - 1);
if (dims.size() > 0) curArray = (ArrayList) dims.lastElement();
sbuf = null;
}
continue;
}
if (sbuf != null) sbuf.append( chars[i] );
}
}
// convert
if ( count == 0 )
count = array.size();
index--;
if ( index + count > array.size() )
throw new PSQLException(GT.tr("The array index is out of range: {0}, number of elements: {1}.", new
Object[]{newLong(index + count), new Long(array.size())}), PSQLState.DATA_ERROR);
return buildArray(array, (int) index, count);
}
/**
* Convert ArrayList to array.
*/
private Object[] buildArray (ArrayList _input, int index, int count) throws SQLException {
int i = 0;
if (count == -1) count = _input.size();
Object[] retVal = null;
boolean multi = false;
for (int z = 0; z < count; z++) {
Object x = _input.get(z);
if (x == null) continue;
else if (x instanceof ArrayList) multi = true;
break;
}
switch ( getBaseType() )
{
case Types.BIT:
retVal = multi ? new Object[count] : new Boolean[count];
for ( ; count > 0; count-- ) {
Object v = _input.get(index++);
retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) : (v == null ? null :
AbstractJdbc2ResultSet.toBoolean((String)v));
}
break;
case Types.SMALLINT:
case Types.INTEGER:
retVal = multi ? new Object[count] : new Integer[count];
for ( ; count > 0; count-- ) {
Object v = _input.get(index++);
retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) : (v == null ? null :
AbstractJdbc2ResultSet.toInt((String)v));
}
break;
case Types.BIGINT:
retVal = multi ? new Object[count] : new Long[count];
for ( ; count > 0; count-- ) {
Object v = _input.get(index++);
retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) : (v == null ? null :
AbstractJdbc2ResultSet.toLong((String)v));
}
break;
case Types.NUMERIC:
retVal = multi ? new Object[count] : new BigDecimal[count];
for ( ; count > 0; count-- ) {
Object v = _input.get(index++);
retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) : (v == null ? null :
AbstractJdbc2ResultSet.toBigDecimal((String)v, -1));
}
break;
case Types.REAL:
retVal = multi ? new Object[count] : new Float[count];
for ( ; count > 0; count-- ) {
Object v = _input.get(index++);
retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) : (v == null ? null :
AbstractJdbc2ResultSet.toFloat((String)v));
}
break;
case Types.DOUBLE:
retVal = multi ? new Object[count] : new Double[count];
for ( ; count > 0; count-- ) {
Object v = _input.get(index++);
retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) : (v == null ? null :
AbstractJdbc2ResultSet.toDouble((String)v));
}
break;
case Types.CHAR:
case Types.VARCHAR:
retVal = multi ? new Object[count] : new String[count];
for ( ; count > 0; count-- ) {
Object v = _input.get(index++);
retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) : v;
}
break;
case Types.DATE:
retVal = multi ? new Object[count] : new java.sql.Date[count];
for ( ; count > 0; count-- ) {
Object v = _input.get(index++);
retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) : (v == null ? null :
conn.getTimestampUtils().toDate(null,(String) v));
}
break;
case Types.TIME:
retVal = multi ? new Object[count] : new java.sql.Time[count];
for ( ; count > 0; count-- ) {
Object v = _input.get(index++);
retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) : (v == null ? null :
conn.getTimestampUtils().toTime(null,(String) v));
}
break;
case Types.TIMESTAMP:
retVal = multi ? new Object[count] : new java.sql.Timestamp[count];
for ( ; count > 0; count-- ) {
Object v = _input.get(index++);
retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) : (v == null ? null :
conn.getTimestampUtils().toTimestamp(null,(String) v));
}
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:
if (conn.getLogger().logDebug())
conn.getLogger().debug("getArrayImpl(long,int,Map) with "+getBaseTypeName());
throw org.postgresql.Driver.notImplemented(this.getClass(), "getArrayImpl(long,int,Map)");
}
return retVal;
}
public int getBaseType() throws SQLException
{
return conn.getSQLType(getBaseTypeName());
}
public String getBaseTypeName() throws SQLException
{
String fType = conn.getPGType(field.getOID());
if ( fType.charAt(0) == '_' )
fType = fType.substring(1);
return fType;
}
public java.sql.ResultSet getResultSet() throws SQLException
{
return getResultSetImpl( 1, 0, null );
}
public java.sql.ResultSet getResultSet(long index, int count) throws SQLException
{
return getResultSetImpl( index, count, null );
}
public java.sql.ResultSet getResultSetImpl(Map map) throws SQLException
{
return getResultSetImpl( 1, 0, map );
}
private void fillIntegerResultSet(long index, int[] intArray, Vector rows) throws SQLException
{
for ( int i = 0; i < intArray.length; i++ )
{
byte[][] tuple = new byte[2][0];
tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
tuple[1] = conn.encodeString( Integer.toString(intArray[i]) ); // Value
rows.addElement(tuple);
}
}
private void fillStringResultSet(long index, String[] strArray, Vector rows) throws SQLException
{
for ( int i = 0; i < strArray.length; i++ )
{
byte[][] tuple = new byte[2][0];
tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
tuple[1] = conn.encodeString( strArray[i] ); // Value
rows.addElement(tuple);
}
}
public java.sql.ResultSet getResultSetImpl(long index, int count, java.util.Map map) throws SQLException
{
Object array = getArrayImpl( index, count, map );
Vector rows = new Vector();
Field[] fields = new Field[2];
fields[0] = new Field("INDEX", Oid.INT2);
switch ( getBaseType() )
{
case Types.BIT:
boolean[] booleanArray = (boolean[]) array;
fields[1] = new Field("VALUE", Oid.BOOL);
for ( int i = 0; i < booleanArray.length; i++ )
{
byte[][] tuple = new byte[2][0];
tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
tuple[1] = conn.encodeString( (booleanArray[i] ? "YES" : "NO") ); // Value
rows.addElement(tuple);
}
break;
case Types.SMALLINT:
fields[1] = new Field("VALUE", Oid.INT2);
fillIntegerResultSet(index, (int[])array, rows);
break;
case Types.INTEGER:
fields[1] = new Field("VALUE", Oid.INT4);
fillIntegerResultSet(index, (int[])array, rows);
break;
case Types.BIGINT:
long[] longArray = (long[]) array;
fields[1] = new Field("VALUE", Oid.INT8);
for ( int i = 0; i < longArray.length; i++ )
{
byte[][] tuple = new byte[2][0];
tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
tuple[1] = conn.encodeString( Long.toString(longArray[i]) ); // Value
rows.addElement(tuple);
}
break;
case Types.NUMERIC:
BigDecimal[] bdArray = (BigDecimal[]) array;
fields[1] = new Field("VALUE", Oid.NUMERIC);
for ( int i = 0; i < bdArray.length; i++ )
{
byte[][] tuple = new byte[2][0];
tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
tuple[1] = conn.encodeString( bdArray[i].toString() ); // Value
rows.addElement(tuple);
}
break;
case Types.REAL:
float[] floatArray = (float[]) array;
fields[1] = new Field("VALUE", Oid.FLOAT4);
for ( int i = 0; i < floatArray.length; i++ )
{
byte[][] tuple = new byte[2][0];
tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
tuple[1] = conn.encodeString( Float.toString(floatArray[i]) ); // Value
rows.addElement(tuple);
}
break;
case Types.DOUBLE:
double[] doubleArray = (double[]) array;
fields[1] = new Field("VALUE", Oid.FLOAT8);
for ( int i = 0; i < doubleArray.length; i++ )
{
byte[][] tuple = new byte[2][0];
tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
tuple[1] = conn.encodeString( Double.toString(doubleArray[i]) ); // Value
rows.addElement(tuple);
}
break;
case Types.CHAR:
fields[1] = new Field("VALUE", Oid.BPCHAR);
fillStringResultSet(index, (String[])array, rows);
break;
case Types.VARCHAR:
fields[1] = new Field("VALUE", Oid.VARCHAR);
fillStringResultSet(index, (String[])array, rows);
break;
case Types.DATE:
java.sql.Date[] dateArray = (java.sql.Date[]) array;
fields[1] = new Field("VALUE", Oid.DATE);
for ( int i = 0; i < dateArray.length; i++ )
{
byte[][] tuple = new byte[2][0];
tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
tuple[1] = conn.encodeString( conn.getTimestampUtils().toString(null, dateArray[i]) ); // Value
rows.addElement(tuple);
}
break;
case Types.TIME:
java.sql.Time[] timeArray = (java.sql.Time[]) array;
fields[1] = new Field("VALUE", Oid.TIME);
for ( int i = 0; i < timeArray.length; i++ )
{
byte[][] tuple = new byte[2][0];
tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
tuple[1] = conn.encodeString( conn.getTimestampUtils().toString(null, timeArray[i]) ); // Value
rows.addElement(tuple);
}
break;
case Types.TIMESTAMP:
java.sql.Timestamp[] timestampArray = (java.sql.Timestamp[]) array;
fields[1] = new Field("VALUE", Oid.TIMESTAMPTZ);
for ( int i = 0; i < timestampArray.length; i++ )
{
byte[][] tuple = new byte[2][0];
tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
tuple[1] = conn.encodeString( conn.getTimestampUtils().toString(null, timestampArray[i]) ); // 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:
if (conn.getLogger().logDebug())
conn.getLogger().debug("getResultSetImpl(long,int,Map) with "+getBaseTypeName());
throw org.postgresql.Driver.notImplemented(this.getClass(), "getResultSetImpl(long,int,Map)");
}
BaseStatement stat = (BaseStatement) conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
return (ResultSet) stat.createDriverResultSet(fields, rows);
}
public String toString()
{
return rawString;
}
}
Marek Lewczuk pisze: > Marek Lewczuk pisze: >> Hello Kris and hello group, > Small correction to my previous post - I forgot to include null support > when bulding final arrays. In the attachment corrected file. No comments or no time to check the patch ? If the code I send before is OK then I can make AbstractJdbc2Array.getResultSet() work with NULL elements of an array and with multi-dimensional arrays.
On Mon, 9 Jul 2007, Marek Lewczuk wrote:
>> in the attachment you can find AbstractJdbc2Array class with two fixed
>> features: null value as element of an array and... multi-dimensional
>> arrays support. There are two things related with this two changes:
>> - all arrays are objects arrays (which means that e.g. boolean[] is
>> represented as Boolean[])
>> - when multi-dimensional array is processed then Object[] is returned by
>> getArray() - it's because we can't declare in Java an array of unknown
>> dimension (so if PostgreSQL returns char[][] then jdbc will return
>> Object[], that contains String[] elements)
>>
I gave this a read and have some comments:
1) Your null checking needs to be version dependent. Releases prior to
8.2 do not support null elements and you'll get:
# select array['a','NULL'];
array
----------
{a,NULL}
(1 row)
2) Changing from returning arrays of primitive types to arrays of objects
is necessary for null and multi-dimension support, but will still break
users code all over the place. Not sure what we can do about that other
than put a strong warning in the release notes.
3) A lot of the code doesn't look like it handles multiple dimensions.
What about things like fillIntegerResultSet? No that we're dealing with
objects can't we abstract a lot of these copies away and provide one
implementation that is multi-dimension aware? If you do
Array.getResultSet you should be able to call ResultSet.getArray and the
getResultSet on that, right?
Kris Jurka
Kris Jurka wrote: > 2) Changing from returning arrays of primitive types to arrays of > objects is necessary for null and multi-dimension support, but will > still break users code all over the place. Not sure what we can do > about that other than put a strong warning in the release notes. I was wondering which behaviour was right -- I thought returning array-of-primitive was correct per the spec at the time I wrote it, and that's why getArray() returns Object not Object[] -- but now the 1.6 javadoc says: > Note: When getArray is used to materialize a base type that maps to a primitive data type, then it is implementation-definedwhether the array returned is an array of that primitive data type or an array of Object. So I suppose that .. in theory .. applications should be expecting to handle both. Ugh. -O
Kris Jurka pisze:
> I gave this a read and have some comments:
>
> 1) Your null checking needs to be version dependent. Releases prior to
> 8.2 do not support null elements and you'll get:
>
> # select array['a','NULL'];
> array
> ----------
> {a,NULL}
> (1 row)
Right, I will fix that.
> 2) Changing from returning arrays of primitive types to arrays of
> objects is necessary for null and multi-dimension support, but will
> still break users code all over the place. Not sure what we can do
> about that other than put a strong warning in the release notes.
For sure BC is important issue and maybe I should check PG version and
if it is < 8.2 then primitive arrays should be returned (just like
before - it needs only more code, but won't break current applications) ?
> 3) A lot of the code doesn't look like it handles multiple dimensions.
> What about things like fillIntegerResultSet? No that we're dealing with
> objects can't we abstract a lot of these copies away and provide one
> implementation that is multi-dimension aware? If you do
> Array.getResultSet you should be able to call ResultSet.getArray and the
> getResultSet on that, right?
Currently my patch applies only to java.sql.Array.getArray() and because
I didn't know what is your opinion about my implementation I didn't
patch any other methods (like java.sql.Array.getResultSet()). If you say
that my implementation + fixes proposed above are OK then I will make a
patch for other methods in order to provide full support of
java.sql.Array. Methods from AbstractJdbc2Array (like
fillIntegerResultSet()) either will be removed or fixed in order to work
with all those changes.
Best wishes,
Marek
Oliver Jowett pisze: > I was wondering which behaviour was right -- I thought returning > array-of-primitive was correct per the spec at the time I wrote it, and > that's why getArray() returns Object not Object[] -- but now the 1.6 > javadoc says: > >> Note: When getArray is used to materialize a base type that maps to a >> primitive data type, then it is implementation-defined whether the >> array returned is an array of that primitive data type or an array of >> Object. > > So I suppose that .. in theory .. applications should be expecting to > handle both. Ugh. My opinion is that we should keep BC either by building two different jdbc versions or checking PG version every time getArray() is used (if < 8.2 then primitive types are used, if >= 8.2 then objects). Second option is quite good, although it doesn't guaranty full BC - let assume that someone is using jdbc application that was written for pg 8.0, but after update to 8.2 the code with following statement: <code>(boolean[]) getArray()</code> would cause cast exception (Cannot cast from Boolean[] to boolean[]). Regards, ML
Marek Lewczuk wrote: > My opinion is that we should keep BC either by building two different > jdbc versions or checking PG version every time getArray() is used (if < > 8.2 then primitive types are used, if >= 8.2 then objects). Use the "compatible" URL option to make this decision, rather than server version (there are some helper functions around to help here) -O
Oliver Jowett pisze: > Use the "compatible" URL option to make this decision, rather than > server version (there are some helper functions around to help here) I will, thanks. Marek