MySQL UDFs (without ODBC)

[TSGarp]

thx for your answer,

but what i really wanted to know was, if it is the right workaround to close and init after a failed connection,

because i guess it is a bug, that the script crashes by trying to connect after a connection error,

see the example script test.au3 - "Fehler Demo"

MsgBox(0,"Fehler-Demo","Fehler-Demo")
$connected = _MySQL_Real_Connect($MysqlConn,"localhostdfdf","droot","","cdcol")
If $connected = 0 Then
    $errno = _MySQL_errno($MysqlConn)
    MsgBox(0,"Error:",$errno & @LF & _MySQL_error($MysqlConn))
    If $errno = $CR_UNKNOWN_HOST Then MsgBox(0,"Error:","$CR_UNKNOWN_HOST" & @LF & $CR_UNKNOWN_HOST)
Endif

; XAMPP cdcol
MsgBox(0, "XAMPP-Cdcol-demo", "XAMPP-Cdcol-demo")

$connected = _MySQL_Real_Connect($MysqlConn, "localhost", "user", "pass", "cdcol") <------------ CRASH ---------

[ProgAndy]

thx for your answer,

but what i really wanted to know was, if it is the right workaround to close and init after a failed connection,

because i guess it is a bug, that the script crashes by trying to connect after a connection error,

see the example script test.au3 - "Fehler Demo"

MsgBox(0,"Fehler-Demo","Fehler-Demo")
$connected = _MySQL_Real_Connect($MysqlConn,"localhostdfdf","droot","","cdcol")
If $connected = 0 Then
    $errno = _MySQL_errno($MysqlConn)
    MsgBox(0,"Error:",$errno & @LF & _MySQL_error($MysqlConn))
    If $errno = $CR_UNKNOWN_HOST Then MsgBox(0,"Error:","$CR_UNKNOWN_HOST" & @LF & $CR_UNKNOWN_HOST)
Endif

; XAMPP cdcol
MsgBox(0, "XAMPP-Cdcol-demo", "XAMPP-Cdcol-demo")

$connected = _MySQL_Real_Connect($MysqlConn, "localhost", "user", "pass", "cdcol") <------------ CRASH ---------

Strange. It is working for me. But your workaround seems to be OK.

Which version of the DLL do you use? The one provided with the most recent download?

Edited January 26, 2010 by ProgAndy

[rolok]

I'm really sorry. It's working for me with Firefox and IE7+8 so i can not reproduce your problem. Anyways, i uploaded it to radpidshare for you. (@all: please do not download it unless you have problems with the original download, there are only 10 available downloads on rapidshare)

I also had trouble. I downloaded the .zip file from the first post, but when trying to unzip it with version 10 of winzip it failed. I updated to version 14 of winzip and it still failed in the same way. It was having issue with the libmysql.dll extraction. Things went fine when I downloaded from the rapidshare link. I used Firefox 3.5.8

Here's the error from winzip:

Extracting libmysql.dll

Error: Invalid compressed data to expand (inflate) the file

Error in file #7: bad Zip file offset (Error local header signature not found): disk #1 offset: 845188

Extracting MySQL.au3

Thanks so much for your contribution. I'm not sure if/when I'll use the MySQL UDF, but I sure want to have it in my pocket in case I need it!

Edited March 3, 2010 by rolok

[ProgAndy]

Sorry, this was a transmission error. Somehow the file was not transferred correctly when I moved to a new server. Just download it again, it should work now.

[Erik.]

Hi,

I am trying to run the example file. When running i get an error from line 16: If @error Then Exit MsgBox(0, '', "")

Do i need to install some drivers? Hope you can help me getting it working.

Some debug information.

Editted line 18 to: If @error Then Exit MsgBox(0, '', @error) got error 2

File mysql.au3 line: 266. Got an return from there. No idea what my problem is.

Got an error -1 at dllopen, so it can not open the dll file. why? Everything is in the same folder.

Edited April 4, 2010 by Erik.

[ProgAndy]

The DLL must be in the same folder as your main script or you have to specify the correct path in _MySQL_InitLibrary(). Also you have to run your script in 32bit (x86) mode since the DLL is not designed for 64bits.

Edited April 5, 2010 by ProgAndy

[Erik.]

Ah okey, 32 bit. How to run it in 32 bit? Think that is the problem.

[ProgAndy]

Open the context-menu for the au3-file and choose Run (x86) or something like that.

[Erik.]

Okey, thanks.

How to change the settings in the editor to 32 bit? now it opens in 64 bit.

[ProgAndy]

I think you have to use the Fulll SciTe4AutoIt and add the line #AutoIt3Wrapper_usex64=y to your script.

[Erik.]

I found another solution, i made a search in regedit and changed a few things.

[slayerz]

First of all, thanks so much for your UDF, really like it.

At first, I'm a bit lost on how to use it but after a few tries, I got it.

I got some question, is there an option to keep the connection alive?

...because after sometimes, the connection will hang.

...or is there an option to auto-reconnect after timeout?

Regards,

slayerz

[ProgAndy]

Hi, it is possible to let the library reconnect when te connection timed out and you send a new query. To do that, you have to change the _MySQL_Real_connect to the following:

_MySQL_Options($hMySQL, $MYSQL_OPT_RECONNECT, 1, 'int*')
_MySQL_Real_Connect($hMySQL, ...)
_MySQL_Options($hMySQL, $MYSQL_OPT_RECONNECT, 1, 'int*')

More options can be found here.

[slayerz]

Thanks ProgAndy, this is much more elegant than sending dummy command every 10 minutes to keep the connection alive.

Some more question :

mysql_real_connect() incorrectly reset the MYSQL_OPT_RECONNECT option to its default value before MySQL 5.1.6. Therefore, prior to that version, if you want reconnect to be enabled for each connection, you must call mysql_options() with the MYSQL_OPT_RECONNECT option after each call to mysql_real_connect(). This is not necessary as of 5.1.6: Call mysql_options() only before mysql_real_connect() as usual.

If I call _MySQL_Options($hMySQL, $MYSQL_OPT_RECONNECT, 1, 'int*') after _MySQL_Real_Connect($hMySQL,...), is it will give the same result as your code?

Sorry, little confuse...

EDIT : It's OK ProgAndy, I got it now, hehe...your solution will be compatible for any MySQL version

Edited April 19, 2010 by slayerz

[Sn3akyP3t3]

I have a few questions to ask about libmysql.dll versions and compatibilities. There must be a difference between versions or there wouldn't be a need for multiple files and associated documentation.

Here are my questions then:

1.) What version of libmysql.dll is bundled with your provided download?

2.) What versions of libmysql.dll are compatible with the included "MYSQL.au3"?

Thanks for your awesome work and allowing me to use AutoIt to communicate with projects using MySQL!

[ProgAndy]

Hi,

I bundled libmysql 5.0.45 (32bit). This shoud be the first message when you execute test.au3

The UDF should work with all versions of libmysql that use the same API. At least, all 5.0 and 6.0-dlls should work, I think. I'm not sure about version 4 since I don't have the function headers for them, but I think, this version should work, too.

[slayerz]

@ProgAndy

I am trying to add function to show to user something like, "SERVER DOWN. SERVICE IS UNAVAILABLE AT THE MOMENT" if the MySQL Server is down. So the users cannot use the program until the server is up again.

Just need some advice from you, can I achieve the above said function using mysql_ping(MYSQL *mysql) ? btw, I set the option to auto reconnect.

Regards,

slayerz

[ProgAndy]

First, you should check the connection after _MySQL_Real_Connect. This function fails when the server is not available. Use _MySQL_Errno and _MYSQL_Error to get the error number and message.

_MySQL_Ping checks if the connection is still alive or a reconnect was successful. If this function returns NONZERO, the mysql-server has gone away. Use again errno & error to get the specific error message.

Edit: The error codes that can occur are listed here (section "Errors"):

http://dev.mysql.com/doc/refman/5.1/en/mysql-real-connect.html

http://dev.mysql.com/doc/refman/5.1/en/mysql-ping.html

Edited April 25, 2010 by ProgAndy

[slayerz]

Thanks for the reply...After some digging, mysql_ping will attempt to reconnect if the connection is lost, I'm not sure whether this is the right option.

Let say, every 2 minutes:

1 - check the status of the server

2 - if server down, display the message to user, try to reconnect

3 - do #1 to #2 until server is up, remove the message

I will try to work it out. BTW, what is the best practices that you would use for an application that will run indefinitely, but only occasionally needs to make database queries. Should I call mysql_init() and mysql_real_connect() each time of the query OR only once at the beginning of the script for mysql_init() and/or mysql_real_connect()

Regards,

slayerz

Edited April 25, 2010 by slayerz

[ProgAndy]

I think, you should connect when the DB is needed for the first time and include the reconnect option. Then the connection will be autmatically reestablished when a query is sent and it was closed.

It also depends on the interval. If you need MySQL only once an hour then create the connection only for the query and close it afterwards, but you shouldn't close and reopen the connection if you send the next query immediately after the first.