Saturday, April 26, 2014

Create dynamic SQL at runtime

Long time ago I used this routine for creating dynamic sql at run time that uses sql parameter instead of string values.

function CreateSQLedit(SqlTable,fieldnames,wherefields : string; vf,vw: array of variant) : integer;
var i : integer; s,t : string; st, sw : tstringlist;
//vw for wherefields clauses & vf for fieldnames
CONST c = #13#10;
begin
 if (pos(' ',fieldnames)>0)or(pos(',',fieldnames)>0) then
 begin showmessage('Error in fieldnames format'); result:=-100;
       exit
 end;
 if (pos(' ',wherefields)>0)or(pos(',',wherefields)>0) then
 begin showmessage('Error in where field format'); result:=-100;
       exit
 end;
  st:=tstringlist.Create; st.Sorted:=false; sW:=tstringlist.Create; sW.Sorted:=false;
  t:=fieldnames;
  repeat
   i:=pos(';',t); if i=0 then break;
   delete(t,i,1); insert(c,t,i)
  until false;
  st.Text:=t;
  if st.count-1<>high(vf) then
  begin showmessage('Mismatch in number of fields and their values'); st.Free; sw.Free;
        exit
  end;
  t:=trim(wherefields);
  repeat
   i:=pos(';',t); if i=0 then break;
   delete(t,i,1); insert(c,t,i)
  until false;
  sw.Text:=t;
  s:='UPDATE '+SqlTable+c+'SET'+c;
  with st do
   for i:=0 to count-1 do
   s:=s+strings[i] +' =:'+strings[i]+',';
   setlength(s, length(s)-1); s:=s+c+'WHERE ';

   with sw do
   for i:=0 to count-1 do
   s:=c+s+strings[i]+' =:'+strings[i]+' AND ';
   setlength(s,length(s)-5);
   //CLIPBOARD.AsText:=S;
   with dm.qrygeneral do
   begin sql.Clear; Parameters.Clear; sql.Text:=s;
     parameters.ParseSQL(s, true); //not neded for BDE
     for i:=0 to high(vf) do
     parameters.ParamByName(st[i]).Value:=vf[i];

     for i:=0 to high(vw) do
     parameters.ParamByName(sw[i]).Value:=vw[i];
     Execsql
   end;
   st.Free; sw.Free
end;

No comments:

Post a Comment