MySQL UDFs

[Burthold]

fixed a bug in the _addrecord routine where it was adding one to many commas to the beginning and not escaping single quotes in charater fields.

If IsArray($vValue) Then
            For $i = 0 To UBound($vValue, 1) - 1
                If $i > 0 And $i <> UBound($vValue, 1) - 1 and IsInt($vValue[$i]) Then
                        $query = $query & "," & $vValue[$i] & ""
                ElseIf $i > 0 And $i <> UBound($vValue, 1) - 1 and not IsInt($vValue[$i]) Then
                        $query = $query & ",'" & StringReplace($vValue[$i],"'","''") & "'"
                ElseIf $i = UBound($vValue, 1) - 1 And $vValue[$i] <> "" and IsInt($vValue[$i]) Then
                        $query = $query & "," & $vValue[$i] & ");"
                ElseIf $i = UBound($vValue, 1) - 1 And $vValue[$i] <> "" and not IsInt($vValue[$i]) Then
                        $query = $query & ",'" & StringReplace($vValue[$i],"'","''") & "');"
                ElseIf $i = 0 and IsInt($vValue[$i]) Then
                        $query = $query & "" & $vValue[$i] & ""
                ElseIf $i = 0 and not IsInt($vValue[$i]) Then
                        $query = $query & "'" & StringReplace($vValue[$i],"'","''") & "'"
                ElseIf $vValue[$i] = "" Then
                    $query = $query & ");"
                EndIf
            Next
        EndIf

[cdkid]

thank you, burthold, i'll update.

[edit]

Updated.

I would like to apologize for being so inactive. I was away for most of the year in Minnesota, and when I got back I had to get ready to move. Just getting settled in... Glad people found this useful

~cdkid

Edited August 31, 2006 by cdkid

[Apzo]

Hello !

Thanks for this master piece, Cdkid

May I suggest this little func : _AQuery() : same as _Query() but returns an array.

The elt [0][0] contains the number of records.

Use Ubound($array, 2) to get the number of columns if you don't have it.

It's useful when your query returns vals from more than one table, getting the number of cols in such a query is sometimes hard.

Func _AQuery($oConnectionObj, $sQuery)
    Local $rec = 0
    Local $Q[1][1]
    $Q[0][0] = 1
    Local $var = _Query($oConnectionObj, $sQuery)
    With $var
        Local $n = .Fields.count ; << the interesting part of this func
        Redim $Q[1][$n]
        While NOT .EOF
            $rec += 1
            Redim $Q[$rec+1][$n]
            $Q[0][0] = $rec
            For $i = 0 To $n - 1
                $Q[$rec][$i] = .Fields($i).value
            Next
            .MoveNext
        WEnd
    EndWith
    Return $Q
EndFunc

It's not UDF compliant, but if you find it useful feel free to improve it, I would be honored

Regards,

Apzo.

[cdkid]

Apzo:

I considered having it return an array but

the reason i set _Query to return an object is because

imagine you do a query and it returns 50 thousand results. This might cause some problems.

~cdkid

[Pozzy]

I am having two major problems. One is when the mysql.au3 is in the includes folder of my Autoit, it gives me the error: "Error reading the file:"

When it is within the same folder, commands that i copied and pasted from the first example of this post give me the following error

_MySQLEnd($sql)

Error: "Func" statement has no matching "Endfunc"

I don't see a function anywhere in the code, could someone please help me out.

Thanks, greatly appreciated.

[theguy0000]

I am having two major problems. One is when the mysql.au3 is in the includes folder of my Autoit, it gives me the error: "Error reading the file:"

When it is within the same folder, commands that i copied and pasted from the first example of this post give me the following error

_MySQLEnd($sql)

Error: "Func" statement has no matching "Endfunc"

I don't see a function anywhere in the code, could someone please help me out.

Thanks, greatly appreciated.

if you are using the beta, it has to go in your beta include directory. Program Files\AutoIt3\beta\include

[joshiieeii]

I guess I don't really understand totally what I am doing. So far I have installed the MySQL server, I have been able to communicate with the database, create a table, and input/delete data.

Bravo!! It works wonderfully!!

I have a question thought, doesn't the data in the DB have to all be on the same row?

How do you send an addrecord and get the data all in the same row?

So if I query Jack Oneil, I get the data thats he's from South Carolina, ect..

I guess I am getting confused on how you add data to specific rows...or maybe I just don't understand DB's that well....

Please see this pic, it's from the MySQL Query tool from MySQL:

Here my current code:

#include 'MySQL.au3'

Dim $user = "tester"
Dim $pass = "test123"
Dim $anydatabase = "testdb"
Dim $host = "10.10.10.10"
Dim $vValue

$objErr = ObjEvent("AutoIt.Error","MyErrFunc")

Dim $sql = _MySQLConnect($user, $pass, $anydatabase, $host)

_AddRecord($sql, "testdb", "username", "Jack ONeil")
_AddRecord($sql, "testdb", "username", "Samantha Carter")
_AddRecord($sql, "testdb", "Location", "South Carolina")


_MySQLEnd($sql)



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 _
                )
    exit
EndFunc

Edited September 22, 2006 by joshiieeii

[ptrex]

@joshiieeii

or maybe I just don't understand DB's that well

I fear this is the reason you' re getting strange results.

When using AddRecord, you are ARE ADDING records.

What you probably want to do is CHANGE a record.

Therefor you need an UPDATE query.

Best way to get to know the SQL statements is start reading this : SQL Tutorial

Regards

ptrex

[joshiieeii]

@joshiieeii

I fear this is the reason you' re getting strange results.

When using AddRecord, you are ARE ADDING records.

What you probably want to do is CHANGE a record.

Therefor you need an UPDATE query.

Best way to get to know the SQL statements is start reading this : SQL Tutorial

Regards

ptrex

Thanks ptrex!! You hit the nail on the head, and thanks for the link!

Sounds like we need to add an "_UpdateRecord" function in....

I was just going by what was in the mysql.au3, there is no update record function. And I am just now learning about the MySQL syntax.

Edited September 22, 2006 by joshiieeii

[Pozzy]

if you are using the beta, it has to go in your beta include directory. Program Files\AutoIt3\beta\include

Ok done, but I still receive the error end func command.

[joshiieeii]

Ok done, but I still receive the error end func command.

So the mysql.au3 is located in the above mentioned directory and you pushed Alt + F5 while in your latest updated SCITE?

[joshiieeii]

Ok, here is my first attempt at a function for a UDF....I am sure someone could make it better..

It's to update a Record within a Table

#cs
    Function name: _UpdateRecord
    Description:     Updates a record from a specified table

    Parameter(s):   $oConnectionObj - As returned by _MySQL Connect. $sTable - The table to put the record in
                    $sTable - The table specified to update within a database
                    $sCol - The column to update within a specified record (explained down a ways)
                    $sVal - The Value to update within the column ($sCol)
                    $wCol - The column to look to match up with a value ($wVal) to select a specific record within a table
                    $wVal - The Value to match up with the column ($wCol) in order to select a specific record within a table to update
    Requirement(s): Autoit 3 with COM support
    Return value(s): On success returns 1. If the connectionobj is not an object returns 0 and sets @error to 2. If there is any other error returns 0 and sets @error to 1.
    Author(s): cdkid, burthold (fixed a problem with extra commas)
#ce

Func _UpdateRecord($oConnectionobj, $sTable, $sCol = "", $sVal = "", $wCol = "", $wVal = "" )
    If IsObj($oConnectionObj) Then
    $query = $oConnectionobj.execute ("UPDATE " & $stable & " SET " & $sCol & "='" & $sVal &"' WHERE " & $wCol & "='" & $wVal &"';")

        $oconnectionobj.execute ($query)
        EndIf
EndFunc

Edited September 22, 2006 by joshiieeii

[daremo]

This seemed to work for me as a silent installation of the ODBC connector. I did it on 2 machines that didn't have the MySql ODBC and it worked.

RunWait('msiexec /i "c:\temp\mysql.msi" /qn')

You need to choose the MSI package from the link:

http://dev.mysql.com/downloads/connector/odbc/3.51.html

[WhiteCrow]

maybe, you should add a port option to this function, becouse when you have a different port then 3306, it may not connect.

_MySQLConnect($sUsername, $sPassword, $sDatabase, $sServer, $sServerPort)

Otherwise, nice udf man, i use it allot

[WhiteCrow]

I think this is a bug, for example, if you have in a database.

Example tables:

fruit

fruit_cars

fruit_fruit

fruit_vedgetables

*note that fruit is a table to*

now if you do:

$counts=_CountRecords($connected_db, "fruit", "somedatafield")

now you get a error (error in line 309 => $sql2.open ($constr, $oConnectionObj))

$counts=_CountRecords($connected_db, "fruit_cars", "somedatafield")

$counts=_CountRecords($connected_db, "fruit_fruit", "somedatafield")

$counts=_CountRecords($connected_db, "fruit_vedgetables", "somedatafield")

you get no error

I want to count the records in "fruit", and thats not possible ?

I dont think this error is just with the counting function, i think its other functions to.

Anyway, if you know how to solve this, please reply

/edit: this is a bug within all functions, tested almost all functions, and all error out.

/edit2: found the sollution to this.

Explicitly name the database if you refer to a tablename like this.

$counts=_CountRecords($connected_db, "databasename.fruit", "somedatafield")

Edited October 11, 2006 by WhiteCrow

[dufran3]

;ODBC test

#include <H:\AutoIt\Scripts\Beta\KMC PC GUI\mysql.au3>

$user = "SQT"
$pass = "hes51"
$anydatabase = "Hes51"
$server = "0.0.0.0"
$driver = "SQL Server"
$vValue = ""

$objErr = ObjEvent("AutoIt.Error","MyErrFunc")

$sql = _MySQLConnect($user, $pass, $anydatabase, $server, $driver)

_MySQLEnd($sql)



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 _
                )
    exit
EndFunc

When I run this, I get no errors, however when I open up the ODBC control panel, it doesn't show my entry in there....help!

[clearguy]

OMG this was exactly what I searched for!!!

AutoIt is very good and so, but with this UDF it should become much better,you know.

Really big thanx for this,it's a reallly good job...

Works fine!

For first some questions;this is the first code:

$sql = _MySQLConnect("sa","sa","mydb","mywebsite.com")
$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)

EDIT

What is about that points on .MoveNext and .Fields, it's the first time I see it.It is special to COM ??

Thanx for explaining.

Edited November 6, 2006 by clearguy

[Smudley]

Ok, here is my first attempt at a function for a UDF....I am sure someone could make it better..

It's to update a Record within a Table

#cs
    Function name: _UpdateRecord
    Description:     Updates a record from a specified table

    Parameter(s):   $oConnectionObj - As returned by _MySQL Connect. $sTable - The table to put the record in
                    $sTable - The table specified to update within a database
                    $sCol - The column to update within a specified record (explained down a ways)
                    $sVal - The Value to update within the column ($sCol)
                    $wCol - The column to look to match up with a value ($wVal) to select a specific record within a table
                    $wVal - The Value to match up with the column ($wCol) in order to select a specific record within a table to update
    Requirement(s): Autoit 3 with COM support
    Return value(s): On success returns 1. If the connectionobj is not an object returns 0 and sets @error to 2. If there is any other error returns 0 and sets @error to 1.
    Author(s): cdkid, burthold (fixed a problem with extra commas)
#ce

Func _UpdateRecord($oConnectionobj, $sTable, $sCol = "", $sVal = "", $wCol = "", $wVal = "" )
    If IsObj($oConnectionObj) Then
    $query = $oConnectionobj.execute ("UPDATE " & $stable & " SET " & $sCol & "='" & $sVal &"' WHERE " & $wCol & "='" & $wVal &"';")

        $oconnectionobj.execute ($query)
        EndIf
EndFunc
 oÝ÷ Ûú®¢×£¢Èbç¢*.­«mzjmÁ« ­æ­nër¢èZu§h¹¹^{º×°'!¶ÞÁ©Þ®º+jëh×6
Func _UpdateRecord($oConnectionobj, $sTable, $sCol = "", $sVal = "", $wCol = "", $wVal = "" )
    If IsObj($oConnectionObj) Then
    $query = $oConnectionobj.execute ("UPDATE " & $stable & " SET " & $sCol & "='" & $sVal &"' WHERE " & $wCol & "='" & $wVal &"';")

        $oconnectionobj.execute ($query)
        EndIf
EndFunc
 oÝ÷ ØuëaÇ!jxjwkyÇ¢½çg¡ê뢻ºÚ"µÍ[ÈÕ]TXÛÜ
    ÌÍÛÐÛÛXÝ[ÛØ    ÌÍÜÕXK  ÌÍÜÐÛÛH   ][ÝÉ][ÝË    ÌÍÜÕ[H  ][ÝÉ][ÝË    ÌÍÝÐÛÛH   ][ÝÉ][ÝË    ÌÍÝÕ[H  ][ÝÉ][ÝÈ
BYÓØ  ÌÍÛÐÛÛXÝ[ÛØH[  ÌÍÜ]YHH  ][ÝÕTUH   ][ÝÈ  [È ÌÍÜÝXH  [È ][ÝÈÑU   ][ÝÈ  [È ÌÍÜÐÛÛ    [È ][ÝÏIÌÎNÉ][ÝÈ    [È ÌÍÜÕ[   [É][ÝÉÌÎNÈÒTH    ][ÝÈ  [È ÌÍÝÐÛÛ    [È ][ÝÏIÌÎNÉ][ÝÈ    [È ÌÍÝÕ[   [É][ÝÉÌÎNÎÉ][Ý    ÌÍÛØÛÛXÝ[ÛØ^XÝ]H
    ÌÍÜ]YJB[Y[[Â

Good job! Works great!

Maybe @cdkid will include it in future versions.

Edited November 9, 2006 by Smudley

[pcprint]

Ok i've done the searching, done the reading, but my noobie head can't get past the scratching stage.

I have:

A web host which runs mysql version 4.0.24

The ODBC (spelling?) drivers installed

The latest beta of autoit installed.

A basic understanding of what i'm doing (which is more dangerous than no idea imo )

What happens when i run my script and try logging in (what it is supposed to use sql for), it sits for around 30 or so seconds then I get the following error:

I'm thinking the problem MAY be with my hosting provider only running mysql version 4.0.24 but this is beyond my control unfortunately so i'm hoping it is something i've screwed up and everyone can point fingers and laugh at the 1 post noob then steer me in the right direction

My script is (although it won't work due to my taking the username etc out):

Oh and you can ignore the login button it has no onclick yet, the shortcut to login is F9

expandcollapse popup

#include <GUIConstants.au3>;required for GUI
#include <mysql.au3>

Hotkeyset("{F9}","ToggleState")
Hotkeyset("{ESC}","ext")

Dim $Running = 0
Dim $Connected = 0
Dim $LoggedIn = 0

#Region ### START Koda GUI section ### Form=I:\Programming\abba\AForm1.kxf
$Form1 = GUICreate("Game Companion", 410, 480, 193, 115)
$LoginGrp = GUICtrlCreateGroup("Login", 8, 0, 209, 73)
$Label8 = GUICtrlCreateLabel("Username :", 24, 24, 58, 17)
$Label9 = GUICtrlCreateLabel("Password :", 24, 48, 56, 17)
$Username = GUICtrlCreateInput("", 96, 16, 65, 21)
$Password = GUICtrlCreateInput("", 96, 40, 65, 21)
$Login = GUICtrlCreateButton("Login", 168, 16, 41, 41, 0)
GUICtrlCreateGroup("", -99, -99, 1, 1)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit

    EndSwitch
WEnd

Func Login()
    Local $Var2, $Var3
    Local $UN = "*****"
    Local $PW = "*****"
    Local $DB = "*****"
    Local $SVR = "*****"
    If Not ($Connected) Then
        $sql = _MySQLConnect($UN,$PW,$DB,$SVR)
        $Connected = 1
    EndIf
    $Var2 = GUICtrlRead($Username)
    $Var3 = GUICtrlRead($Password)
    $Var = _Query($sql,"SELECT UserID FROM albusers WHERE Username = $Var2 AND Password = $Var3")
    With $Var
    While NOT .EOF
    $LoggedIn = .Fields("UserID").value
    .MoveNext
    WEnd
EndFunc

Func ext()
    _MySQLEnd($sql);CLOSES THE MYSQL DATABASE CONNECTION
    Exit
EndFunc

Func ToggleState()
    If $LoggedIn = 0 Then
        Login()
        If $LoggedIn > 0 and $Running = 0  Then 
            $Running = 1
        EndIf
        If $LoggedIn > 0 and $Running = 1  Then 
            $Running = 0
        EndIf
    EndIf
    If $LoggedIn > 0 Then
        If $Running = 0  Then   
            $Running = 1
        EndIf
        If $Running = 1  Then   
            $Running = 0
        EndIf
    EndIf
    EndFunc
    
    Func Start()
    While 1
        If $Running = 1 Then
            MsgBox( 0, "loggedin", "true")
        EndIf
    WEnd
    
EndFunc

Start()

[clearguy]

Hi,

the first time I was using this great UDF I got the same error, because the credentials for the SQL query weren't correct.

I think you have this error because of the 'in-query' variables, which are variables used only by au3 and that the SQL query doesn't know these variables....

How I would try :

_Query($sql,"SELECT UserID FROM albusers WHERE Username = "&$Var2&" AND Password ="&$Var3)

it's worth to try

GL.