|
Hello, I have an application running
under JBoss. Up to today, I was using Postgres 7.3 and the appropriate version of the jdbc driver. In my application, I have to
call a user-defined function which accept in
parameters 2 arrays. Here is the header of my function : CREATE OR REPLACE FUNCTION getmembers(int8,
int8, _text, _float8) So I called it using a
prepared statement with setArray() : double[] weights = {0.5}; String[] names = {“foo1”, “foo2”}; java.sql.Array a_names = PostgresArray.create(names); java.sql.Array a_weights = PostgresArray.create(weights); ps = conn.prepareStatement("SELECT
* FROM getmembers(?,?,?::_text,?::_float8);"); ps.setLong(1,
1); ps.setLong(2,
2); ps.setArray(3,
a_names); ps.setArray(4, a_weights); ps.executeQuery(); PostgresArray is a
class which I found on the archives.postgresql.org. The code is given is
attached. All worked fine. But today, I decided to
upgrade to Postgres 8.0 beta 3. No problem with the definition
of my function. I downloaded the appropriate
JDBC driver : pgdev.306.jdbc3.jar. Now running the same code as
before, I get the error while executing the query : java.sql.SQLException: ERROR: cannot cast type text to text[] So, what am I doing wrong? Is it a beta bug or is my code
incorrect? What is the correct way to use
SetArray()? Thanks JR |
package com.postgresql; import java.math.BigDecimal; import java.sql.*; import java.util.ArrayList; import java.util.Map;
/*
* Array is used collect one column of query result data.
*
* <p>Read a field of type Array into either a natively-typed
* Java array object or a ResultSet. Accessor methods provide
* the ability to capture array slices.
*
* <p>Other than the constructor all methods are direct implementations
* of those specified for java.sql.Array. Please refer to the javadoc
* for java.sql.Array for detailed descriptions of the functionality
* and parameters of the methods of this class.
*
* <b>This class stolen from postgresql 7.2.1's source tree!!!</b>
*
* @see ResultSet#getArray
*
*/
public class PostgresArray implements Array {
private String rawString = null;
private String typeName = null;
private int baseType = -1;
private static final String jdbc2Types[] = {
"int2",
"int4", "oid",
"int8",
"cash", "money",
"numeric",
"float4",
"float8",
"bpchar", "char", "char2", "char4", "char8", "char16",
"varchar", "text", "name", "filename",
"bytea",
"bool",
"date",
"time",
"abstime", "timestamp", "timestamptz",
"_bool", "_char", "_int2", "_int4", "_text",
"_oid", "_varchar", "_int8", "_float4", "_float8",
"_abstime", "_date", "_time", "_timestamp", "_numeric",
"_bytea"
};
/*
* This table holds the JDBC type for each entry above.
*
* Note: This must be in the same order as above
*
* Tip: keep these grouped together by the Types. value
*/
private static final int jdbc2Typei[] = {
Types.SMALLINT,
Types.INTEGER, Types.INTEGER,
Types.BIGINT,
Types.DOUBLE, Types.DOUBLE,
Types.NUMERIC,
Types.REAL,
Types.DOUBLE,
Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR,
Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
Types.BINARY,
Types.BIT,
Types.DATE,
Types.TIME,
Types.TIMESTAMP, Types.TIMESTAMP, Types.TIMESTAMP,
Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY,
Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY,
Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY,
Types.ARRAY
};
/*
* Create a new Array
*
* @param conn a database connection
* @param idx 1-based index of the query field to load into this Array
* @param field the Field descriptor for the field to load into this Array
* @param rs the ResultSet from which to get the data for this Array
*/
PostgresArray(String rawString, int baseType, String typeName) throws SQLException
{
this.rawString = rawString;
this.baseType = baseType;
this.typeName = typeName;
if (this.typeName.startsWith("_")) {
this.typeName = this.typeName.substring(1);
}
}
public static Array create(Object[] array) throws SQLException {
throw new SQLException("Not Implemented");
}
public static Array create(int[] array) throws SQLException {
if (array == null)
return new PostgresArray(null, -1, null);
StringBuffer sb = new StringBuffer("{");
for (int x = 0; x < array.length; x++) {
if (x > 0)
sb.append(",");
sb.append("\"").append(array[x]).append("\"");
}
sb.append("}");
return new PostgresArray(sb.toString(), Types.INTEGER, "int");
}
public static Array create(boolean[] array) throws SQLException {
if (array == null)
return new PostgresArray(null, -1, null);
StringBuffer sb = new StringBuffer("{");
for (int x = 0; x < array.length; x++) {
if (x > 0)
sb.append(",");
sb.append("\"").append(array[x]).append("\"");
}
sb.append("}");
return new PostgresArray(sb.toString(), Types.BIT, "boolean");
}
public static Array create(long[] array) throws SQLException {
if (array == null)
return new PostgresArray(null, -1, null);
StringBuffer sb = new StringBuffer("{");
for (int x = 0; x < array.length; x++) {
if (x > 0)
sb.append(",");
sb.append("\"").append(array[x]).append("\"");
}
sb.append("}");
return new PostgresArray(sb.toString(), Types.BIT, "bigint");
}
public static Array create(BigDecimal[] array) throws SQLException {
if (array == null)
return new PostgresArray(null, -1, null);
StringBuffer sb = new StringBuffer("{");
for (int x = 0; x < array.length; x++) {
if (x > 0)
sb.append(",");
sb.append("\"").append(array[x]).append("\"");
}
sb.append("}");
return new PostgresArray(sb.toString(), Types.NUMERIC, "NUMERIC");
}
public static Array create(float[] array) throws SQLException {
if (array == null)
return new PostgresArray(null, -1, null);
StringBuffer sb = new StringBuffer("{");
for (int x = 0; x < array.length; x++) {
if (x > 0)
sb.append(",");
sb.append("\"").append(array[x]).append("\"");
}
sb.append("}");
return new PostgresArray(sb.toString(), Types.REAL, "float");
}
public static Array create(double[] array) throws SQLException {
if (array == null)
return new PostgresArray(null, -1, null);
StringBuffer sb = new StringBuffer("{");
for (int x = 0; x < array.length; x++) {
if (x > 0)
sb.append(",");
sb.append("\"").append(array[x]).append("\"");
}
sb.append("}");
return new PostgresArray(sb.toString(), Types.DOUBLE, "double");
}
public static Array create(char[] array) throws SQLException {
if (array == null)
return new PostgresArray(null, -1, null);
StringBuffer sb = new StringBuffer("{");
for (int x = 0; x < array.length; x++) {
if (x > 0)
sb.append(",");
sb.append("\"").append(array[x]).append("\"");
}
sb.append("}");
return new PostgresArray(sb.toString(), Types.CHAR, "char");
}
public static Array create(String[] array) throws SQLException {
if (array == null)
return new PostgresArray(null, -1, null);
StringBuffer sb = new StringBuffer("{");
for (int x = 0; x < array.length; x++) {
if (x > 0)
sb.append(",");
sb.append("\"").append(array[x]).append("\"");
}
sb.append("}");
return new PostgresArray(sb.toString(), Types.VARCHAR, "varchar");
}
public static Array create(Date[] array) throws SQLException {
if (array == null)
return new PostgresArray(null, -1, null);
StringBuffer sb = new StringBuffer("{");
for (int x = 0; x < array.length; x++) {
if (x > 0)
sb.append(",");
sb.append("\"").append(array[x]).append("\"");
}
sb.append("}");
return new PostgresArray(sb.toString(), Types.DATE, "date");
}
public static Array create(java.util.Date[] array) throws SQLException {
if (array == null)
return new PostgresArray(null, -1, null);
StringBuffer sb = new StringBuffer("{");
for (int x = 0; x < array.length; x++) {
if (x > 0)
sb.append(",");
sb.append("\"").append(new Date(array[x].getTime())).append("\"");
}
sb.append("}");
return new PostgresArray(sb.toString(), Types.DATE, "date");
}
public static Array create(Time[] array) throws SQLException {
if (array == null)
return new PostgresArray(null, -1, null);
StringBuffer sb = new StringBuffer("{");
for (int x = 0; x < array.length; x++) {
if (x > 0)
sb.append(",");
sb.append("\"").append(array[x]).append("\"");
}
sb.append("}");
return new PostgresArray(sb.toString(), Types.TIME, "time");
}
public static Array create(Timestamp[] array) throws SQLException {
if (array == null)
return new PostgresArray(null, -1, null);
StringBuffer sb = new StringBuffer("{");
for (int x = 0; x < array.length; x++) {
if (x > 0)
sb.append(",");
sb.append("\"").append(array[x]).append("\"");
}
sb.append("}");
return new PostgresArray(sb.toString(), Types.TIMESTAMP, "timestamp");
}
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 new SQLException("Maps are not supported in Array.getArray");
if (index < 1)
throw new SQLException("index < 1");
Object retVal = null;
ArrayList array = new ArrayList();
if (rawString != null) {
char[] chars = rawString.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 SQLException("index + count > arrayContents");
int i = 0;
switch (getBaseType()) {
case Types.BIT:
retVal = new boolean[count];
for (; count > 0; count--)
((boolean[]) retVal)[i++] =
ResultSet_ClientSide.toBoolean(arrayContents[(int) index++]);
break;
case Types.SMALLINT:
case Types.INTEGER:
retVal = new int[count];
for (; count > 0; count--)
((int[]) retVal)[i++] =
ResultSet_ClientSide.toInt(arrayContents[(int) index++]);
break;
case Types.BIGINT:
retVal = new long[count];
for (; count > 0; count--)
((long[]) retVal)[i++] =
ResultSet_ClientSide.toLong(arrayContents[(int) index++]);
break;
case Types.NUMERIC:
retVal = new BigDecimal[count];
for (; count > 0; count--)
((BigDecimal[]) retVal)[i] =
ResultSet_ClientSide.toBigDecimal(arrayContents[(int) index++], 0);
break;
case Types.REAL:
retVal = new float[count];
for (; count > 0; count--)
((float[]) retVal)[i++] =
ResultSet_ClientSide.toFloat(arrayContents[(int) index++]);
break;
case Types.DOUBLE:
retVal = new double[count];
for (; count > 0; count--)
((double[]) retVal)[i++] =
ResultSet_ClientSide.toDouble(arrayContents[(int) index++]);
break;
case Types.CHAR:
case Types.VARCHAR:
retVal = new String[count];
for (; count > 0; count--)
((String[]) retVal)[i++] = arrayContents[(int) index++];
break;
case Types.DATE:
retVal = new Date[count];
for (; count > 0; count--)
((Date[]) retVal)[i++] =
ResultSet_ClientSide.toDate(arrayContents[(int) index++]);
break;
case Types.TIME:
retVal = new Time[count];
for (; count > 0; count--)
((Time[]) retVal)[i++] =
ResultSet_ClientSide.toTime(arrayContents[(int) index++]);
break;
case Types.TIMESTAMP:
retVal = new Timestamp[count];
for (; count > 0; count--)
((Timestamp[]) retVal)[i++] =
ResultSet_ClientSide.toTimestamp(arrayContents[(int) index]);
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 new SQLException(getBaseType() + "(" + this.typeName + ") is an
unsupported array type");
}
return retVal;*/
throw new SQLException("Array.getArray() not supported");
}
public int getBaseType() throws SQLException {
int sqlType = Types.OTHER; // default value
for (int i = 0; i < jdbc2Types.length; i++) {
if (this.typeName.equals(jdbc2Types[i])) {
sqlType = jdbc2Typei[i];
break;
}
}
return sqlType;
}
public String getBaseTypeName() throws SQLException {
return this.typeName;
}
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, Map map) throws
SQLException {
throw new SQLException("Array.getResultSet() not supported");
}
public String toString() {
return rawString;
}
}
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
