Jump to content
ProgAndy

MySQL UDFs (without ODBC)

Recommended Posts

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 ---------

Share this post


Link to post
Share on other sites

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 by ProgAndy

*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Share this post


Link to post
Share on other sites

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 by rolok

Share this post


Link to post
Share on other sites

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.


*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Share this post


Link to post
Share on other sites

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 by Erik.

I little problem, hard to find and fix

Share this post


Link to post
Share on other sites

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 by ProgAndy

*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Share this post


Link to post
Share on other sites

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


I little problem, hard to find and fix

Share this post


Link to post
Share on other sites

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


*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Share this post


Link to post
Share on other sites

Okey, thanks.

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


I little problem, hard to find and fix

Share this post


Link to post
Share on other sites

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


*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Share this post


Link to post
Share on other sites

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


I little problem, hard to find and fix

Share this post


Link to post
Share on other sites

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


AUTOIT[sup] I'm lovin' it![/sup]

Share this post


Link to post
Share on other sites

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.


*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Share this post


Link to post
Share on other sites

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 by slayerz

AUTOIT[sup] I'm lovin' it![/sup]

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

Hi,

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

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.


*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Share this post


Link to post
Share on other sites

@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


AUTOIT[sup] I'm lovin' it![/sup]

Share this post


Link to post
Share on other sites

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 by ProgAndy

*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Share this post


Link to post
Share on other sites

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 by slayerz

AUTOIT[sup] I'm lovin' it![/sup]

Share this post


Link to post
Share on other sites

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.


*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Colduction
      Hi AutoIt Scripters/Programmers. I have a question about MIME Tools for Notepad++:
      I've recently found a UDF about Base64 in forum, but they can't decode\encode correctly some emojis , other UTFs and etc. so i decided to use mimeTools.dll of Notepad++ or main site
      My problem is how to use this dll in AutoIt Language?

      I will be happy with your comments and answers❤ Thanks.
    • By FrancescoDiMuro
      Good morning Forums 
      In these days, I am working on a project that involved me to use some Windows APIs to obtain some information about Terminal Servers.
      I'm doing this using wtsapi32.dll in a VBA Project, but, the lack of knowledge about few things threated in the articles make this quite difficult to
      implement and understand at the same time.
      The most difficult thing I'm facing is "translating" C/C++ functions or struct in VBA when pointers are used, or pointers of pointers, and so on.
      Since VBA seems to not have a pointer type, to make those functions work I need to implement other functions taken from other DLLs, and this confuses me a lot.
      For example, starting from this code, I splitted all the functions and all the definitions to understand why they are there, and why I need to use them.
      At the end, I've found out that the code I was going to implement starting from the functions provided in the Microsoft Docs won't be ever be able to work without some supplementary functions which are not mentioned anywhere.
      So, I was wondering if someone would please point me out to a good and practical exaplanation about pointers (in general) or specifically for VBA, because I need to use them quite often in these days, and I'd like to understand what I am doing.
      Thanks in advance.

      Best Regards and Stay at home 🏡
       
    • By zuladabef
      All my scripts were working fine and now I am getting this error.  How do I resolve it?  Which folder(s) do the DLLs need to be in?
      ! Dll not found or Call Dll error !  
    • By argumentum
      $sQueryUpdateTime = "select intUpdateTime from tblStudies " . $where . " ORDER BY intUpdateTime DESC limit 1"; $rs = mysqli_query($conn, $sQueryUpdateTime); $row = mysqli_fetch_assoc($rs); the above used to take 300+ ms. to query. Then I set it as index and takes 30 ms. Cool.
      $sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC limit $offset,$rows"; // takes 30 ms. on the indexed int $sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC , PatientName ASC limit $offset,$rows"; // takes 300 ms. due to "PatientName" been a text field, even as I did index it So my observation is that "PatientName" takes a long time to sort, even tho "$rows = 20". Sorting text in 20 rows should be fast.
      ..tho, I find that any 2nd argument in the ORDER BY is just slow.
      Is there a way to query this in a way to have a faster result back ?
      Thanks
      PS: added ADD INDEX `StudyDate_2` (`StudyDate`, `PatientBirthDate`) USING BTREE;  and searched by those two with not much speed change ( StudyDate and PatientBirthDate are integer ).
    • By argumentum
      I was thinking but I don't have the experience, so you may have the experience.
      I was thinking to chop a DB with 100.000 ( 20 columns ) in 10.000 DB chunks ( 10 DBs ) and query concurrently all of them to speed up a search, then add the results. Is that a sound idea ? Or will I run in trouble down the road. Should a DB better be keep in one piece ?
      The DB is now in MySQL. I wanna do all this chopping and use SQLite. 
      Thanks
      [solved]
×
×
  • Create New...