MySQL UDFs (without ODBC)

[level20peon]

  On 2/13/2009 at 4:31 AM, seesoe said:

i have a php code that i want to translate from php to autoit, how would i do this?

$results = mysql_query("SELECT * FROM online_order WHERE status='ordering") or die(mysql_error());

$row = mysql_fetch_array( $results );

$old_item = $row['item'];

$first_name = $row['first_name'];

$old_items = unserialize($old_item);

and display the info in a loop

$old_items is now an array and $first_name is just a varchar with the users name

 

If anyone is still watching this thread: I would like to know pretty much the same thing, i.e. whether or not there is a possibility to access fields by name instead of index. I would like to do this after using "_MySQL_Fetch_Row"... is there maybe a possibility to access rows as objects?

Edited December 14, 2014 by level20peon

[maniootek]

any idea how to send query with utf8 charset?

[jchd]

Doesn't MySQL accept UTF16 input?

[maniootek]

I don't know which charset AutoIt use to send string but when I try to insert this string

to my MySQL database then it look that way when I explore table using MySQL Administrator

Please note that my MySQL database table setting is set to utf-8

[mLipok]

@maniootek I have instaled MySQL on my computer and on remote host, I do not have any problem like that.
How you are connecting to MySQL ?
I just check how this is set on my remonte host, and this are exactly the same settings.

 

[maniootek]

I am connecting to MySQL with UDF from forum thread we are writing

[maniootek]

I have found function in this UDF called _MySQL_Set_Character_Set() which I added to my script but it still does not save properly polish chars into database.

this is the functions order I use:

_MySQL_InitLibrary()
$conn = _MySQL_Init()
_MySQL_Real_Connect($conn, $dbhost, $dbuser, $dbpass, $dbname)
_MySQL_Character_Set_Name($conn) ;return 'latin1', I think this is default
_MySQL_Set_Character_Set($conn, "utf8")
_MySQL_Character_Set_Name($conn) ;return 'utf8' - properly
_MySQL_Real_Query($conn, $query)

Now it look that way in database:

any idea?

Added:

One more thing. When I execute mysql query from autoit to read values from database then I can see polish chars normally (with latin1 insert and latin1 select).

Edited June 23, 2016 by maniootek

[wolf9228]

  On 12/7/2008 at 7:15 PM, ptrex said:

@ProgAndy

 

I like anything that smells to SQL !!

 

Unfortunately you link does not work (for me ?).

 

Rgds

 

ptrex

Expand  

 

ProgAndy good work 

What is the difference between using the Windows ODBC libraries and the use of MySQL library

Is there a difference in speed and possibilities

Knowing that MySQL library subsidiary of special software and ODBC libraries subsidiary of developer Windows system

Edited June 26, 2016 by wolf9228

[jchd]

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?

[maniootek]

  On 6/26/2016 at 8:08 PM, jchd said:

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?

Expand  

I have many scripts based on this UDF and I simply don't want to rewrite huge part of code.

[mLipok]

  On 7/5/2016 at 10:10 AM, maniootek said:

I have many scripts based on this UDF and I simply don't want to rewrite huge part of code.

Expand  

So as I understand it work before, and now it does not work ?

[maniootek]

Before I didn't use polish characters.

Let me show you a list of things I've done so far:

1. I have set "Encoding" to "UTF-8" in SciTE

2. I have added these lines into SciTEGlobal.properties file

utf8.auto.check=2 #detect utf8 and do not add BOM
output.code.page=65001 #utf8
code.page=65001 #utf8

3. In MySql server I have set default encoding to utf8/utf8_global_ci for specified table and column

4. I have set connection charset to utf8 and also send special query "SET NAMES"

_MySQL_InitLibrary()
$conn = _MySQL_Init()
_MySQL_Real_Connect($conn, $dbhost, $dbuser, $dbpass, $dbname)
_MySQL_Set_Character_Set($conn, "utf8")
_MySQL_Character_Set_Name($conn) ;return 'utf8' - properly
$query_charset = "SET NAMES 'utf8' COLLATE 'utf8_global_ci'"
$query_insert = "INSERT INTO `tablename`.`columnname` (`test`) VALUES ('aąącćeęlłnńoósśzżzź')"
_MySQL_Real_Query($conn, $query_charset)
_MySQL_Real_Query($conn, $query_insert)

nothing worked.

EDIT:

I HAVE FOUND SOLUTION, PROBLEM IS RESOLVED

I think problem was with the libmysql.dll which simple does not support utf8. I have googled "libmysql.dll utf8" and found this:

http://stackoverflow.com/questions/1597768/delphi-7-personal-mysql-using-libmysql-dll-utf8

I have changed charset in my code from utf8 to cp1250 and now it shows all polish chars.

_MySQL_InitLibrary()
$conn = _MySQL_Init()
_MySQL_Real_Connect($conn, $dbhost, $dbuser, $dbpass, $dbname)
_MySQL_Set_Character_Set($conn, "cp1250")
_MySQL_Character_Set_Name($conn)
$query_insert = "INSERT INTO `tablename`.`columnname` (`test`) VALUES ('aąącćeęlłnńoósśzżzź')"
_MySQL_Real_Query($conn, $query_insert)

Edited July 5, 2016 by maniootek
found solution

[mLipok]

Well done.
You are still growing up in my eyes
Keep learning.

mLipok

[jchd]

That isn't a perfect solution. I suspect that the problem you experience is due to the fact that you don't pass UTF8 strings to the library. Remember that native AutoIt strings are UTF16-LE encoded. If the library expects str type as parameter, then AutoIt will convert UTF16 to the locale ISO codepage you use.

Try converting UTF16 strings to UTF8 before passing them by means of _WinAPI_WideCharToMultiByte with output codepage = 65001.

[bobmcrae]

I have been testing the stability of using mysqllib.dll & the associated UDF, like this fine one.  However, I have run into a problem for which I don't see an elegant solution.  Specifically, once the connection to the server is lost (I have only tested when I lose internet connection so far), the DLL becomes non-responsive and then crashes the AutoIT script with an exit code of 3221225477.  I will post the simple test below, which is an adaptation of @ProgAndy's test.au3.  I added in a response to a lost connection, including a poll until internet connection is re-established.  

I have tried different versions of the mysqllib.dll, but to no avail.  Also, while there are plenty of posts out there on this issue, I have not found a good work-around.

expandcollapse popup

#include "mysql.au3"
Global $MysqlConn

_MySQL_InitLibrary()
$MysqlConn = _MySQL_Init()
$connected = _MySQL_Real_Connect($MysqlConn,"****","adminuser","****","wellchec", 3306)

While True
    $ping = _MySQL_Ping($MysqlConn)
    ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') :     $ping = ' &     $ping & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console

    If $ping=1 Then _reconnectDB($MysqlConn)

    $sleepTime = Random(1000,20000,1)
    ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $sleepTime = ' & $sleepTime & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console
    Sleep($sleepTime)

WEnd

_MySQL_Close($MysqlConn)
_MySQL_EndLibrary()



Func _reconnectDB($MysqlConn)
    _MySQL_Close($MysqlConn)
    _MySQL_EndLibrary()

    Do
        Sleep(1000)
    Until Ping('google.com')

    _MySQL_InitLibrary()
    If @error Then Exit MsgBox(0, '', "")
    $MysqlConn = _MySQL_Init()

    _MySQL_Real_Connect($MysqlConn,"****","adminuser","****","wellchec", 3306)
EndFunc ; _reconnectDB()

Here is the console listing after a run where I disconnect from my wireless network, then reconnect.

>Running:(3.3.14.1):C:\Program Files\AutoIt3\autoit3.exe "C:\Users\Public\Desktop\Pt.Data\Reports\mysql(20160710010357)\test.au3"    
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
@@ Debug(19) :  $ping = 0
>Error code: 0
@@ Debug(24) : $sleepTime = 15798
>Error code: 0
@@ Debug(19) :  $ping = 1
>Error code: 0
@@ Debug(24) : $sleepTime = 5969
>Error code: 0
!>09:33:32 AutoIt3.exe ended.rc:-1073741819
+>09:33:32 AutoIt3Wrapper Finished.
>Exit code: 3221225477    Time: 81.51

 

[editcheck]

Hi. im using your UDF.
With:

****

; Zugriff
MsgBox(0, '', "Zugriff - alles in ein 2D Array")
$array = _MySQL_Fetch_Result_StringArray($res)
_ArrayDisplay($array)

****

Can "read" (show) the values on database.... i can use those values and make changes...

So i wanna know how save the changes ?

[nbg15]

one word.... WOWWWW

[mLipok]

  On 8/3/2016 at 11:19 AM, editcheck said:

So i wanna know how save the changes ?

Expand  

you should use SQL Query :
 

INSERT INTO .......

or

UPDATE ....

 

[editcheck]

  On 8/9/2016 at 11:09 PM, mLipok said:

you should use SQL Query :
 

INSERT INTO .......

or

UPDATE ....

 

Expand  

 

Hi, can give me a sample ? ty

 

Edit: resolved, ty

Edited August 19, 2016 by editcheck

[TheDcoder]

Hello, sorry bumping an old thread but is there an way to know the cause of failure in _MySQL_Real_Query function?