MySQL UDFs

[cdkid]

[uPDATE]

Added returning 0 and setting @Error to 2 when Driver is not installed (tested, fixed last one)

If you run into any problems with it tell me.

~cdkid

Edited March 18, 2006 by cdkid

[EFF]

Hi. I was playing with this UDF, and finally found sth like that.

With $var
While NOT .EOF
;_ArrayAdd($aaa,.Fields("name").value)
_ArrayInsert ( $aaa, $i, .Fields("name").value )
.MoveNext
$i +=1 
WEnd
EndWith
_MySQLEnd($sql)

I wanted to ask, if there is some other way to get an Array with elements from DB I asked for in query?

I can`t understand some of the syntax code here. What Does this .Fields.("name").value do? What is .MoveNext used for? I couldn`t find this in AutoIt manual, so i suppose it`s some code from ODBC.

ps. God bless You for that UDF

[theguy0000]

notice the "With $var", so .EOF is the same as $var.EOF, .Fields("name").value is the same as $var.Fields("name").value, etc.

[erebus]

.MoveNext reads the next raw from the MySQL query output (assuming your query gives many raws with data, not just one).

.Field("name").value, returns the value of the field "name" in your MySQL output.

If you want to have it more clear, download the MySQL Query Browser from www.mysql.org. Give in this program your queries manually and check the output of them on your screen.

You will then understand the logic of "raws" and "fields".

[cdkid]

Yes, the query browser displays the results like this

COLUMN 1 would be $var.Fields(0)

or

COLUMN 'first_name' would be $var.Fields("first_name")

And then reading the next ROW would be $var.MoveNext

so let's say you have a table called names with two fields

first name & last name

and you have two records

it would look like this

First_Name---Last_Name

---Some---------Guy

---Another-------Guy

To get all values from that you would do

$var = _Query($sql, "SELECT * FROM names")
With $var
While NOT .EOF; is equal to "Do this until we run out of records"
MsgBox(0,'',.Fields('first_name') & " - - - - " & .Fields("last_name"));Msgbox the first & last name of the first record
.MoveNext;Move to the next row
WEnd
EndWith

I hope this clears things up a bit.

The query browser (as erebus said) is extremely useful for working with ODBC/MySQL COM

~cdkid

Edited March 18, 2006 by cdkid

[erebus]

@cdkid: I managed to handle the COM errors with your tip in the 3rd post of this thread. However I was thinking, since the normal @error macros doesn't seem to work with the COM objects, why don't you remove them from your UDFs?

I think (since we can't use them) we will all save some 'execution' time at least with this.

[jvanegmond]

$query = "SELECT id FROM `rooster` WHERE dag = '" & $dag & "' AND klas = '" & $klas &"' AND uur = '" & $uur & "' LIMIT 1"
$id = _Query($sql, $query)
MsgBox(0, "", $id )

This query returns 3 when i run it in phpMyadmin but when i run it here it returns 0@¬ and stuff like p©? .

Is this a known problem?

Edit: I worked my way around this but i still think this should be fixed.

Edited March 19, 2006 by Manadar

[cdkid]

It is returning an object... try

$query = "SELECT id FROM `rooster` WHERE dag = '" & $dag & "' AND klas = '" & $klas &"' AND uur = '" & $uur & "' LIMIT 1"
$id = _Query($sql, $query)
With $id
While NOT .EOF
MsgBox(0, "", $id.Fields('id').Value) 
.MoveNext
WEnd
EndWith

Please read the examples before you claim there is a bug in my code...

~cdkid

Edited March 19, 2006 by cdkid

[EFF]

@Manadar - In PHP when you display query result you will get a resource(see http://pl2.php.net/manual/en/function.mysql-query.php )

Only when you put that resource i mysql_fetch_array() function you will recive an array with asked elemnts.

PHPMyAdmin does this thing for You.

Edited March 20, 2006 by EFF

[jvanegmond]

Oh, right. Thanks guys this helps

[jvanegmond]

Next problem

Unknown MySQL server host '##########:####' (11004)

It's a MySQL 5 server, i've looked at ODBC's site but there's nothing on SQL 5 compatibility.. Soo ?

[cdkid]

Well, i dont know what's going on, but are you sure that it's set up as a MySQL Server?

[jvanegmond]

Absolutely, I'm using SQL 5.0 now, i used to run 4.1.9 i think and that worked fine. When i'm using netstat -a it says it's listening on port 3306 so i guess it's up and running.

Edited March 21, 2006 by Manadar

[cdkid]

You're using SQL? or MySQL?

[EFF]

Absolutely, I'm using SQL 5.0 now, i used to run 4.1.9 i think and that worked fine. When i'm using netstat -a it says it's listening on port 3306 so i guess it's up and running.

I have MySql server 5.0.18 and everything runs fine. I suppose you have sth wrong with your odbc driver.

[jvanegmond]

I'm running MySql server 5.0.17 . I'll try to get MySql server 5.0.18 up and running and then try again. If it doesn't i will redownload and reinstall ODBC driver's. I'll get back to you soon.

I installed 5.0.19 and it's working exactly as it is supposed to. I wrote a little SQL analyser, pretty nifty. I'll post it later i have homework now

Edited March 22, 2006 by Manadar

[jvanegmond]

So much for homework lol... No includes, ready to compile and use wherever you like.

expandcollapse popup

Global Const $ES_PASSWORD           = 32
Global Const $ES_AUTOVSCROLL        = 64
Global Const $ES_AUTOHSCROLL        = 128


$sDriver = "{MySQL ODBC 3.51 Driver}"
Local $v = StringMid($sDriver, 2, StringLen($sDriver) - 2)
Local $key = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $val = RegRead($key, $v)
If @error or $val = "" Then
    $error = 1
EndIf

Dim $error, $sql, $dontshow

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

GUICreate("MySQL Analyser", 350, 205)
GUICtrlCreateLabel("User:", 10, 15)
$username=GUICtrlCreateInput("", 65, 10, 100, 25, 128) 
GUICtrlCreateLabel("Password:", 10, 50)
$password=GUICtrlCreateInput("", 65, 45, 100, 25, 32)

GUICtrlCreateLabel("Server*:", 175, 15)
$server=GUICtrlCreateInput("", 230, 10, 100, 25, 128)

$logbox=GUICtrlCreateEdit("", 10, 75, 330, 95, 64)


$vinkje=GUICtrlCreateCheckbox("Msgbox Errors", 10, 175)

$connect=GUICtrlCreateButton("Connect", 260, 175, 80, 25)

If $error = 1 Then
    GUICtrlSetState($username,128)
    GUICtrlSetState($password,128)
    GUICtrlSetState($server,128)
    GUICtrlSetState($vinkje,128)
    GUICtrlSetState($logbox,128)
    GUICtrlSetState($connect,128)
    GUICtrlSetData($logbox,@CRLF & "MySQL ODBC Driver 3.51 not installed!" & @CRLF & "Download from http://dev.mysql.com/downloads/connector/odbc/3.51.html", 1)
Else
    GUICtrlSetData($logbox,"Welcome to MySQL Analyser" & @CRLF & "Be careful, this program will test on database 'fiets123'" & @CRLF & "Any data stored there will be lost" & @CRLF, 1)
EndIf


GUISetState()


While 1
    $msg = GUIGetMsg()

    If $msg = -3 Then Exit
    If $msg = $connect Then
        If GUICtrlRead($server) = "" Then
            GUICtrlSetData($logbox, "Server field is required" & @CRLF, 1)
        Else
            GUICtrlSetData($logbox, "Connecting.." & @CRLF, 1)
            $sql = _MySQLConnect(GUICtrlRead($username), GUICtrlRead($password), "", GUICtrlRead($server))
            If $error = "" Then
                GUICtrlSetData($logbox, "Starting queries" & @CRLF, 1)
                $dontshow = "1"
                _Query($sql, "CREATE DATABASE `fiets123`;")
                If $error = "1" Then
                    _Query($sql, "DROP DATABASE `fiets123` ")
                    $dontshow = ""
                    _Query($sql, "CREATE DATABASE `fiets123`;")
                EndIf
                $sql = _MySQLConnect(GUICtrlRead($username), GUICtrlRead($password), "fiets123", GUICtrlRead($server))
                Queries()
            Else
                GUICtrlSetData($logbox, "Unable to connect" & @CRLF, 1)
            EndIf
            $error = ""
        EndIf   
    EndIf
Wend

Func Queries()
    _Query($sql, "CREATE TABLE `aapje321` (`id` BIGINT NOT NULL AUTO_INCREMENT ,`546` VARCHAR( 255 ) NOT NULL ,`aaan` VARCHAR( 243 ) NOT NULL ,`gekkie` VARCHAR( 111 ) NOT NULL ,PRIMARY KEY ( `id` ) );")
    _Query($sql, "INSERT INTO `aapje321` ( `id` , `546` , `aaan` , `gekkie` ) VALUES ('', 'gegeven1', 'gegeven2', 'gegeven3');")
    _Query($sql, "TRUNCATE TABLE `aapje321` ")
    _Query($sql, "DROP TABLE `aapje321` ")
    _Query($sql, "DROP DATABASE `fiets123` ")
    If $error = "" Then
        GUICtrlSetData($logbox, "All queries were performed succesfully" & @CRLF, 1)
    EndIf
EndFunc

Func _Query($oConnectionObj,$sQuery)
    if isobj($oConnectionobj) Then
        Return $oConnectionobj.execute($sQuery)
    EndIf
    if @error Then
        seterror(1)
        return 0
    EndIf
EndFunc

Func _MySQLConnect($sUsername, $sPassword, $sDatabase, $sServer, $sDriver = "{MySQL ODBC 3.51 Driver}")
    $ObjConn = ObjCreate("ADODB.Connection")
    $Objconn.open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";")
    if @error Then
        seterror(1)
        return 0
    Else
        return $objconn
    EndIf
EndFunc

func _MySQLEnd($oConnectionObj)
    if isobj($oConnectionObj) Then
    $oConnectionObj.close
    return 1
Else
    seterror(1)
    return 0
    EndIf
EndFunc

Func MyErrFunc()
    If $dontshow = "" Then
        If GUICtrlRead($vinkje) = 1 Then
            $hexnum=hex($objErr.number,8)
            Msgbox(0,"","COM Error"     & @CRLF                & @CRLF & _
                         "err.description is: " & $objErr.description   & @CRLF & _
                         "err.windescription is: " & $objErr.windescription & @CRLF & _
                         "err.number is: "       & $hexnum               & @CRLF & _
                         "err.source is: "       & $objErr.source _
                        )
            GUICtrlSetData($logbox, StringReplace($objErr.description, "[MySQL][ODBC 3.51 Driver]", "[Error]") & @CRLF, 1)
            $error = "1"
        Else
            GUICtrlSetData($logbox, StringReplace($objErr.description, "[MySQL][ODBC 3.51 Driver]", "[Error]") & @CRLF, 1)
            $error = "1"
        EndIf
    EndIf
EndFunc

[EFF]

Well it doesn`t work for me

When I test my localhost, it works fine, but after this when i try to test some fake adress it says that everything is ok

[pinkfoyd]

Hi !

First of all, congratulation for this udf !

I have myself coded mysql function, but your look better, so i give it a try

but i have a probleme with your first exemple :

$sql = _MySQLConnect('root','','test','localhost')
$test = _CreateTable($sql, 'testtable', 'tt_id')
_AddRecord($sql, 'testtable', 'tt_id', 1)

$count = _CountRecords($sql,  'testtable', 'tt_id', 1)
;this executes the query "SELECT * FROM `testtable` WHERE `tt_id` = 1 then counts the records
_DropTbl($sql,'testtable')
_MySQLEnd($sql)

it return me a msg box with this :

CREATE TABLE testtable (tt_id INTERGER UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (tt_id) ) ) TYPE = InnoDB;

and autoit return this :

>Running:(3.1.1.114):C:\Program Files\AutoIt3\beta\autoit3.exe "G:\Temp\autoit\test_mysql.au3"

G:\Temp\autoit\mysql.au3 (200) : ==> The requested action with this object has failed.:

$oConnectionObj.execute ($str)

$oConnectionObj.execute ($str)^ ERROR

- I use WAMP5 (mysql)

- installed odbc driver with the provided link in your topic

- for test $sql (ligne 1 of your exemple) not return me a 0

- the table test exist in my database, like testtable, tt_id with value of 1 ( so the create function work well)

- if i run again the script with a another add record, this work well too ( have got 2 record in the base) but i got the same error :

G:\Temp\autoit\mysql.au3 (131) : ==> The requested action with this object has failed.:

$oconnectionobj.execute ($query)

$oconnectionobj.execute ($query)^ ERROR

Thanks if you can help me

PS : if i run the script of Manadar ( 2 post before ) all working well without any error message

Edited March 23, 2006 by pinkfoyd

[jvanegmond]

a "The requested action with this object has failed." Often pops up when the query itself is wrong, you might want to try running the same query in a phpMyAdmin environment. Or try to find the mistake in the query yourself if you have some knowledge of SQL.