Jump to content

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

Check if connection to MySQL works?


  • Please log in to reply
21 replies to this topic
Soulchen
  • Guests
  • Last active:
  • Joined: --
Hello.

After 2 hours of trying, I can give you 2 "common" error messages, along wil a solution, if PhiLO's code does not word.

Error 1 : "(1251) Client does not support Authentication Protocol"

Simply means that your libmysql.dll is too old.
Be careful : It may be that this references to some copy of the libmysql.dll,
as regarding to the path-variable and other circumstances.
(I found the one I used in the \windows\system32 - directory, from year 2006)

Solution : Update the libmysql.dll to a current version
(which is normally shipped with mysql)

Error 2 : The libmysql.dll can't be loaded
Explanation :

http://bugs.mysql.com/bug.php?id=28358

From Version 5.0.41 on, (until 5.0.43), if you take the binaries of the mysql installation, you can't load the libmysql.dll - file.

This seems to be fixed in Version 5.0.44,
and also in the newest version, 5.0.51a.

I can confirm that this is fixed in the most current version.

Solution : Update your mysql-server.

Regards, Soulchen aka Karlheinz Meier

ribbs2521
  • Members
  • 279 posts
  • Last active: Feb 23 2012 05:58 PM
  • Joined: 28 Sep 2007
OK, so I have this "wrapper" for MySQL which was created by PhiLho, thanks a lot, I use this all of the time. The problem is, whenever I return a result (specifically a column result) I have a trailing `n which gives me an empty field at the end.

Here is the original snippet
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"
   }

Here is what I tried to do (I only changed the last few rows)
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 . "|" 
      } 
        (If A_Index > 1)
          resultString := "`n" . resultString
        Else
          resultString := resultString
   }

I must be making a stupid mistake somewhere because in the results after my change I get about 100 carriage returns and then all of the results are smashed together on the last line.

Can anyone tell me why this is happening?

This also brought up another question which I was wonderinga bout. Is there a way to chop off the trailing carriage return without having to loop parse throught the whole variable?

lucgod1
  • Members
  • 5 posts
  • Last active: Jul 23 2010 07:16 PM
  • Joined: 27 Oct 2006
@PhiLo

I want to thank you for this SCRIPT which helps me a lot. That little thing is really very useful to easily make the link between AHK and MySQL.
-
Traductor : "http://www.frengly.com/"
Je souhaite vous remercier pour ce SCRIPT qui m' aide beaucoup. Cette petite chose est vraiment très utile pour faire facilement le lien entre AHK et MySql.
-
BYE from BELGIUM.
lucgod1 (LIEGE - BELGIUM)

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

DUDE! your awesome! THANKS!
I didn't realize how easy it would be to add mysql to my ahk scripts.
Your script works perfect!

This code should definitely get a nice wrapper and be included in AHK_L.

panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
There were some minor bugs in the error handling.
Here is my fix:

...

sql = 
(
	SELECT request,
			 user,
			 priority,
			 version,
			 requestdate,
			 frames,
			 fail,
			 status,
			 title
	  FROM jobtab 
	  ORDER BY requestdate DESC
)

result := MySQL_ProcessQueryWithResults(db, sql)


...

;============================================================
; Connect to mysql database
;============================================================ 

dbConnect:

	hModule := DllCall("LoadLibrary", "Str", ".\libmySQL.dll")
		
	If (hModule = 0)
	{
		MsgBox 16, %AHKTITLE%, Can't load libmySQL.dll
		ExitApp
	}

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

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

	If (connection = 0)
	{
		errorMsg = Cannot connect to database
      GoSub, HandleMySQLError
		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
;============================================================
; mysql error handling
;============================================================ 

HandleMySQLError:
   errorCode := DllCall("libmySQL.dll\mysql_errno", "UInt", db)
   errorStr := DllCall("libmySQL.dll\mysql_error", "UInt", db, "Str")
   MsgBox 16, %AHKTITLE%, Error %errorCode%: %errorStr%`n`n%errorMsg% 
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
;============================================================ 

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

   result := DllCall("libmySQL.dll\mysql_query", "UInt", _db , "Str", _query)
			
   If (result != 0) {
      errorMsg = %_query%
      GoSub, HandleMySQLError
		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
}



  • Guests
  • Last active:
  • Joined: --
For anyone looking at this wondering why it isn't working with AHK_L...
Replace STR with AStr to make it work.

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

  • Guests
  • Last active:
  • Joined: --
:( :? 8-)