[Pljava-dev] ResultSet getString

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