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
Check if connection to MySQL works?
Started by
Kerry
, Sep 08 2006 07:59 PM
21 replies to this topic
Try to connect. If not possible you'll get a response ...
#2
-
Posted 09 September 2006 - 07:49 AM
I know - would I do that by using one of the other languages? or how do i go about tryin to connect?
#3
-
Posted 09 September 2006 - 08:33 AM
I know, but how do I get AHK to execute MySQL?
#5
-
Posted 09 September 2006 - 08:02 PM
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.
#6
-
Posted 09 September 2006 - 08:56 PM
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.
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.
#7
-
Posted 09 September 2006 - 09:48 PM
Thank you! that's what I was looking for
#8
-
Posted 10 September 2006 - 02:38 AM
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:
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.
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.
#9
-
Posted 10 September 2006 - 10:58 PM
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
-Kerry
#10
-
Posted 11 September 2006 - 03:56 AM
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.
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.
#11
-
Posted 11 September 2006 - 04:20 AM
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).
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.
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 }
#12
-
Posted 11 September 2006 - 12:53 PM
vPhiLho := RegExReplace("Philippe Lhoste", "^(\w{3})\w*\s+\b(\w{3})\w*$", "$1$2")
PhilHo,
Your code works like a charm! Good job!! I am using a remote mysql db running mysql 4.1.13a-nt.
Your code works like a charm! Good job!! I am using a remote mysql db running mysql 4.1.13a-nt.
#13
-
Posted 11 September 2006 - 01:58 PM
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
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
#14
-
Posted 11 September 2006 - 06:30 PM
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?
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?
#15
-
Posted 12 September 2006 - 09:33 AM
vPhiLho := RegExReplace("Philippe Lhoste", "^(\w{3})\w*\s+\b(\w{3})\w*$", "$1$2")