Reference SQL Parser

[mCompany]

I need to parse SQL statements directly and extract each segment
individually. Is there a way reference the Microsoft SQL Parser
directly from VB.Net?

Thanks!



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

Re: Reference SQL Parser


"mCompany" <mail@mvestcapi tal.com> wrote in message
news:40419b99$0 $193$75868355@n ews.frii.net...[color=blue]
> I need to parse SQL statements directly and extract each segment
> individually. Is there a way reference the Microsoft SQL Parser
> directly from VB.Net?
>
> Thanks!
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

If you're referring to the MSSQLParser COM object, I belive it's MS
internal, and isn't documented or supported. But if you can explain why you
need to do this, someone may have a better answer.

Simon

[Erland Sommarskog]

Re: Reference SQL Parser

mCompany (mail@mvestcapi tal.com) writes:[color=blue]
> I need to parse SQL statements directly and extract each segment
> individually. Is there a way reference the Microsoft SQL Parser
> directly from VB.Net?[/color]

As Simon said, if you are more specific what you are up to, you can
get more precise advice.

The simple way is to send the statements to SQL Server, and first send
SET PARSEONLY ON.

If you want find where there statement boundaries, there is a really
obscure variant which requires you to use DB-Library. Which probably
precludes Visual Basic, because I doubt there is a DB-lib interface
for VB these days. You first send SET OFFSETS ON to SQL Server and then
you retrieve the information with dboffsets. I should hasten to add that
I played with this at some occasion. I got different results back from
SQL 6.5 and SQL 7, but none of them appeared correct to me.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[mCompany]

Re: Reference SQL Parser

All we really need to do is split out the various SQL segments (SELECT,
FROM, WHERE, ORDER BY). It's not that difficult to write, but if
there's a quick way to do, we'll use it. Any ideas?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

Re: Reference SQL Parser


"mCompany" <mail@mvestcapi tal.com> wrote in message
news:404202bd$0 $194$75868355@n ews.frii.net...[color=blue]
> All we really need to do is split out the various SQL segments (SELECT,
> FROM, WHERE, ORDER BY). It's not that difficult to write, but if
> there's a quick way to do, we'll use it. Any ideas?
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

I don't know of any supported tool, but roughly parsing a very simple SELECT
into its parts should be straightforward with most string/regex tools.
However, you need to think about the limitations of that solution - SQL
queries can be written in numerous ways, and with subqueries, derived
tables, unions etc, you may have problems.

You still haven't really explained your goal, so may want to clarify that to
help someone give you a better answer. For example, are you implementing
custom syntax highlighting? Or are you accepting arbitrary SQL strings from
a client, and you want to ensure they are syntactically correct before
execution? Or do you want to validate SQL strings as being SQL92/99
compliant?

Simon

[mCompany]

Re: Reference SQL Parser

The SET OFFSETS should work. If it does not, we'll write our own basic
parser.

Thanks

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Re: Reference SQL Parser

mCompany (mail@mvestcapi tal.com) writes:[color=blue]
> All we really need to do is split out the various SQL segments (SELECT,
> FROM, WHERE, ORDER BY). It's not that difficult to write, but if
> there's a quick way to do, we'll use it. Any ideas?[/color]

Not that difficult? Boy, you are in for a real treat.

Believe me, I have written Perl code that parses bits of T-SQL, and this
is no simple exercise if you want it to work perfectly. If you can cut
down on the ambitions and miss out on some constructs, the work you need
to put in it, is of moderate size.

The reason why this is difficult is that T-SQL has a wretched ad-hoc
syntax onto which things have been added through the years, and lot of
old syntax retained for compatibility. For instance, do you think these
two statements parse the same?

SELECT 5ee
SELECT 5dd

They don't.

What I needed to was to find all references to table in the code. To this
end I sifted through the code and woke up on keywords that can be followed
by a table name: INSERT, UPDATE, DELETE, FROM and JOIN. FROM is most
tricky, because there may come a list of tables. And in the middle of
there may be a rowset fucntion like OPENQUERY or OPENXML. This is perfectly
legal T-SQL:

SELECT *
FROM a, b, OPENQUERY(REMOT ESRV, 'SELECT * FROM a') AS c, d
JOIN e ON a.col = e.col, f
WHERE a.col2 = b.col2
AND a.col3 = c.col3
AND a.col4 = d.col4
AND e.col5 = f.col5

But the code I wrote will not find d and f. It would be crazy to write
code like that.

A tip is that start by doing a pass that eliminates all constants, and
replaces all string literals with simple tokens. String literals here
includes all that is enclosed in '', "" and [].


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp