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