Arrays Was (Re: ERROR in 8.0 driver, but not 7.4: SELECT DISTINCT, ORDER BY expressions must appear in select list)

Thanks, Dave for the pointer. I guess I didn't have the right key
words in my googling.

Also I had one more question, concerning sql Arrays. I didn't find
too much documentation on this other than mailing list discussions.
I've created some minimal utility wrapper classes to create
java.sql.Array objects for inserting/updating (i.e. using
PreparedStatement.setArray(x, x)) int, float, double, and String
arrays. The functions for int and String are at the bottom of this
message and the float and double versions are similar.

Now my question. I think the int/float/double are reasonably ok, but
the String version needs some help. Is there a way I can escape the
individual elements? Also any other comments on the general
implementation? I believe this should be ok but if future versions
need other methods implemented besides Array.toString() and Array.
than this needs to be modified. Also can this example be placed in
the documentation for the JDBC driver? I don't mind adding some more
pointers and explanation if people would find it useful. I spent
quite bit of time googling on how to do this, but I ended up having
to dig around the code to see what methods in java.sql.Array required
more than a stub implementation.

Thanks,
Sumit

public static Array convertStringToPgSqlArray(final String[] s)
throws Exception {
if(s == null || s.length < 1) return null;
Array a = new Array() {
public String getBaseTypeName() {return "text";}
public int getBaseType() {return 0;}
public Object getArray() {return null;}
public Object getArray(Map<String, Class<?>> map) {return null;}
public Object getArray(long index, int count) {return null;}
public Object getArray(long index, int count, Map<String,
Class<?>> map) {return null;}
public ResultSet getResultSet() {return null;}
public ResultSet getResultSet(Map<String, Class<?>> map)
{return null;}
public ResultSet getResultSet(long index, int count) {return
null;}
public ResultSet getResultSet(long index, int count,
Map<String, Class<?>> map) {return null;}

public String toString() {
String p = "{";
if(s.length == 0) {
} else {
for(int i = 0; i < s.length - 1; i++) p += s[i] + ",";
p += "'" + s[s.length - 1] + "'";
}
p+="}";
return p;
}
};
return a;
}

public static Array convertIntegerToPgSqlArray(final int[] p) {
if(p == null || p.length < 1) return null;
Array a = new Array() {
public String getBaseTypeName() {return "int4";}
public int getBaseType() {return 0;}
public Object getArray() {return null;}
public Object getArray(Map<String, Class<?>> map) {return null;}
public Object getArray(long index, int count) {return null;}
public Object getArray(long index, int count, Map<String,
Class<?>> map) {return null;}
public ResultSet getResultSet() {return null;}
public ResultSet getResultSet(Map<String, Class<?>> map)
{return null;}
public ResultSet getResultSet(long index, int count) {return
null;}
public ResultSet getResultSet(long index, int count,
Map<String, Class<?>> map) {return null;}

public String toString() {
String fp = "{";
if(p.length == 0) {
} else {
for(int i = 0; i < p.length - 1; i++) fp += p[i] + ",";
fp += p[p.length - 1];
}
fp+="}";
return fp;
}
};
return a;
}

On May 12, 2005, at 9:57 AM, Dave Cramer wrote:

> Yes, it is expected behaviour
>
> see
>
> http://archives.postgresql.org/pgsql-jdbc/2005-05/msg00013.php
>
> for details.
>
> sumit shah wrote:
>
>
>> Dear Postgres JDBC hackers:
>>
>> I've just recently started testing the postgres JDBC 3 driver
>> (8.0-311) after using JDBC 3 (pg74.216.jdbc). I now get an error
>> on a query of the type:
>>
>> SELECT DISTINCT column1, column2 FROM table1 ORDER BY 1
>>
>> where the "1" in the ORDER BY is set by PreparedStatement.setInt
>> method
>>
>> The error is: "SELECT DISTINCT, ORDER BY expressions must appear
>> in select list"
>>
>> The error disappears when I change the query to:
>>
>> SELECT DISTINCT column1, column2 FROM table1 ORDER BY column1
>>
>> or when I hard code the ORDER BY 1 in the query instead of setting
>> it via setInt.
>>
>> This error occurs only with the 8.0-311 driver or the 8.1dev-400
>> jdbc drivers, it does not occur with the pg74-216 driver. This
>> occurs with both the 7.4 and 8.0 backends.
>>
>> Is this change in behavior expected and our code was simply
>> relying on old semantics or misinterpretation of the JDBC standard?
>>
>> Thanks,
>> Sumit
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 4: Don't 'kill -9' the postmaster
>>
>>
>>
>
> --
> Dave Cramer
> http://www.postgresintl.com
> 519 939 0336
> ICQ#14675561
>
>