Jump to content

MySQL UDFs


cdkid
 Share

Recommended Posts

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
Link to comment
Share on other sites

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 by cdkid
AutoIt Console written in C#. Write au3 code right at the console :D_FileWriteToLineWrite to a specific line in a file.My UDF Libraries: MySQL UDF Library version 1.6 MySQL Database UDF's for AutoItI have stopped updating the MySQL thread above, all future updates will be on my SVN. The svn location is:kan2.sytes.net/publicsvn/mysqlnote: This will still be available, but due to my new job, and school hours, am no longer developing this udf.My business: www.hirethebrain.com Hire The Brain HireTheBrain.com Computer Consulting, Design, Assembly and RepairOh no! I've commited Scriptocide!
Link to comment
Share on other sites

  • 2 weeks later...

Hello !

Thanks for this master piece, Cdkid :P

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 :nuke:

Regards,

Apzo.

Link to comment
Share on other sites

  • 2 weeks later...

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

AutoIt Console written in C#. Write au3 code right at the console :D_FileWriteToLineWrite to a specific line in a file.My UDF Libraries: MySQL UDF Library version 1.6 MySQL Database UDF's for AutoItI have stopped updating the MySQL thread above, all future updates will be on my SVN. The svn location is:kan2.sytes.net/publicsvn/mysqlnote: This will still be available, but due to my new job, and school hours, am no longer developing this udf.My business: www.hirethebrain.com Hire The Brain HireTheBrain.com Computer Consulting, Design, Assembly and RepairOh no! I've commited Scriptocide!
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

The cake is a lie.www.theguy0000.com is currentlyUP images.theguy0000.com is currentlyUP all other *.theguy0000.com sites are DOWN

Link to comment
Share on other sites

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:

post-4763-1158948245_thumb.jpg

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 by joshiieeii
Link to comment
Share on other sites

@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

Link to comment
Share on other sites

@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 by joshiieeii
Link to comment
Share on other sites

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 by joshiieeii
Link to comment
Share on other sites

  • 2 weeks later...

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 :lmao:

/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 by WhiteCrow
Link to comment
Share on other sites

;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!

Link to comment
Share on other sites

  • 4 weeks later...

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...

:whistle:;):P;):D:idea:-_-o:)B):x:huh::huh:

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 by clearguy
Link to comment
Share on other sites

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 by Smudley
-= Smudley =-
Link to comment
Share on other sites

  • 2 weeks later...

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:

Posted Image

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 :lmao:

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 :evil:

#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()
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...