Parameter error in 7.1.2-stable
Posted: 16.10.2013, 20:33
on Delphi 2009 / Windows XP, PosgreSQL 9.2 (windows xp)
using the svn://svn.code.sf.net/p/zeoslib/code-0/tags/7.1.2-stable revision 2835
a issue occurs when use parameters with same name "select * from test where ( id = :X0 or :X0 is null)"
In version 7.0.6-stable that problem nor occurs
Sample:
Log of postgres server
Log of ZSQLMonitor
using the svn://svn.code.sf.net/p/zeoslib/code-0/tags/7.1.2-stable revision 2835
a issue occurs when use parameters with same name "select * from test where ( id = :X0 or :X0 is null)"
In version 7.0.6-stable that problem nor occurs
Sample:
Code: Select all
procedure TForm1.Button1Click(Sender: TObject);
var QAux : TZQuery;
begin
QAux := TZQuery.Create(Self);
try
QAux.Connection := ZConnection1;
QAux.SQL.Text := 'drop table if exists test;';
QAux.ExecSQL;
// Create table
QAux.SQL.Text := 'create table test ( id int4, name varchar(50) );';
QAux.ExecSQL;
// Populate data
QAux.SQL.Text := 'select * from test';
QAux.Open;
QAux.Append;
QAux.FieldByName('id').asInteger := 1;
QAux.FieldByName('name').asString := 'nam1';
QAux.Post;
QAux.Append;
QAux.FieldByName('id').asInteger := 2;
QAux.FieldByName('name').asString := 'nam2';
QAux.Post;
QAux.Append;
QAux.FieldByName('id').asInteger := 3;
QAux.FieldByName('name').asString := 'nam3';
QAux.Post;
QAux.Append;
QAux.FieldByName('id').Clear;
QAux.FieldByName('name').asString := 'namnul';
QAux.Post;
// Produce problem
QAux.SQL.Text := 'select * from test where ( id = :P0 or :P0 is null) ';
QAux.ParamByName('P0').asInteger := 2;
QAux.Open;
ShowMessage( IntToStr(QAux.RecordCount) );
QAux.Close;
finally
QAux.Free;
end;
end;
Code: Select all
2013-10-09 18:00:43 BRT LOG: duração: 20.000 ms comando: select setting from pg_settings where name = 'bytea_output'
2013-10-09 18:00:46 BRT LOG: duração: 10.000 ms comando: SELECT version()
2013-10-09 18:00:46 BRT LOG: duração: 20.000 ms comando: drop table if exists test;
2013-10-09 18:00:46 BRT LOG: duração: 10.000 ms comando: create table test ( id int4, name varchar(50) );
2013-10-09 18:00:46 BRT LOG: duração: 0.000 ms comando: select * from test
2013-10-09 18:00:46 BRT LOG: duração: 30.000 ms comando: SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,pg_get_expr(def.adbin, def.adrelid) as adsrc,dsc.description FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog') WHERE a.attnum > 0 AND NOT a.attisdropped AND c.relname = E'test' ORDER BY nspname,relname,attnum
2013-10-09 18:00:46 BRT LOG: duração: 0.000 ms comando: SELECT oid, typname, typbasetype,typtype FROM pg_type WHERE (typtype = 'b' and oid < 10000) OR typtype = 'p' OR typtype = 'e' OR typbasetype<>0 ORDER BY oid
2013-10-09 18:00:46 BRT LOG: duração: 0.000 ms análise de 131811589: INSERT INTO public.test (id,"name") VALUES ($1,$2)
2013-10-09 18:00:46 BRT LOG: duração: 0.000 ms ligação 131811589: INSERT INTO public.test (id,"name") VALUES ($1,$2)
2013-10-09 18:00:46 BRT DETALHE: parâmetros: $1 = '1', $2 = 'nam1'
2013-10-09 18:00:46 BRT LOG: duração: 0.000 ms executar 131811589: INSERT INTO public.test (id,"name") VALUES ($1,$2)
2013-10-09 18:00:46 BRT DETALHE: parâmetros: $1 = '1', $2 = 'nam1'
2013-10-09 18:00:46 BRT LOG: duração: 0.000 ms ligação 131811589: INSERT INTO public.test (id,"name") VALUES ($1,$2)
2013-10-09 18:00:46 BRT DETALHE: parâmetros: $1 = '2', $2 = 'nam2'
2013-10-09 18:00:46 BRT LOG: duração: 0.000 ms executar 131811589: INSERT INTO public.test (id,"name") VALUES ($1,$2)
2013-10-09 18:00:46 BRT DETALHE: parâmetros: $1 = '2', $2 = 'nam2'
2013-10-09 18:00:46 BRT LOG: duração: 0.000 ms ligação 131811589: INSERT INTO public.test (id,"name") VALUES ($1,$2)
2013-10-09 18:00:46 BRT DETALHE: parâmetros: $1 = '3', $2 = 'nam3'
2013-10-09 18:00:46 BRT LOG: duração: 0.000 ms executar 131811589: INSERT INTO public.test (id,"name") VALUES ($1,$2)
2013-10-09 18:00:46 BRT DETALHE: parâmetros: $1 = '3', $2 = 'nam3'
2013-10-09 18:00:46 BRT LOG: duração: 0.000 ms ligação 131811589: INSERT INTO public.test (id,"name") VALUES ($1,$2)
2013-10-09 18:00:46 BRT DETALHE: parâmetros: $1 = NULL, $2 = 'namnul'
2013-10-09 18:00:46 BRT LOG: duração: 0.000 ms executar 131811589: INSERT INTO public.test (id,"name") VALUES ($1,$2)
2013-10-09 18:00:46 BRT DETALHE: parâmetros: $1 = NULL, $2 = 'namnul'
2013-10-09 18:00:46 BRT LOG: duração: 0.000 ms comando: DEALLOCATE "131811589";
2013-10-09 18:00:46 BRT ERRO: não pôde determinar o tipo de dado do parâmetro $2
2013-10-09 18:00:46 BRT COMANDO: select * from test where ( id = $1 or $2 is null)
2013-10-09 18:00:46 BRT LOG: duração: 0.000 ms comando: select * from test where ( id = 2 or 2 is null)
Log of ZSQLMonitor
Code: Select all
2013-10-09 17:53:10 cat: Execute, proto: postgresql-9, msg: select setting from pg_settings where name = 'bytea_output'
2013-10-09 17:53:12 cat: Execute, proto: postgresql-9, msg: SELECT version()
2013-10-09 17:53:12 cat: Execute, proto: postgresql-9, msg: drop table if exists test;
2013-10-09 17:53:12 cat: Execute prepared, proto: postgresql-9, msg: Statement 1
2013-10-09 17:53:12 cat: Execute, proto: postgresql-9, msg: create table test ( id int4, name varchar(50) );
2013-10-09 17:53:12 cat: Execute prepared, proto: postgresql-9, msg: Statement 2
2013-10-09 17:53:12 cat: Execute, proto: postgresql-9, msg: select * from test
2013-10-09 17:53:12 cat: Execute prepared, proto: postgresql-9, msg: Statement 3
2013-10-09 17:53:12 cat: Execute, proto: postgresql-9, msg: SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,pg_get_expr(def.adbin, def.adrelid) as adsrc,dsc.description FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog') WHERE a.attnum > 0 AND NOT a.attisdropped AND c.relname = E'test' ORDER BY nspname,relname,attnum
2013-10-09 17:53:12 cat: Execute, proto: postgresql-9, msg: SELECT oid, typname, typbasetype,typtype FROM pg_type WHERE (typtype = 'b' and oid < 10000) OR typtype = 'p' OR typtype = 'e' OR typbasetype<>0 ORDER BY oid
2013-10-09 17:53:12 cat: Prepare, proto: postgresql-9, msg: PREPARE 'INSERT INTO public.test (id,"name") VALUES ($1,$2)'
2013-10-09 17:53:12 cat: Execute prepared, proto: postgresql-9, msg: INSERT INTO public.test (id,"name") VALUES (?,?)
2013-10-09 17:53:12 cat: Execute prepared, proto: postgresql-9, msg: Statement 4
2013-10-09 17:53:12 cat: Execute prepared, proto: postgresql-9, msg: INSERT INTO public.test (id,"name") VALUES (?,?)
2013-10-09 17:53:12 cat: Execute prepared, proto: postgresql-9, msg: Statement 4
2013-10-09 17:53:12 cat: Execute prepared, proto: postgresql-9, msg: INSERT INTO public.test (id,"name") VALUES (?,?)
2013-10-09 17:53:12 cat: Execute prepared, proto: postgresql-9, msg: Statement 4
2013-10-09 17:53:12 cat: Execute prepared, proto: postgresql-9, msg: INSERT INTO public.test (id,"name") VALUES (?,?)
2013-10-09 17:53:12 cat: Execute prepared, proto: postgresql-9, msg: Statement 4
2013-10-09 17:53:12 cat: Unprepare prepared, proto: postgresql-9, msg: DEALLOCATE "131811589";
2013-10-09 17:53:12 cat: Prepare, proto: postgresql-9, msg: PREPARE 'select * from test where ( id = $1 or $2 is null)', errcode: 0, error: ERRO: não pôde determinar o tipo de dado do parâmetro $2
2013-10-09 17:53:12 cat: Prepare, proto: postgresql-9, msg: PREPARE 'select * from test where ( id = $1 or $2 is null)'
2013-10-09 17:53:12 cat: Execute, proto: postgresql-9, msg: select * from test where ( id = ? or ? is null)
2013-10-09 17:53:12 cat: Execute prepared, proto: postgresql-9, msg: Statement 5