Thread: neooffice form problem with numerics

Hi, I am trying to use a neooffice form to insert numerical values
into a postgres database and get the following error:

ESTERROR:  42804: column "back_load" is of type numeric but expression
is of type character varying at character 35

ESTHINT:  You will need to rewrite or cast the expression.

ESTLOCATION:  transformAssignedExpr,
/Users/Shared/src/postgresql/postgresql-8.3.1/build-leo/src/../../src/backend/parser/parse_target.c:440

ESTSTATEMENT:  INSERT INTO "public"."security" ( "back_load",
"company_id", "expense_ratio", "front_load", "is_benchmark",
"proxy_id", "security_name", "security_type_id", "ticker", "turnover"
) VALUES ( $1, $2, $3, $4, $5, $6, $7, $8, $9, $10 )

I am using PostgreSQL 8.3.1 on Mac OS X 10.5.5 and have tried the
following drivers:
pg74.216.jdbc3
postgresql-8.3-603.jdbc3
postgresql-8.4dev-700.jdbc3

This also happens no matter what kind of form field I use, formatted
or numerical.

Any advice? Thanks!

Reuben Cummings

On Sat, 22 Nov 2008, Reuben Cummings wrote:

> Hi, I am trying to use a neooffice form to insert numerical values
> into a postgres database and get the following error:
>
> ESTERROR:  42804: column "back_load" is of type numeric but expression
> is of type character varying at character 35

Neooffice is apparently calling setString for the numeric parameter.  The
driver assumes this means it is really a string type and the backend gets
the type mismatch error.  Neooffice should really be calling setBigDecimal
for a numeric expression, but you can work around this by providing the
driver with an instruction to avoid specifying a type for values provided
by setString.  Use the URL parameter "stringtype=unspecified" when
connecting to the database and that should hopefully work.

Kris Jurka

Thanks!!! Worked like a charm!! I was banging my head for days trying
to figure it out.

Reuben

On Sat, Nov 22, 2008 at 8:21 PM, Kris Jurka <books@ejurka.com> wrote:
>
>
> On Sat, 22 Nov 2008, Reuben Cummings wrote:
>
>> Hi, I am trying to use a neooffice form to insert numerical values
>> into a postgres database and get the following error:
>>
>> ESTERROR:  42804: column "back_load" is of type numeric but expression
>> is of type character varying at character 35
>
> Neooffice is apparently calling setString for the numeric parameter.  The
> driver assumes this means it is really a string type and the backend gets
> the type mismatch error.  Neooffice should really be calling setBigDecimal
> for a numeric expression, but you can work around this by providing the
> driver with an instruction to avoid specifying a type for values provided by
> setString.  Use the URL parameter "stringtype=unspecified" when connecting
> to the database and that should hopefully work.
>
> Kris Jurka
>