Jump to content

MySQL UDFs (without ODBC)


Recommended Posts

Sorry,

You have to shutdown your computer, wait for 30 seconds, hit it with an axe and then turn the power on...

Honestly, without the error messages / numbers I cannot help you guys.

I notice when looking in the include file that it attempts to check the client version. Am I to assume that this script must run on the same server as the MySQL database is running on? (i.e. it won't work across the network?)

It will work across the network if the server allows remote connections. The example only reads the version of the dll wihtout any further meaning.

PS: The dll has to be called libmysql.dll or libmysql_x64.dll (for x64). If you have another name, you have to specify it as a parameter.

*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

Link to post
Share on other sites
  • Replies 189
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

MySQL UDFs using libmysql.dll functions: most functions from MySQL API all are prefixed with an underscore: _MySql... e.g.: _MySQL_Real_Query( sometimes parameters are chaged - read function descripti

I don't get it. @mLipok has polished (pun intended) a nice ADO UDF which works flawlessly. Why insist on using another one which causes issues?

@ProgAndy I like anything that smells to SQL !! Unfortunately you link does not work (for me ?). Rgds ptrex

Posted Images

Sorry,

You have to shutdown your computer, wait for 30 seconds, hit it with an axe and then turn the power on...

Honestly, without the error messages / numbers I cannot help you guys.

It will work across the network if the server allows remote connections. The example only reads the version of the dll wihtout any further meaning.

PS: The dll has to be called libmysql.dll or libmysql_x64.dll (for x64). If you have another name, you have to specify it as a parameter.

Ok. So here's the deal:

I downloaded and unzipped all of the files you have listed on your first post into the includes folder. Then, I opened up the SciTE editor, and put the following code in:

#include <array.au3>
#include "mysql.au3"

; MYSQL starten, DLL im PATH (enthält auch @ScriptDir), sont Pfad zur DLL angeben. DLL muss libmysql.dll heißen.
_MySQL_InitLibrary()
If @error Then Exit MsgBox(0, '', "could nit init MySQL")
MsgBox(0, "DLL Version:",_MySQL_Get_Client_Version()&@CRLF& _MySQL_Get_Client_Info())

$MysqlConn = _MySQL_Init()

Now, it gives me this in a MsgBox:

50146

5.1.46

What am I doing wrong? The next thing I want to do is connect to a database that is hosted online, and start working with my existing data.

I appreciate the time and effort you have put into this entire project ProgAndy. I'm a big fan, and I just want to get to know this system better so I can use my database with AutoIt, just like your project intends. Let me know what I have to do (even putting an axe in my computer) and I will do it, as long as it makes this work! :)

Cheers!

Manic

Link to post
Share on other sites

There is nothing wrong. The DLL is loaded and a Messagebox with the dll-version appears. That is expected behaviour in this example. _MySQL_Init() initlializes a handle you will use to connect to the server.

By the way, EzMySQL should be easier to understand since the API is more or less hidden in simpler functions. My UDF exposes the C-API 1:1 (without prepared statements) and is therefore more powerful but also more difficult to understand. The example for EzMySql should be straight forward (open dll, open connection, query data, ...)

[You already found that UDF I suppose ;) ]

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

Link to post
Share on other sites

There is nothing wrong. The DLL is loaded and a Messagebox with the dll-version appears. That is expected behaviour in this example. _MySQL_Init() initlializes a handle you will use to connect to the server.

By the way, EzMySQL should be easier to understand since the API is more or less hidden in simpler functions. My UDF exposes the C-API 1:1 (without prepared statements) and is therefore more powerful but also more difficult to understand. The example for EzMySql should be straight forward (open dll, open connection, query data, ...)

[You already found that UDF I suppose ;) ]

Well I dabbled in EzMySQL, but there aren't too many functions (some which I absolutely require). Also, the example's seem to be ok, but I think my syntax is off... I hope you don't mind helping me solve this small problem. If so, just let me know and I'll do my best ;)

My code:

#include <array.au3>
#include "mysql.au3"

; MYSQL starten, DLL im PATH (enthält auch @ScriptDir), sont Pfad zur DLL angeben. DLL muss libmysql.dll heißen.
_MySQL_InitLibrary()
If @error Then Exit MsgBox(0, '', "could nit init MySQL")
MsgBox(0, "DLL Version:",_MySQL_Get_Client_Version()&@CRLF& _MySQL_Get_Client_Info())

$MysqlConn = _MySQL_Init()

;Fehler Demo:
MsgBox(0,"Error-demo","Error-Demo")
$connected = _MySQL_Real_Connect($MysqlConn,"http://myhost.com:2083","account","password","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

It returns:

Error-Demo (as it should I believe)

2005 - Unknown MySQL server host 'http://myhost.com:2083' (11004)

$CR_UNKNOWN_HOST

2005

Any idea on where I went wrong? Once I get past the basics, I should be ok.

I appreciate it :)

Manic

Link to post
Share on other sites

The description for the function and its parameters can be found here. The host must not contain http nor the port. There is an extra parameter for the port. The other functions are described there as well.

Also, you don't habe to copy everything from the example. For example, you are neither required to show a messagebox with the version nor the messagebox with error-demo...

*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

Link to post
Share on other sites
  • 2 weeks later...

I must be missing something simple, but I do not seem to be able to get a query to run without error.

The following test code always returns "_MySQL_Real_Query() failed with 1"

#include <MySQL.au3>
Global $MySQLServer="myserver"
Global $MySQLDatabase="mydatabase"
Global $MySQLTable="mytable"
Global $MySQLUser="myuser"
Global $MySQLPassword="mypassword"
Global $MysqlConn
Global $MysqlConnected

somefunc()

Func somefunc()
    Local $query, $res
    _MySQL_InitLibrary()
    If @error Then
        Exit MsgBox(0, "debug", "_MySQL_InitLibrary() failed")
    Else
        $SQLloaded =True
        $MysqlConn = _MySQL_Init()
        $MysqlConnected = _MySQL_Real_Connect($MysqlConn, $MySQLServer, $MySQLUser, $MySQLPassword, $MySQLDatabase)
        If $MysqlConnected = 0 Then Exit MsgBox(0, "debug", "_MySQL_Real_Connect() failed")
    EndIf

    $query = "REPLACE INTO " & $MySQLTable & " ( dataa, datab, datac ) VALUES ( 'valuea', 'valueb', 'valuec' )"
    $res = _MySQL_Real_Query($MysqlConn, $query)
    If $res <> 0 Then MsgBox(0,"debug", "_MySQL_Real_Query() failed with " & $res)
    _MySQL_EndLibrary()
EndFunc

Any ideas what I am missing? (hand poised and ready for forehead slap)

----- EDIT -----

No forehead slap, but scratching my head.

I came back in the office, and re-ran the code that had been failing all afternoon yesterday, and it works perfectly today, with no changes. Must have been some other app running interference, but at this point, I have no idea what it may have been.

Thanks again for a great UDF!

Edited by willichan
Link to post
Share on other sites
  • 1 month later...

I've got a large database I need to process that a couple of hundred tables and often up to a hundred plus fields per table. In short, lots of columns.

After _EzMySql_FetchNames obtains the column name I generate a custom Select into another table based on the existence of matching columns. Column names that happen to match the reserved words in MySQL are causing _EzMySql_Exec function to error out.

Here are some of the offending column names:

'INT'

'DESC'

'TABLE'

'RESTRICT'

'GROUP'

'ORDER'

'SPECIFIC'

'USE'

'DEFAULT'

'EXPLAIN'

'LIMIT'

I'm thinking about searching for and renaming the columns before doing processing and changing them back later, but is there an easier way?

Link to post
Share on other sites

You should always escape column names with backticks: `column`

Btw, why did you post here and not in the EzMySQL-thread? Not that it matters, though :graduated:

*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

Link to post
Share on other sites
  • 3 weeks later...

When I run MySQL queries the GUI title window gets a "NOT RESPONDING" message added to it. The query is actually running but apparently hits some kind of time limit where the GUI expects to be able to respond. Is there a way to make it more patient or turn that feature off entirely? Some of my queries are combining tables and the wait could be 15 min + and the warning kicks in way before then.

Edited by dixonpete
Link to post
Share on other sites

When the query needs a long time, you'll have to use a second thread to execute it. Since AutoIt doesn't support threads, this thread has to be created with a DLL, embedded C (TCC) or ASM.

*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

Link to post
Share on other sites

If I decide I can live with a Not Responding message ( after all for my app the queries do have to run sequentially anyway) will all be well in the end? By this I mean AutoIt won't stop or give up or anything right?

When the query needs a long time, you'll have to use a second thread to execute it. Since AutoIt doesn't support threads, this thread has to be created with a DLL, embedded C (TCC) or ASM.

Edited by dixonpete
Link to post
Share on other sites

AutoIt won't stop until the user decides to kill the task since Windows labels it with "Not responding". Maybe you should add a warning to your GUI before executing the query :graduated:

*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

Link to post
Share on other sites

I'm trying to display the output of a query to the user by placing the results in an Edit box. The following code actually works fine but the two For/Next loops that I'm using to format the data is awfully slow - over 3 minutes for 20,000 records. Running the query and updating the GUI element are pretty quick. Is there a more efficient way? I'm just trying to make the columns line up properly in the final output. I'm already formatting the Edit box as Courier.

....

$aOk = _EzMySql_GetTable2d("SELECT `DOCID`, `NAME`, `BILLS`, `PROVIDER`, `DOCTOR_ABBREVIATION`, `ADDRESS1`, `ADDRESS2`,`CITY` FROM CONCODE ORDER BY 3 DESC,2 ")

local $result = ""

local $i, $j

local $NumCols = ubound( $aOk) -1

local $NumRows = ubound( $aOk,2 ) -1

For $i = 0 To $NumCols Step 1

For $j = 0 To $NumRows Step 1

$result = $result & StringLeft( $aOk[$i][$j] & " ", 18)

Next

$result = $result & @CRLF

Next

; $result gets GUICtrlSetData into an EditBox

Link to post
Share on other sites
  • 5 months later...

Sorry for bringing up this topic, but I went through every single post and I still cannot find a single working download link.

It will be much appreciated if somebody (re)uploads this UDF somewhere.

Sorry forgot to update the domain when I changed bought a new one. It seems the old one has now expired.

*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

Link to post
Share on other sites

Hello,

I have multiple-statement execution turned on and what I'm trying to do is have all the SQL statements executed as one transaction.

So I turned off auto commit using _MySQL_AutoCommit() and it still doesn't execute as one transaction.

My first SQL statement is executed successfully then the next statement fails and the third statement also does not get executed I guess because the second statement failed. However was the first statement was successful return code that I receive is a zero.

My question is how can I have all the statements executed as one transaction and if one of them fails should get a return code of -1.

Thanks

Steven

Link to post
Share on other sites

Hi all,

I don't know if my question was clear but what I am asking is how can I have multiple sql statements execute as one transaction and if one fails all will be rolled back and that the return code returned to the executing program will reflect that there was an error?

Steven

Link to post
Share on other sites

MyISAM tables do not support transactions. Read the MySQL documentation for further details and workarounds: http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html

*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

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 DevMode
      welcome
      I need help dealing with a C++ DLL
      Specify that I need help with how to use this function
      struct libusb_device; typedef struct libusb_context libusb_context; ssize_t LIBUSB_CALL libusb_get_device_list(libusb_context *ctx,libusb_device ***list); My problem is how to deal with "libusb_device ***list"
      my code 
      $libusb_device = DllStructCreate("PTR") Func libusb_init() $Var = DllCall($libusb0, "ptr", "libusb_init",'ptr',$_libusb_device_handle_Ptr) If @error Then Return False Return $Var[1] EndFunc Func libusb_get_device_list() MsgBox(0,VarGetType($_libusb_device_handle_Ptr),$_libusb_device_handle_Ptr) $Var = DllCall($libusb0, "int", "libusb_get_device_list","ptr",$_libusb_device_handle_Ptr,"ptr*",POINTER(POINTER($libusb_device))) $Error = @error If $Error Then Return False MsgBox(0,VarGetType($Var),$Error&@CRLF&$Var) _ArrayDisplay($Var) Return $Var[1] EndFunc  
      Thank you
      iLibUSB.dll iLibUSB_32.dll libusb.h
    • By bobflumox
      Hi all,
      My programming knowledge is very basic.
      I have an old script that creates shares and assign permissions. It normally registers SetAcl.ocx if necessary and creates an object to assign permissions.
      The command that registers SetAcl was apparently working fine under Windows 7 but is not working under Windows 10.
      RunWait("regsvr32.exe path\to\setacl.ocx /s", "", @SW_HIDE) As I'm logged in as admin, I changed this command to :
      RunAsWait(@UserName, "", "", 0, "regsvr32.exe path\to\setacl.ocx /s", "", @SW_HIDE) It seems to terminate correctly but the script still doesn't work as expected.
      To check that, I've created that small script :
      Local $objSetAcl = ObjCreate("SETACL.SetACLCtrl.1") If IsObj($objSetAcl) Then ConsoleWrite("Object successfully created." & @CRLF) Else ConsoleWrite("Object not created. Registering SetAcl.ocx" & @CRLF) Local $result = RunAsWait(@UserName, "", "", 0, "regsvr32.exe path\to\setacl.ocx /s", "", @SW_HIDE); Use of my admin username to elevate CMD ConsoleWrite("Return code : " & $result & @CRLF) ConsoleWrite("Creating object" & @CRLF) $objSetAcl = ObjCreate("SETACL.SetACLCtrl.1") If IsObj($objSetAcl) Then ConsoleWrite("Object successfully created." & @CRLF) Else ConsoleWrite("Object creation failed." & @CRLF) EndIf EndIf It tries to register SetAcl.ocx, return code 0 seems to be fine but still can't use SetAcl.
      But if I go to CMD as admin, run the regsvr32 command and restart my script, it can create the object without issue.
      I know my poor knowledge makes me miss something. Anyone can help me figure this out ?
    • By Gowrisankar
      Dear members of the forum,
      I'm working on a project in which I have to use Image recognition technique. 
      Due to client restrictions, I couldn't use AutoIt for this project. 
      Is there a way to use this DLL "ImageSearchDLL.dll" (which is used to do image recognition steps in AutoIt) in VB.Net to achieve the same result? 
      I have used this DLL few years before and got good results. If there is a latest version of this DLL and if you can share it, that will be helpful too.
      Any guidance is deeply appreciated.
    • 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 🏡
       
×
×
  • Create New...