Results 1 to 33 of 33

Thread: VB - SQL 'Select' statement formatter/checker

  1. #1

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    VB - SQL 'Select' statement formatter/checker

    The attached module contains a function called FormatSelect, which formats an SQL Select statement string so that it is easier to read.
    This should hopefully help you to make changes more easiy, or (if you have errors) enable you to see any mistakes much more quickly.

    As a little bonus, the function will also notify you if there are obvious errors, such as the wrong number of quotes to mark the start/end of strings.

    Example usage:
    VB Code:
    1. Dim sSQL as String
    2.   sSQL = "SELECT Table1.Field1 FROM Table1 WHERE Field2 = 3"
    3.   sSQL = FormatSelect(sSQL)
    4.   MsgBox sSQL

    A couple of examples of the inputs/outputs of the function:
    1) if the following Select statement is provided as input:
    Code:
    SELECT Table1.Field1 FROM Table1 WHERE Field2 = 3
    ...it gets converted into this:
    Code:
    SELECT    Table1.Field1
    
    FROM      Table1
    
    WHERE     Field2 = 3

    2) if this Select statement is provided as input:
    Code:
    SELECT Table1.Field1 AS Field1, Table1.Field2 AS Field2, CASE WHEN Table2.Field3 = 0 THEN 'NO' 
    WHEN Table2.Field3 = 1 THEN 'YES' WHEN Table2.Field3 IS NULL THEN 'NO' END CASE as Field3, IIf(Field4=1,1,0)
    From Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1 LEFT JOIN Table3 ON Table1.Field1 = Table3.Field1
    Where Table1.Field5 Between '1/1/2005' AND '1/20/2005' AND Table1.Field6 Between '1/1/2005' AND '1/20/2005' 
    OR Table1.Field7 = '1/20/2005' 
    AND Table1.Field8 IN(SELECT Field1 FROM table4 WHERE field2 IN(SELECT Field1 WHERE a=b FROM table2))
    ...it gets converted into this:
    Code:
    SELECT    Table1.Field1 AS Field1,
              Table1.Field2 AS Field2,
              CASE WHEN Table2.Field3 = 0 THEN 'NO' WHEN Table2.Field3 = 1 THEN 'YES' WHEN Table2.Field3 IS NULL THEN 'NO' END CASE as Field3,
              IIf ( Field4 = 1, 1, 0 )
    
    FROM      Table1
              LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1
              LEFT JOIN Table3 ON Table1.Field1 = Table3.Field1
    
    WHERE     Table1.Field5 Between '1/1/2005' AND '1/20/2005'
              AND Table1.Field6 Between '1/1/2005' AND '1/20/2005'
              OR Table1.Field7 = '1/20/2005'
              AND Table1.Field8 IN (
    
                        SELECT    Field1
                        
                        FROM      table4
                        
                        WHERE     field2 IN (
                        
                                            SELECT    Field1
                                            
                                            WHERE     a = b
                                            
                                            FROM      table2
                        
                                  )
    ...with the following warning message:
    Code:
    (in sub-query: 
      (in sub-query: 
        Incorrect section ordering: FROM should be before WHERE
        )
      )
    Note that the blank lines are optional, and can be disabled like this:
    VB Code:
    1. sSQL = FormatSelect(sSQL, False)

    The number of spaces to use as an indent (for 'normal' text, and for subqueries) can also be specified, using two further optional parameters. eg:
    VB Code:
    1. sSQL = FormatSelect(sSQL, , 4, 2)

    An alternative version by CVMichael can be found here: VB - SQL Select Formatter.


    Please let me know if there are any parts of SQL syntax which are not handled appropriately.
    Attached Files Attached Files
    Last edited by si_the_geek; Nov 17th, 2005 at 04:57 PM. Reason: updated attachment and examples (see my posts below for reasons)

  2. #2
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: VB - SQL Statement formatter/checker

    Hi si_the_geek,
    First off... I've been looking for something like this for a long time because in my application I made for work I have over 100 SQL statements that are like reports.
    All the SQL statements are very complex, so I guess it's perfect for testing your code.

    First off, I tried with 3 SQL statements until now, and 2 of them I get an infinite loop in this function: FormatSelect_Worker
    In this code:
    VB Code:
    1. Select Case aKeywords(lThisSectionType)
    2.     Case "SELECT", "GROUP BY", "ORDER BY"
    3.      
    4.       lTempPos = lCurrPos
    5.       Do
    6.         Do
    7.           lTempPos = InStr(lTempPos, sText, ",")
    8.           lBracketStart = InStr(lCurrPos, sText, "(")
    9.           If (lBracketStart > 0) And (lBracketStart < lTempPos) Then
    10.             lBracketEnd = FindCloseBracket(sText, lBracketStart)
    11.             If lBracketEnd < lNextSectionStart Then
    12.               lTempPos = lBracketEnd
    13.             Else
    14.               lBracketStart = 0
    15.             End If
    16.           Else
    17.             lBracketStart = 0
    18.           End If
    19.         Loop While (lBracketStart > 0) And (lTempPos > 0)
    This is one of the simple SQL statements it's doing this:
    Code:
    SELECT FORMAT(abt.DateChanged, "MM/DD/YYYY") AS "Date", aa.Action, Count(*) AS "Total"
    FROM tblAbnormalTran abt, tblAbnormalTranActions aa
    WHERE abt.Action = aa.[ID]
    AND CDATE(FORMAT(abt.DateChanged, "MM/DD/YYYY"))
    BETWEEN a AND b
    GROUP BY FORMAT(abt.DateChanged, "MM/DD/YYYY"), aa.Action
    ORDER BY FORMAT(abt.DateChanged, "MM/DD/YYYY") DESC
    Last edited by CVMichael; Nov 8th, 2005 at 06:30 PM. Reason: Some spelling mistakes...

  3. #3

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: VB - SQL Statement formatter/checker

    Ok, I've fixed that - it was due to a 'last minute' change I made, that obviously wasn't quite right!

    I also spotted a couple of things raised by your SQL, and a couple of code issues/improvements.

    Updates:
    • Allowed strings to be marked by ' or " (both can be used in the same SQL statement)
    • Changed formatting of brackets (they now have a space before and after them)
    • Fixed a couple of potenial issues where part of the next 'section' could be added to the same section
    • Fixed issue when strings start/end with doubled-up quote characters (they were previously removed from the string)

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: VB - SQL Statement formatter/checker

    Si, we've had a major server/network meltdown - and I was visiting a client for a couple ofday - nothing ever goes smooth...

    I've got lots and lots of stored procedures - would you like me to send you some or should I just run the function on them and report back to you?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: VB - SQL Statement formatter/checker

    I read about the meltdown in another thread, sounds very very painful - hope you manage to get it all sorted quickly.


    Either way is fine by me - although I'd like to see any SQL statements that show up issues with my code!

    Note tho that the formatter only works for Select statements (for the moment at least!).

  6. #6
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: VB - SQL Statement formatter/checker

    Quote Originally Posted by si_the_geek
    I read about the meltdown in another thread, sounds very very painful - hope you manage to get it all sorted quickly.


    Either way is fine by me - although I'd like to see any SQL statements that show up issues with my code!

    Note tho that the formatter only works for Select statements (for the moment at least!).
    I'm waiting for your version on those other sql's specially for action queries...
    Last edited by dee-u; Nov 9th, 2005 at 10:19 PM.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  7. #7

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: VB - SQL 'Select' statement formatter/checker

    That could be quite awkward, as different DBMS's have much more variation in formatting for those, so don't hold your breath!

    I wont attempt that until I'm sure it works for Select's tho, as most action queries can include them!

  8. #8
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: VB - SQL 'Select' statement formatter/checker

    OK... weird things are hapening.... I will highlight in bold those weird things:
    Original SQL
    Code:
    SELECT FORMAT(fo.FileDate, "MM/DD/YYYY") AS "File Date",
    Count(*) AS KITs, pa.Parts
    
    FROM (tblOrderFileList ofl INNER JOIN tblOrderFile fo ON fo.OrderFileID = ofl.OrderFileID) LEFT JOIN
    (
    SELECT CDate(FORMAT(fo.FileDate, "MM/DD/YYYY")) AS FileDate,
    OrderInstructionCode AS "Type",
    Count(*) AS Parts
    
    FROM tblOrderFileList ofl INNER JOIN tblOrderFile fo ON fo.OrderFileID = ofl.OrderFileID
    WHERE fo.CompanyName = 'ABC'
    AND fo.FileDate BETWEEN a AND b
    AND OrderInstructionCode = "PART"
    GROUP BY FORMAT(fo.FileDate, "MM/DD/YYYY"), OrderInstructionCode
    ) AS pa ON pa.FileDate = CDate(FORMAT(fo.FileDate, "MM/DD/YYYY"))
    WHERE fo.CompanyName = 'ABC'
    AND fo.FileDate BETWEEN INSERT_VALUE_1 AND INSERT_VALUE_2 + 1
    AND OrderInstructionCode = "KIT"
    GROUP BY FORMAT(fo.FileDate, "MM/DD/YYYY"), OrderInstructionCode, pa.Parts
    ORDER BY CDate(FORMAT(fo.FileDate, "MM/DD/YYYY")) DESC
    Formated SQL:
    Code:
    SELECT
             FORMAT ( fo.FileDate, "MM/DD/YYYY" ) AS "File Date",
             Count ( * ) AS KITs,
             pa.Parts
    FROM
             ( tblOrderFileList ofl INNER
             JOIN tblOrderFile fo ON fo.OrderFileID = ofl.OrderFileID )
             LEFT JOIN (
                      SELECT
                               CDate ( FORMAT ( fo.FileDate, "" ) ) AS FileDate,
                               OrderInstructionCode AS "",
                               Count ( * ) AS Parts
                      FROM
                               tblOrderFileList ofl INNER
                               JOIN tblOrderFile fo ON fo.OrderFileID = ofl.OrderFileID
                      WHERE
                               fo.CompanyName = ''
                               AND fo.FileDate BETWEEN a AND b
                               AND OrderInstructionCode = ""
                      GROUP BY
                               FORMAT ( fo.FileDate, "" ),
                               OrderInstructionCode
             ) AS pa ON pa.FileDate = CDate ( FORMAT ( fo.FileDate, "MM/DD/YYYY" ) )
    WHERE
             fo.CompanyName = 'ABC'
             AND fo.FileDate BETWEEN INSERT_VALUE_1 AND INSERT_VALUE_2 + 1
             AND OrderInstructionCode = "Type"
    GROUP BY
             FORMAT ( fo.FileDate, "PART" ),
             OrderInstructionCode,
             pa.Parts
    ORDER BY
             CDate ( FORMAT ( fo.FileDate, "MM/DD/YYYY" ) ) DESC
    It's a problem with the quotes, it's removing stuff in the quotes, or replacing with wrong things...

  9. #9

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: VB - SQL 'Select' statement formatter/checker

    Ah yes, thats a perfect example to test my flaw!

    I remove the strings & subqueries at the start of the routine, but my code to add them back to the final string was in the wrong order.

    I've uploaded the corrected version.

  10. #10
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: VB - SQL 'Select' statement formatter/checker

    Found more errors...
    Original SQL:
    Code:
    SELECT FORMAT(ShipDate, "YYYY MM") AS [Month], Count(*) AS Total
    FROM tblInvoiceOrderFileList
    WHERE OrderClaimStatusCode = 4 AND ShipDate
    BETWEEN a AND b
    GROUP BY FORMAT(ShipDate, "YYYY MM")
    ORDER BY FORMAT(ShipDate, "YYYY MM") DESC
    Formated SQL:
    Code:
    SELECT
             FORMAT ( ShipDate, "YYYY MM" ) AS [Month],
             Count ( * ) AS Total
    FROM
             tblInvoiceOrderFileList
    WHERE
             
             OR derClaimStatusCode = 4
             AND ShipDate BETWEEN a AND b
    GROUP BY
             FORMAT ( ShipDate, "YYYY MM" )
    ORDER BY
             FORMAT ( ShipDate, "YYYY MM" ) DESC
    I think you have to make it search for "OR " (with space after) because some words (like the previous) might contain or...

    [Edit]
    PS, I made my own SQL formater, should I post it here, or should I make a new thread ?

  11. #11

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: VB - SQL 'Select' statement formatter/checker

    Fixed, I had a ">" where I should have had a ">="!

    it checks for any of these characters: " '""#()=<>[]+-/*" (or end of string) before & after strings to find, but missed the 'after' check when the match was the first character.

    PS, I made my own SQL formater, should I post it here, or should I make a new thread ?
    It's probably best to start a new thread (as I'm sure there'll be discussions for both), but add a link to the thread here - I'll add a link in the top post too.

  12. #12
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: VB - SQL 'Select' statement formatter/checker

    Quote Originally Posted by si_the_geek
    It's probably best to start a new thread (as I'm sure there'll be discussions for both), but add a link to the thread here - I'll add a link in the top post too.
    OK, I'm gonna post it soon, I just want to do a little more testing...

    I found another problem with your SQL formatter:
    I get an error at this line (in FindFirstByArray function):
    VB Code:
    1. Const csPunctuation = " '""#()=<>[]+-/*"
    2.  
    3. Dim lFirstPos As Long
    4. Dim lArrayIndex As Long
    5. Dim lTempPos As Long
    6. Dim lCount As Long
    7.  
    8.   lFirstPos = Len(sText) + 1
    9.   lArrayIndex = 0
    10.  
    11.   For lCount = 0 To UBound(vArray)
    12.     lTempPos = InStr(lStartPos, sText, vArray(lCount), vbTextCompare)
    13.     Do While (lTempPos >= lStartPos)
    14.       [COLOR=Orange][B]If InStr(csPunctuation, Mid$(sText, lTempPos - 1, 1)) Then[/B][/COLOR]
    15.         If lTempPos + Len(vArray(lCount)) > Len(sText) Then Exit Do
    16.         If InStr(csPunctuation, Mid$(sText, lTempPos + Len(vArray(lCount)), 1)) Then Exit Do
    17.       End If
    18.       lTempPos = InStr(lTempPos + 1, sText, vArray(lCount), vbTextCompare)
    19.     Loop
    20.     If (lTempPos > 0) And (lTempPos < lFirstPos) Then
    21.       lFirstPos = lTempPos
    22.       lArrayIndex = lCount
    23.     End If
    24.   Next lCount
    lTempPos is = 1, so the result is 1 - 1 = 0, therefore an invalid # for the MID

    And if I take off the -1, then the formatter says the select query is wrong on all my selects...

    [Edit]
    Also, something I forgot to say, Your formatter formats the code like this:
    Code:
    SELECT
         Field1,
         Field2,
         ....
    When it's putting the "Field1" (the first field) on the second line, and I run the query, for some reason it does not return any data. But when I change it to this:
    Code:
    SELECT Field1,
         Field2,
         .....
    Then everything is OK... it executes properly, and returns data...
    Last edited by CVMichael; Nov 11th, 2005 at 12:23 PM.

  13. #13

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: VB - SQL 'Select' statement formatter/checker

    I added an extra bug when removing the previous one.

    I'm gonna take a little while over it, and make sure I get it right this time.

  14. #14
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: VB - SQL 'Select' statement formatter/checker

    How's it going with your formatter ?

    I posted my formatter here:
    VB - SQL Select Formatter

  15. #15

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: VB - SQL 'Select' statement formatter/checker

    Cool, I've added a link

    Well I finally got my head into a coding mood again - I really don't know what was wrong with me!

    Here's the updates:
    • Corrected FindFirstByArray.
    • Due to the problem mentioned above (SQL will not run in ?Access?), I removed the new line immediately after "Select"/"From"/etc, and added the option for blank lines between each 'section' instead.
    • Fixed typo for bracket errors.
    • Corrected mis-use of a variable for passing the And in a Between clause (could cause infinite loop if Between was malformed!).
    • Better formatting of error messages.
    • Allowed # for dates, rather than just ' or ".
    • Added more Join types.

  16. #16
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: VB - SQL 'Select' statement formatter/checker

    The new version is MUCH better ! No errors at all !

    But could you make it have an option for how many spaces to put for indent ?
    Because 10 spaces is WAY too much for me, I like 4 spaces...

    To give you an idea, this:
    Code:
    SELECT DISTINCT MFrom AS "From Model", MWith AS "To Model" FROM
    (
    SELECT DISTINCT MFrom, TRIM(IIF(MWith LIKE 'abc5%', MID(MWith, 5, 100), MWith)) AS MWith FROM
    (
    SELECT MFrom, IIF(INSTR(MWith, '(') = 0, MWith, LEFT(MWith, INSTR(MWith, '(')-1)) AS MWith FROM
    (
    SELECT DISTINCT MFrom, MWith FROM
    (
    SELECT MFrom, TRIM(IIF(Num3 = 0, MWith, LEFT(MWith, Num3 - 1))) AS MWith FROM
    (
    SELECT MFrom, MWith, IIF(Num3 = 0, Num2, Num3) AS Num3 FROM
    (
    SELECT MFrom, MWith, IIF(Num2 = 0, Num1, Num2) AS Num2, IIF(Num2 = 0, 0, INSTR(Num2 + 1, MWith, '-')) AS Num3
    FROM
    (
    SELECT MFrom, MWith, Num1, IIF(Num1 = 0, 0, INSTR(Num1 + 1, MWith, '-')) AS Num2 FROM
    (
    SELECT MFrom, MWith, INSTR(MWith, '-') AS Num1 FROM
    (
    SELECT LEFT(m_ofl.Notes, INSTR(m_ofl.Notes, '|') - 1) AS MFrom
    , IIF(INSTR(repl.Notes, ',') > 0, LEFT(repl.Notes, INSTR(repl.Notes, ',')-1), repl.Notes) AS MWith
    
    FROM tblInvoice AS repl INNER JOIN
    (
    SELECT OrderNumber, Notes FROM tblInvoice
    WHERE StatusCode = 4
    AND INSTR(Notes, '|') > 0
    ) AS m_ofl ON m_ofl.OrderNumber = repl.OrderNumber
    
    WHERE repl.RecordIDType = 'STAT'
    AND repl.StatusCode = 7
    AND repl.Notes NOT LIKE '%abc%'
    AND repl.Notes NOT LIKE '%abc2%'
    AND repl.Notes NOT LIKE '%abc3%'
    AND ShipDate > #Apr 01, 2004#
    ))))))
    WHERE LEN(MWith) > 1
    )
    WHERE INSTR(MWith, MFrom) = 0
    ))
    ORDER BY MFrom, MWith
    You get:
    Code:
    SELECT    DISTINCT MFrom AS "From Model",
              MWith AS "To Model"
    FROM      (
                        SELECT    DISTINCT MFrom,
                                  TRIM ( IIF ( MWith LIKE 'abc5%', MID ( MWith, 5, 100 ), MWith ) ) AS MWith
                        FROM      (
                                            SELECT    MFrom,
                                                      IIF ( INSTR ( MWith, '(' ) = 0, MWith, LEFT ( MWith, INSTR ( MWith, '(' ) - 1 ) ) AS MWith
                                            FROM      (
                                                                SELECT    DISTINCT MFrom,
                                                                          MWith
                                                                FROM      (
                                                                                    SELECT    MFrom,
                                                                                              TRIM ( IIF ( Num3 = 0, MWith, LEFT ( MWith, Num3 - 1 ) ) ) AS MWith
                                                                                    FROM      (
                                                                                                        SELECT    MFrom,
                                                                                                                  MWith,
                                                                                                                  IIF ( Num3 = 0, Num2, Num3 ) AS Num3
                                                                                                        FROM      (
                                                                                                                            SELECT    MFrom,
                                                                                                                                      MWith,
                                                                                                                                      IIF ( Num2 = 0, Num1, Num2 ) AS Num2,
                                                                                                                                      IIF ( Num2 = 0, 0, INSTR ( Num2 + 1, MWith, '-' ) ) AS Num3
                                                                                                                            FROM      (
                                                                                                                                                SELECT    MFrom,
                                                                                                                                                          MWith,
                                                                                                                                                          Num1,
                                                                                                                                                          IIF ( Num1 = 0, 0, INSTR ( Num1 + 1, MWith, '-' ) ) AS Num2
                                                                                                                                                FROM      (
                                                                                                                                                                    SELECT    MFrom,
                                                                                                                                                                              MWith,
                                                                                                                                                                              INSTR ( MWith, '-' ) AS Num1
                                                                                                                                                                    FROM      (
                                                                                                                                                                                        SELECT    LEFT ( m_ofl.Notes, INSTR ( m_ofl.Notes, '|' ) - 1 ) AS MFrom,
                                                                                                                                                                                                  IIF ( INSTR ( repl.Notes, ',' ) > 0, LEFT ( repl.Notes, INSTR ( repl.Notes, ',' ) - 1 ), repl.Notes ) AS MWith
                                                                                                                                                                                        FROM      tblInvoice AS repl
                                                                                                                                                                                                  INNER JOIN (
                                                                                                                                                                                                            SELECT    OrderNumber,
                                                                                                                                                                                                                      Notes
                                                                                                                                                                                                            FROM      tblInvoice
                                                                                                                                                                                                            WHERE     StatusCode = 4
                                                                                                                                                                                                                      AND INSTR ( Notes, '|' ) > 0
                                                                                                                                                                                                  ) AS m_ofl ON m_ofl.OrderNumber = repl.OrderNumber
                                                                                                                                                                                        WHERE     repl.RecordIDType = 'STAT'
                                                                                                                                                                                                  AND repl.StatusCode = 7
                                                                                                                                                                                                  AND repl.Notes NOT LIKE '%abc%'
                                                                                                                                                                                                  AND repl.Notes NOT LIKE '%abc2%'
                                                                                                                                                                                                  AND repl.Notes NOT LIKE '%abc3%'
                                                                                                                                                                                                  AND
    (I could not paste all the code, too many characters)
    Wich is way to much indenting for me....

    Also, where whould I have to change your code to make it act as if {} brackets are like a string ? I don't want your formatter to make any changes between the {} brackets, so how do I do that ?

  17. #17

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: VB - SQL 'Select' statement formatter/checker

    I disagree with your spacing amounts, but agree with making it optional I have done that, and changed the default subquery indent size to be just over the normal indent, rather than double it.

    To make {} act as a string, the best course of action would probably be to add an extra bit of code before/after the strings are removed to store the {} 'strings' to a separate array, then re-insert them after/before the strings (opposite of what you did above).

    Unfortunately the strings code can't really be altered for this easily enough, as all other markers are the same for start/end, whereas these differ - a modified version ofthe strings code would work well tho. On the plus side, for this you would only need a one dimensional array - as you know the markers.

  18. #18
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: VB - SQL 'Select' statement formatter/checker

    When I call it like this:
    Me.txtQuery.Text = FormatSelect(Me.txtQuery.Text, False, 4, 8)

    I get an error at this line (In FormatSelect_Worker):
    VB Code:
    1. 'add keyword (Ucase), and spaces to indent
    2. sRetString = sRetString & aKeywords(lThisSectionType) & Space(Len(sIndent) - Len(aKeywords(lThisSectionType)))
    But I fixed the problem by changing the code to this:
    VB Code:
    1. 'add keyword (Ucase), and spaces to indent
    2.       If Len(sIndent) - Len(aKeywords(lThisSectionType)) <= 0 Then
    3.         sRetString = sRetString & aKeywords(lThisSectionType) & " "
    4.       Else
    5.         sRetString = sRetString & aKeywords(lThisSectionType) & Space(Len(sIndent) - Len(aKeywords(lThisSectionType)))
    6.       End If

  19. #19

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: VB - SQL 'Select' statement formatter/checker

    Ah yes, I should have checked a few values like that!

    I've updated using basically the same code you posted, with just a minor variation of the If for efficiency:
    VB Code:
    1. If Len(aKeywords(lThisSectionType)) >= lIndentSize Then

  20. #20
    New Member
    Join Date
    Nov 2005
    Posts
    1

    Re: VB - SQL 'Select' statement formatter/checker

    i tried this on the odbc commands and it got problem ...

    exec sp_executesql N'SELECT "pk" FROM "dbo"."m_clients" WHERE id = @P1 AND closing_status = @P2 AND calc_status = @P3 AND opening_effec_date <= @P4 AND closing_effec_date > @P5 ', N'@P1 int,@P2 int,@P3 int,@P4 datetime,@P5 datetime', 11893738, 20, 0, 'Jul 1 2004 12:00:00:000AM', 'Jul 1 2004 12:00:00:000AM'

    exec sp_executesql N'INSERT INTO "dbo"."p_costing_sheet_results" ("policy_no","cover_no","benefit_type","role","role_id","membership_category_no","series","opening_s tatus","opening_reg_date","opening_effec_date","opening_ref","closing_status","closing_reg_date","cl osing_effec_date","closing_ref","member_id","gender","entry_age","from_age","to_age","salary","pensi onable_salary","quantity","annual_premium","annual_basic_premium","sum_assured_amount","basic_premiu m_rate","total_restricted_sa_amount","capitalised_sum_assured","last_charging_date","current_net_ann ual_premium","prev_net_annual_premium","periodic_premium","action_code","adjustment_code","effec_dat e_adjustment_feed","charging_season_factor","event_date","dob","occupation","internal_cover_no","clo sing_effec_date_at_term","restricted_sa_max_benefit","total_sa_for_high_earners","capitalise_factor" ,"factor_for_retiree","closed_record_invoiced_date","benefit_type_param1","ref_date_time_changes" ) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P2 2,@P23,@P24,@P25,@P26,@P27,@P28,@P29,@P30,@P31,@P32,@P33,@P34,@P35,@P36,@P37,@P38,@P39,@P40,@P41,@P4 2,@P43,@P44,@P45,@P46,@P47,@P48,@P49)', N'@P1 float,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 datetime,@P10 datetime,@P11 varchar(255),@P12 int,@P13 datetime,@P14 datetime,@P15 varchar(255),@P16 int,@P17 int,@P18 int,@P19 int,@P20 int,@P21 float,@P22 float,@P23 int,@P24 float,@P25 float,@P26 float,@P27 float,@P28 float,@P29 float,@P30 datetime,@P31 float,@P32 float,@P33 float,@P34 int,@P35 int,@P36 datetime,@P37 float,@P38 datetime,@P39 datetime,@P40 int,@P41 int,@P42 datetime,@P43 float,@P44 float,@P45 float,@P46 float,@P47 datetime,@P48 int,@P49 datetime', 6.348980000000000e+005, 9000, 0, 13, 11893738, 4, 0, 10, 'Oct 5 2005 12:00:00:000AM', 'Jul 1 2005 12:00:00:000AM', '', 0, 'Jan 1 3000 12:00:00:000AM', 'Jan 1 3000 12:00:00:000AM', '', 12605586, 2, 37, 37, 0, 2.565040000000000e+004, 0.000000000000000e+000, 1, 2.360000000000000e+001, 2.360000000000000e+001, 5.130100000000000e+004, 4.600000000000000e-001, 5.130080000000000e+004, 0.000000000000000e+000, 'Jan 1 1900 12:00:00:000AM', 2.139359900000000e+001, 0.000000000000000e+000, 2.360000000000000e+001, 1, 0, 'Jan 1 1900 12:00:00:000AM', 1.000000000000000e+000, 'Jan 1 1900 12:00:00:000AM', 'Jul 11 1968 12:00:00:000AM', 0, 0, 'Jan 1 1900 12:00:00:000AM', 5.130080000000000e+004, 0.000000000000000e+000, 0.000000000000000e+000, 1.000000000000000e+000, 'Jan 1 1900 12:00:00:000AM', 0, 'Jan 1 1900 12:00:00:000AM'

  21. #21
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: VB - SQL 'Select' statement formatter/checker

    avihayt , it works only for SELECT statements...

  22. #22

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: VB - SQL 'Select' statement formatter/checker

    Yep, if you pass it just the text from "SELECT" up to the first "@P5" then it works.

    The second statement is not compatible at all at this time, as I have not allowed for action statements (Insert/Update/etc) yet.

    I am not sure if I at some point in the future I will allow for SP's or commands such as Exec.

  23. #23
    New Member
    Join Date
    May 2006
    Posts
    1

    Smile Re: VB - SQL 'Select' statement formatter/checker

    si,

    Are you still working on this formatting project? I am working on VB.Net project which can use your formatting function. My project basically allow in place formatting in SQL QUERY ANALYZER using Hooks.

    - AJ

  24. #24

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: VB - SQL 'Select' statement formatter/checker

    Hi ajviradia, welcome to VBForums!

    I haven't done anything more to it yet, the trouble is that there are many different formats for Inserts (for example) depending on what DBMS you are using. I may have a go at adding more this weekend, assuming I am up to date on my work!

    Your use of it sounds good, I'd like to see that when you get it working.

  25. #25
    Member chocoloco's Avatar
    Join Date
    Mar 2006
    Posts
    42

    Re: VB - SQL 'Select' statement formatter/checker

    Why not just format the Sql string while typing??? Why all this fuss??

  26. #26

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: VB - SQL 'Select' statement formatter/checker

    I originally designed it because we had lots of threads along the lines of "what is the error in this SQL statement?", and they didn't have any formatting at all (just one long string!).

    That meant we had to manually format the SQL statements before we could attempt to answer. I realised that the amount of time I would save by having the code offset the time to write it.. and thankfully it was easy to add basic error checking too.


    It also allows for those times when you are given an existing project to support, and the SQL statements aren't formatted (from my experience, this is often the case!).

  27. #27
    New Member
    Join Date
    Aug 2011
    Posts
    13

    Re: VB - SQL 'Select' statement formatter/checker

    Thanx)) using it really makes everything easier: making changes and seeing mistakes much more quickly,notifying errors..

  28. #28
    New Member
    Join Date
    Nov 2011
    Posts
    1

    Re: VB - SQL 'Select' statement formatter/checker

    Hi, nice code!

    I noticed the VB formatter doesn't handle simple UNIONs, eg
    "SELECT * FROM A UNION SELECT * FROM B"

    I am using VBA 6.5

  29. #29

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: VB - SQL 'Select' statement formatter/checker

    Welcome to VBForums

    Thanks for your comments... I can confirm that it doesn't handle Unions at the moment (I'm surprised nobody has mentioned it before!), and I will try to add that in the next few days.

  30. #30
    New Member
    Join Date
    Jul 2005
    Posts
    1

    VB.NET - SQL 'Select' statement formatter/checker!

    This project was incredibly helpful to me, so I figured I would post my changes to make this VB.NET compliant. Nothing else seemed to be out there and completely free.

    Best Regards to all,

    David
    Attached Files Attached Files

  31. #31
    Lively Member
    Join Date
    Nov 2006
    Posts
    110

    Re: VB - SQL 'Select' statement formatter/checker

    Hi

    I would really like to try this module out but need help please.
    Using Excel and/or Access how can I make use of this module?

    Thanks in advance

  32. #32

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: VB - SQL 'Select' statement formatter/checker

    You should be able to add the .bas file by right-clicking in the Project Explorer window (or whatever it is called where you can see the code files), make sure you right-click on a folder rather than a file.

    If that doesn't work just create a new module, then use Notepad to copy the text of the .bas file (except the first line), and paste the text into the new module.


    You can then use it as shown earlier in this thread.

  33. #33
    Lively Member
    Join Date
    Nov 2006
    Posts
    110

    Re: VB - SQL 'Select' statement formatter/checker

    Quote Originally Posted by si_the_geek View Post
    You should be able to add the .bas file by right-clicking in the Project Explorer window (or whatever it is called where you can see the code files), make sure you right-click on a folder rather than a file.

    If that doesn't work just create a new module, then use Notepad to copy the text of the .bas file (except the first line), and paste the text into the new module.


    You can then use it as shown earlier in this thread.
    Alright thanks I think the best thing for me to do woudl be to go through some tutorials and try to figure out how I can write something to implement this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width