How to pass long queries to TZQuery component
lazarus / 16-Databases

----------------------------------------------------------------------
Ronan · 2013-09-29 14:39

DEar All,

I always find it very difficult to pass long queries to the SQL
components, generally it's error prone and is very difficult to spot
where actually the malfunctioning portion of SQL query is. Because you
have to stick to the pascal and SQL syntax simultaneously and this 
cumbersome sometimes.

Isn't there any elegant or effective way of dealing when passing the
arguments to the SQL components.

For example, my code that I would like to pass to SQL component is
below.

Code: [Select]  SDS.SQL.Add('insert into Dc_copy
(elm_num,combo_name,Nmax,V2max,V3max,M2max,M3max) ' +
'with cte1 as (SELECT a.ELM_NUM as num,( trim(a.COMBO)||+0.3*||
trim(b.COMBO )) as comb,(a.N_1+0.3*b.N_1) as N1, '
+'(a.N_2+0.3*b.N_2) as N2,(a.v2_1+0.3*b.V2_1) as
V21,(a.v2_2+0.3*b.V2_2) as V22,(a.v3_1+0.3*b.V3_1) as
V31,(a.v3_2+0.3*b.V3_2) as V32, '
+'(a.M2_1+0.3*b.M2_1) as M21,(a.M2_2+0.3*b.M2_2) as
M22,(a.M3_1+0.3*b.M3_1) as M31,(a.M3_2+0.3*b.M3_2) as M32 '
+' FROM ntm a cross join ntm b where a.COMBO<>b.COMBO and '
+'a.ELM_NUM=b.ELM_NUM and ( ( position(''X'',a.COMBO)>0 and
position(''Y'',B.COMBO)>0) or ( position(''Y'',a.COMBO)>0 and
position(''X'',B.COMBO)>0) )'
+'order by a.ELM_NUM, a.combo),'

+'cte2 as (sELECT a.ELM_NUM as num, (trim(''1.4*''||a.COMBO)|| +||
''1.6*''||b.COMBO) as com2, b.ELM_NUM as num2, '
+'(1.4*a.N_1+1.6*b.N_1 ) as Nb1, (1.4*a.N_2+1.6*b.N_2) as
N2,(1.4*a.v2_1+1.6*b.V2_1) as V21,(1.4*a.v2_2+1.6*b.V2_2) as V22, '
+'(1.4*a.v3_1+1.6*b.V3_1) as V31,(1.4*a.v3_2+1.6*b.V3_2) as
V32,(1.4*a.M2_1+1.6*b.M2_1) as M21,(1.4*a.M2_2+1.6*b.M2_2) as M22,'
+'(1.4*a.M3_1+1.6*b.M3_1) as M31,(1.4*a.M3_2+1.6*b.M3_2) as M32 '
+'FROM NTM a cross join ntm b '
+'where ( (a.Combo=''dead'' and b.combo=''live'') or (a.Combo=''dead''
and b.combo=''live'') ) and a.ELM_NUM=b.ELM_NUM) '
+'select cte1.num, trim(cte2.com2)||'+'||trim(cte1.comb), '

+'case when abs(cte1.n1+cte2.nb1) >= abs(cte1.n2+cte2.n2) then
(cte1.n1+cte2.nb1) '
+'when abs(cte1.n1+cte2.nb1) < abs(cte1.n2+cte2.n2) then
(cte1.n2+cte2.n2) end as Nmax ,'

+'case when abs(cte1.V21+cte2.V21) >= abs(cte1.V22+cte2.V22) then
(cte1.V21+cte2.V21) when abs(cte1.V21+cte2.V21) <
abs(cte1.V22+cte2.V22) then (cte1.V22+cte2.V22) end as V2max, '

+' case when abs(cte1.V31+cte2.V31) >= abs(cte1.V32+cte2.V32) then
(cte1.V31+cte2.V31) when abs(cte1.V31+cte2.V31) <
abs(cte1.V32+cte2.V32) then (cte1.V32+cte2.V32) end as V3max,'

+' case when abs(cte1.M21+cte2.M21) >= abs(cte1.M22+cte2.M22) then
(cte1.M21+cte2.M21) when abs(cte1.M21+cte2.M21) <
abs(cte1.M22+cte2.M22) then (cte1.M22+cte2.M22) end as M2max,'

+'case when abs(cte1.M31+cte2.M31) >= abs(cte1.M32+cte2.M32) then
(cte1.M31+cte2.M31) when abs(cte1.M31+cte2.M31) <
abs(cte1.M32+cte2.M32) then (cte1.M32+cte2.M32) end as M3max'

+' from cte1 cross join cte2 where cte1.num = cte2.num2)');

----------------------------------------------------------------------
kodok.buncit · 2013-09-29 16:33

use TZSQLProcessor

----------------------------------------------------------------------
Ronan · 2013-09-29 18:09

Thank you very much,

It seems that zsqlProcessor is thing that I want, I googled a bit to
find the further information regarding the usage of SQLProcessor but
it was not available in the net probably due to the host change/move
in the ZeosLib library .


I kindly will ask you whether you can summarize it, in a short, how to
use it. For example, If I want to execute a query then  should I copy
the content to the script property of SQLProcessor and then call
SQLProcessor.Execute?

Even if I do so I again get bunch of unintelligible errors from SQL
engine, your help will be appreciated,

----------------------------------------------------------------------
zeljko · 2013-10-22 20:15

Quote from: Ronan on September 29, 2013, 04:39:30 pm
For example, my code that I would like to pass to SQL component is
below.

Code: [Select]  SDS.SQL.Add('insert into Dc_copy
(elm_num,combo_name,Nmax,V2max,V3max,M2max,M3max) ' +
'with cte1 as (SELECT a.ELM_NUM as num,( trim(a.COMBO)||+0.3*||
trim(b.COMBO )) as comb,(a.N_1+0.3*b.N_1) as N1, '
+'(a.N_2+0.3*b.N_2) as N2,(a.v2_1+0.3*b.V2_1) as
V21,(a.v2_2+0.3*b.V2_2) as V22,(a.v3_1+0.3*b.V3_1) as
V31,(a.v3_2+0.3*b.V3_2) as V32, '
+'(a.M2_1+0.3*b.M2_1) as M21,(a.M2_2+0.3*b.M2_2) as
M22,(a.M3_1+0.3*b.M3_1) as M31,(a.M3_2+0.3*b.M3_2) as M32 '
+' FROM ntm a cross join ntm b where a.COMBO<>b.COMBO and '
+'a.ELM_NUM=b.ELM_NUM and ( ( position(''X'',a.COMBO)>0 and
position(''Y'',B.COMBO)>0) or ( position(''Y'',a.COMBO)>0 and
position(''X'',B.COMBO)>0) )'
+'order by a.ELM_NUM, a.combo),'

+'cte2 as (sELECT a.ELM_NUM as num, (trim(''1.4*''||a.COMBO)|| +||
''1.6*''||b.COMBO) as com2, b.ELM_NUM as num2, '
+'(1.4*a.N_1+1.6*b.N_1 ) as Nb1, (1.4*a.N_2+1.6*b.N_2) as
N2,(1.4*a.v2_1+1.6*b.V2_1) as V21,(1.4*a.v2_2+1.6*b.V2_2) as V22, '
+'(1.4*a.v3_1+1.6*b.V3_1) as V31,(1.4*a.v3_2+1.6*b.V3_2) as
V32,(1.4*a.M2_1+1.6*b.M2_1) as M21,(1.4*a.M2_2+1.6*b.M2_2) as M22,'
+'(1.4*a.M3_1+1.6*b.M3_1) as M31,(1.4*a.M3_2+1.6*b.M3_2) as M32 '
+'FROM NTM a cross join ntm b '
+'where ( (a.Combo=''dead'' and b.combo=''live'') or (a.Combo=''dead''
and b.combo=''live'') ) and a.ELM_NUM=b.ELM_NUM) '
+'select cte1.num, trim(cte2.com2)||'+'||trim(cte1.comb), '

+'case when abs(cte1.n1+cte2.nb1) >= abs(cte1.n2+cte2.n2) then
(cte1.n1+cte2.nb1) '
+'when abs(cte1.n1+cte2.nb1) < abs(cte1.n2+cte2.n2) then
(cte1.n2+cte2.n2) end as Nmax ,'

+'case when abs(cte1.V21+cte2.V21) >= abs(cte1.V22+cte2.V22) then
(cte1.V21+cte2.V21) when abs(cte1.V21+cte2.V21) <
abs(cte1.V22+cte2.V22) then (cte1.V22+cte2.V22) end as V2max, '

+' case when abs(cte1.V31+cte2.V31) >= abs(cte1.V32+cte2.V32) then
(cte1.V31+cte2.V31) when abs(cte1.V31+cte2.V31) <
abs(cte1.V32+cte2.V32) then (cte1.V32+cte2.V32) end as V3max,'

+' case when abs(cte1.M21+cte2.M21) >= abs(cte1.M22+cte2.M22) then
(cte1.M21+cte2.M21) when abs(cte1.M21+cte2.M21) <
abs(cte1.M22+cte2.M22) then (cte1.M22+cte2.M22) end as M2max,'

+'case when abs(cte1.M31+cte2.M31) >= abs(cte1.M32+cte2.M32) then
(cte1.M31+cte2.M31) when abs(cte1.M31+cte2.M31) <
abs(cte1.M32+cte2.M32) then (cte1.M32+cte2.M32) end as M3max'

+' from cte1 cross join cte2 where cte1.num = cte2.num2)');

That looks pretty bad. I'm using such scenario:
with MyTZReadOnlyQuery.SQL do
begin
   Add('SELECT bl,bla,sf.dfs,dbldgs,');
   Add(etc,etc,ect ');
   Add(' FROM table1 tb1 ');
   Add(' JOIN table2 tb2 ON tb2.id=tb1.tb2id ');
   Add(' WHERE something .....');
end;

This is much cleaner than "put everything in one string".