Parse params from an sql query using delphi
How i can parse and extract the parameters from an SQL Query开发者_运维百科 using delphi?
example :
from this query
SELECT * FROM MyTable
WHERE Field1=:Param1
AND Field2=:Param2
AND (Field3=:Param3 OR Field4=:Param4)
i want to obtain
Param1 Param2 Param3 Param4
Thanks in advance.
@Salvador, you can use the TParams.ParseSQL function to get the parameters.
see this simple code
program Project241;
{$APPTYPE CONSOLE}
uses
db, //for use TParams
Classes,//
SysUtils;
procedure GetParamsFromSQL(SQL:string;Const ListParams:TStrings);
var
ParamList: TParams;
i: integer;
begin
ListParams.Clear;//Clear the list
ParamList := TParams.Create(nil);
try
ParamList.ParseSQL(SQL, True); //Force to create the params from the SQL
for i := 0 to ParamList.Count - 1 do
ListParams.Add(ParamList[i].Name);
finally
ParamList.Free;
end;
end;
var
ParamList : TStrings;
begin
ParamList:=TStringList.Create;
try
GetParamsFromSQL('SELECT * FROM MyTable WHERE Field1=:Param1 AND Field2=:Param2 AND (Field3=:Param3 OR Field4=:Param4)',ParamList);
Writeln(ParamList.text);
Readln;
finally
ParamList.Free;
end;
end.
returns :
Param1
Param2
Param3
Param4
For a simple query like this, you can hack up a simple text-reading algorithm like the following. It works for your specific query, and it might be "good enough". Just pass in an empty TStringList.
uses
StrUtils;
procedure ExtractParams(input: string; output: TStrings);
var
colon, endpoint: integer;
begin
colon := pos(':', input);
while colon <> 0 do
begin
input := RightStr(input, length(input) - colon);
endpoint := 0;
repeat
inc(endpoint)
until input[endpoint] in [' ', ')']; //add other characters here as necessary
output.Add(LeftStr(input, endpoint - 1));
colon := pos(':', input);
end;
end;
If you want to do more complicated SQL parsing, though, your best bet would be to look at a real parser. Take a look at GOLD Parser, which can parse several different languages, including SQL, based on language definition files. There's a Delphi implementation of the parser available on the website.
精彩评论