Jump to content
ProgAndy

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

Share this post


Link to post
Share on other sites

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

Share this post


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

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


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

Share this post


Link to post
Share on other sites

Thanks, just seemed like a much more active thread..

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:

Share this post


Link to post
Share on other sites

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

Share this post


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

Share this post


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

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


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

Share this post


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

Share this post


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

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

  • Similar Content

    • 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]
    • By RAMzor
      Hello all!
      I got the Acroname Programmable USB Hub based on BrainStem platform. I will use it to manage ports and read current.
      They have development kit (BRAINSTEM DEVELOPMENT KIT) with examples for any languages like C#, C++, LabVIEW and python but no AutoIt.
      The kit include BrainStem2.dll (x32 and x64), BrainStem2_LabVIEW.dll, BrainStem2CLI.dll and many examples.
      Anyone used this dll and implemented it in AutoIt?
      Please share some example for Device Discovering, User LED Flashing or power enable/disable on port to start play with this HUB
      BrainStem Reference 
       
      BrainStem2 lib+example.7z USB HUB User Manual s79-usbhub-3p_v1.10_0.pdf
    • By matwachich
      Hi guys!
      A pretty simple UDF to convert HTML to PDF using wkHTMLtoPDF.
      It uses the C API of the tool (DLL), so no external process, no ActiveX or COM sh*t.
      See the example, and the documentation of wkHTMLtoPDF.
      Cheers
      https://github.com/matwachich/wkhtmltopdf-au3
×
×
  • Create New...