Not too long ago I ran across a reference to the MSSQLParser object in a newsgroup posting which discussed that it was used to "colorize" a sql string in much the same way it's done in Query Analyzer. I couldn't find anything on MSDN, but I did run a across a
short blurb about it that said it was installed as part of Visual Studio. So while I'm not clear on whether you can redistribute it (assume not to be safe), those of us who have VS loaded could potentially make use of this object.
Turns out that the parser object has a single method that returns the results as a string - in RTF! To get it to work with the standard richtextbox control in VB you'd have to concatenate a color table with the results before you could display it. Probably not too hard, but since RTF is a tagged format not far from HTML, I decided to just convert the RTF color tags to HTML font tags, like this:
| Function ParseSQLReturnHTML(SQL As String, ReturnHTML As Boolean) '8/6/01 law ' Set a reference to MSSQLParser - it returns RTF, replacing RTF color tags with 'HTML Dim osQL As MSSQLParser.vbSQLParser Dim sTemp As String On Error Resume Next If SQL <> "" Then Set osQL = New MSSQLParser.vbSQLParser sTemp = osQL.ParseSQLSyntax(SQL, vbSqlServerSyntax) Set osQL = Nothing If ReturnHTML = True Then sTemp = Replace$(sTemp, "\cf10", "<FONT COLOR=BLUE>") sTemp = Replace$(sTemp, "\cf5", "<FONT COLOR=RED>") sTemp = Replace$(sTemp, "\cf1", "<FONT COLOR=GREEN>") sTemp = Replace$(sTemp, "\cf6", "<FONT COLOR=ORANGE>") sTemp = Replace$(sTemp, "\cf", "</FONT>") sTemp = Replace$(sTemp, "\par", "<P>") End If ParseSQLReturnHTML = sTemp End If End Function |
So what will I use it for? Don't know yet! I can see where this would be handy if you need to put together a clone of Query Analyzer. My friend Leon Platt actually wrote something similar to this WITHOUT the object for a project where clients where building queries through a web interface - color coding does make it easier to read.
I've put together a small demo program that will let you type in different queries and see how the string will get formatted in both RTF and HTML. Keep in mind I didn't do a great deal of testing - it's a demo, not a commercial release! Click here for VB source code and the compiled exe. Remember, it won't run unless you have the MSSQLParser library already installed on your machine.
If anyone has any more info about the dll (especially on distribution) please click the 'Dicuss this Article' link and post a comment.
