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
Kerry
  • Members
  • 144 posts
  • Last active: Sep 25 2006 07:33 PM
  • Joined: 20 Jul 2006
Is there a way to do this? Would I have to use one of the scripts to implement VBScript or PERL to do it? I'm thinking of making a program that makes a really easy setup for databases, and I want to be able to test if the connection worked.

-Kerry

BoBo
  • Guests
  • Last active:
  • Joined: --
Try to connect. If not possible you'll get a response ...

Kerry
  • Members
  • 144 posts
  • Last active: Sep 25 2006 07:33 PM
  • Joined: 20 Jul 2006
I know - would I do that by using one of the other languages? or how do i go about tryin to connect?

BoBo
  • Guests
  • Last active:
  • Joined: --
MySQL :roll:

Kerry
  • Members
  • 144 posts
  • Last active: Sep 25 2006 07:33 PM
  • Joined: 20 Jul 2006
I know, but how do I get AHK to execute MySQL?

BoBo
  • Guests
  • Last active:
  • Joined: --
You wanna connect to a SQL DB but you're not able to identify the AHK command to run such a query? Well, ... that'll be a long journey. Good luck. :)

Nerd610.10
  • Guests
  • Last active:
  • Joined: --
Just use the mysql.exe command line tool that comes with Mysql. Like this:

mysql.exe -s -N -h hostname -P 3306 -D databasename -u username -ppassword

For help:
mysql.exe -?

You could write a small sql script and pass it to mysql.exe to test success or not.

Kerry
  • Members
  • 144 posts
  • Last active: Sep 25 2006 07:33 PM
  • Joined: 20 Jul 2006
Thank you! that's what I was looking for

Nerd610.10.1
  • Guests
  • Last active:
  • Joined: --
Ideally, you'd want to use libmySQL.dll with DllCall in ahk, but I could never get it to work properly. I could connect, but could not get back queries that made sense. If you want to give it a shot, here is some code to get you started:

hModule := DllCall("LoadLibrary", "str", "libmySQL.dll","UInt") 
if !hModule 
{ 
   MsgBox Can't load DLL. 
   Exit 
}

conn:=DllCall("libmySQL.dll\mysql_init",str,NULL,"UInt64 *") 
myconn:=DllCall("libmySQL.dll\mysql_real_connect","UInt",&conn,"str","hostname","str","username","str","password"
,"str","databasename","UInt",3306,"UInt",NULL,"UInt",0,UInt64)
myquery:=DllCall("libmySQL.dll\mysql_get_server_info","UInt",&conn,"Cdecl Str") 
myerr:=DllCall("libmySQL.dll\mysql_errno","UInt",&conn)
msgbox, myerr %myerr%
mysql:="show tables"
VarSetCapacity(myquery,1)
myquery:=DllCall("libmySQL.dll\mysql_query","UInt",&conn,"str",mysql)

Note that if you are going to be using a remote db and doing a lot of queries, updates, etc, connecting and re-connecting to the db slows things down quite a bit. I ended up using perl. I wrote a perl module that would run like a daemon after first connecting to the db. It would then jjust pass mysql statements from my ahk program to the already opened db.

Kerry
  • Members
  • 144 posts
  • Last active: Sep 25 2006 07:33 PM
  • Joined: 20 Jul 2006
Thanks, you could you tell that something was connected? I'm actuall not going to be doing anything other than checking the connection. The program I am writing will be designing part of a website that does database things, but it's not doing the queries, it's making a website that willl. One of the files is "dbconnection.php" which contains the database data, and I want them to be able to check to make sure it's the right connection data

-Kerry

Nerd610.10.2
  • Guests
  • Last active:
  • Joined: --
See - http://dev.mysql.com... ... nnect.html

Mysql_real_connect() Return Values
A MYSQL* connection handle if the connection was successful, NULL if the connection was unsuccessful. For a successful connection, the return value is the same as the value of the first parameter.

PhiLho
  • Moderators
  • 6850 posts
  • Last active: Jan 02 2012 10:09 PM
  • Joined: 27 Dec 2005
Nerd610.10.2, thank you for the code snippet, it gets me starting, while I would have been too lazy to do the whole research myself...

I tried to improve it (I think you got lost with pointer handling), but I keep getting error 1043: Bad handshake. It seems it has something to do with hashing of passwords, different when going to versions 4.1.0 and up.

Well, I give my code snippet, you might try to run it on your system or see if something is wrong (it can be my install of MySQL, done by EasyPHP).
appTitle = MySQL Test
hModule := DllCall("LoadLibrary"
	, "Str", "C:\Program Files\EasyPHP\php\libmySQL.dll")
[color=red]; Snip! See below...[/color]

Kerry, you might be interested by mysql_ping() function too.

Oh, just before sending this message, I checked a VB wrapper for MySQL. It has the good idea to include a libmySQL.dll which works for me!
Excellent, plus I get a list of values for constants.
It is strange anyway, as PHP works on my localhost... Oh well, let's go on.
appTitle = MySQL Test
hModule := DllCall("LoadLibrary"
	, "Str", ".\libmySQL.dll")
If (hModule = 0)
{
	MsgBox 16, %appTitle%, Can't load libmySQL.dll
	ExitApp
}

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

connection := DllCall("libmySQL.dll\mysql_real_connect"
		, "UInt", mySQL
		, "Str", "localhost"	; host name
		, "Str", "upl"	; user name
		, "Str", "uplp"	; password
		, "Str", "s9y"	; database name
		, "UInt", 3306	; port
		, "UInt", 0	; unix_socket
		, "UInt", 0)	; client_flag
If (connection = 0)
{
	errorMsg = Cannot connect to database
	Goto HandleMySQLError
}
serverVersion := DllCall("libmySQL.dll\mysql_get_server_info"
		, "UInt", mySQL
		, "Str")
MsgBox % "Ping database: " . DllCall("libmySQL.dll\mysql_ping"
		, "UInt", mySQL) . "`nServer version: " . serverVersion

resultString := MySQL_ProcessQueryWithResults(mySQL, "SHOW TABLES")
MsgBox Tables in chosen database:`n%resultString%

resultString := MySQL_ProcessQueryWithResults(mySQL, "SELECT * FROM s9y_config LIMIT 0, 30")
MsgBox Config:`n%resultString%

Return

HandleMySQLError:
	errorCode := DllCall("libmySQL.dll\mysql_errno"
			, "UInt", mySQL)
	errorStr := DllCall("libmySQL.dll\mysql_error"
			, "UInt", mySQL
			, "Str")
	MsgBox 16, %appTitle%, %errorMsg% (%errorCode%):`n%errorStr%
ExitApp

GetUIntAtAddress(_addr, _offset)
{
	local addr

	addr := _addr + _offset * 4

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

; TODO: use a less brutal error handling...
MySQL_ProcessQueryWithResults(_mySQL, _query)
{
	local resultString, result, requestResult, fieldCount
	local row, lengths, length, fieldPointer, field

	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
}

Posted Image vPhiLho := RegExReplace("Philippe Lhoste", "^(\w{3})\w*\s+\b(\w{3})\w*$", "$1$2")

Nerd610.10.3
  • Guests
  • Last active:
  • Joined: --
PhilHo,

Your code works like a charm! Good job!! I am using a remote mysql db running mysql 4.1.13a-nt.

Kerry
  • Members
  • 144 posts
  • Last active: Sep 25 2006 07:33 PM
  • Joined: 20 Jul 2006
PhiLHo, you're amazing! I was thinking, up to you, but it would be awesome if you made it a function for a mysql query like

MySQLQuery("Select * from `users` where id = 50") or whatever, with a bunch of variables at the top which the user's have to setup for their connection.

-Kerry

PhiLho
  • Moderators
  • 6850 posts
  • Last active: Jan 02 2012 10:09 PM
  • Joined: 27 Dec 2005
Note that the back-tick ` specific to MySQL (quotes around field names with spaces or special chars) doesn't mix well with AutoHotkey syntax (you have to double them). You can drop them with simple field names.

I think you already have 90% (or more) of the needed code (I might have missed to close cleanly the connection, it is more a hack that a real wrapper).
Your function would be rather inefficient, having to make a connection on each call. But well, it is in AutoHotkey' spirit, like FileAppend, doing stuff inefficiently (but hardly noticeable at user level for most cases) but very simply.

I will think about doing this someday, so I can move some of this code to the Scripts & Functions section. Probably not soon...
One difficulty is: how to return the results? One big string, like I do in MySQL_ProcessQueryWithResults, which is a hack for display? Creating a global array (or several)? Having a secondary function to fetch the results sequencially?
Posted Image vPhiLho := RegExReplace("Philippe Lhoste", "^(\w{3})\w*\s+\b(\w{3})\w*$", "$1$2")