MySQL UDFs

[Gigli]

Ok it's solved for me... problem was in the database

[AtomR]

Good afternoon,

I have been using this udf for a few months now and I am very pleased with it. I was using a my sql server hosted on my machine. But now I deployed the database to a remote server and I get the following error "[MySQL][OBDC 5.1 Driver] Unknown MySQL server host ' instance7146.db.xeround.com.:5528 ' (11004)". What do you think is the problem? That server address is working properly in the connection string of my php but not in autoit.

I've even tried adding an http:// to the server name but the result is the same.

The server is running mysql server version 5.1.42.

Is it possible that that host address cannot be resolved due to the dot right after the .com?

Any help would be appreciated. Thank you

[edit]

I don't think the problem is with my ODBC driver because the mysql server version on my localhost is 5.5.16-log so i'm assuming it's a more recent server version.

Edited March 1, 2012 by AtomR

[AtomR]

I found the problem. It was my fault. I was using:

$sql = _MySQLConnect("xxx","xxx","xxx","instance7146.db.xeround.com.:5528")

I happened to look at the function signature in the udf and saw that there are other default values one of them is the port.

$sql = _MySQLConnect("xxx","xxx","xxx","instance7146.db.xeround.com.","{MySQL ODBC 5.1 Driver}",5528)

This is how it should have been all along.

Edited March 2, 2012 by AtomR

[bsdos]

get an array from SQL query

expandcollapse popup

#cs
Function name: _Query2array
Description: Send a query to the database
Parameters: $oConnectionObj - As returned by _MySQLConnect. $sQuery - The query to execute
Return values: On success returns the query result by array. On failure returns 0 and sets @error to 1
Requirement(s):Autoit3 with COM support
Author: WayneHu
#ce
Func _Query2array($oConnectionObj,$sQuery)
$var = _Query($oConnectionObj,$sQuery)
If @error Then
  SetError(1)
  Return 0
EndIf

With $var
  $i = 0
  While Not .eof
   $i = $i + 1
   .movenext
  WEnd
EndWith

$var.movefirst

Dim $array[$i+1][$var.Fields.count+1]
$array[0][0] = $i
$array[0][1] = $var.Fields.count

For $r = 1 To $i
  For $c = 0 To $var.Fields.count-1
   $array[$r][$c+1] = $var.Fields($c).value
  Next
  $var.MoveNext
Next
Return $array
EndFunc

Example:

#include <mysql.au3>
#include <array.au3>
$conn = _MySQLConnect("root","","dbname","localhost")
;~ _Query($conn,"set names gb2312")
$var = _Query2array($conn,"SELECT * FROM table_name where userid=156")
_ArrayDisplay($var)
_MySQLEnd($conn)

[Siwa]

can anybody please help me, with :

==> Only Object-type variables allowed in a "With" statement.:

With $TableContents

With ^ ERROR

in this topic ===>

I did create another topic for my question. So please if anyone can help, post at that topic.

Edited April 28, 2012 by Siwa

[hagaizenberg]

Solution Found: I installed the 32 bit version of mysql odbc - mysql-connector-odbc-5.1.11-win32.msi

and it's working just fine (i didn't uninstall the 64 version but don't believe it matters)

___

Hi,

I've been using this UDF for a long time now but upgraded to windows 7 64 bit and now can't connect to mysql.

to be more specific, even the RegRead doesn't work:

$v=MySQL ODBC 5.1 Driver
$key=HKEY_LOCAL_MACHINESOFTWAREODBCODBCINST.INIODBC Drivers
$val = RegRead($key, $v)

$val returns an empty string even-though I installed MYSQL ODBC 5.1 64bit driver and I see it in window's regedit:

and further-more,

if I ignore the RegRead,

and take it further to creating the connection:

$ObjConn = ObjCreate("ADODB.Connection")
traceLog("ObjCreate error="&@error)
$connString="DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";PORT="&$iPort
traceLog("$connString="&$connString)
$Objconn.open ($connString)

where $connString is:

"DRIVER={MySQL ODBC 5.1 Driver};SERVER=xxx.org;DATABASE=xxx;UID=xxx;PWD=xxx;PORT=3306"

I see that the ObjCreate returns error=0 (so I assume it found the object) but I get this failure:

--> COM Error Encountered in social submitter.au3

----> $IEComErrorScriptline = 44

----> $IEComErrorNumberHex = 80020009

----> $IEComErrorNumber = -2147352567

----> $IEComErrorWinDescription = Unspecified error

----> $IEComErrorDescription = [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

----> $IEComErrorSource = Microsoft OLE DB Provider for ODBC Drivers

----> $IEComErrorHelpFile =

----> $IEComErrorHelpContext = 0

----> $IEComErrorLastDllError = 0

Any help would much more appreciate, this code worked fine for me in my old laptop with XP so I'm afraid it's something to do with windows 7

or the driver I installed? the 64 bit one: mysql-connector-odbc-5.1.11-winx64.msi

Many Thanks!!

Edited May 23, 2012 by hagaizenberg

[imbrija]

Maybe this will help about registry

Win 7 x64

HKEY_LOCAL_MACHINESOFTWAREWow6432NodeODBCODBCINST.INIODBC Drivers

Win 7 x86

HKEY_LOCAL_MACHINESOFTWAREODBCODBCINST.INIODBC Drivers

Sorry, didn't try it....but it's a clue

Edited June 11, 2012 by imbrija

[choidk]

if input wrong $sUsername, $sPassword, $sDatabase, or $sServer this Exit

and e

C:UsersBao VietDesktopmysql.au3 (27) : ==> The requested action with this object has failed.:
$Objconn.open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";PORT="&$iPort)
$Objconn.open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";PORT="&$iPort)^ ERROR
>Exit code: 1 Time: 6.468

how to run MsgBox when wrong ?

Edited July 20, 2012 by choidk

[eawedat]

---------------------------

AutoIt Error

---------------------------

Line 5 (File "C:UsersUserDesktopMySQLwork.au3"):

With $var

With ^ ERROR

Error: Only Object-type variables allowed in a "With" statement.

---------------------------

OK

---------------------------

[jive512]

cdkid - this is beautiful! Great job!!

[michaelslamet]

Hi fellow members,

This is a great UDF, I'm using it on some of my little application sucessfully - thanks to cdkid!

Currently I'm facing this problem: after a very short time (maybe around 60 secs) after connection is made, this error occured: "MySQL server has gone away".

I want it to connect when the application run at the first time and only disconnect when the user quit.

Is there anyway to initiated somekind of auto-connect or extend the timeout period to the maximum?

ODBC version is 3.5.1 running on Windows XP SP2

Thanks in advance

[xwizard]

Hi,

Very cool library, many thanks!! ))))

How I can get the help about functions of this library?

It can be very useful if we can install some help-dictionary to the standard help for editor SciTE.

[SeunghunBaek]

do i need to escape query string by myself when using _Query function?

[russell]

Is there a way to update a record?

[doinguyen]

Dear all,

I have a issue with utf8 please help me

i input data ( ex:"Khát Vọng" ) to DB, check DB data the same but when query then result : "Khát V?ng"

Character set / Collation : utf8 / utf8_general_ci

Thanks so much..

[jchd]

Post a short reproducer example code here and you can expect help.

[doinguyen]

Hi jchd, 

 My code

$MYSQLUserName = "u_autoit"

    $MYSQLPassword = "123"
    $MYSQLDatabase = "autoit"
    $MySQLServerName = "localhost"
    Global $SQLQuery = _MySQLConnect($MYSQLUserName,$MYSQLPassword,$MYSQLDatabase,$MySQLServerName)
    $Result = _Query($SQLQuery,"SELECT name FROM information")
    If @error Then
        MsgBox(0, "Error SQL", "Error when executing SQL")
    Elseif IsObj($Result) Then
        With $Result
        While Not .EOF
            $data = .Fields ("name").value
            .MoveNext
        WEnd
        EndWith
    Else
        MsgBox(0, "SQL Error", "Something went wrong without error. Maybe no result ?")
    EndIf
                msgbox(0,'Hello', $name)

Field name in mysql contain data: 'Khát Vọng' but when the window msg show: Khát V?ng 

( If i get data in file .txt that's ok , just issue with use mysql )

Edited October 20, 2013 by doinguyen

[jchd]

Which charset do you use for the column and are you positive that the specific row contains Unicode data (not Big5 or such MBCS charset)?

Can you display your DB schema?

Your best bet is to always use UTF8 with BOM AutoIt source files.

[doinguyen]

Thanks for your reply

 

SciTe encoding use UTF8 with BOM.

You can help me

Edited October 21, 2013 by doinguyen

[jchd]

In your code excerpt, you load $data with the column result, but you display $name in the MsgBox.

It sounds like $name contained the same string have passed thru Unicode to ANSI conversion (hidden in some AutoIt functions).

Can you double check by running the following:

Local $MYSQLUserName = "u_autoit"
Local $MYSQLPassword = "123"
Local $MYSQLDatabase = "autoit"
Local $MySQLServerName = "localhost"
Local $Literal = 'Khát Vọng'
Local $SQLQuery = _MySQLConnect($MYSQLUserName, $MYSQLPassword, $MYSQLDatabase, $MySQLServerName)
Local $data
Local $Result = _Query($SQLQuery,"SELECT " & $Literal & as test;")
If @error Then
    MsgBox(0, "Error SQL", "Error when executing SQL")
Elseif IsObj($Result) Then
    With $Result
        While Not .EOF
            $data = .Fields("test").value
            .MoveNext
        WEnd
    EndWith
Else
    MsgBox(0, "SQL Error", "Something went wrong without error. Maybe no result ?")
EndIf
MsgBox(0, 'MySQL literal', $data)

MsgBox(0, 'AutoIt literal', $Literal)