Jump to content

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

MySQL database code conversion for unicode autohotkey_L


  • Please log in to reply
15 replies to this topic
panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
If you work with mysql like me, then you'll appreciate the simplicity of this include which allows you to easily connect and query to your mysql database.

Unfortunately, it is not compatible with autohotkey_L unicode.
It currently only works with autohotkey basic and autohotkey_L (ansi).

Can anyone help change this code so that it will be compatible with autohotkey_L (unicode)?
I am sure many people can use it and it will help encourage the adoption of autohotkey_L (unicode).

Here is an example of how simple it is to connect to a mysql database and execute a query using this include.

#Include mysql.ahk 

; Connect to the sql database at the top of the program
db := dbConnect("hostname","userid","password","databasename")     

sql = 
(
	SELECT request,
		    user,
		    priority
	  FROM table1 
	  WHERE Status = 'Pending' 
	  ORDER BY requestdate DESC
)

result := dbQuery(db, sql)

This is the mysql.ahk include file:

;============================================================
; mysql.ahk
;
;   Provides a set of functions to connect and query a mysql database
;============================================================

;============================================================
; The fileinstall command does 2 things.
; 1. When this ahk program is compiled into an exe, fileinstall indicates which files should be embedded inside the exe.
; 2. When the exe version of the program is run, fileinstall extracts the embedded file to the specified folder.
; 
; note: #include files are automatically embedded at compile time, so you don't need to use fileinstall for them.
;============================================================ 

FileInstall, libmysql.dll, %A_AppData%\libmysql.dll, 1

;============================================================
; Connect to mysql database and return db handle
;
; host = DTWRO-WS0061    
; user = alan
; password = reddog
; database = rush
;============================================================ 

dbConnect(host,user,password,database){	

	if (A_IsCompiled) {
		ExternDir := A_AppData
	} else {
		ExternDir := A_WorkingDir
	}

	hModule := DllCall("LoadLibrary", "Str", ExternDir "\libmySQL.dll")
		
	If (hModule = 0)
	{
		MsgBox 16, MySQL Error 233, Can't load libmySQL.dll from directory %ExternDir%
		ExitApp
	}

	db := DllCall("libmySQL.dll\mysql_init", "UInt", 0)
			
	If (db = 0)
	{
		MsgBox 16, MySQL Error 445, Not enough memory to connect to MySQL
		ExitApp
	}

	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)
	{
      HandleMySQLError(db, "Cannot connect to database")
		Return
	}

	serverVersion := DllCall("libmySQL.dll\mysql_get_server_info", "UInt", db, "Str")
			
	;MsgBox % "Ping database: " . DllCall("libmySQL.dll\mysql_ping", "UInt", db) . "`nServer version: " . serverVersion

	return db

}

;============================================================
; mysql error handling
;============================================================ 

HandleMySQLError(db, message, query="") {        ; the equal sign means optional
   errorCode := DllCall("libmySQL.dll\mysql_errno", "UInt", db)
   errorStr := DllCall("libmySQL.dll\mysql_error", "UInt", db, "Str")
   MsgBox 16, MySQL Error: %message%, Error %errorCode%: %errorStr%`n`n%query%
	Return
}

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

GetUIntAtAddress(_addr, _offset)
{
   local addr

   addr := _addr + _offset * 4

   Return *addr + (*(addr + 1) << 8) +  (*(addr + 2) << 16) + (*(addr + 3) << 24)
}

;============================================================
; process query
;============================================================ 

dbQuery(_db, _query)
{
   local resultString, result, requestResult, fieldCount
   local row, lengths, length, fieldPointer, field

	query4error := RegExReplace(_query , "\t", "   ")    ; convert tabs to spaces so error message formatting is legible
   result := DllCall("libmySQL.dll\mysql_query", "UInt", _db , "Str", _query)
			
   If (result != 0) {
      errorMsg = %_query%
		HandleMySQLError(_db, "dbQuery Fail", query4error)
		Return
   }
	
   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 := GetUIntAtAddress(lengths, A_Index - 1)
         fieldPointer := 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
}


OceanMachine
  • Members
  • 790 posts
  • Last active: Aug 23 2013 02:10 PM
  • Joined: 15 Oct 2007
Read the bit about DllCall compatibility issues between ANSI and Unicode versions here.

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

Read the bit about DllCall compatibility issues between ANSI and Unicode versions here.


Thanks... I did, but it's a bit over my head at this time. I'm not sure if I understand enough to convert the code as needed.

  • Guests
  • Last active:
  • Joined: --
Replacing "Str" with "AStr" in your DllCalls will get you running with autohotkey_L (unicode).

Nice script btw. :wink:

Tyrsius
  • Members
  • 140 posts
  • Last active: Jun 20 2011 10:08 PM
  • Joined: 09 Jul 2009

Replacing "Str" with "AStr" in your DllCalls will get you running with autohotkey_L (unicode).

Nice script btw. :wink:


I tried this, and it causes the libary to fail to load. Using "Str" allows it to load, but then it crashes.

Has anyone been able to get this working in AHK_L (unicode)?

Tyrsius
  • Members
  • 140 posts
  • Last active: Jun 20 2011 10:08 PM
  • Joined: 09 Jul 2009
Nvm, I got a working sql connection with AHK_L's Com scriptControl

stansult
  • Members
  • 19 posts
  • Last active: Apr 04 2015 05:36 AM
  • Joined: 08 Aug 2009
hi!
thanks for the mysql.ahk :)
i'm trying to use it, and get issue:
right after i use
dbConnect(host,user,password,database)
with all correct values, it says:

MySQL Error: Cannot connect to database
Error 1045: Access denied for user 'user'@'host' (using password: YES)

The thing is it shows correct 'user' and 'host', but each in single quotes.
could it be the reason it fails? the quotes?

stansult
  • Members
  • 19 posts
  • Last active: Apr 04 2015 05:36 AM
  • Joined: 08 Aug 2009
Hi guys!
Any updates on library’s compatibility with autohotkey_L?
I’m trying to use it, but unfortunately it crashes (dbConnect works, but dbQuery crashes the autohotkey_L).
I tried to put some breakpoints, and found that (within dbQuery) first call (libmySQL.dll\mysql_query) doesn’t break anything, but then it crashes at HandleMySQLError(_db, "dbQuery Fail", query4error). I thought maybe it could help you to debug the issue…
Anyway, thanks for nice library, I was using it with classic autohotkey a lot!

panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
The latest mysql libary is available here:

http://www.autohotke...topic77860.html

Unfortunately, it stills needs to be converted to Unicode. :(

capbat
  • Members
  • 191 posts
  • Last active: Feb 08 2017 06:57 PM
  • Joined: 29 Nov 2007
Hi

In your first example after the connection to the DB
The commands between the brackets are just SQL commands?
And are you the author?

Tks
Bat

panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
yes and yes :)

Check out the latest post for this mysql connection library...
http://www.autohotke...pic.php?t=77860

capbat
  • Members
  • 191 posts
  • Last active: Feb 08 2017 06:57 PM
  • Joined: 29 Nov 2007
Thanks Mr. Fish
I was just reading that when you answered. :)
I am using AutoHotkeyA32 Confim this will be compatible.?

And if so I will surely have some more questions.

Tks

Mr. Bat :wink:

panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
should work fine... only autohotkey_L unicode is a problem, but it does also work with autohotkey_L ansi.
-------------------------------
correction... it contains some oop code, so it may only work with autohotkey_L.
But that shouldn't be a problem since autohotkey_L is backward compatible with standard autohotkey.
I recommend you install autohotkey_L ANSI (not unicode) and that will work with all you old code as well. You may have to make a few minor tweaks, but they are not common and are pretty easy.

Before I upgraded to autohotkey_L, I was concerned that all my previous code would break with autohotkey_L, but I actually had little or no problems. I highly recommend you use autohotkey_L going forward because of the added functionality.

capbat
  • Members
  • 191 posts
  • Last active: Feb 08 2017 06:57 PM
  • Joined: 29 Nov 2007
Tks

Yes I have AHK_L Ansi Latest version So it should be ok Yes?

Bat

panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
Absolutely... I have used this mysql library extensively at work for a couple years and it works fantastically.
My mysql library function is simpler and easier to use than the full oop mysql library and more thoroughly tested.

The newer oop mysql library may eventually be the better choice, but I have found it has some issues to work out before I can use it. One of its advantages is the ability to use sqllite.