Last winter, Jan Karel and I worked a little on the QueryManager add-in. I was interested in that project because I wanted an easy way to add a parameter to an external data query. After the first round, the method I wrote to add a parameter was just too hokey. It merely added a criterion with a question mark, and didn’t give the user a whole lot of options. Once the question mark was added to the SQL, it was up to the user to define the parameter in the UI. We needed something that did the whole job.
On subsequent rounds, everything was getting complicateder and complicateder. I ended up just parsing the whole SQL statement to try to accomplish what I wanted. The problem was I don’t know SQL well enough to be parsing it for a general purpose utility. From what I read, even people who do know SQL found the task difficult. I like the utility that Jan Karel developed, but I was never (and am not) satisfied with my end of it.
I should just quit thinking about, but it haunts me. My latest foray into SQL Parsing was going to be brilliant. I was going to use DAO automation to create a query in Jet, then read the contents of the MSysQueries table (a hidden table in Access). If you use queries in Access and you want to see how Access stores the details, go to Tools > Options and check the Hidden Objects check box. Then open the MSysQueries table and you can see the various components of a query. I wanted to put this table into a recordset so I could manipulate the pieces and reassemble them, but I ran into access restrictions. I don’t know enough about Access security to even understand the problem, much less the solution.
I was perusing the References dialog in the VBE earlier. I was actually looking for a mozilla dll to see what I could do with it and came upon this:
An SQL parsing library? Sweet! I haven’t got it all figured out and from what I’ve done, I wouldn’t necessarily call this robust. It seems to be a step in the right direction, though.
Dim prsApp As MSSQLParser.vbSQLParser
Dim sSql As String
Dim sTemp As String
Dim vaParsed As Variant
Dim i As Long
Const sSPLIT As String = “”
Set prsApp = New MSSQLParser.vbSQLParser
sSql = “SELECT * FROM TblCustomers WHERE TblCustomers.CustId = ‘1234’ “
sSql = sSql & “AND TblCustomers.State = ‘NE'”
sTemp = prsApp.ParseSQLSyntax(sSql, vbSqlServerSyntax)
vaParsed = Split(sTemp, sSPLIT)
For i = LBound(vaParsed) To UBound(vaParsed)
Debug.Print i, vaParsed(i)
Next i
End Sub
I don’t have this thing quite figured out, but here’s what I’ve gathered so far.
- cf1 = SQL keywords like SELECT, FROM, WHERE
- cf5 = punctuations, mathematical operators, and logical operators
- cf6 = string literals
- cf7 = numeric literals
- cf10 = This appears to be a catch all. I thought it was table names and fields, but after misspelling SELECT, it put it as cf10. So now I think this is just anything it doesn’t recognize.
Kind of a strange parsing system, I think. Not really as useful as MSysQueries, but at least it can identify SQL keywords, which is more than I can say for myself. I’m not sure if this will turn into something useful for me, but it has renewed my interest in our QueryManager project. Now I need to figure out how to get Rob involved and take the best of his Query Editor program and ours.
That does look interesting.
Sounds as if my contribution to this Querymanager is becoming less and less important though.
So let me continue doing the UI, OK?
Dick,
I can’t tell where you want to go with this.
Are you interested in replacing MS Query?
or more aimed at a sophisticated WHERE clause editor?
Rob
JKP: I’m thinking about raising my contribution from zero, so no worries there. Besides, I stink at UI, so you’re in.
Rob: More of the latter. Simply said, I want to create a parameter without opening MSQuery. To me, it’s more complicated than just parsing the SQL, but that seems to be the place I got stuck before. Once I can manage the SQL parts better, I think I’ll be able to manage the parameter objects better.
Dick,
My query editor add-in allows you to work without MSQuery, but you have to edit the SQL manually.
eg. adding a cust_id parameter means appending “where cust_id = ?”
I’m thinking though that you’re after a click through solution to building a where clause?
I’ve read that the primary use of this library is for automatically colouring / formatting / tabbing SQL.
I have Office 2003 Pro but I can’t figure out how to get a reference to SQL Parser Object Library.
Cheers,
Rob
“you have to edit the SQL manually”
I know, I’ve using it for a couple of weeks now:)
I guess I wasn’t thinking in terms of building a WHERE clause, but that’s essentially what I’d be doing. I didn’t intend that the user could add any criteria, just a parameter criterion, but that they could but it any where in the WHERE clause they liked – without having to type it manually.
Did you search for SQLPARSE.DLL? I have no idea how it’s on my system or where it comes from.
Dick,
I think SQLPARSE comes with Visual Studio – which I don’t have here at home.
Rob
Rob: Foiled again. I guess I won’t be able to include that in a general purpose utility. I searched microsoft.com for sqlparse.dll and it looks like it’s also installed with Publisher – go figure that.
Hi Guys
I did a quick check for this file on my computer and found it under the Office folder. Also if you look at the screenshot provided by Dick, the full path to the DLL file is provided.
Hi,
Info about Microsofts DLL’s can be found from http://support.microsoft.com/dllhelp/default.aspx
SQLPARSE.DLL ships with Office 2k Pro, Office XP Pro and Visual Studio 6.0 EE.
Jazzer: Thanks for that link. I always wondered how people determined which programs supplied which files.
My experience is the DLL Help Database doesn’t always help. For example, I searched it yesterday using By Product + File, English (United States is the only flavour …oops, I mean, flavor they have), Microsoft Office 2003, Microsoft Office Professional Edition 2003, msjet40.dll. It found no results, yet I find it hard to believe that an Office suite that includes MS Access does not ship with the latest version of Jet.
Jamie.
Many thanks for your article. I am trying to xref the queries and tables used by all the queries in an Access project, and your reference to ParseSQLSyntax looks like it could save a lot of coding time.
The immediate catch was Access’s use of [] to bracket file and field names – unrecognised by ParseSQLSyntax, so the names came out in bits – but using a function to replace them in the SQL with quotes, means that each name now comes out of ParseSQLSyntax, as CF10s, but altogether. So anyway, now I am on my way!
What if i want to differentiate Comments? Can this dll help?
You might find this site interesting:
http://www.sqlservercentral.com/columnists/awarren/trythesqlparserobjecttocolorizeyoursql!.asp
Turns out that the one method in the SQLPARSE.DLL returns the results as a string – in RTF!
Hi there,
I stumble upon this site while trying to understand about ParseSQLSyntax for my VB6 project.
Btw, if already know by now then sorry for the redundant info. But those “cf1? chars are RTF format. If output your syntax to a RichTextBox, this is what you’d get: http://img117.imageshack.us/my.php?image=sqlparser6pq.png
regards,
lanux