MySQL UDFs

[mianz]

nothing is wrong wif it. my bad, not connected correctly to the sql.

[dmob]

I have been looking at your mysql udf and it looks great.

Is there a way to read the results of the query into a 2d array? Is there a code snippet or link I could see?

Here's one way I whipped up rather too quickly after a disaster...

CODE

#include <MySQL.au3>

Local $aRows = _CountRecords($myConn, tblName, $column, $value)

Local $nColCount = _GetColCount($myConn, tblName)

Dim $ret[$aRows+1][$nColCount], $nRow = 0

$sql = "SELECT * FROM tblName ;"

$quer = $myConn.Execute($sql)

With $quer

While Not .EOF

For $col = 1 To $nColCount - 1

$ret[$nRow][$col] = .Fields($col).value

Next

$nRow += 1

.MoveNext

WEnd

EndWith

_ArrayDisplay($ret)

Edited October 6, 2008 by dalisuit

[SoulA]

Can anyone tell me why this code might not be working?

#Include <File.au3>
#Include <mysql.au3>

$folder = "C:\test"
$database = "C:\test1"
While 1
    $FileList = _FileListToArray($folder, "*", 1)
    If NOT @error Then
        For $i = 1 to $FileList[0]
            FileMove($folder & "\" & $FileList[$i], $database & "\" & $FileList[$i], 1)
        Next
        addData($FileList)
    EndIf
    sleep(10000)
WEnd

Func addData($FileList)
    Dim $values[$FileList[0] * 2 + 1]
    $sql = _MySQLConnect("user", "pass", "captured_pictures", "localhost")
    If NOT @error Then
        $index = 1
        For $i = 0 to $FileList[0]
            $values[$i] = @Hour
            $values[$i + 1] = $FileList[$index]
            $i = $i + 2
            $index = $index + 1
        Next
        $values[UBound($values) - 1] = ""
        $error = _AddRecord($sql, 'mytable', $values, $values)      ;THIS IS FILE NAME ONLY NOT THE FULL PATH
        msgbox(0,"error",$error) ;0 is error 1 is success DEBUG
        _MySQLEnd($sql)
    EndIf
EndFunc

[dmob]

1. Why connect and disconnect to your server each for each iteration of your loop. You only need to connect once before the loop and disconnect once out of the loop.

2. Which part aint working?

Edited October 8, 2008 by dalisuit

[SoulA]

I did that because I want the program to run without attention for long spans of time. If I connect only once will that connection stay connected? I don't know how MySQL remote connections work really. This is why I have it making the connection continuously. It just seems more reliable. Also what if the server goes down and comes back up? I also have some modified code.

What isn't working is the _AddRecord(). It gives me a $oconnectionobj.execute ($query) error.

#Include <File.au3>
#Include <mysql.au3>

Dim $values[3]
$folder = "C:\test"
$database = "C:\test1"

While 1
    $FileList = _FileListToArray($folder, "*", 1)
    If NOT @error Then                              
        $sql = _MySQLConnect("root", "", "captured_pictures", "localhost")
        If NOT @error Then                                              
            For $index = 1 to $FileList[0] step 1
                $values[0] = @Hour
                $values[1] = $FileList[$index]
                $values[2] = ""
                _AddRecord($sql, 'mytable', $values, $values)       ;THIS IS FILE NAME ONLY NOT THE FULL PATH
                If @error Then 
                    ExitLoop
                Else
                    FileMove($folder & "\" & $FileList[$index], $database & "\" & $FileList[$index], 1)
                EndIf
            Next
            _MySQLEnd($sql) 
        EndIf
    EndIf
    sleep(10000)
WEnd

Edited October 8, 2008 by SoulA

[dmob]

how many columns in your table? It seems you are supplying the same values for the column names and values thereof?

Edited October 9, 2008 by dalisuit

[SoulA]

There are two columns. This is what I'm not getting... I think their names are just "A" and "B" so would this work?

_AddRecord($sql, 'mytable', 'A', $time)
_AddRecord($sql, 'mytable', 'B', $FileList[$index])

Edited October 9, 2008 by SoulA

[dmob]

If you still can't get it to work, try this

CODE

$objErr = ObjEvent("AutoIt.Error", "MyErrFunc") ; Initialize COM error handler

While 1

$FileList = _FileListToArray($folder, "*", 1)

If NOT @error Then

$sql = _MySQLConnect("root", "", "captured_pictures", "localhost")

If NOT @error Then

For $index = 1 to $FileList[0] step 1

; assuming table has 3 columns of type string

$values = "'" & @HOUR & "', '" & $FileList[$index] & "', '' "

$sql.execute ("INSERT INTO tableName VALUES(" & $values & ") ;")

; check for error here (return value from MyErrFunc)

;....

FileMove($folder & "\" & $FileList[$index], $database & "\" & $FileList[$index], 1)

Next

_MySQLEnd($sql)

EndIf

EndIf

sleep(10000)

WEnd

Func MyErrFunc()

$hexnum = Hex($objErr.number, 8)

MsgBox(0, "", "We intercepted a COM Error!!" & @CRLF & @CRLF & _

"err.description is: " & $objErr.description & @CRLF & _

"err.windescription is: " & $objErr.windescription & @CRLF & _

"err.lastdllerror is: " & $objErr.lastdllerror & @CRLF & _

"err.scriptline is: " & $objErr.scriptline & @CRLF & _

"err.number is: " & $hexnum & @CRLF & _

"err.source is: " & $objErr.source & @CRLF & _

"err.helpfile is: " & $objErr.helpfile & @CRLF & _

"err.helpcontext is: " & $objErr.helpcontext _

)

; return value to check for here

EndFunc ;==>MyErrFunc

I haven't tested this, but it should work.

Edited October 9, 2008 by dalisuit

[dmob]

CODE

$objErr = ObjEvent("AutoIt.Error", "MyErrFunc") ; Initialize COM error handler

While 1

$FileList = _FileListToArray($folder, "*", 1)

If NOT @error Then

$sql = _MySQLConnect("root", "", "captured_pictures", "localhost")

If NOT @error Then

For $index = 1 to $FileList[0] step 1

; assuming table has 2 columns of type string

$values = "'" & @HOUR & "', '" & $FileList[$index] & "'"

$sql.execute ("INSERT INTO mytable VALUES (" & $values & ") ;")

; check for error here (return value from MyErrFunc)

;....

FileMove($folder & "\" & $FileList[$index], $database & "\" & $FileList[$index], 1)

Next

_MySQLEnd($sql)

EndIf

EndIf

sleep(10000)

WEnd

Func MyErrFunc()

$hexnum = Hex($objErr.number, 8)

MsgBox(0, "", "We intercepted a COM Error!!" & @CRLF & @CRLF & _

"err.description is: " & $objErr.description & @CRLF & _

"err.windescription is: " & $objErr.windescription & @CRLF & _

"err.lastdllerror is: " & $objErr.lastdllerror & @CRLF & _

"err.scriptline is: " & $objErr.scriptline & @CRLF & _

"err.number is: " & $hexnum & @CRLF & _

"err.source is: " & $objErr.source & @CRLF & _

"err.helpfile is: " & $objErr.helpfile & @CRLF & _

"err.helpcontext is: " & $objErr.helpcontext _

)

; return value to check for here

EndFunc ;==>MyErrFunc

Edited October 10, 2008 by dalisuit

[MatteoGuallini]

I've the following error:

C:\Documents and Settings\else044\Desktop\mysql.au3 (39) : ==> 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: 2.251

I've tried to connect to server using a client like HeidiSQL and it runs correctly.

The driver version I've installe is 3.51.26.00.

The driver appears on the list of ODBC driver.

The OS is WinXP.

I've already tried to restart the system.

The same installation on another my PC runs correctly.

Have you some hints?

Thanks

[MatteoGuallini]

Sorry, the previous error was my mistake.

I've anoter question. Someone knows how read more than one value from the same row?

The function "_GetColVals" import only one value per row. I need a simailar function for more than row.

I'm sorry but I don't know mysql.

[MatteoGuallini]

This is the function I need. This is a little bit raw but it just run.

#cs
    Function name: _GetColsVals
    Description: Gets all of the values of a specified columns in a specified table
    Parameters: $oConnectionObj - As returned by _MySQLConnect(), $sTable - the table that the column is in
    $sColumn - the columns to get values from divided by "," ex. ColumnA,ColumnB.
    Return value(s): On success returns an array where $array[0] is the number of values and $array[n] is the Nth value
    On failure sets @error to 1 and returns 0
    Author: cdkid
#ce

Func _GetColsVals($oConnectionObj, $sTable, $sColumn)
    $Columns=StringSplit($sColumn,",")
    MsgBox(0,"",$Columns[0],0)
    If IsObj($oConnectionObj) Then
        Dim $ret[1][$Columns[0]] 
        $quer = $oConnectionObj.Execute("SELECT " & $sColumn & " FROM " & $sTable & ";")
        With $quer
            While Not .EOF
                ReDim $ret[UBound($ret, 1) + 1][$Columns[0]]
                    For $eachcolumn=0 to $Columns[0]-1
                        
                        $ret[UBound($ret, 1) - 1][$eachcolumn] = .Fields ($eachcolumn).value
                    Next
                    .MoveNext
            WEnd
        EndWith
        For $eachcolumn=0 to $Columns[0]-1
            $ret[0][$eachcolumn] = UBound($ret, 1) - 1
        Next
        Return $ret
    EndIf
EndFunc  ;==>_GetColsVals

[cyanidemonkey]

do you think it would be possible to do an automated SQL dump using this UDF, maybe use the _Query() to run "mysqldump wordpress > wordpress.sql"

Edited November 7, 2008 by cyanidemonkey

[cyanidemonkey]

to answer my own question - no. i guess mysqldump needs to ran from the SQL server itself, and not via a remote connection?

have been using this very usefull mySQL UDF for some software, but the host has been flaky at best lately, so would like to code up a automated back up of our own just to be sure i got a copy local as well.

i found this .php version of a SQLdump query, i don't write in .php, so am not sure what I need to convert (i come from a .asp/VB background), but it could be converted to AutoIt i would think: http://snipplr.com/view/173/mysql-dump/ (have started converting it to work with this UDF, will let you know how i get on)

i've made a script that automates the SQLdump in SQLyog, but would ruther do it all in AutoIt without having to control a second application.

Edited November 7, 2008 by cyanidemonkey

[subfighter]

i'm using the udf from kan2.sytes.net/publicsvn/mysql and mysql-connector-odbc-noinstall-5.1.5-win32

i have change the mysql.au3

this line for version 5.1

-> Func _MySQLConnect($sUsername, $sPassword, $sServer, $sDatabase, $iPort = 3306, $sDriver = "{MySQL ODBC 5.1 Driver}")

i have this error.. can some1 help?

C:\Program Files\AutoIt3\Include\mysql.au3 (30) : ==> 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

can you let me know more details is i got the same driver and made the same changes and when i run and try to connect i get the exact same error.. appreciate if someone can tell me what the problem is?

CODE

>Running:(3.2.12.1):C:\Program Files\AutoIt3\autoit3.exe "C:\Users\Dirtbag\Downloads\JTUBE\mysql\connect.au3"

C:\Users\Dirtbag\Downloads\JTUBE\mysql\mysql.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

[subfighter]

to fix the error above please install the newer version of the UDF.. Once again thanks to weaponX as i found a post where he pointed this out and copied the new version and it worked

CODE

I think we are using 2 different versions of the UDF.

You probably got the file from here:

http://www.autoitscript.com/forum/index.ph...14&hl=mysql

There is a little link in that first post that says:

I have stopped updating the MySQL thread above, all future updates will be on my SVN. The svn location is:

kan2.sytes.net/publicsvn/mysql now at revision:

I use the version from here:

http://kan2.sytes.net/publicsvn/mysql/

I believe the order of the parameters changed in that newer version.

[subfighter]

i get the MySQL UDF to read and write to my Database.. But i want it to search the TABLE and a COLUMN and see if the Value exists and if it does it will return TRUE of FALSE??

Anyone have and ideas on how i can get this to work.. this just displays nothing to the console?

$SQLOBJ = _MySQLConnect("login", "password", "64.56.138.13", "jtube")
$sQuery = _MySQLExec($SQLOBJ, "SELECT * FROM jos_jtube_videos WHERE remote_id = '3k1Y5xX7A8I'")
ConsoleWrite (@error)
ConsoleWrite ($sQuery)

_MySQLEnd($SQLOBJ)oÝ÷ Ù´-Q!1Q8Ä0CÑRQ#ÎI0(Ï9$ÄöÚºÚ"µÍÌÍÔÔSÐHÓ^TÔSÛÛXÝ
    ][ÝÛÙÚ[][ÝË   ][ÝÜÜÝÛÜ  ][ÝË  ][ÝÍMLÎLÉ][ÝË ][ÝÚXI][ÝÊBÌÍÜ]YHH   ][ÝÔÑSPÕ
ÓHÜ×ÚXWÝY[ÜÈÒTH[[ÝWÚYH    ÌÎNÌÚÌVM^
ÐNIÌÎNÉ][ÝÂÌÍÚYHÓ^TÔS^XÊ    ÌÍÔÔSÐ ÌÍÜ]YJBÚ]   ÌÍÚYÚ[HÕSÑÙÐÞ
    ][ÝÉ][ÝË    ÌÍÚYY[Ê ÌÎNÙ][ÛÌÎNÊK[YJBÛÛÛÛUÜ]H
    ÌÍÚYY[Ê ÌÎNÚY    ÌÎNÊK[YJB[ÝS^Ñ[[Ú]]]ÈY[ÈHÙ[XÝYTUSÓ[QÓ^TÔS[
    ÌÍÔÔSÐ

Edited November 15, 2008 by subfighter

[radhoo]

Hi, I am having some problem with your fist example.

#include<mysql.au3>

$sql = _MySQLConnect("root","","db","localhost")
$var = _Query($sql,"SELECT * FROM mytable WHERE user = 'username'")
With $var
While NOT .EOF
FileWriteLine("c:\test.txt",.Fields("user_name").value & @CRLF)
.MoveNext
WEnd
EndWith
_MySQLEnd($sql)

I am trying to run the script with a sql db from xampp(localhost). The data base is called db, user is root, no password.

The script is returning the following error : C:\Program Files\AutoIt3\Include\mysql.au3 (48) : ==> The requested action with this object has failed.:

Can you help me with this? Thanks

[Drakken]

Hi,

Someone can post the last version of MySql.AU3 ??

http://kan2.sytes.net/publicsvn/mysql/ seems to be offline :s

Thanks

[Ealric]

I'm using Tortoise SVN and can't connect to the SVN address..

Is it offline?

Can someone post the UDFs?