[Pljava-dev] ResultSet getString

Hi,
I'm testing new version of pljava 1.3 and it seems that there is
something wrong (or not...). In previous versions ResultSet.getString
returned String for columns that in postgresql are defined as varchar[].
Right now exception is thrown that "Cannot derive a value of class
java.lang.String from an object of class [Ljava.lang.String;" - is this
a proper behavior ?

Regards,
ML

Hi Marek,
Yes, this is part of the new and improved data type mapping in PL/Java.
Arrays are recognized as such so a varchar[] will map to a String[].
You'll find a complete list of mappings here:
http://wiki.tada.se/display/pljava/Default+Type+Mapping.

Your current code rely on the fact that unrecognized types are all
mapped to String.

Regards,
Thomas Hallgren

Marek Lewczuk wrote:
> Hi,
> I'm testing new version of pljava 1.3 and it seems that there is
> something wrong (or not...). In previous versions ResultSet.getString
> returned String for columns that in postgresql are defined as varchar[].
> Right now exception is thrown that "Cannot derive a value of class
> java.lang.String from an object of class [Ljava.lang.String;" - is this
> a proper behavior ?
>
> Regards,
> ML
>
> _______________________________________________
> Pljava-dev mailing list
> Pljava-dev at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/pljava-dev
>

Thomas Hallgren napisa?(a):
> Hi Marek,
> Yes, this is part of the new and improved data type mapping in PL/Java.
> Arrays are recognized as such so a varchar[] will map to a String[].
> You'll find a complete list of mappings here:
> http://wiki.tada.se/display/pljava/Default+Type+Mapping.
Ok, I thought that getString() will return any postgresql type as string
- e.g. varchar[] would be {"val","val"}, boolean would be "t" or "f" -
that is how pgsql works, when assigning value of any type to variable
defined as text/varchar.

ML

Marek Lewczuk wrote:
> Ok, I thought that getString() will return any postgresql type as string
> - e.g. varchar[] would be {"val","val"}, boolean would be "t" or "f" -
> that is how pgsql works, when assigning value of any type to variable
> defined as text/varchar.
>
>
I see what you mean. Question is, what string representation should be
used? What is more intuitive? Using the Java 'toString()' representation
of an object or the PostgreSQL String representation of the SQL type?

A typical Java developer who's not fluent in PostgreSQL type coercion
would expect that ResultSet.getString() on a boolean would return "true"
or "false", i.e. something that can be passed to Boolean.valueOf(String)
and that Array types would follow the format stipulated by
java.util.Arrays.toString(<type>[]) family of methods.

At present, the getString() method will coerce basic types to strings
using Java semantics. Arrays are not coerced though, since that
functionality was introduced in Java 1.5.

Regards,
Thomas Hallgren

Thomas Hallgren napisa?(a):
> Marek Lewczuk wrote:
> I see what you mean. Question is, what string representation should be
> used? What is more intuitive? Using the Java 'toString()' representation
> of an object or the PostgreSQL String representation of the SQL type?
>
> A typical Java developer who's not fluent in PostgreSQL type coercion
> would expect that ResultSet.getString() on a boolean would return "true"
> or "false", i.e. something that can be passed to Boolean.valueOf(String)
> and that Array types would follow the format stipulated by
> java.util.Arrays.toString(<type>[]) family of methods.
>
> At present, the getString() method will coerce basic types to strings
> using Java semantics. Arrays are not coerced though, since that
> functionality was introduced in Java 1.

Lets see what Java api say about ResultSet.getString():
"Retrieves the value of the designated column in the current row of this
ResultSet object as a String in the Java programming language."

It doesn't say, that getString() will return "a string representation of
the object". Now, if I would like to get PostgreSQL String
representation of multi-dimensional array, what method should I use ?
For me obvious thing to do is to call getString() and I wouldn't expect
that this will return same as ResultSet.getBoolean().toString(). Another
example:

Statement statement = ...
statement.execute()...
ResultSet result = ...
while (result.next()) {
// result contains 3 columns: varchar[], boolean, integer[]

// we create a duplicated row, without worrying about the SQL type
Statement s = connection.createStatement();
s.executeUpdate("INSERT ... (c1, c2, c3) VALUES ('" +
result.getString(0) + "', '" + result.getString(1) + "', '" +
result.getString(2) + "')");
}

My opinion is that you should keep getString() as a method to get
PostgreSQL string representation - this will keep compatibility with
previous pljava versions.

ML

Marek Lewczuk wrote:
>
> Lets see what Java api say about ResultSet.getString():
> "Retrieves the value of the designated column in the current row of
> this ResultSet object as a String in the Java programming language."
>
> It doesn't say, that getString() will return "a string representation
> of the object".
Well, it has to, doesn't it? Either that or throw an exception. Question
is, what representation?

> Now, if I would like to get PostgreSQL String representation of
> multi-dimensional array, what method should I use ?
Simply perform SQL coercions in SQL rather than in Java, i.e. "SELECT
array_out(arrayValue)". The array_out function will produce a pseudo
type called cstring and PL/Java will treat that as a String.

> For me obvious thing to do is to call getString() and I wouldn't
> expect that this will return same as
> ResultSet.getBoolean().toString(). Another example:
>
> Statement statement = ...
> statement.execute()...
> ResultSet result = ...
> while (result.next()) {
> // result contains 3 columns: varchar[], boolean, integer[]
>
> // we create a duplicated row, without worrying about the SQL type
> Statement s = connection.createStatement();
> s.executeUpdate("INSERT ... (c1, c2, c3) VALUES ('" +
> result.getString(0) + "', '" + result.getString(1) + "', '" +
> result.getString(2) + "')");
> }
>
Try this instead:

PreparedStatement s = connection.prepareStatement("INSERT ...(c1, c2,
c3) VALUES(?,?,?)");
s.setObject(result.getObject(1));
s.setObject(result.getObject(2));
s.setObject(result.getObject(3));
s.executeUpdate();

Far more efficient since:
a) no String coercion is needed
b) no String concatenation is needed
c) the actual statement can be cached
d) it's completely insensitive to the types of the involved columns.

Regards,
Thomas Hallgren

On 18-May-06, at 9:29 AM, Thomas Hallgren wrote:

> Marek Lewczuk wrote:
>>
>> Lets see what Java api say about ResultSet.getString():
>> "Retrieves the value of the designated column in the current row of
>> this ResultSet object as a String in the Java programming language."
>>
>> It doesn't say, that getString() will return "a string representation
>> of the object".
> Well, it has to, doesn't it? Either that or throw an exception.
> Question
> is, what representation?
>
>> Now, if I would like to get PostgreSQL String representation of
>> multi-dimensional array, what method should I use ?
> Simply perform SQL coercions in SQL rather than in Java, i.e. "SELECT
> array_out(arrayValue)". The array_out function will produce a pseudo
> type called cstring and PL/Java will treat that as a String.
>
>> For me obvious thing to do is to call getString() and I wouldn't
>> expect that this will return same as
>> ResultSet.getBoolean().toString(). Another example:
>>
>> Statement statement = ...
>> statement.execute()...
>> ResultSet result = ...
>> while (result.next()) {
>> // result contains 3 columns: varchar[], boolean, integer[]
>>
>> // we create a duplicated row, without worrying about the SQL type
>> Statement s = connection.createStatement();
>> s.executeUpdate("INSERT ... (c1, c2, c3) VALUES ('" +
>> result.getString(0) + "', '" + result.getString(1) + "', '" +
>> result.getString(2) + "')");
>> }
>>
> Try this instead:
>
> PreparedStatement s = connection.prepareStatement("INSERT ...(c1, c2,
> c3) VALUES(?,?,?)");
> s.setObject(result.getObject(1));
> s.setObject(result.getObject(2));
> s.setObject(result.getObject(3));
> s.executeUpdate();
>
> Far more efficient since:
> a) no String coercion is needed
> b) no String concatenation is needed
> c) the actual statement can be cached
> d) it's completely insensitive to the types of the involved columns.
>
> Regards,
> Thomas Hallgren

This is much more elegant. I had been doing something similar in the
earlier version while sending objects to the back-end by making my
custom objects extend PGobject. My SQL statements were much simpler
with just one parameter, doing s.setObject( 1, myObject, Types.OTHER )

Rakesh

Marek Lewczuk wrote:
>
> My opinion is that you should keep getString() as a method to get
> PostgreSQL string representation - this will keep compatibility with
> previous pljava versions.
>
And it would maintain an inconsistent way of representing objects as
strings since the basic types are coerced according to Java semantics.
I'm not too keen on doing that.

Regards,
Thomas Hallgren

Thomas Hallgren napisa?(a):
> And it would maintain an inconsistent way of representing objects as
> strings since the basic types are coerced according to Java semantics.
> I'm not too keen on doing that.
:-) The end. I just wanted to be sure, that present behavior is correct
one. Remember to add BIG NOTES about this on pljava wiki - many people
should know about this before moving to new pljava version.

ML

Marek Lewczuk wrote:
> Thomas Hallgren napisa?(a):
>> And it would maintain an inconsistent way of representing objects as
>> strings since the basic types are coerced according to Java
>> semantics. I'm not too keen on doing that.
> :-) The end. I just wanted to be sure, that present behavior is
> correct one. Remember to add BIG NOTES about this on pljava wiki -
> many people should know about this before moving to new pljava version.
>
> ML
Good point. I haven't written a "Migration guide" page yet. This time
it's really needed.

Regards,
Thomas Hallgren