SQL to AU3 String converter

[BigDaddyO]

I've been doing a lot of sql queries to pull data for some automation and I got tired of manually converting my queries into strings that I can use in my scripts. "last one was 288 lines" 

This will convert either your current clipboard or a selected .sql file into an AutoIt $sSQL = "" string.

It will automatically remove any comments "not block comments though" and it should keep the formatting that you had.  I like to have my queries in a nice spacing so it's easy to tell where a sub-query starts and ends so I wanted to keep that look in the AutoIt string.

I'm sure this could be done more efficient so if you have suggestions please let me know.

expandcollapse popup

#include <GUIConstantsEx.au3>
Opt("GUIOnEventMode", 1)

$iGuiW = 350
$iGuiH = 80
$hGUI = GUICreate("Convert SQL Query to Au3 String", $iGuiW, $iGuiH)
GUISetOnEvent($GUI_EVENT_CLOSE, "_ExitApp")
GUICtrlCreateButton("Convert .sql file", 15, 20, 150, 40)
GUICtrlSetOnEvent(-1, "_ConvertFile")
GUICtrlCreateButton("Convert Clipboard", $iGuiW - 165, 20, 150, 40)
GUICtrlSetOnEvent(-1, "_ConvertClipboard")
GUISetState(@SW_SHOW, $hGUI)                                            ;Display the GUI for the User
While 1                                                                 ;Loop waiting for the user to click something.
    sleep(100)
WEnd


;Function if a user selects the "Convert .sql file" button
Func _ConvertFile()
    $sFile = FileOpenDialog("SQL File", @ScriptDir, "SQL (*.sql)")      ;Select the .sql file to convert
    If @error Then Return

    $sQuery = FileRead($sFile)                                          ;Read the file into a string
    $sOutput = _GenerateString($sQuery)                                 ;Use the convert function to return the updated string

    $sSaveAs = FileSaveDialog("SaveAs", @ScriptDir, "Text (*.txt)")     ;Prompt for where to save the new file
    If @error Then Return

    FileWrite($sSaveAs, $sOutput)                                       ;Write the file
    If MsgBox(4, "Finished", "Your string has been saved as " & $sSaveAs & @CRLF & "Would you like to open the file now?") = 6 Then
        ShellExecute($sSaveAs)                                          ;Launch the new file if they pick [Yes]
    EndIf

    Exit
EndFunc


;Function if a user selects "Convert Clipboard" button
Func _ConvertClipboard()
    $sQuery = ClipGet()                                                 ;Read the clipboard into a string
    ClipPut(_GenerateString($sQuery))                                   ;Use the convert function and write results directly to clipboard
    MsgBox(0, "Finished", "Your Clipboard has been Updated with the AutoIt String")
    Exit
EndFunc


;Function that converts the sql query into an actual String that can be pasted into an AutoIt script
Func _GenerateString($sQuery)
    $aRawData = StringSplit($sQuery, @CRLF, 3)
    $iLines = UBound($aRawData)

    Local $sStringValue = ""
    Local $sSpace = "                                                                                                                "

    For $i = 0 to $iLines - 1

        $sLine = StringStripWS(StringReplace($aRawData[$i], '"', '""'), 2)                      ;change any " in the query with double "" and remove all spaces from the end of the line
        If StringLeft(StringStripWS($sLine, 1), 1) = "--" Then ContinueLoop                     ;skip any comment lines

        $iComment = StringInStr($sLine, "--")                                                   ;Remove any comments at the end of the line
        If $iComment > 0 Then $sLine = StringStripWS(StringLeft($sLine, $iComment - 1), 2)

        If StringStripWS($sLine, 3) = "" Then ContinueLoop                                      ;Remove any empty lines

        If $sStringValue = "" Then
            $sStringValue = @TAB & '$sSQL = "' & StringStripWS($sLine, 3) & ' " & _' & @CRLF    ;Don't include leading spaces in the first line
        Else

            If $i = $iLines - 1 Then
                $sStringValue &= @TAB & @TAB & @TAB & '"' & StringStripWS($sLine, 3)  & '"'     ;Don't include leading spaces in the last line
            Else
                $iLeadingSpaces = StringLen($sLine) - StringLen(StringStripWS($sLine, 1))       ;Find how many leading spaces are there
                $sLine = StringLeft($sSpace, $iLeadingSpaces) & '"' & StringStripWS($sLine, 1)  ;insert a " between leading spaces and sql line
                $sStringValue &= @TAB & @TAB & @TAB & $sLine  & ' " & _' & @CRLF
            EndIf

        EndIf

    Next

    Return $sStringValue

EndFunc


Func _ExitApp()
    Exit
EndFunc

 

[mLipok]

Some time ago @Jos made lua script addition to scite which change selected text into string variable

[jchd]

Quickly browsing the posted code I've one nitpicky warning: if a literal or schema name contains two dashes, they are removed with the rest of line, destroying the SQL.

create table "dual--dash" (blah text default 'leave -- me -- alone') -- pathological table