Select SQL String
-
My problem has 2 workflows: WF1:=>I'm OK<=With an active connection, the user can drag and drop fields in a table and I'm succesfully generate an SQL string. WF2:=>I'm now in trouble<= I let the user type the SQL string in a richtextbox. And now I must display all the tables, the relation ships, the fields etc... specified in the SQL string typed by the user. Would you please give me some helps? I really need an effective algorithm to parse this SQL string. Thank you in advance
-
My problem has 2 workflows: WF1:=>I'm OK<=With an active connection, the user can drag and drop fields in a table and I'm succesfully generate an SQL string. WF2:=>I'm now in trouble<= I let the user type the SQL string in a richtextbox. And now I must display all the tables, the relation ships, the fields etc... specified in the SQL string typed by the user. Would you please give me some helps? I really need an effective algorithm to parse this SQL string. Thank you in advance
I have written a basic SQL parser before, but it was parsing generated SQL, which was fairly tightly under my control anyway. I would not want to do it for SQL that the user typed! SQL can be very complex, with nested queries and multiple ways of writing the same query. Most typed queries will be simple though, so maybe you can get by with something basic, that works in 90% of cases. Alternate solution 1: I would leverage what I could from SQL Server. In SQL Query Analyzer, you can look at the query plan for a particular query. This shows some of what you are interested in. Perhaps you could find out how that is done, and use a similar technique. Alternate solution 2: SQL Enterprise and SQL Query analyzer both have a query designer tool that does something similar to what you want. It may be that there is an underlying API that you could utilize. Alternate solution 3: Create a temporary view from the SQL, and then use various techniques to analyze that view. sp_depends will tell you the tables, sp_help will tell you the result structure. Not sure how you would tell the relationships though.
-
I have written a basic SQL parser before, but it was parsing generated SQL, which was fairly tightly under my control anyway. I would not want to do it for SQL that the user typed! SQL can be very complex, with nested queries and multiple ways of writing the same query. Most typed queries will be simple though, so maybe you can get by with something basic, that works in 90% of cases. Alternate solution 1: I would leverage what I could from SQL Server. In SQL Query Analyzer, you can look at the query plan for a particular query. This shows some of what you are interested in. Perhaps you could find out how that is done, and use a similar technique. Alternate solution 2: SQL Enterprise and SQL Query analyzer both have a query designer tool that does something similar to what you want. It may be that there is an underlying API that you could utilize. Alternate solution 3: Create a temporary view from the SQL, and then use various techniques to analyze that view. sp_depends will tell you the tables, sp_help will tell you the result structure. Not sure how you would tell the relationships though.
Would you please tell me more about the Solution 2? It will be better if I can utilize some API, but which API? Can you show me an example using these API? And would you mind send me your project that you've written? Thank you.