EzMySql UDF - Use MySql Databases with autoit

[jchd]

@mLipok is right as he implies that the code used to invoke MySQL insert statement has to cope with the right encoding, and that is what a good ADO UDF has to do. So use his proven working UDF and all will be fine.

[rony2006]

Func _EzMySql_AddTable2d($sTableName, $aData) NOT WORKING!!!

Bellow is a modification:

Func _EzMySql_AddTable2d($sTableName, $aData)
    Local $querystring, $iResult
    If Not $sTableName Then Return SetError(6, 0, 0)
    If Not IsArray($aData) Then Return SetError(7, 0, 0)
    If Not UBound($aData) > 1 Then Return SetError(8, 0, 0)
    If Not UBound($aData, 2) Then Return SetError(9, 0, 0)
    Local $iColumns = UBound($aData,2)-1
    For $iRow = 1 To UBound($aData)-1
         $querystring &= "INSERT INTO " & "`" &$sTableName &"`"& " ("
        For $i = 0 To $iColumns Step 1
            $querystring &= "`" & $aData[0][$i] &"`" & ","
        Next
        $querystring = StringTrimRight($querystring, 1)
        $querystring &= ")  VALUES ('"
        For $i = 0 To $iColumns Step 1
            $querystring &= $aData[$iRow][$i] & "','"
        Next
        $querystring = StringTrimRight($querystring, 2)
        $querystring &= ");"
    Next
    If Not _EzMySql_Exec($querystring) Then Return SetError(@error, 0, 0)
    Return 1
EndFunc

 

[jchd]

As a sidenote, it would be much better efficiency-wise to wrap the outer FOR loop inside a transaction.... BUT:

More importantly, if a string in a cell of the added array contains a single quote, the whole insert will fail. Any string content must be escaped (by doubling internal single quote).

Also it may be good to think about insertion of a huge array in a single insert: some (all?) engine might very well limit SQL statement size.

Finally, the datatype of the array's cells is ignored and everything is inserted as string literal, which not all tables will cope with.

All in all, I'm afraid real-world things aren't that simple.

Edited April 14, 2017 by jchd

[rony2006]

Do you have another suggestion to insert huge 2d arrays to sql?

[jchd]

Not much more than what I suggested. The actual real issue is you know nothing about the potential content of the array. It may be 15 rows of 3 columns containing all short strings, but it might as well be 12 million rows array of 153 columns with some cell holding 600kb strings. In such situation, the basic principle is "know your data", which is essentially impossible in a generic function.

[GoldenEagle]

If Not _EzMySql_Startup() Then
    MsgBox(0, "Error Starting MySql", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf
If Not _EzMySql_Open("myhostname", "mydbusername", "mydbpassword", "mydbname", "3306") Then
    MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    amtt("Error Connecting To DB")
    Exit
EndIf
$sql = "SELECT userID FROM ws_LS_user WHERE statslinkcode='"& $igstatslinkcode &"';"
$quserid = _EzMySql_Query($sql)

Hello, 

I am trying to return the value of userID based on the WHERE Condition. The user enters the unique code ($igstatslinkcode) which matches one they already created on the Website (statslinkcode) - i believe this part is working correctly.

However, i cannot get the contents of the userID Field, i can only get $quserid = 1.

Could someone please explain how i can retrieve the value of a single field from a single column?

Many thanks in advance.

[GoldenEagle]

Anyone?

If i use the code from the example:

For $i = 1 To _EzMySql_Rows() Step 1
    $a1Row = _EzMySql_FetchData()
    _ArrayDisplay($a1Row, "Result: " & $i)
Next

I can even get the correct result displayed in the _ArrayDisplay but still no idea how to convert this one value to a variable i can use...

[Earthshine]

can you post the output please to give a clearer example of what is in the array? which element in the array do you need? just dummy up the data if you need to

 

Edited March 6, 2018 by Earthshine

[GoldenEagle]

Well firstly, thank-you Earthshine for replying to me.

Just to reiterate what i'm trying to do so it's clear:

$sql = "SELECT userID FROM ws_LS_user WHERE statslinkcode='"& $igstatslinkcode &"';"
                $quserid = _EzMySql_Query($sql)
                For $i = 1 To _EzMySql_Rows() Step 1
                $fuserid = _EzMySql_FetchData()
                $auserid = _ArrayDisplay($fuserid, "Result: " & $i)

"statslinkcode" exists in the Database, it is created by each user on my Website and each code is unique.

"$igstatslinkcode" is entered by the User within the Autoit Application, if it matches one in the Web-DB it should return the userID of that User from the Web-DB and obviously, only that one result as it's 1x unique code per User.

I would then like to have this single unique userid returned from the Web-DB available as a variable such as: $userid so i can use it to do various other things for that single user.

In testing (on my own User) when i enter the Unique Code attached to my User it seems to produce the correct result within this "_ArrayDisplay" Window (see attached image). My userID is 2. So i want the value of Col 0 as a variable whenever this query is run and don't need this ArrayDisplay window popping up at all :-D

Cheers!

PS. Sorry for the huge image - my resolution is 4k.

 

Edited March 6, 2018 by GoldenEagle

[Earthshine]

the arraydisplay is just to show you what's in it. you can comment that out.

try just printing out $fuserid[1] and assign to variable if you need to

Edited March 6, 2018 by Earthshine

[GoldenEagle]

27 minutes ago, Earthshine said:

the arraydisplay is just to show you what's in it. you can comment that out.

try just printing out $fuserid[1] and assign to variable if you need to

Thank-you so much for pointing me in the right direction. As you can tell i'm totally new to AutoIT (massive noob) and in fact i'm starting to edit and add to the Source Code of a program that another developer gave to me.

I really felt foolish being unable to work this out on my own or from reading through this or similar topics and had spent hours going round in circles. I also had a feeling the answer would be simple in the end. When i attempted to use $userid[1] the Program crashed, so i tried with 0 considering it was Col 0 and it's working perfectly.

This is the code now working for me:

$sql = "SELECT userID FROM ws_LS_user WHERE statslinkcode='"& $igstatslinkcode &"';"
                $quserid = _EzMySql_Query($sql)
                For $i = 1 To _EzMySql_Rows() Step 1
                $userid = _EzMySql_FetchData()
                $fuserid = $userid[0]
                Next

I wonder if i need this: "For $i = 1 To _EzMySql_Rows() Step 1" at all?

Cheers again & Have a great evening!

= )

[Earthshine]

if all you are going to get back is a one row array, you won't need that for loop.

that is for looping through and getting a bunch in a loop.

The help file is a great place to look for some of this stuff. For loops are covered as an example, which, in your case you don't need right now but may one day.

happy programming! (ps i gave you the wrong index number but that was so you would get it on your own through experimentation, and you did!! congrats!)

Edited March 6, 2018 by Earthshine

[Earthshine]

@GoldenEagle, your name makes me think of this

KAI T-50 Golden Eagle

 

which is a good thing.. LOL, love jets. ps, i prefer the Boeing trainer

http://www.boeing.com/defense/t-x/index.page

Edited March 6, 2018 by Earthshine

[jcobb]

Has anyone been able to connect to mysql server using ssl?  If so, how?

[Letraindusoir]

On 6/20/2010 at 12:32 AM, Yoriz said:

Download EzMySql_Dll.au3

Unable to access this site

[Zuldan23]

We just upgraded our MySql servers and they now required SSL connections. Took me a couple days to dig out all the info I needed to make it work so I thought it may be useful for other people. I have added a new function, _EzmySql_SetOptions, and updated the DLLs to support SSL connections.

For my servers, these are the options I needed to set before opening the connection to the database.

If Not _EzMySql_Startup() Then
    MsgBox(262144, "Error Starting MySql", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_SetOption($MYSQL_ENABLE_CLEARTEXT_PLUGIN, 1) Then
    MsgBox(262144, "Error setting Option", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_SetOption($MYSQL_OPT_SSL_CA, $pathToCA) Then
    MsgBox(262144, "Error setting Option", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_SetOption($MYSQL_OPT_SSL_CERT, $pathToCert) Then
    MsgBox(262144, "Error setting Option", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_SetOption($MYSQL_OPT_SSL_KEY, $pathToKey) Then
    MsgBox(262144, "Error setting Option", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_Open($dbip, $dbuser, $dbpw, "", $dbport) Then
    MsgBox(262144, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

 

EZmySql.zip

[Efo74]

Hi everyone, I wanted to know if anyone has used this library with BLOB type data. I was able to successfully insert a file into the database, but now there is no way I can save it to disk. By running the query ... "INTO DUMPFILE" I cannot decide the path to save locally "Permission Problem". While with the code below I am not able to save the binary file to disk. Can someone help me ?

$sQuery_Func = "SELECT * FROM " & $Table & " WHERE ID=" & _MySQL_FastEscape($ID) & ";"

$aRecord = _EzMySql_GetTable1d($sQuery_Func)

if Not IsArray($aRecord) Then Exit

$BlobField = $aRecord[1]
$BlobExtension = $aRecord[2]
MsgBox(0,"","DataType=" & VarGetType($BlobField) & @LF) ;This Will Print 'String'

$FilePath_Output = "c:\testfile." & $BlobExtension

    $tmp_file_blob=StringToBinary($BlobField)
    $file = FileOpen($FilePath_Output,18)
    FileWrite($file, $tmp_file_blob)
    FileClose($file)

Thanks in advance 

[t0nZ]

HI to all, I'am working on  a project using mySql and this UDF saved my life.

I started with the files from  the post of @Zuldan23 but I made slight changes (only in file EZmySqlConstants.au3) to avoid warnings in compile (not declared variables), transforming some variables in global consts.

I attach my files.

EZmySql.zip

[walec]

Hello

Using EzMySql UDF I display the contents of the table.

$aOk = _EzMySql_GetTable2d("SELECT post_title, max_price, stock_quantity FROM `fdgh57jgh_posts`, `fdgh57jgh_wc_product_meta_lookup` WHERE fdgh57jgh_posts.ID = fdgh57jgh_wc_product_meta_lookup.product_id")
$error = @error
If Not IsArray($aOk) Then MsgBox(0, $sMySqlStatement & " error", $error)
_ArrayDisplay($aOk, "2d Array Names of certain eyecolour")

How can you modify the encoding of the displayed Polish characters? (e.g. ł, ń, ą ...)

 

[mLipok]

13 hours ago, walec said:

How can you modify the encoding of the displayed Polish characters? (e.g. ł, ń, ą ...)

I suppose, there is not the problem.

IMHO You should change connection properties.