Running MySQL Queries from AHK

[arathra]

Can AHK connect to MySQL and run a couple of basic queries (SELECT and also UPDATE).

I looked around the forum and site and found a MySQL library thread but, to be honest, I didn't really understand how to implement it and join AHK with MySQL.

I can set up the DB fine and have a decent enough working knowledge of MySQL to write the queries and so on, but I can't quite work out how to access it from within AHK.

I'd appreciate it if anyone could offer a few pointers for a complete beginner at this!

Many thanks!

[ahkForWork]

Yes! it is possible. I use it in my apps daily.
You will need to get the LibMySql.dll. Not sure how to obtain it, someone found it for me and sent it to me.

Use the code below to connect and you're good to go!

Code: Select all

ConnectToSQL:
{
appTitle := % "MySQL Test"
hModule := DllCall("LoadLibrary", "Str", A_MyDocuments "\libmysql.dll")
if (hModule = 0)
{
	MsgBox 16, % appTitle, % "Can't load libmySQL.dll.`nIf you are trying to save, your data may be lost."
	return
}

mySQL := DllCall("libmySQL.dll\mysql_init", "UInt", 0)
if (mySQL = 0) {
	MsgBox 16, % appTitle, % "Not enough memory to connect to MySQL.`nIf you are trying to save, your data may be lost."
	return
}

connection := DllCall("libmySQL.dll\mysql_real_connect"
		, "UInt", mySQL
		, "Str", ""	; host name
		, "Str", ""	; user name
		, "Str", ""	; password
		, "Str", ""	; database name
		, "UInt", 3306	; port
		, "UInt", 0	; unix_socket
		, "UInt", 0)	; client_flag

if (connection = 0) {
	errorMsg = Cannot connect to database
	Goto HandleMySQLError
}
return
}


; TODO: use a less brutal error handling...
MySQL_ProcessQueryWithResults(_mySQL, _query)
{
	local resultString, result, requestResult, fieldCount
	local row, lengths, length, fieldPointer, field
	if (!InStr(_query,"insert")) {
		if (!InStr(_query,"where")) {
			MsgBox, 4144, % "Missing 'WHERE'.", % "Missing 'WHERE' clause in SQL statement!"
			return
		}
	}

	result := DllCall("libmySQL.dll\mysql_query", "UInt", _mySQL, "Str", _query)
	if (result != 0) {
		errorMsg = Error while running request:`n%request%`n
		Goto HandleMySQLError
	}
	requestResult := DllCall("libmySQL.dll\mysql_store_result", "UInt", mySQL)
	if (requestResult = 0) {
		errorMsg = Error while storing request result:`n%request%
		Goto HandleMySQLError
	}
	fieldCount := DllCall("libmySQL.dll\mysql_num_fields", "UInt", requestResult)
	;~ OutputDebug fc: %fieldCount%

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

		; Get a pointer on a table of lengths (unsigned long)
		lengths := DllCall("libmySQL.dll\mysql_fetch_lengths", "UInt", requestResult)
		;~ OutputDebug r: %row% / ls: %lengths%
		Loop % fieldCount
		{
			length := GetUIntAtAddress(lengths, A_Index - 1)
			fieldPointer := GetUIntAtAddress(row, A_Index - 1)
			;~ OutputDebug l: %length% / fp: %fieldPointer%
			VarSetCapacity(field, length)
			DllCall("lstrcpy", "Str", field, "UInt", fieldPointer)
			resultString := resultString . field
			if (A_Index < fieldCount)
				resultString := resultString . "|"
		}
		resultString := resultString . "`n"
	}
	return resultString
}


HandleMySQLError:
{
	errorCode := DllCall("libmySQL.dll\mysql_errno", "UInt", mySQL)
	errorStr := DllCall("libmySQL.dll\mysql_error", "UInt", mySQL, "Str")
	MsgBox 17, % appTitle, % errorMsg "(" errorCode "):`n"errorStr "`nPress Ok reload.`nPress Cancel to Exit.`n`nIf you are trying to save, your data may be lost."
	IfMsgBox Ok
		Reload
	IfMsgBox Cancel
		ExitApp
}

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

[Jovannb]

Hi,

I'm using ADOSQL.AHK (https://autohotkey.com/boards/viewtopic.php?t=74) instead of using a DLL-call, that works well for me with an access-database.
As you can see there it is prepared to work with MySql.

Have a look at https://autohotkey.com/boards/viewtopic.php?f=5&t=23948, to see how to call it

regards

J.B.

[arathra]

I've spent the last couple of hours with the dll system.

I can access my MySQL database remotely using HeidiSQL so I know that access is possible and that the passwords and username is all correct. However, when I use exactly the same info in connect_mysql.ahk I get this error message.

Cannot connect to database (2005)

Followed by what appears to be Chinese writing and the rest of the error message.

Before I could run connect_mysql.ahk I had to make a few changes to avoid warning messages. This basically meant putting this at the beginning.

Code: Select all

; set some global variables
global errorMsg := ""
global MySQL := ""
global appTitle := ""

I'll keep plugging away, but I'm not sure where to go next on this.