Jump to content

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

[AHK L] DBA 1.6 (OOP-SQL DataBase {SQLite, MySQL, ADO})


  • Please log in to reply
183 replies to this topic
panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
Thanks IsNull. As you can see, I am attempting to use your mysql library in place of my own. I like your queryFirstField solution, but I'd prefer a shorter name (queryField)

I also need a method for sql query that returns only 1 row with multiple columns similar to queryFirstField. It would also provide access to fields by colname. And provide the colnames themselves. The call would look something like this:

rec := db.QueryRow("SELECT id,name FROM table1 where id = 5")

id := rec["id"] 
name := rec["name"]

These 3 query methods, along with the OpenRecordSet method should cover the majority of frequently needed calls:

1. query
2. queryField
3. queryRow

IsNull
  • Moderators
  • 990 posts
  • Last active: May 15 2014 11:56 AM
  • Joined: 10 May 2007
Updated to 0.8, fixed several issues. The code now uses real Exceptions.

IsNull
  • Moderators
  • 990 posts
  • Last active: May 15 2014 11:56 AM
  • Joined: 10 May 2007
I've implemented 1. and 2. and 3. in the abstract Database Class
Query(...)
QueryValue(...)
QueryRow(...)


class DataBase
{
;...
	QueryValue(sQry){
		rs := this.OpenRecordSet(sQry)
		value := rs[1]
		rs.Close()
		return value
	}
	
	QueryRow(sQry){
		rs := this.OpenRecordSet(sQry)
		myrow := rs.getCurrentRow()
		rs.Close()
		return myrow
	}
;...
}


panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
Your awesome IsNull... I will keep you informed as I rewrite my existing application with this library of any other issues I encounter. THANKS!

panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
I've tested the 0.8 changes.
There are some bugs and the modified library can be downloaded from here:
You can best see my changes by using a diff program. I use beyond compare.

http://www.panofish....HK-panofish.zip

Changes I made:

1. DataBaseMySQL.ahk - line 41, changed handleDB to dbHandle
2. DataBaseMySQL.ahk - line 172, changed 2004 to 2003
3. MySQL.ahk - line 33 - 41, commented dbHandle logic and added return connnectionData

I left all of the global declares in the code because I get errors when they are removed and I don't understand the code well enough to debug the errors.

All of the mysql database calls work fine with this code including QueryValue and QueryRow.

IsNull
  • Moderators
  • 990 posts
  • Last active: May 15 2014 11:56 AM
  • Joined: 10 May 2007

I've tested the 0.8 changes.

Thanks! I'm sorry that I didn't test the mysql part, as my current machine is laking a mysql test server service at the moment

1. DataBaseMySQL.ahk - line 41, changed handleDB to dbHandle

This was really an ugly one. Fixed!

2. DataBaseMySQL.ahk - line 172, changed 2004 to 2003

This issue was actually fixed in 0.8, but it might be a cache problem.

3. MySQL.ahk - line 33 - 41, commented dbHandle logic and added return connnectionData

Jup I forgot to finish that one. Thanks for the hint!




I left all of the global declares in the code because I get errors when they are removed and I don't understand the code well enough to debug the errors.

Use the current AHK_L Version, which has a breaking change in the class decalration behaviour. Parsed Class-Names are now super global, therefore there is no need to make class names global in methods. :)

Updated the code with the fixes.

IsNull
  • Moderators
  • 990 posts
  • Last active: May 15 2014 11:56 AM
  • Joined: 10 May 2007
The only missing thing for now would be, to allow UPDATES on the Recordset, to make changes more easily.

To get this working, I have to retrieve the PKs form the table in question, and then the easy part is to create an appropriate Update SQL-Statement.

Sqlite supports retriving PK info with some Query macros (table info), for MySQL I've to do some research.

panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
I've also created an improved version of the showtable listview function.
I use this frequently in my applications.
I'd like to see it included in this library.

Improvements are:

1. column resizes correctly and accounts for column header and column data.
2. you can automatically hide columns by adding a $ to the variable name (e.g. select id$, name, address from table1).
3. listview can remember previous selection.
4. integer columns are handled correctly for sorting

;============================================================
; fill listview with results from query 
; note: the current data in the listview is replaced with the new data
;
; inputs:
;     listviewname: the name of the listview to fill
;
;    table:         provide a table object which is usually generated from mysql call.
;                   Example:   
;                       datatable := db.Query(sql) 
;
;                   Underscores are automatically removed from aliasname before displaying as column headers
;
;                   To hide a column put a $ at the end of the column name 
;                   Example: 
;                       select id as id$, name from table1
;                
;    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(listviewname, table, 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  
    
    ;-------------------------------------------
    ; remember current selection in listview
    ;-------------------------------------------
    
    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
    }
        
    ;-------------------------------------------
    ; delete any pre-existing rows and columns in listview
    ;-------------------------------------------

    LV_Delete()  ; delete all rows in listview
    
    Loop, % LV_GetCount("Column")    ; delete all columns in listview
	   LV_DeleteCol(1)
    
    ;-------------------------------------------
    ; create new columns
    ;-------------------------------------------

    for each, colName in table.Columns 
    {
        colName := RegExReplace(colName , "_", " ")    ; remove underscores from column names
		LV_InsertCol(A_Index,"", colName)
    }

    ;columnCount := table.Columns.Count()

    ;-------------------------------------------
    ; insert rows
    ;-------------------------------------------
    
	for each, row in table.Rows
	{
		rowNum := LV_Add("", "")
		for each, colName in table.Columns 
			LV_Modify(rowNum, "Col" . A_index, row[A_index])
	}

    ;-------------------------------------------
    ; use first row values to set integer columns
    ;-------------------------------------------
    
    if table.Rows.Count()    ; only if table contains rows
        for each, colName in table.Columns 
        {
            data := table[1][A_Index]    ; table[row][column]
            
            StringReplace, data, data, % " KB",,   ; remove " KB" so this column can be interpreted as an integer
            if data is integer
                LV_ModifyCol(A_Index, "Integer")  ; For sorting purposes, indicate column is an integer.
        }
    
    
    ;-------------------------------------------
    ; autosize columns: should be done outside the row loop to improve performance
    ;-------------------------------------------
    
    LV_ModifyCol()  ; Auto-size each column to fit its contents.
    
    for each, colName in table.Columns
    {
        LV_ModifyCol(A_Index,"AutoHdr")   ; Autosize headers (does last header need this?)
        if RegExMatch(colName, "\$$")    ;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
    }
    
    Gui, Submit, NoHide               ; update v control variables	

    ;-------------------------------------------
    ; re-select row in listview
    ;-------------------------------------------

    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

}


panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
The QueryRow method has a bug.

Whenever you do a select that returns nothing you get error:
"A non-object value was improperly invoked. Specifically 0"

The other methods: query, queryvalue and openrecordset work correctly and do not show an error when your select returns nothing.

Example:
RequestId 436 does not exist on the table and this select should not error, but should return nothing:
rec := db.QueryRow("SELECT projectid,userid FROM request where requestid = 436")


panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
Please prefix the classes.
It takes a while to debug these errors which are caused by using a variable of the same name as a class.

This is an excerpt of my code that was failing.
The get for rec["status"] failed with a "non-object value invoked".
It took me a while to discover that my use of the variable "row" was causing the error, just like my previous use of a variable called "table".

PickList2:
       
    if IgnoreEvent()
        return
    
    gui_status("","clear.ico")    ; clear statusbar
    
    ;------------------------------------------------
    ; get request id from selected row
    ;------------------------------------------------    
        
    Gui, ListView, LIST2    ; specify which listview 
    
    row := LV_GetNext("")   ; get selected row number
    
    if (row = 0)     ; return if no row selected
        return

    LV_GetText(requestid, row, 1)  ; Get the text from specified column
    
    ;------------------------------------------------
    ; update form fields for selected request id
    ;------------------------------------------------    

    ; use requestid to get all data from request table
          
    sql = 
    (
         select status,
                DATE_FORMAT(opendate, '`%m-`%d-`%Y') as opendate,
                DATE_FORMAT(duedate, '`%m-`%d-`%Y') as duedate,
                DATE_FORMAT(closedate, '`%m-`%d-`%Y') as closedate,
                requesttype,
                concat(modelyear, " ", brand, " ", make) as vehicle,
                description,
                priority
           from request 
          WHERE requestid = %requestid%
    )    

    rec := db.QueryRow(sql) 
    status := rec["status"]
    opendate := rec["opendate"]
    ...


IsNull
  • Moderators
  • 990 posts
  • Last active: May 15 2014 11:56 AM
  • Joined: 10 May 2007

Please prefix the classes.

I'll do it in the next release, and also add better error handling to the query methods.

showtable listview function

This is a nice one, and I can include it in the example. However, this library should be about a Database wrapper and not ship stuff which is too off topic.

Btw:
Creating a Databinding for a Listview to a Table (or any other Objects) is a task which was requested a lot and I may give it a try. What I also like to see is some support for standard classes including Collections, Collections with Change Events (eg. "ObservableCollections"), some common methods like compareTo(other) etc. to have a widely reusable base. Best would be it ships directly with AHK in the stdlib.

Anyway, I think this lib should focus on Database access. :wink:

panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
Excellent IsNull... I agree.

This database library significantly strengthens AHK when compared with other languages.

figure8car
  • Members
  • 2 posts
  • Last active: Jan 26 2012 06:36 AM
  • Joined: 21 Nov 2008
Thanks for all this work. I'm looking forward to using SQLite with AutoHotKey! I'm using AHK 1.1.05.06 on Windows 7 64. When I try to run this sample code on a database that I'm now using in a Python program, I have to remove the <> symbols in the #Include statements and append the Include file name with .ahk. If I do that, it stops complaining. Also, the database I'm using has a file extenstion of .db instead of .sqlite. It works in Python and with SQLite Manager in FireFox. Does that matter here? The reason I ask is because no error messages occur until I try to read some data, then I get "A non object value was improperly invoked"

Then in the diagnostic window:
045: columnCount := tables.Columns.Count()
091: ListLines

A problem reading the database?


[/img]

IsNull
  • Moderators
  • 990 posts
  • Last active: May 15 2014 11:56 AM
  • Joined: 10 May 2007

I have to remove the <> symbols in the #Include statements and append the Include file name with .ahk.

Very interesting. I don't get that, as the #Include <libname> was added some versions before the one you use.

I currently use it on Windows 7, 64 bit, BUT with AHK Unicode 32bit, therefore it could be also an 64bit issue.




I'm using AHK 1.1.05.06 on Windows 7 64.

What AHK Version do you use? Ansi or Unicode? 32bit or 64bit?

Also, the database I'm using has a file extenstion of .db instead of .sqlite

The extension doesn't matter. Does it work with the example script?


EDIT:
If you use AHK 64bit, you must use 64bit Versions of the provided dlls.

panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
I'm using AHK v1.1.05.05 ANSI 32-bit running on 64 bit Windows XP.
No include problem here.