SEO

Hi,
I've stumbled into a bug, it seems the driver is not able to
retrive money data type from the db properly unless a getString
is used.... unfortunately in my case the code does not know
what kind of attributes is reading, and it uses getObject instead,
getting back an exception.

Running the attached code all I get is:

Exception in thread "main" org.postgresql.util.PSQLException: Il valore
«1,000.00» non è adeguato al tipo «double».
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.toDoub le(AbstractJdbc2ResultSet.java:2645)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getDou ble(AbstractJdbc2ResultSet.java:2032)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.intern alGetObject(AbstractJdbc2ResultSet.java:134)
at
org.postgresql.jdbc3.AbstractJdbc3ResultSet.intern alGetObject(AbstractJdbc3ResultSet.java:39)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getObj ect(AbstractJdbc2ResultSet.java:2348)
at PgMoneyIssue.main(PgMoneyIssue.java:15)

(translated from italian, "the value 1,000.00 is not adequate for the
type double". Not sure if the problem is locale dependent, since it has
been reported as a GeoServer bug by a user living in USA, and I then
tracked it down to this issue with the driver).

I looked for a bug tracker but could not find one, so here I am, posting
the bug report to the ml.

Contact me back if you need any further information
Cheers
Andrea


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/*

To run this example run the following sql:

CREATE TABLE testmoney (
id serial,
cost money NOT NULL
);
INSERT INTO testmoney (id, cost) VALUES (1, '$999.00');
INSERT INTO testmoney (id, cost) VALUES (2, '$1,000.00');
INSERT INTO testmoney (id, cost) VALUES (3, '$10,000.01');

and then fix the connection paramters

*/

public class PgMoneyIssue {
public static void main(String[] args) throws Exception {
Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection("jdbcostgresql://localhost/postgis", "username", "password");
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("select * from testmoney");
while(rs.next()) {
System.out.println(rs.getObject(1));
System.out.println(rs.getObject(2)); // kaboom on the second record!
}
}
}


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Tue, 26 Feb 2008, Andrea Aime wrote:

> I've stumbled into a bug, it seems the driver is not able to
> retrive money data type from the db properly unless a getString
> is used.... unfortunately in my case the code does not know
> what kind of attributes is reading, and it uses getObject instead,
> getting back an exception.


We already knew the support for money in the driver was pretty poor (it
doesn't work for locale's whose currency symbol is not $), but the fact
that it doesn't support separators makes it especially useless.
Since every getInt/Float/... call is paying a performance price for
checking for the money type and doesn't always work, I suggest we rip all
that out and return money as either a String or the PGmoney type.

That will break existing applications that want to be able to do getDouble
on money data, but I'm OK with that because they should be pretty rare.

Kris Jurka


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Hello Kris,

Kris Jurka schrieb:
> That will break existing applications that want to be able to do
> getDouble on money data, but I'm OK with that because they should be
> pretty rare.
If you are on breaking existing applications anyway, please reconsider
my patch given in the Thread with the name "[JDBC] TypeInfoCache" )
>
> Kris Jurka
With best regards,
Daniel Migowski

PS: This patch even enables an application, namely Chrystal Reports JDBC
Implementation (which is not so rare).


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Fri, 29 Feb 2008, Daniel Migowski wrote:

> Kris Jurka schrieb:
>> That will break existing applications that want to be able to do getDouble
>> on money data, but I'm OK with that because they should be pretty rare.
>
> If you are on breaking existing applications anyway, please reconsider my
> patch given in the Thread with the name "[JDBC] TypeInfoCache" )
>

I still don't like your patch, but I am planning on implementing the
solution that Oliver and I liked better (making up a bogus precision for
types without lengths).

http://pgfoundry.org/tracker/index.p...224 &atid=854

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly