Issue 72582 - can't insert or update into money fields with postgresql & jdbc
Summary: can't insert or update into money fields with postgresql & jdbc
Status: CLOSED WONT_FIX
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 2.0.4
Hardware: All All
: P3 Trivial (vote)
Target Milestone: OOo 2.x
Assignee: ocke.janssen
QA Contact: issues@dba
URL:
Keywords: needmoreinfo
Depends on:
Blocks:
 
Reported: 2006-12-14 16:43 UTC by bryancole
Modified: 2007-10-25 13:57 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description bryancole 2006-12-14 16:43:44 UTC 
OO-Base will not allow any inserts or updates to database fields of either the
'money' or 'numeric' data types, when using JDBC to connect to a postgresql DB.
I've tried both the 8.1 and 8.2 jdbc drivers. I'm using Sun JRE 1.5.0_9.

For a numeric field, the error dialog says:

ERROR: column "xxx" is of type numeric but expression is of type character varying

I get a similar error with 'money' fields. Basically, it's impossible to handle
currency with JDBC and postgresql.

The table data types appear correctly identified if I open the Edit Table
dialog. This appears to be over zealous type checking on the part of OOo. I can
insert or update stuff by executing SQL statements in the "SQL..." dialog.
Comment 1 christoph.lukasiak 2007-06-26 15:09:53 UTC 
does this problem still occur in a current version?
Comment 2 roosoft 2007-08-10 09:39:34 UTC 
I can confirm this too. Database is postgresql 8.2 and the field data type is
numeric (16,2). However, no matter what type of control you put on the form
(numeric, money, text, number) it will always come back with a type mis-match.
Specifically :

Error: column "Total" is of type numeric but expression is of type character
varying.

I can only assume that OO intentionally keeps the type as VAR char even though
the postgres driver correctly identifies the field type.

for the record I am using the postgresql-8.3dev-601.jdbc3 with OO 2.2.1 (Ubuntu)
Comment 3 roosoft 2007-08-12 01:51:46 UTC 
Addendum. It looks like definitely an issue with OO as stetting the JDBC driver
with the option compatible=7.4 which in turn sets the Stingtype to 'unspecified'
solves the issue. From the documentation for the JDBC Driver:

"stringtype = String

    Specify the type to use when binding PreparedStatement parameters set via
setString(). If stringtype is set to varchar (the default), such parameters will
be sent to the server as varchar parameters. If stringtype is set to
unspecified, parameters will be sent to the server as untyped values, and the
server will attempt to infer an appropriate type. This is useful if you have an
existing application that uses setString() to set parameters that are actually
some other type, such as integers, and you are unable to change the application
to use an appropriate method such as setInt(). "

So it infers that OO is using SetString() incorrectly.
Comment 4 christoph.lukasiak 2007-10-23 14:59:47 UTC 
i can repro that in a src680m232 - driver problem?
Comment 5 christoph.lukasiak 2007-10-23 15:00:48 UTC 
set to new
Comment 6 ocke.janssen 2007-10-25 13:56:21 UTC 
Our own JDBC driver doesn't set the option compatible=7.4 That must happen
inside the JDBC from posgresql.

It seems to be a driver problem. When I insert data in a numeric field with a
insert statement. String values are excepted. And I don't know which method I
should use other than string when I see this
"The type numeric can store numbers with up to 1000 digits of precision"
Comment 7 ocke.janssen 2007-10-25 13:57:01 UTC 
Feel free to open it again. Closing it now.