sql-server – SQL语法解析器在Delphi for SQL Server

发布时间：2020-12-12 08:43:37 所属栏目：MsSql教程来源：网络整理

导读：我在Delphi(2010)为SQL Server寻找一个好的SQL解析器.我需要这样的东西来解析一个查询并提取：select list,where and order by子句.无论是商业还是开源解决方案都没关系.它也可以是一个DLL(当然用任何语言编写),但是我更喜欢使用VCL组件. 解决方法 Rafael Del

我在Delphi(2010)为SQL Server寻找一个好的SQL解析器.我需要这样的东西来解析一个查询并提取：select list,where and order by子句.无论是商业还是开源解决方案都没关系.它也可以是一个DLL(当然用任何语言编写),但是我更喜欢使用VCL组件.

解决方法

Rafael Delphi自带了一个位于DBCommon单元中的sql解析器

检查这些功能

function NextSQLToken(var p: PAnsiChar; out Token: AnsiString; CurSection: TSQLToken): TSQLToken; overload;
function NextSQLToken(var p: PWideChar; out Token: WideString; CurSection: TSQLToken): TSQLToken; overload;
function NextSQLToken(var p: PChar; out Token: String; CurSection: TSQLToken): TSQLToken; overload;

function GetIndexForOrderBy(const SQL: WideString; DataSet: TDataSet): TIndexDef;
function GetTableNameFromSQL(const SQL: WideString): WideString;
function GetTableNameFromQuery(const SQL: Widestring): Widestring;
function AddParamSQLForDetail(Params: TParams; SQL: WideString; Native: Boolean; QuoteChar: WideString = ''): WideString;
function IsMultiTableQuery(const SQL: WideString): Boolean;
function SQLRequiresParams(const SQL: WideString): Boolean;

function NextSQLTokenEx(var p: PWideChar; out Token: UnicodeString; CurSection: TSQLToken; IdOption: IDENTIFIEROption): TSQLToken; overload;
function NextSQLTokenEx(var p: PWideChar; out Token: WideString; CurSection: TSQLToken; IdOption: IDENTIFIEROption): TSQLToken; overload;
function NextSQLTokenEx(var p: PAnsiChar; out Token: AnsiString; CurSection: TSQLToken; IdOption: IDENTIFIEROption): TSQLToken; overload;
function GetTableNameFromSQLEx(const SQL: WideString; IdOption: IDENTIFIEROption): WideString;

这是一个非常简单的示例来显示如何解析sql语句并获取所有元素.

uses
  TypInfo,DbCommon,SysUtils;


const
  StrSql ='Select Field1,Field2,54 field3,Field4 from Mytable1 Order by Field1,Field5';

procedure ParseSql(Const Sql : string);
var
  SQLToken     : TSQLToken;
  CurSection   : TSQLToken;
  Start        : PWideChar;
  Token        : WideString;
  IdOption     : IDENTIFIEROption;
begin
  IdOption   :=idMixCase;
  Start      :=PWideChar(StrSql);
  CurSection := stUnknown;
  repeat
    SQLToken := NextSQLTokenEx(Start,Token,CurSection,IdOption);
    if SQLToken<>stEnd then
    Writeln(Format('Type %s Token %s',[GetEnumName(TypeInfo(TSQLToken),integer(SQLToken)),Token]));
    CurSection := SQLToken;
  until SQLToken in [stEnd];
end;



begin
  try
    ParseSql(StrSql);
  except
    on E: Exception do
      Writeln(E.ClassName,': ',E.Message);
  end;
  Readln;
end.

这将返回

Type stSelect Token Select
Type stFieldName Token Field1
Type stFieldName Token Field2
Type stNumber Token 54
Type stFieldName Token field3
Type stFieldName Token Field4
Type stFrom Token from
Type stTableName Token Mytable1
Type stOrderBy Token Order by
Type stFieldName Token Field1
Type stFieldName Token Field5

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!