Library to parse SQL statements.

Question

I would like to be able to parse an arbitrary SQL SELECT statement and retrieve the various component parts (columns, relations, JOIN conditions, WHERE conditions, ORDER BY columns), ideally using Delphi. A quick google search shows several different freeware products, but it's not clear if they are complete and/or under active development.

My immediate need to just to extract the list of relations used in a series of VIEW definitions in order ensure that required views or tables exist before I try to CREATE the view. So, for instance, for the statement:

SELECT PersonID, LastName, OrderID 
FROM People P INNER JOIN Orders O ON P.PersonID = O.PersonID

I need to get back the values "People" and "Orders". (Obviously, this is a simple example. I want to be able to handle more complex cases where, for instance, the word "FROM" might appear in the column list as part of an expression).

I'm trying to provide this service in a database that allows use of STDCALL functions exported from DLLs, so ideally any candidate library would be callable from Delphi or C.

Answer 1

SQL parsers are complicated.

Did you think of such approach:

1. Begin transaction.
2. Send the CREATE VIEW command to the server.
3. catch the error (any decent database driver should be able to do this).
4. if there was an error, parse the error message and show missing tables to the client.
5. rollback

see this example (PostgreSQL):

=> begin;
BEGIN
=> create view testview as select foo,bar from a join b on a.x=b.y;
ERROR:  relation "a" does not exist
LINE 1: create view testview as select foo,bar from a join b on a.x=...
                                                    ^
=> rollback;
ROLLBACK

or this one (Oracle):

SQL> create view testview as select foo,bar from a join b on a.x=b.y;
create view testview as select foo,bar from a join b on a.x=b.y
                                                   *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> rollback;

Rollback complete.

Answer 2

Take a look at Gold Parser. It's got a Delphi version available, and SQL grammar on the download page.

Answer 3

You can use Delphi with ADODB.

Use a TADOQuery to test whether your query is good or not without really opening the recordset. Your can also retrive the fields names of the query.

Drop a TADOConnection on a form. Drop a TMemo and a TButton and try this code :

procedure TForm1.Button1Click(Sender: TObject);
var
  lADOQuery : TADOQuery;
  lFieldNames : TStrings;
begin
  lADOQuery := TADOQuery.Create(nil);
  try
    lADOQuery.Connection := ADOConnection1;
    lADOQuery.SQL.Text := Memo1.Text;
    lFieldNames := TStringList.create;
    try
      lADOQuery.GetFieldNames(lFieldNames);

      showmessage(lFieldNames.Text); // Show fieldNames of the query

      // To show that the dataset is not actually opened try this :
      // Throws an exception ( Dataset closed )
      //showmessage(inttostr(  lADOQuery.RecordCount ));
    except
      On e: Exception do
        ShowMessage('Invalid query');
    end;
    lFieldNames.free;
  finally
    lADOQuery.free;
  End;
end;