Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

MySQL Library functions


  • Please log in to reply
66 replies to this topic
panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
Connect to MySQL server and execute standard MySQL calls in AutoHotkey. Includes a useful function to update a listview with sql data.

Thanks to PhiLho and others.
Feedback welcome.

Compatibility: AutoHotkey: v1.1.04.00 (autohotkey_L ANSI version), untested on other versions

Features:

- MySQL class to enable connection and query to mysql database.
- The lvfill function can be used to automatically fill a gui listview with the output from an sql select.
- The database connection is automatically re-established if connection is lost (usually due to server restart or sql connection timeout)
- SQL error messages are automatically handled, but can be disabled if you wish.
- Connect and query calls return error and errstr.
- Multiple rows are separated by newline `n characters.
- Multiple columns are separated by pipe | characters.
- Listview columns can be automatically hidden by adding $ to the end of column name (easy to add to your sql select statement).
- File icons can be easily added with icon column (see example 7).

ChangeLog:
10-31-11
- improved handling of file icons in listview lv_fill function

11-01-11
- fixed sql string bug in error messages for query function

11-03-11
- added selectmode option to lvfill function

11-29-11
- Added DEMO program
- Added brokenlink.ico
- Added lvread
- other minor bug fixes and added features

The library can be downloaded from http://www.panofish....sql_library.zip

DEMO:

;====================================================================
; 
; Demo of mysql library
;
; You must create a new database schema in mysql called "demo" and create a user with privileges to it.
; This demo app will create a table and present a simple gui to add records to the table.
;
; Programmer: Alan Lilly ([email protected]) 
; AutoHotkey: v1.1.04.00 (autohotkey_L ANSI version)
;
;====================================================================

#SingleInstance force
#NoENV              ; Avoids checking empty variables to see if they are environment variables (recommended for all new scripts and increases performance).
SetBatchLines -1    ; have the script run at maximum speed and never sleep
ListLines Off       ; a debugging option

;outputdebug DBGVIEWCLEAR

;#include <mysql>      ; pull from library
#include mysql.ahk     ; pull from local directory

;============================================================
; make database connection to mysql 
;============================================================ 

mysql := new mysql      ; instantiates an object using this class

; note host can be hostname or ip address
db := mysql.connect("10.11.3.85","alan","password","demo")     ; host,user,password,database

if db =
    return

;============================================================
; create address table
; if table already exists we ignore the error because we set errmsg = 0
;============================================================ 

sql =
(
    CREATE TABLE address (
        name VARCHAR(50) NULL,
        address VARCHAR(50) NULL,
        city VARCHAR(50) NULL,
        state VARCHAR(2) NULL,
        zip INT(5) NULL,
        PRIMARY KEY (name)  )
    COLLATE='latin1_swedish_ci'
    ENGINE=InnoDB
    ROW_FORMAT=DEFAULT
)


fullname := mysql.query(db, sql, 0)

;if (mysql.error) {
;    msgbox, 16, % "MySQL Error: " mysql.error , % mysql.errstr "`n`n" sql      
;    exitapp
;}

;============================================================
; Build gui:
;============================================================ 

Gui, 1:Default 

Gui, Add, Text, section right w70, Name
Gui, Add, Edit, x+10 w150 vname

Gui, Add, Text, xs right w70, Address
Gui, Add, Edit, x+10 w250 vaddress

Gui, Add, Text, xs right w70, City
Gui, Add, Edit, x+10 w150 vcity

Gui, Add, Text, xs right w70, State
Gui, Add, Edit, x+10 w30 vstate

Gui, Add, Text, xs right w70, Zip
Gui, Add, Edit, x+10 w60 vzip

Gui, Add, Button, x+10 h21 gAddName Default, Add to database

Gui, Add, ListView, xs y+20 r10 w700 AltSubmit vList1 -multi, Name|Address|City|State|Zip         ; to increase performance use count500 if you know the max number of lines

Gui, Add, StatusBar

Gui, Show,,MySQL Demo

;--------------------------------------
; fill listview will existing addresses from database
;-------------------------------------- 

Gosub, UpdateList1

return

;============================================================
; 
;============================================================ 

UpdateList1:

    Gui, Submit, NoHide      ; update control variables
    
    sql = 
    (    
         select name as Full_Name,
                address as Street,
                City,
                State,
                Zip
           from address
       order by name 
    )
    
    ; it is recommended to use underscores in alias names instead of quoted spaces so that the column can be referred to by aliasname potential having clause
    ; mysql.lvfill will remove underscores from aliasname before displaying as column headers
    
    result := mysql.query(db, sql)
    
    mysql.lvfill(sql, result, "List1")  

    ;LV_ModifyCol(2, 95)     ; limit width of a lengthy column
    
return

;============================================================
; Edit gui and insert request to table
;============================================================

AddName:
    
    gui, submit, nohide
    
    name := mysql.escape_string(name)   ; escape mysql special characters in case user entered them
    address := mysql.escape_string(address)   ; escape mysql special characters in case user entered them
    state := mysql.escape_string(state)   ; escape mysql special characters in case user entered them
    zip := mysql.escape_string(zip)   ; escape mysql special characters in case user entered them
    
    ;------------------------------------------------
    ; insert new request on table
    ;------------------------------------------------    

    SB_SetText("Inserting new request")
    
    sql = 
(
        INSERT INTO address (
            name,
            address,
            city,
            state,
            zip)
        VALUES (
            '%name%',
            '%address%',
            '%city%',
            '%state%',
            '%zip%')
)

    result := mysql.query(db, sql)
    
    newid := mysql.query(db, "SELECT LAST_INSERT_ID()")
    
    Gosub, UpdateList1  
    
    gui, submit, nohide    ; IMPORTANT
    
return

;============================================================
; when you click x or close button
;============================================================ 

GuiClose:
        
ExitApp





EXAMPLE QUERIES:

;====================================================================
; MySQL Library Usage Examples
;
; Programmer: Alan Lilly ([email protected]) 
; AutoHotkey: v1.1.04.00 (autohotkey_L ANSI version)
;====================================================================

#SingleInstance force
outputdebug DBGVIEWCLEAR

#include mysql.ahk     ; reference local directory copy
;#include <mysql>      ; reference lib copy

;============================================================
; make database connection to mysql 
;============================================================ 

mysql := new mysql     ; instantiates mysql object

db := mysql.connect("host","userid","password","database")           ; host,user,password,database

if mysql.error
    exitapp

;============================================================
; single column select example
;============================================================ 

fullname := mysql.query(db, "SELECT username FROM user WHERE userid='" A_UserName "'")

;============================================================
; disable error handling in mysql function and handle error locally
;============================================================ 

sql := "xELECT username FROM user WHERE userid='alilly'"

fullname := mysql.query(db, sql, 0)

if mysql.error
    outputdebug % "MySQL Error = " mysql.error "`nMySQL Error String =" mysql.errstr 

;============================================================
; multi column select example (columns divided by pipe)
;============================================================ 
    
id := "alilly"
    
sql = 
(
     select username,
            userid
       from user
      WHERE userid = "%id%"
)

rec := mysql.query(db, sql) 

StringSplit, array, rec, |  

username     := array1
userid       := array2

;============================================================
; update example with escape string 
;============================================================ 

username := "Alan Lilly"
username := mysql.escape_string(username)

sql = 
(
    UPDATE user
       SET username = "%username%"
     WHERE userid = "%id%"
)

result := mysql.query(db, sql)

;============================================================
; multi-row / multi-column select example (rows divided by newline)
; and display results in a gui listview
;============================================================ 
    
sql = 
(
     select username,
            userid
       from user
)

result := mysql.query(db, sql) 

Loop, Parse, result, `n     ; parse rows
{
    StringSplit, array, A_LoopField, |      ; parse columns

    username     := array1
    userid       := array2

}

;============================================================
; Fill Listview with sql output example
;============================================================ 

Gui, Add, ListView, section r15 w300 vLIST1 

Gui, Show,,MySQL Lib Example

sql = 
(
     select username as "Full Name",
            userid
       from user
)

result := mysql.query(db, sql) 
    
mysql.lvfill(sql, result, "LIST1")  

return

LIBRARY:

;============================================================
; MySQL class to enable connection and query to mysql database.
; The lvfill function can be used to automatically fill a gui listview with the output from an sql select.
; The database connection is automatically re-established if connection is lost (usually due to server restart or sql connection timeout)
; SQL error messages are automatically handled, but can be disabled if you wish.
; Connect and query calls return error and errstr.
;
; Multiple rows are separated by newline `n characters.
; Multiple columns are separated by pipe | characters.
;
; Copy the following files to your ahk project folder.
; So they can be found by the fileinstall command and properly embedded at compile time.
;    brokenlink.ico  (indicate missing file for icon file list)
;    libmysql.dll    (required to connect and make mysql query calls)
;
; EXAMPLE USAGE:
;
;    #include <mysql>    ; includes simple.ahk from lib 
;    mysql := new mysql     ; instantiates an object using this class
;    db := mysql.connect("sqlserver","userid","password","database")    ; connect to database
;    result := mysql.query(db, sql)    ; execute mysql select, update, delete, insert... etc) note: db will be updated if reconnect is needed
;============================================================ 

Class mysql {

    ;============================================================
    ; Connect to mysql database and return db handle
    ; 
    ;    host     = ip address or hostname of mysql server
    ;    user     = authorized userid for mysql connection
    ;    password = self explanitory
    ;    database = mysql database schema to connect to
    ;    errmsg   = 1-display errors in a msgbox, 0-no msgbox.. only return error and errstr
    ;============================================================
    
    connect(host,user,password,database,errmsg=1)
    {	
    
        this.host := host
        this.user := user
        this.password := password
        this.database := database
        
        RegExMatch(A_ScriptName, "^(.*?)\.", basename) 
        if Not InStr(FileExist(A_AppData "\" basename1), "D")    ; create appdata folder if doesnt exist
            FileCreateDir , % A_AppData "\" basename1

        libmysql = %A_AppData%\%basename1%\libmysql.dll  
            
        ; note: fileinstall must be called inside the functions.. it wont work in the header for library functions like it works in the main program!
        FileInstall, libmysql.dll, %libmysql%, 0   ; 0=no overwrite, 1=overwrite

        ;----------
        
        hModule := DllCall("LoadLibrary", "Str", libmysql)
        
        If (hModule = 0)
        {
            this.error := 9999
            this.errstr := "Can't load libmySQL.dll from directory " libmysql
            if errmsg
                msgbox, 16, % "MySQL Error: " this.error , % this.errstr "`n`n" sql 
            Return            
        }

        db := DllCall("libmySQL.dll\mysql_init", "UInt", 0)
                
        If (db = 0)
        {
            this.error := 9999
            this.errstr := "Not enough memory to connect to MySQL"
            if errmsg
                msgbox, 16, % "MySQL Error: " this.error , % this.errstr "`n`n" sql 
            Return 
        }
        
        connection := DllCall("libmySQL.dll\mysql_real_connect"
                , "UInt", db
                , "Str", host       ; host name
                , "Str", user       ; user name
                , "Str", password   ; password
                , "Str", database   ; database name
                , "UInt", 3306      ; port
                , "UInt", 0         ; unix_socket
                , "UInt", 0)        ; client_flag

        If (connection = 0)
        {
            this.error := DllCall("libmySQL.dll\mysql_errno", "UInt", db)
            this.errstr := DllCall("libmySQL.dll\mysql_error", "UInt", db, "Str")
            if errmsg
                msgbox, 16, % "MySQL Error: " this.error , % this.errstr "`n`n" sql 
            Return
        }

        serverVersion := DllCall("libmySQL.dll\mysql_get_server_info", "UInt", db, "Str")

        return db

    }

    ;============================================================
    ; mysql_query
    ;    _db    = database connection pointer returned from dbConnect call 
    ;    _query = sql query string.  can be a select, insert, update, delete ... etc
    ;    msg    = 1-display errors in a msgbox, 0-no msgbox.. only return error and errstr
    ;
    ;    if reconnect is needed then the new _db pointer is returned byref
    ;
    ;    returns error and errstr by way of associate array (eg. mysql.error and mysql.errstr)
    ;============================================================

    query(ByRef _db, _query, errmsg=1)
    {
        local resultString, result, requestResult, fieldCount
        local row, lengths, length, fieldPointer, field
        
        result := DllCall("libmySQL.dll\mysql_query", "UInt", _db , "Str", _query)
        
        this.error := 0
        this.errstr := ""
                
        If (result != 0) {
            errorcde := DllCall("libmySQL.dll\mysql_errno", "UInt", db)
            
            if (errorcde = 2003) or (errorcde = 2006) or (errorcde = 0) {     ; sql connection lost (2003) or sql connection timeout (2006)
                ; attempt sql reconnect
                _db := this.connect(this.host,this.user,this.password,this.database)   ; reconnect to mysql database
                    
                If (_db = "") {   ; reconnect failed
                    this.error := 2006
                    this.errstr := "MySQL server unavailable"
                    if errmsg
                        msgbox, 16, % "MySQL Error: " this.error , % this.errstr "`n`n" _query 
                    Return
                }
                
                result := DllCall("libmySQL.dll\mysql_query", "UInt", _db , "Str", _query) ; redo sql call
                
                If (result != 0) {   ; sql still failed after reconnect
                    this.error := DllCall("libmySQL.dll\mysql_errno", "UInt", db)
                    this.errstr := DllCall("libmySQL.dll\mysql_error", "UInt", db, "Str")
                    if errmsg
                        msgbox, 16, % "MySQL Error: " this.error , % this.errstr "`n`n" _query 
                    Return  
                }
                
            } else {    ; all other sql errors
                this.error := DllCall("libmySQL.dll\mysql_errno", "UInt", db)
                this.errstr := DllCall("libmySQL.dll\mysql_error", "UInt", db, "Str")
                if errmsg
                    msgbox, 16, % "MySQL Error: " this.error , % this.errstr "`n`n" _query                 
                Return            
            }
            
        }

        ; success... process results
        
        requestResult := DllCall("libmySQL.dll\mysql_store_result", "UInt", _db)

        if (requestResult = 0) {    ; call must have been an insert or delete ... a select would return results to pass back
            return
        }

        fieldCount := DllCall("libmySQL.dll\mysql_num_fields", "UInt", requestResult)

        Loop
        {
            row := DllCall("libmySQL.dll\mysql_fetch_row", "UInt", requestResult)
            If (row = 0 || row == "")
                Break

            ; Get a pointer on a table of lengths (unsigned long)
            lengths := DllCall("libmySQL.dll\mysql_fetch_lengths" , "UInt", requestResult)
                
            Loop %fieldCount%
            {
                length := this.GetUIntAtAddress(lengths, A_Index - 1)
                fieldPointer := this.GetUIntAtAddress(row, A_Index - 1)
                VarSetCapacity(field, length)
                DllCall("lstrcpy", "Str", field, "UInt", fieldPointer)
                resultString := resultString . field
                If (A_Index < fieldCount)
                    resultString := resultString . "|"     ; seperator for fields
            }

            resultString := resultString . "`n"          ; seperator for records  

        }

        ; remove last newline from resultString
        resultString := RegExReplace(resultString , "`n$", "") 	

        Return resultString
    }

    ;============================================================
    ; mysql get address
    ;============================================================ 

    GetUIntAtAddress(_addr, _offset)
    {
       local addr
       addr := _addr + _offset * 4
       Return *addr + (*(addr + 1) << 8) +  (*(addr + 2) << 16) + (*(addr + 3) << 24)
    }

    ;============================================================
    ; Escape mysql special characters
    ; This must be done to sql insert columns where the characters might contain special characters, such as user input fields
    ;
    ; Escape Sequence     Character Represented by Sequence
    ; \'     A single quote (“'”) character.
    ; \"     A double quote (“"”) character.
    ; \n     A newline (linefeed) character.
    ; \r     A carriage return character.
    ; \t     A tab character.
    ; \\     A backslash (“\”) character.
    ; \%     A “%” character. Usually indicates a wildcard character
    ; \_     A “_” character. Usually indicates a wildcard character
    ; \b     A backspace character.
    ;
    ; these 2 have not yet been included yet
    ; \Z     ASCII 26 (Control+Z). Stands for END-OF-FILE on Windows
    ; \0     An ASCII NUL (0x00) character.
    ;
    ; example call:
    ;     description := mysql_escape_string(description)
    ;============================================================

    escape_string(unescaped_string)
    {
        escaped_string := RegExReplace(unescaped_string, "\\", "\\")     ; \
        escaped_string := RegExReplace(escaped_string, "'", "\'")        ; '
        
        escaped_string := RegExReplace(escaped_string, "`t", "\t")       ; \t
        escaped_string := RegExReplace(escaped_string, "`n", "\n")       ; \n
        escaped_string := RegExReplace(escaped_string, "`r", "\r")       ; \r
        escaped_string := RegExReplace(escaped_string, "`b", "\b")       ; \b
        
        ; these characters appear to insert fine in mysql    
        ;escaped_string := RegExReplace(escaped_string, "%", "\%")        ; %
        ;escaped_string := RegExReplace(escaped_string, "_", "\_")        ; _
        ;escaped_string := RegExReplace(escaped_string, """", "\""")      ; "
        
        return escaped_string
    }
    
    ;============================================================
    ; fill listview with results from query 
    ; note: the current data in the listview is replaced with the new data
    ;
    ; inputs:
    ;    column names:  provide a comma delimited list of names to be used as column headers in the listview
    ;                   OR 
    ;                   provide the sql query string and column names will pulled from select clause
    ;                   (eg. "select name as User_Name from table" then column will be "User Name")
    ;                   (eg. "select name from table" then column will be "Name")
    ;
    ;                   Underscores are automatically removed from aliasname before displaying as column headers
    ;
    ;                   To hide a column put a $ at the end of the column name 
    ;                   (eg. "select name as User_Name$ from table")
    ;                
    ;                   If you include a column named "icon", then its value will be used to add an icon to the listview.
    ;                   The icon column should contain a full path to a file or folder to extract the icon from.
    ;
    ;                   If path is not found then brokenlink.ico will be used.
    ;                
    ;                 * Displaying icons significantly reduces performance and is only recommended for short lists.
    ;                   This is because the current logic stores a unique icon for each file, 
    ;                   even when there is already a file in the list with the same icon.  Perhaps in the future this logic can be added.
    ;
    ;    sql result:    provide data returned from mysql.query 
    ;                   (rows should be \n delimited and columns | delimited)
    ;
    ;    listview name
    ;
    ;    selectmode:    (optional) Important when refreshing an existing listview.  Set how to re-select the same row.
    ;                   0 = no re-select  (default)
    ;                   1 = select by column 1 value  (column 1 is assumed to be unique)
    ;                   2 = select by row number (recommended only if your list is relatively static)
    ;
    ;============================================================ 

    lvfill(sql, result, listviewname, selectmode=0)
    {
    
        ;-------------------------------------------
        ; delete all rows in listview
        ;-------------------------------------------
    
        GuiControl, -Redraw, %listviewname%     ; to improve performance, turn off redraw then turn back on at end
        
        Gui, ListView, %listviewname%    ; specify which listview will be updated with LV commands  
        
        if (selectmode = 1) {
            column1value := ""
            selectedrow := LV_GetNext(0)     ; get current selected row
            if selectedrow |= 0
                LV_GetText(column1value, selectedrow, 1) ; get column 1 value for current row          
        } else if (selectmode = 2) {
            selectedrow := LV_GetNext(0)     ; get current selected row
        }
        
        LV_Delete()  ; delete all rows in listview
        
        ;-------------------------------------------
        ; delete all pre-existing columns (must delete in reverse order because it is a shifting target)
        ;-------------------------------------------

        columncount := LV_GetCount("Column")

        if columncount > 0
            Loop, %columncount%
            {	
                LV_DeleteCol(columncount)
                columncount--
                if columncount = 0
                    break
            }
        
        ;-------------------------------------------
        ; create columns
        ;-------------------------------------------

        columns := this.sqlcolumns(sql)    ; get list of column names in comma delimited list
        
        totalcolumns := 0
        iconcolumn := 0
        Loop, parse, columns, CSV
        {	
            totalcolumns++
            
            ;colname := RegExReplace(A_LoopField, "\$", "")
            ;LV_DeleteCol(A_Index)   already deleted above
            
            LV_InsertCol(A_Index,"",A_LoopField)   ; create column with name from sql, but remove possible $ which indicates a hidden field
            
            if (A_LoopField = "icon$" ) {  ; detect optional icon column 
                iconcolumn := A_Index    ; save icon column number for later
                ; create imagelist for icons
                ImageListID := IL_Create(10)  ; Create an ImageList to hold small icons, this list can grow, so 10 is ok
                LV_SetImageList(ImageListID)  ; Assign the above ImageList to the current ListView.
                VarSetCapacity(Filename, 260)   ; Ensure the variable has enough capacity to hold the longest file path.
                sfi_size = 352
                VarSetCapacity(sfi, sfi_size)   ; This is done because ExtractAssociatedIconA() needs to be able to store a new filename in it.
            }
        }
        
        ;-------------------------------------------
        ; fileinstall brokenlink.ico to represent missing files in icon file list
        ;-------------------------------------------
        
        if (iconcolumn != 0) {
            RegExMatch(A_ScriptName, "^(.*?)\.", basename) 
            if Not InStr(FileExist(A_AppData "\" basename1), "D")    ; create appdata folder if doesnt exist
                FileCreateDir , % A_AppData "\" basename1

            file := "brokenlink.ico"
            brokenlink = %A_AppData%\%basename1%\brokenlink.ico  
            
            If FileExist( "./brokenlink.ico" ) {  ; if brokenlink.ico exists then install in appdata
                FileInstall, brokenlink.ico, %brokenlink%, 0   ; 0=no overwrite, 1=overwrite
            }
        }
        
        ;-------------------------------------------
        ; using first row values, set integer columns
        ;-------------------------------------------
        
        StringGetPos, pos, result, `n   ; extract first row from result
        StringLeft, row, result, pos
        Loop, parse, row, |
        {	
            StringReplace, data, A_LoopField, % " KB",,   ; remove " KB" so that column can be interpreted as an integer
            if data is integer
                LV_ModifyCol(A_Index, "Integer")  ; For sorting purposes, indicate column is an integer.
        }

        ;-------------------------------------------
        ; parse rows
        ;-------------------------------------------
        
        count := 0
        Loop, parse, result, `n
        {		
            
            IfEqual, A_LoopField, , Continue  ; Ignore blank rows (usually last row)

            LV_Add("") ; add blank row to listview
            
            StringSplit, array, A_LoopField, |      ; extract columns
            
            ; if icon column exists then use given path to create icon for current row
            if (iconcolumn != 0) {   
            
                iconpath := array%iconcolumn%     ; get column text
                
                ; Get the high-quality small-icon associated with this file extension:
                if DllCall("Shell32\SHGetFileInfoA", "str", iconpath, "uint", 0, "str", sfi, "uint", sfi_size, "uint", 0x101)  ; 0x101 is SHGFI_ICON+SHGFI_SMALLICON
                {
                    ; Extract the hIcon member from the structure:
                    hIcon = 0
                    Loop 4
                        hIcon += *(&sfi + A_Index-1) << 8*(A_Index-1)
                    ; Add the HICON directly to the small-icon and large-icon lists.
                    ; Below uses +1 to convert the returned index from zero-based to one-based:
                    IconNumber := DllCall("ImageList_ReplaceIcon", "uint", ImageListID, "int", -1, "uint", hIcon) + 1
                    DllCall("DestroyIcon", "uint", hIcon)   ; Now that it's been copied into the ImageLists, the original should be destroyed
                } else {
                    if DllCall("Shell32\SHGetFileInfoA", "str", brokenlink, "uint", 0, "str", sfi, "uint", sfi_size, "uint", 0x101)  ; 0x101 is SHGFI_ICON+SHGFI_SMALLICON
                    {
                        ; Extract the hIcon member from the structure:
                        hIcon = 0
                        Loop 4
                            hIcon += *(&sfi + A_Index-1) << 8*(A_Index-1)
                        ; Add the HICON directly to the small-icon and large-icon lists.
                        ; Below uses +1 to convert the returned index from zero-based to one-based:
                        IconNumber := DllCall("ImageList_ReplaceIcon", "uint", ImageListID, "int", -1, "uint", hIcon) + 1
                        DllCall("DestroyIcon", "uint", hIcon)   ; Now that it's been copied into the ImageLists, the original should be destroyed                
                    } else {
                        IconNumber := 9999999  ; Set it out of bounds to display a blank icon.
                    }
                }
                
                LV_Modify(A_Index, "Icon" . IconNumber)   ; set row icon             
            }
            
            row := A_Index
            
            ; populate columns of current row
            Loop, parse, columns, CSV     
            {
                data = col%A_index%      ; trick to indicate colx in following LV_Modify command
                LV_Modify(row,data,array%A_Index%)      ; update current column of current row
            }
                    
        }
        
        ;-------------------------------------------
        ; autosize columns: should be done outside the row loop to improve performance
        ;-------------------------------------------
        
        LV_ModifyCol()  ; Auto-size each column to fit its contents.
        Loop, parse, columns, CSV
        {	
            if (A_Index != totalcolumns)     ; do all except last column
                LV_ModifyCol(A_Index,"AutoHdr")   ; Autosize header.
            
            if RegExMatch(A_LoopField, "\$$")    ;If there is a $ at end of column name, that indicates a hidden column
                LV_ModifyCol(A_Index,0)   ; set width to 0 to create hidden column
            
        }
        
        ;LV_ModifyCol(2,0)    ; makes column 0 width... therefore, hidden
        
        Gui, Submit, NoHide               ; update v control variables	

        ; re-select logic

        if (selectmode = 1) {    ;reselect row by column1value
            if (column1value != "") {
                Loop % LV_GetCount()   ; loop through all rows in listview to find column1value
                {
                    LV_GetText(value, A_Index, 1)    ; get column1 value for current row

                    If (value = column1value) {
                        LV_Modify(A_Index, "+Select +Focus")     ; select originally selected row in list  
                        break
                    }
                }
            }
        } else if (selectmode = 2) {    ; reselect row by row number
            if (selectedrow != 0)
                LV_Modify(selectedrow, "+Select +Focus")     ; select originally selected row in list   
        }
        
        GuiControl, +Redraw, %listviewname%     ; to improve performance, turn off redraw at beginning then turn back on at end
        
        Return

    }

    ;============================================================ 
    ; lvread
    ; gets the contents of a listview and returns in result form (columns are | delimited and rows are `n delimited)
    ;============================================================ 

    lvread(listviewname)
    {
        Gui, ListView, %listviewname%    ; specify which listview will be updated with LV commands  
    
        result := ""
        
        Loop % LV_GetCount()   ; loop through all rows in listview 
        {
            row := A_Index

            Loop % LV_GetCount("Column")    ; loop through all columns
            {
                LV_GetText(value, row, A_Index)    ; get column value
            
                result .= value "|"  
            }
            
            result .= "`n"
        }
        
        return result
    }    
        
    ;============================================================
    ; extract column names from sql string and return in a comma delimited list
    ;============================================================ 

    sqlcolumns(sql)
    {
        sql := RegExReplace(sql , "\n", " ")    ; collapse multiline string ... replace \n with spaces
        sql := RegExReplace(sql , "\t", " ")    ; replace \t with space
        sql := RegExReplace(sql , "\s+", " ")   ; collapse multiple spaces to single space replace \s+ with " "
        sql := RegExReplace(sql , "\([^\(]+?\)", "")   ; remove parenthetical items because they may contain commas... 	
        sql := RegExReplace(sql , "\([^\(]+?\)", "")   ; run a second time to account for parens inside parens
        sql := RegExReplace(sql , "\([^\(]+?\)", "")   ; run a third time to account for parens inside parens (this will handle 3 levels deep for parens)
        
        if (RegExMatch(sql, "i)SELECT (.*?) FROM ", data) )     ; extract substring using regex and store subpatterns (.*) into data1, data2...etc
            selectclause := data1     
        else
            return sql     ; data does not contain select clause, so it may already be a comma delimited list
        

        columns := ""
        Loop, Parse, selectclause , CSV 
        {

            if A_LoopField =     ; skip blanks
                continue 
            
            item := RegExReplace(A_LoopField , "^\s+", "")    ; remove beginning spaces
            item := RegExReplace(item , "\s+$", "")           ; remove ending spaces
            
            ; find possible alias
            if (RegExMatch(item, "i).* as (.*)", alias)) { ; extract substring using regex and store subpatterns (.*) into data1, data2...etc
                aliasname := RegExReplace(alias1 , "_", " ")   ; replace possible underscores with spaces in aliasname
                columns = %columns%%aliasname%,
            } else {
                columns = %columns%%item%,
            }
            
        }
            
        ; remove last comma delimiter    
        columns := RegExReplace(columns , ",$", "") 

        return columns
    }

    ;============================================================
    ; return the text for a given columnName and row number
    ; Same as LV_GetText, except columnname can be given instead of column number
    ;============================================================

    lv_gettext2(ByRef OutputVar, RowNumber, ColumnName)
    {
        ; Find ColumnNumber for given ColumnName
        
        Loop % LV_GetCount("Column")
        {
            LV_GetText(name, 0, A_Index)  ; get column name  
            
            If (Name = ColumnName) {
                ; A_Index is the columnnumber
                LV_GetText(OutputVar, RowNumber, A_Index)
                return
            }
        }
        
        return 
    }    
}


Learning one
  • Members
  • 1483 posts
  • Last active: Jan 02 2016 02:30 PM
  • Joined: 04 Apr 2009
I don't have opportunity to test it yet, but looks nice. Thanks for sharing! :)

panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
I've updated this library to include a couple fixes.

- improved handling of file icons in listview lv_fill function
- fixed sql string bug in error messages for query function

panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
Added re-select feature to lvfill
Added listview file icon example 7.

JasonDavis
  • Members
  • 6 posts
  • Last active: Dec 15 2011 09:22 AM
  • Joined: 04 May 2010
This is super cool, any chance of a demo app to see it in action

  • Guests
  • Last active:
  • Joined: --
That's a good idea... I hope I can find some time in the near future to create a nice demo.

Guest2
  • Guests
  • Last active:
  • Joined: --
I'm getting an error at line 22 in the mysql.ahk.

Its saying that "Class mysql{" does not contain a recognized action.

Any ideas??

This looks totally sweet by the way.

panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
What version of AutoHotkey are you using?
This library was coded for AutoHotkey: v1.1.04.00 (autohotkey_L ANSI version).

Guest2
  • Guests
  • Last active:
  • Joined: --
1.0.48.05 it looks like

I believe you answered my question. I am using Basic version, which doesn't support objects..

I guess I will have to suck it up and re-write all my scripts to work with the ANSI version.

Thanks

panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
Not really... you should'nt have to change anything from basic to the ANSI L version. It's the UNICODE version that may be more difficult adopt.

Just download and install the latest Autohotkey L version here https://ahknet.autoh...s/AutoHotkey_L/

Be sure to check ANSI during the install. That's it.

Guest2
  • Guests
  • Last active:
  • Joined: --
Maybe that was my problem.

When I tried about 6 months ago, a bunch of my scripts had some errors and wouldn't run. If I remember correctly, it had to do with some COM functions I was using. I have a HUGE script that navigates and builds a website on a cms. I am building like 15 to 20 websites a week with it, so needless to say, I switched back pretty quickly after running into those problems.

I'll give it another shot though and make sure I am selecting ANSI.

Thanks again

Guest2
  • Guests
  • Last active:
  • Joined: --
Ok, one more dumb question.

Where to I install the libmysql.dll

I have the functions in my script for now just to test, but will use in a seperate functions file when I implement it.

Thanks!

panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
Just put the libmysql.dll file with the mysql.ahk library file.
When you call mysql.connect it will look for the libmysql.dll file.

It uses the fileinstall command to embed the libmysql.dll file into your exe when you compile your ahk into a standalone executable.

It's a thing of beauty... you get an exe that can make sql calls that doesn't need any other components or installation!

Guest2
  • Guests
  • Last active:
  • Joined: --
I love it. Absolutely perfect for what I need!

I am writing an http post action on my android phone (using the app "Tasker" ) that will update a field in a database. On my computer, I have a script that will be checking this field periodically, and upon change of this field to the correct value, my script will launch. This makes it possible for me to run certain programs, scripts, etc. with the quick push of an icon on my android.

I love your library, thanks so much for the time and sharing it with us all!!

panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
Your welcome.

As it is, it works very well, but I have recently made some improvements and a couple minor bugfixes that I will release to the public as soon as I can.