[PostgreSQL] inconsistent types deduced for parameter $1

MamProblem · Post by **MamProblem** » Thu 15 Sep 2011 15:39

Hi!

I use UniDAC 3.70.0.19, C++ Builder XE and PostgreSQL 9.0.4

Following query was causing 'inconsistent types deduced for parameter $1' error:

Code: Select all

INSERT INTO tbl_name (f1,f2,f3,f4,f5) VALUES (:_f1,:_f2,(SELECT id FROM tbl2_name WHERE name = :_f1),:_f4,:_f5) RETURNING id;
...
q->ParamByName("_f1")->AsString = "ala";
q->ParamByName("_f2")->AsString = "ma";
...
q->ParamByName("_f5")->AsString = "kota";

As far as You can see, I used param _f1 twice, and that was the problem, I think. I solved this by splitting this param into two new params:

Code: Select all

INSERT INTO tbl_name (f1,f2,f3,f4,f5) VALUES (:_f1,:_f2,(SELECT id FROM tbl2_name WHERE name = :_f12),:_f4,:_f5) RETURNING id;
...
q->ParamByName("_f1")->AsString = "ala";
q->ParamByName("_f11")->AsString = "ala";
q->ParamByName("_f2")->AsString = "ma";
...
q->ParamByName("_f5")->AsString = "kota";

But that's just provisional fix, or maybe I have to use each parameter only one time in a query?

Hope U'll solve this problem

Thx!

AlexP · Post by **AlexP** » Fri 16 Sep 2011 10:13

Hello,

This problem is connected with the fact that tbl_name.f1 and tbl2_name.name fields in your tables have different type and you cannot use the same parameter for different data types.

MamProblem · Post by **MamProblem** » Fri 16 Sep 2011 11:23

Hi!

I just checked it, and both fields are strings. Maybe it's connected with the fact, that I try to use this param with nested query? - In this situation third param in a row is integer:

Code: Select all

    1             2                   3?                     lol?
_f1{STRING},_f2{STRING},(SELECT {INT} ...WHERE x{STRING} = _f1{STRING}),...

And btw: I wrote tbl_name and tbl2_name ,but both are the same table.

AlexP · Post by **AlexP** » Fri 16 Sep 2011 12:00

Hello,

Please send a complete script to create your table.

MamProblem · Post by **MamProblem** » Fri 16 Sep 2011 12:14

Code: Select all

CREATE TABLE czesc
(
  id SERIAL NOT NULL,
  nazwa character varying(20),
  seria character varying(25),
  id_typ integer,
  data_waznosci date,
  data_wprowadzenia date,
  czas_wprowadzenia time without time zone,
  nr_osoby_wprowadz integer,
  ilosc_wprowadzonych integer,
  CONSTRAINT czesc_kg PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

Code: Select all

INSERT INTO czesc (nazwa,seria,id_typ,data_waznosci,data_wprowadzenia,czas_wprowadzenia,nr_osoby_wprowadz,ilosc_wprowadzonych) 
VALUES (:__nazwa,:__seria,(SELECT DISTINCT id_typ FROM czesc WHERE nazwa = :__nazwa LIMIT 1),
:__data_waznosci,current_date,current_time,:__os_wprowadz,1) RETURNING id;

AlexP · Post by **AlexP** » Mon 19 Sep 2011 09:42

Hello,

Thank you for the information.
We have reproduced the problem.
We will notify you as soon as we have any results.

AlexP · Post by **AlexP** » Mon 03 Oct 2011 11:10

Hello,

To resolve the problem, you should set the UseParamTypes option to True like:

PgQuery1.Options.UseParamTypes:= True;