Library To Parse Sql Statements

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

Solution 1:

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

Solution 2:

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=>createview testview asselect foo,bar from a join b on a.x=b.y;
ERROR:  relation "a" does not exist
LINE 1: createview testview asselect foo,bar from a join b on a.x=...
                                                    ^=>rollback;
ROLLBACK

or this one (Oracle):

SQL>createview testview asselect foo,bar from a join b on a.x=b.y;
createview testview asselect foo,bar from a join b on a.x=b.y
                                                   *
ERROR at line 1:
ORA-00942: tableorview does not exist

SQL>rollback;

Rollback complete.

Solution 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;