looking for a T-SQL Parser for .NET

  • Thread starter Thread starter google
  • Start date Start date
G

google

Hi,

sorry for the cross-post but I'm a bit unsure on which side of the line
I could find such things. I'm looking for a T-SQL Parser in .NET that
produces an abstract syntax tree. Ideally it should also be able to
parse Stored Procedures as well. I would hate to have to write one
myself but would be willing to continue the work on it in case someone
has already started working on it.

Thanks for any input,

Eduard Ralph
 
Eduard,

Why would you want to parse TSQL at all? Usually that's SQL Server's
job.
 
Alexander Kuznetsov said:
Eduard,

Why would you want to parse TSQL at all? Usually that's SQL Server's
job.
Click to expand...

Alas, I know all to well a reason: MS does not make its QA SQL editor
component available for reuse. I have my own tools to author stored
procedures and such in both thin and thick client, and in both cases I have
to go without colored text because there is no reasonably good T-SQL parser
out there, .NET or otherwise.

Paul
 
PJ6 said:
I have my own tools to author stored
procedures and such in both thin and thick client, and in both cases I have
to go without colored text because there is no reasonably good T-SQL parser
out there, .NET or otherwise.
Click to expand...

The COM component Microsoft SQL Parser Object Library (SQLPARSE.DLL) is
reasonably good, IMO.

Jamie.

--
 
There are many good reasons. One good reason is for automated T-SQL code
analysis such as enforcing coding standards. Another good reason is to
refactor a script.

Linchi
 
There usually is a little problem with "automated T-SQL code analysis
that is enforcing coding standards": it is very challenging to do it
right.
In most cases such enforsings are a little bit behind the features of
the databases, they won't let you use some more advanced features, and
some time down the road they might even start introducing errors.

For instance, once upon a time, long long ago someone started to
enforse a rule "each table name should be preceded with schema". For a
while it worked. Yet several years and versions later it introduced an
error:
-------------------------VVVVVVVV my_schema added to enforse a coding
standard
select <columns> from (my_schema.select sum(amount) total_amt ...
 
Linchi said:
I don't think it returns you a parse tree for further processing.
Click to expand...

No but I was replying to PJ6, whose goal is 'colored text', for which
SQLPARSE.DLL works fine.

Jamie.

--
 
Good point,

aside from trying to do pretty printing and colouring or enforcing
coding standards, another possibility (the trail I'm following now) is
to analyze the stored proc and automate the calling from C# of it. I
realize the overhead and being able to short circuit at least some of
it would be a blessing.
Contrary to what one of the posters said, I've found one which seems to
look like a passable parser after googling. But it seems to be the only
one and it is missing a few of the more advanced features like groups,
etc. I'm surprised that there are so few of the around - I would have
thought that 20 years of SQL would have inspired at least a few.
Anyway, if someone has a better parser or alternative, I welcome any
input.

Thanks,
Eduard
 
There are indeed many toy or half-baked SQL parsers out there. It may be
interesting to play with them just for the fun of it. But you can't really
use them in any serious way. If half-baked T-SQL parser is enough (e.g. for
narrowly defined special scanrios), one could just settle for some simple
regular expressions.

Linchi
 
Most sql parsers use contect free grammars and not regular expressions,
So that would be an improvement anyway.
I know SQL parisng is actually a mess, but this because SQL as a
(sub)language is a mess.

DM Unseen
 
Jamie Collins said:
No but I was replying to PJ6, whose goal is 'colored text', for which
SQLPARSE.DLL works fine.

Jamie.
Click to expand...

D'oh. Time to revisit my thick client query tools!

Thanks,
Paul
 
I wrote an app I call dot400 that can grab data from an iSeries DB2
database and put it in a DataGrid (I might put it up on CodeProject some
day).

Any way, my parsing is simple, but maybe you can build on it:


private void SQLExecuteButton_Click(object sender, System.EventArgs e){

string[] sqlStatementPart = new string[100];
sqlStatementPart = sqlStatement.Text.Split(' ');

if(sqlStatementPart[0].ToString().ToUpper()=="SELECT")
fillDataGrid(processSqlRequest(sqlStatement.Text,1));
else
processSqlRequest(sqlStatement.Text,0);
}


iDB2DataReader processSqlRequest(string qs, int q)
{
string connString = ConfigurationSettings.AppSettings["as400Dsn"];

iDB2DataReader iread = null;
iDB2Connection iconn = new iDB2Connection(connString);
iDB2Command icmd = new iDB2Command(qs,iconn);
DataSet sourcedata = new DataSet();

try
{
iconn.Open();
if(q==1)
iread = icmd.ExecuteReader();
else
icmd.ExecuteNonQuery();
}
catch ( Exception e )
{
Debug.WriteLine(e.ToString());
errorBox.Text=(e.ToString());
}
return iread;
}
 
I would like to thank you DM Unseen, Linchi Shea and John A. Bailo for
your input.

Eduard
 
Back
Top