Jump to content
Sign in to follow this  
cdkid

MySQL UDFs

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

$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 by Manadar

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


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!

Share this post


Link to post
Share on other sites

You're using SQL? or MySQL?


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!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by Manadar

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by pinkfoyd

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
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
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...