MySQL UDFs (without ODBC)

[ProgAndy]

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.

[Manic]

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

[ProgAndy]

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 June 30, 2011 by ProgAndy

[Manic]

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

[ProgAndy]

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

[Ghost21]

Is there a way to use your DLL for the connection and use my code ?

[willichan]

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 July 19, 2011 by willichan

[dixonpete]

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?

[ProgAndy]

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

[dixonpete]

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

[dixonpete]

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 October 5, 2011 by dixonpete

[ProgAndy]

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.

[dixonpete]

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 October 5, 2011 by dixonpete

[ProgAndy]

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

[dixonpete]

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

[hamster]

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.

[ProgAndy]

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.

[shx]

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

[shx]

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

[ProgAndy]

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