Re: error: operator does not exist: bigint = character varying

Hi,

I am updating from 8.2.4 to 8.3dev
the jdbc driver is postgresql-8.3dev-600.jdbc3.jar

Now my application raises this exception (all was ok with 8.2):
org.postgresql.util.PSQLException: ERROR: operator does not exist:
bigint = character varying

this is my code fragment
----------------------------------------------------------------------
PreparedStatement ps = connessione.prepareStatement("SELECT
strato.idplot, strato.idstrato, strato.nome, strato.copertura,
strato.altezza, strato.note FROM plot WHERE idplot=?");
ResultSet risultatoStrati;
ps.setString(1, rilievo.dato[Rilievo.ID]);
risultatoStrati = istruzioneStrati.executeQuery();
----------------------------------------------------------------------
The error is in the last istruction, it seems that I am doing somehing
not clean with types.

the type of idplot is
"idplot integer NOT NULL DEFAULT nextval('sequenza_plot'::regclass)"

I did a try with
ps.setInt(1, Integer.parseInt(rilievo.dato[Rilievo.ID]));

but nothing is changed.

Whath I am doing wrong?

If I exec "SELECT
strato.idplot,strato.idstrato,strato.nome,strato.copertura,strato.altezza,strato.note
FROM strato WHERE idplot=567" from pgadmin all goes well.

thank you
Edoardo

--
Jabber: edoardopa(at)talk(dot)google(dot)com
tel: 075 9142766

Edoardo Panfili wrote:

> org.postgresql.util.PSQLException: ERROR: operator does not exist:
> bigint = character varying

> ps.setString(1, rilievo.dato[Rilievo.ID]);

Use a setter that's appropriate to the type e.g. setLong(), not setString().

-O

Edoardo Panfili wrote:

> I did a try with
> ps.setInt(1, Integer.parseInt(rilievo.dato[Rilievo.ID]));
>
> but nothing is changed.

Oops, sorry, I missed that you'd already tried that. That is the right
idea, so I'd doublecheck that you're running the updated code when you
do that and that you still get exactly the same error.

-O

Edoardo Panfili <edoardo(at)aspix(dot)it> writes:
> Now my application raises this exception (all was ok with 8.2):
> org.postgresql.util.PSQLException: ERROR: operator does not exist:
> bigint = character varying

> this is my code fragment
> ----------------------------------------------------------------------
> PreparedStatement ps = connessione.prepareStatement("SELECT
> strato.idplot, strato.idstrato, strato.nome, strato.copertura,
> strato.altezza, strato.note FROM plot WHERE idplot=?");
> ps.setString(1, rilievo.dato[Rilievo.ID]);
> ----------------------------------------------------------------------
> the type of idplot is
> "idplot integer NOT NULL DEFAULT nextval('sequenza_plot'::regclass)"

Better look again, because that error message says that the lefthand
input is bigint not integer. Are you looking at the right table?

In any case you should not be using setString() for an integer query
parameter. This would have worked in 8.2 only for rather small values
of "work" --- you would have gotten a textual not a numeric comparison,
which among other things would have defeated the use of any index on the
idplot column.

regards, tom lane

Tom Lane ha scritto:
> Edoardo Panfili <edoardo(at)aspix(dot)it> writes:
>> Now my application raises this exception (all was ok with 8.2):
>> org.postgresql.util.PSQLException: ERROR: operator does not exist:
>> bigint = character varying
>
>> this is my code fragment
>> ----------------------------------------------------------------------
>> PreparedStatement ps = connessione.prepareStatement("SELECT
>> strato.idplot, strato.idstrato, strato.nome, strato.copertura,
>> strato.altezza, strato.note FROM plot WHERE idplot=?");
>> ps.setString(1, rilievo.dato[Rilievo.ID]);
>> ----------------------------------------------------------------------
>> the type of idplot is
>> "idplot integer NOT NULL DEFAULT nextval('sequenza_plot'::regclass)"
>
> Better look again, because that error message says that the lefthand
> input is bigint not integer. Are you looking at the right table?
Only partially, there are other query that must be cleaned in my code,
(it seems that postgres does more type checking in every new version, a
good thing).

> In any case you should not be using setString() for an integer query
> parameter. This would have worked in 8.2 only for rather small values
> of "work" --- you would have gotten a textual not a numeric comparison,
> which among other things would have defeated the use of any index on the
> idplot column.
All goes well now,
thank you (also to Oliver).

Edoardo
--
Jabber: edoardopa(at)talk(dot)google(dot)com
tel: 075 9142766