Parsing SQL

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:

ParseSQL1

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.

Sub TestParse()
   
    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

ParseSQL2

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.

Posted in Uncategorized

15 thoughts on “Parsing SQL

  1. 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?

  2. 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.

  3. 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

  4. “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.

  5. 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.

  6. 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.

  7. Jazzer: Thanks for that link. I always wondered how people determined which programs supplied which files.

  8. 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.

    –

  9. 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!


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.