Page 1 of 1
[PostgreSQL] inconsistent types deduced for parameter $1
Posted: Thu 15 Sep 2011 15:39
by MamProblem
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!
Posted: Fri 16 Sep 2011 10:13
by AlexP
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.
Posted: Fri 16 Sep 2011 11:23
by MamProblem
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.
Posted: Fri 16 Sep 2011 12:00
by AlexP
Hello,
Please send a complete script to create your table.
Posted: Fri 16 Sep 2011 12:14
by MamProblem
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;
Posted: Mon 19 Sep 2011 09:42
by AlexP
Hello,
Thank you for the information.
We have reproduced the problem.
We will notify you as soon as we have any results.
Posted: Mon 03 Oct 2011 11:10
by AlexP
Hello,
To resolve the problem, you should set the UseParamTypes option to True like:
PgQuery1.Options.UseParamTypes:= True;