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

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

From

Antony Paul

Date:

11 February 2005, 10:42:17

Hi,
    I am testing PostgreSQL 8.0.1. I compiled and installed it.
Downloaded JDBC driver postgresql-8.0-310.jdbc3.jar for JDK 1.4.(I was
unable to find it in source) Dumped my 7.3.3 database. Restored it in
new installation. Run the application and it gives this error.
    The error is when I use setString() to insert into a numeric
field. I hope it is bug. Otherwise we have to rewrite our application.
    The error message can be seen in server log also.

rgds
Antony Paul

Re: ERROR: column "total_cost" is of type numeric but expression

From

Markus Schaber

Date:

11 February 2005, 11:02:58

Hi, Antony,

Antony Paul schrieb:

>     I am testing PostgreSQL 8.0.1. I compiled and installed it.
> Downloaded JDBC driver postgresql-8.0-310.jdbc3.jar for JDK 1.4.(I was
> unable to find it in source) Dumped my 7.3.3 database. Restored it in
> new installation. Run the application and it gives this error.
>     The error is when I use setString() to insert into a numeric
> field. I hope it is bug. Otherwise we have to rewrite our application.
>     The error message can be seen in server log also.

Using setString() to insert anything else from text types is not
possible any more using pgjdbc 8.0 jars, as the new driver makes
stricter use of type checking. One of our third-party apps also suffers
from this problems.

I can three solutions / workarounds for your problem:

- Use the pgjdbc 7.4 jar file, it should work fine against a postgresql
8.0 server. You can also stay with the 7.3 jar you used against your
7.3.3 server, as AFAIK pg8.0 still supports the V2 query protocol.

- Use a "?::numeric" cast in your prepared statement (not tested here).

- Update your application to use the proper numeric types.

Markus

--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com

Attachment

signature.asc

Re: ERROR: column "total_cost" is of type numeric but expression

From

Antony Paul

Date:

11 February 2005, 11:28:10

7.3.3 driver have problem in set autocommit.

I used setString() for number in Oracle 8i.

rgds
Antony Paul


On Fri, 11 Feb 2005 12:02:41 +0100, Markus Schaber
<schabios@logi-track.com> wrote:
> Hi, Antony,
>
> Antony Paul schrieb:
>
> >     I am testing PostgreSQL 8.0.1. I compiled and installed it.
> > Downloaded JDBC driver postgresql-8.0-310.jdbc3.jar for JDK 1.4.(I was
> > unable to find it in source) Dumped my 7.3.3 database. Restored it in
> > new installation. Run the application and it gives this error.
> >     The error is when I use setString() to insert into a numeric
> > field. I hope it is bug. Otherwise we have to rewrite our application.
> >     The error message can be seen in server log also.
>
> Using setString() to insert anything else from text types is not
> possible any more using pgjdbc 8.0 jars, as the new driver makes
> stricter use of type checking. One of our third-party apps also suffers
> from this problems.
>
> I can three solutions / workarounds for your problem:
>
> - Use the pgjdbc 7.4 jar file, it should work fine against a postgresql
> 8.0 server. You can also stay with the 7.3 jar you used against your
> 7.3.3 server, as AFAIK pg8.0 still supports the V2 query protocol.
>
> - Use a "?::numeric" cast in your prepared statement (not tested here).
>
> - Update your application to use the proper numeric types.
>
> Markus
>
> --
> markus schaber | dipl. informatiker
> logi-track ag | rennweg 14-16 | ch 8001 zürich
> phone +41-43-888 62 52 | fax +41-43-888 62 53
> mailto:schabios@logi-track.com | www.logi-track.com
>
>
>

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

From

Antony Paul

Date:

11 February 2005, 11:31:57

Updating application is going to be a lot of work. If it breaks
existing applications why it is implemented. I dont think type
checking is a big thing. What is its use ?.

I will try 7.4 driver.

rgds
Antony Paul


On Fri, 11 Feb 2005 12:02:41 +0100, Markus Schaber
<schabios@logi-track.com> wrote:
> Hi, Antony,
>
> Antony Paul schrieb:
>
> >     I am testing PostgreSQL 8.0.1. I compiled and installed it.
> > Downloaded JDBC driver postgresql-8.0-310.jdbc3.jar for JDK 1.4.(I was
> > unable to find it in source) Dumped my 7.3.3 database. Restored it in
> > new installation. Run the application and it gives this error.
> >     The error is when I use setString() to insert into a numeric
> > field. I hope it is bug. Otherwise we have to rewrite our application.
> >     The error message can be seen in server log also.
>
> Using setString() to insert anything else from text types is not
> possible any more using pgjdbc 8.0 jars, as the new driver makes
> stricter use of type checking. One of our third-party apps also suffers
> from this problems.
>
> I can three solutions / workarounds for your problem:
>
> - Use the pgjdbc 7.4 jar file, it should work fine against a postgresql
> 8.0 server. You can also stay with the 7.3 jar you used against your
> 7.3.3 server, as AFAIK pg8.0 still supports the V2 query protocol.
>
> - Use a "?::numeric" cast in your prepared statement (not tested here).
>
> - Update your application to use the proper numeric types.
>
> Markus
>
> --
> markus schaber | dipl. informatiker
> logi-track ag | rennweg 14-16 | ch 8001 zürich
> phone +41-43-888 62 52 | fax +41-43-888 62 53
> mailto:schabios@logi-track.com | www.logi-track.com
>
>
>

Re: ERROR: column "total_cost" is of type numeric but expression

From

Markus Schaber

Date:

11 February 2005, 11:37:49

Hi, Antony,

Antony Paul schrieb:
> Updating application is going to be a lot of work. If it breaks
> existing applications why it is implemented. I dont think type
> checking is a big thing. What is its use ?.

I'm not 100% shure, as I'm no jdbc developer, but I think the 8.0 driver
consequently uses server-side prepared statements from V3 protocol, for
speed benefits, and thus needs all typing information. (I do not know
whether it is possible to send untyped parameters via this approach.)

Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com

Attachment

signature.asc

Re: ERROR: column "total_cost" is of type numeric but expression

From

Antony Paul

Date:

11 February 2005, 11:48:11

Now I understand why 8.0 drive dont print query parameters in the
server log while 7.4 prints query parameters since 8.0 uses real
prepared statements.

rgds
Antony Paul


On Fri, 11 Feb 2005 12:37:25 +0100, Markus Schaber
<schabios@logi-track.com> wrote:
> Hi, Antony,
>
> Antony Paul schrieb:
> > Updating application is going to be a lot of work. If it breaks
> > existing applications why it is implemented. I dont think type
> > checking is a big thing. What is its use ?.
>
> I'm not 100% shure, as I'm no jdbc developer, but I think the 8.0 driver
> consequently uses server-side prepared statements from V3 protocol, for
> speed benefits, and thus needs all typing information. (I do not know
> whether it is possible to send untyped parameters via this approach.)
>
> Markus
> --
> markus schaber | dipl. informatiker
> logi-track ag | rennweg 14-16 | ch 8001 zürich
> phone +41-43-888 62 52 | fax +41-43-888 62 53
> mailto:schabios@logi-track.com | www.logi-track.com
>
>
>