183 replies to this topic

[IsNull]

AHK DBA
see here for German Thread

DBA is an object oriented wrapper around several different databases/database providers to standardize the access interface. It is similar to ADO from MS or the jdbc driver in Java. Currently DBA supports SQLite, MySQL and ADO. (ADO is itself an abstraction of Database Access and incorporates many drivers such as OLEDB for MSAccess and others for MS SQL Servers)
Please note that this project is in an early stage and bugs are expected, feature requests are welcome.

The basic idea beyond this Project is to abstract the underling database to uniformize and simplify the access to a database. You get an very intuitive way to access a database, which will be demonstrated below. This is done in wrapping the native table data/resultsets in Objects with several helper Classes for Tables, Rows, Fields and finally the dll wrappers.


Requirements

AHK_L 1.1.08 or above. Unicode, Ansi, 32bit, 64bit is supported in any combination.
DBA 64bit support: MySQL and SQLite3 are supported in 64bit AHK build. (64bit versions of mysqlib.dll and sqlite3.dll are included in /Lib/x64/, the internal DLL Loader will automatically choose the correct Dll for you)
Everything runs out of the box


Download Lib & Example
The example runs out of the box, using an SQLite Database.

DBA on









Usage



To open a connection to a Database, use the DBA.DataBaseFactory.OpenDataBase Method:

SQLite:
 

connectionString := "C:\my\sqlite\database.sqlite"
db := DBA.DataBaseFactory.OpenDataBase("SQLite", connectionString)

OpenDataBase is a static method, which will build an instance of "DataBase" Class, which then is stored in the db variable -> db
Beyond the facade it will resolve the DB Type and create a concrete Instance for the given DB Type, but as a user of this code you don't have to care about.

MySQL:
 
 

connectionString := "Server=localhost;Port=3306;Database=test;Uid=root;Pwd=toor;"
db := DBA.DataBaseFactory.OpenDataBase("MySQL", connectionString) ; MySQL

ADO - MsAccess:
 

connectionString := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" A_ScriptDir "\Test\TestDB.mdb"
db := DBA.DataBaseFactory.OpenDataBase("ADO", connectionString) ; ADO

 

Database Queries

Whichever database we use, we know that our DB Object will have a Method called "Query(sql)", to perform a simple SQL-Query.

 
 
 
 

res := db.Query("Select * from Test")

res might be a bool true/false (on a command statement) or a table Object, which contains a full resultset-dump.

Now you can iterate through the Tables Rows and access the fields:
 
 

	table := db.Query("Select * from Test")

	columnCount := table.Columns.Count()
	for each, row in table.Rows
	{
		Loop, % columnCount
			msgbox % row[A_index]
	}

However, you can also access the fields by its column-name. This is demonstrated below in the record-set show-case:


RecordSet (Get the rows step by step)

If you have a lot of data you may not wan't to dump it all in your clients memory. In those cases you can use a RecordSet:
 
 
 

 rs := db.OpenRecordSet("Select * from Test")
   while(!rs.EOF){   
      name := rs["Name"] 
      phone := rs["Phone"]  ; column-name oder Index

      MsgBox %name% %phone%
      rs.MoveNext()
   }
   rs.Close()

Insert Data

To fill data into the DB, you create a simple Object which must have the same Propertynames as the Table Columnames are:

 
 
 

	;Table Layout: Name, Fname, Phone, Room
	record := {}
	record.Name := "Hans"
	record.Fname := "Meier"
	record.Phone := "93737337"
	record.Room := "wtf is room!? :D"

	db.Insert(record, "Test")

Simple as that. The Insert-SQL will be generated automatically.

so far
IsNull


Credits:
 
 

• [*:g7kj61hw] SQLite uses big parts of "ich_L"'s library @ <!-- m -->http://de.autohotkey...opic.php?t=7350<!-- m -->
  [*:g7kj61hw] MySQL.ahk was inspired from "panofish" @ <!-- m -->http://www.autohotke...topic67280.html<!-- m -->
  [*:g7kj61hw] infogulch - Several contributions on github

Old Versions
Deprecated Version 0.9
 
 
 
 

1.6 Several Bugfixes & refactorings, introducing BLOB support
1.5a Fixed path related issues when #NoEnv is not present
1.5 Fixed ADO Non-Selection Queries, merged several fixes from infogulch (FilePath-Check, DllCall fixes.)
1.4 Fixed ADO Connection Closed bug, updated sqlite.dll (32bit) to newest version (supporting multiline SQL Inserts, 64 bit is already the newest version)
1.3 several minor fixes according to SQLite. SQLite has now 64bit support out of the box!
1.2 several minor fixes according to mySQL (AHK_L 64bit works now as expected!)
1.1b merged several fixes by infogulch
1.1a added DB Types thx @ infogulch
1.1 - fixed InsertMany in ADO, using nested classes as namespaces DBA.Recordset etc.
1.0 - first support for ADO, prefixed class names with "DBA"
0.9 - support 64bit AHK for MySQL.
0.8b - added string escaping on autogenerated inserts, added db.EscapeString(string)
0.8a - fixed some mysql related bugs, fixed bugs introduced with 0.8
0.8 - several fixes, updated to current AHK_L Syntax, added QueryVaule(...) Method
0.7 - Implemented auto reconnect on MySQL Servers (thx @ panofish)
0.6 - Support MySQL

[fragman]

Very nice! I'll think of this if I ever need to use DB access in AHK.

[IsNull]

Thanks for the response

Currently I'm thinking of a way to make deletions also possible when dealing with a record-row Object, on the one hand it should be easy to use on the other hand I don't want to build a full blown ORM. (You should not have to use any mapping definitions whats the PK etc.)

Maybe someone has a good idea?

[Learning one]

Thanks IsNull.
I did some quick tests and it seems to work fine.

[IsNull]

Added auto-reconnect when the connection times out. (thx @ panofish)

[BenJarry]

Hi, thank IsNull for this. I have used it to connect to mysql, everything sounds good, but there's a little problem. When I query the database, the chinese charater will reults in the question mark like "??".

My ahk version is v1.1.05.01 ahk_L unicode version. I'm not sure whether this library support the unicode like chinese charater or others.

Can anyone provide some tips for handling this problem?

[panofish]

I want to do a single select from a table.
Where only 1 row with 1 column is returned.

For example:
SELECT name FROM table1 WHERE id = 'alilly'

returns:
Alan Lilly

I would like to return the value in a single line of code, but unfortunately it requires 2 lines of code because an object is returned:

rs := db.OpenRecordSet("SELECT name FROM table1 WHERE id = 'alilly'")
name := rs["name"]
rs.Close()

How can I make a call similar to this that returns the value and not an object (this call does not return a value):

name := db.Query("SELECT name FROM table1 WHERE id = 'alilly'")

I tried adding this logic to _GetTableObj, but got an error because it was expecting to return an object. After the throw, the correct value was returned however.

if (columnCount = 1 AND rowIndex = 1) {
   return fieldValue
}

[nimda]

db.OpenRecordSet("SELECT name FROM table1 WHERE id = 'alilly'")["name"]

:wink:

[panofish]

Correction:

It actually takes 3 lines of code to do what I want to do in 1 line of code:

rs := db.OpenRecordSet("SELECT name FROM table1 WHERE id = 'alilly'")
name := rs["name"]
rs.Close()

Maybe the proper solution is to code an additional method like this?

name := db.SingleSelect("SELECT name FROM table1 WHERE id = 'alilly'")

[panofish]

I believe there is a bug in library MySQL.ahk.
Currently it returns dbHandle, but it should actually return connectionData.

[panofish]

Possible solution:

I added this code to _GetTableObj before "return tbl" (in DataBaseMySQL.ahk and made similar change in DataBaseSQLLite.ahk)

if (columnCount = 1 AND rowIndex = 1) {
    return fieldValue
}

Now this code works as desired:

name := db.Query("SELECT username FROM user WHERE userid='alilly'")

EDIT: The above solution works, but it also creates a problem when you have a select that usually returns multiple rows, but could occassionally return a single row. Therefore, the above logic must be removed. Perhaps the solution for handling a select which will always return a single fieldvalue, is to create another method?

[panofish]

Why does it cause an error when I use variable name "table" like this?

table:= db.Query("SELECT username FROM user WHERE userid='alilly'")

I know the name Table is used inside DataBaseMySQL.ahk, but I thought it was private?

Is Table a reserved word?

[panofish]

An SQLite call which has a syntax error does not return a friendly error message like the corresponding MySQL call does.

table := db.Query("xSELECT fname FROM test")

This is the error I get from a MySQL select which contains a syntax error:


This is the error I get from a Sqlite select which has the same syntax error:

[panofish]

I've added 2 new methods to my version of this MySQL library.

QueryRow: returns the first row with it's column array.
QueryValue: returns the first value.

Example usage:

;=================================================
; select 1 row with multiple columns 
;=================================================

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

columnCount := rec.Count()

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

;=================================================
; select single item from table 
;=================================================

name := db.QueryValue("SELECT name FROM table1 where id = 5")

These 2 new methods were added to DataBaseMySQL.ahk:

;==================================================
    ; execute an sql query that returns a single value 
    ; if the query returns multiple rows/columns then only the first value is returned
    ;==================================================
        
	QueryValue(sql) {
		global Collection, Row, Table
		
		result := MySQL_Query(this._handleDB, sql)
		
		if (result != 0) {
			errCode := this.ErrCode()
			if(errCode == 2003 || errCode == 2006 || errCode == 0){ ;// we probably lost the connection
				;// try reconnect
				this.Connect()
				result := MySQL_Query(this._handleDB, sql)
				if (result != 0)
					return false ; we failed again. bye bye
			} else {
				HandleMySQLError(this._handleDB, "dbQuery Fail", sql)
				return false ; unexpected error. bye bye
			}
		}

		requestResult := MySql_Store_Result(this._handleDB)

		if (!requestResult) ; the query was a non {SELECT, SHOW, DESCRIBE, EXPLAIN or CHECK TABLE} statement which doesn't yield any resultset
			return
		
        ;----------------------------
        ; get single row/column value
        ;----------------------------        
        
		rowptr := 0
		rowptr := MySQL_fetch_row(requestResult)

		lengths := MySQL_fetch_lengths(requestResult)
		length := GetUIntAtAddress(lengths, 0)
		fieldPointer := GetUIntAtAddress(rowptr, 0)
		fieldValue := StrGet(fieldPointer, length, "CP0")
            
		MySQL_free_result(requestResult)
		
        return fieldValue
	}    

    ;==================================================
    ; execute an sql query that returns a row of column data (1 dimensional array)
    ;==================================================	
	
	QueryRow(sql) {
		global Collection, Row, Table
		
		result := MySQL_Query(this._handleDB, sql)
		
		if (result != 0) {
			errCode := this.ErrCode()
			if(errCode == 2003 || errCode == 2006 || errCode == 0){ ;// we probably lost the connection
				;// try reconnect
				this.Connect()
				result := MySQL_Query(this._handleDB, sql)
				if (result != 0)
					return false ; we failed again. bye bye
			} else {
				HandleMySQLError(this._handleDB, "dbQuery Fail", sql)
				return false ; unexpected error. bye bye
			}
		}

		requestResult := MySql_Store_Result(this._handleDB)

		if (!requestResult) ; the query was a non {SELECT, SHOW, DESCRIBE, EXPLAIN or CHECK TABLE} statement which doesn't yield any resultset
			return
		
        ;----------------------------
        ; get column names
        ;----------------------------
        
		mysqlFields := MySQL_fetch_fields(requestResult)
		colNames := new Collection()
		columnCount := 0
		for each, mysqlField in mysqlFields
		{
			colNames.Add(mysqlField.Name())
			columnCount++
		}

        ;----------------------------
        ; get rows
        ;----------------------------        
        
		rowptr := 0
        rowptr := MySQL_fetch_row(requestResult)

        rowIndex := A_Index
		datafields := new Collection()
			
        lengths := MySQL_fetch_lengths(requestResult)
        Loop, % columnCount
        {
            length := GetUIntAtAddress(lengths, A_Index - 1)
            fieldPointer := GetUIntAtAddress(rowptr, A_Index - 1)
            fieldValue := StrGet(fieldPointer, length, "CP0")
            datafields.Add(fieldValue)
        }
        data := new Row(colNames, datafields)
            
		MySQL_free_result(requestResult)
		
		return data
	}

[/code]

[IsNull]

@BenJarry: It actually should work with unicode chars, but I've to investigate further.

@panofish:
WhooHoo thats really a big fish swarm in my thread now :lol:

1. To get the first field of the first record, I'd assume the following Method:

queryFirstField(sQry){
  rs := this.OpenRecordSet(sQry)
  value := rs[1]
  rs.Close() ; its very important to close the Resultset!
  return value
}

Usage

name := db.queryFirstField("SELECT name FROM table1 WHERE id = 'alilly'")

I may add something similar in the future.

Why does it cause an error when I use variable name "table" like this?

"Table" is the class name of a very core class of this wrapper. If you asign it with another value, you break the script indeed. I may prefix my classes so avoid collisions with common english names; Table then would become DBATable etc.

An SQLite call which has a syntax error does not return a friendly error message like the corresponding MySQL call does.

Edit: You should handle Errors like:

   res := db.Query(SQL)
	if(!IsObject(res) && !res){
			msgbox % "Error: " db.GetLastErrorMsg()
	}

I may add Exception-Handling to the code.