Library for parsing SQL statements

Question

I would like to be able to parse an arbitrary SQL SELECT statement and retrieve various components (columns, relationships, JOIN conditions, WHERE conditions, ORDER BY columns), ideally using Delphi. A quick Google search reveals several different free products, but it is unclear whether they are complete and / or under active development.

I need to immediately extract the list of relationships used in the VIEW series of definitions to ensure that the required views or tables exist before I try to CREATE the view. So, for example, for approval:

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

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

I'm trying to provide this service in a database that allows you to use the STDCALL functions exported from a DLL, so ideally any candidate library can be called from Delphi or C.

Answer 1

Take a look at Gold Parser. It has a Delphi version and SQL grammar on the download page.

Answer 2

SQL parsers are complex.

You are thinking about this approach:

• Start a transaction.
• Send the CREATE VIEW command to the server.
• catch the error (any decent database driver should do this).
• If an error occurs, analyze the error message and show the missing tables to the client.
• Rollback

see this example (PostgreSQL):

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

or this one (Oracle):

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

Answer 3

You can use Delphi with ADODB.

Use TADOQuery to check if your query is good or not without opening a recordset. You can also get query field names.

Drop the TADOConnection on the form. Remove TMemo and 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;