MySQL UDFs

[pcprint]

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)
oÝ÷ Ú+wöÌ(®Øm¢Úò¿êº^#f笲)ào'+yק¶&¥³*.槺ǫ©¥«,u¹Úæ§vÇ«½êâbr±æ¥æÉ×­j)lj·¢ºÞrØƧrçyËm¢Ø^Øk¢è!jiÁæéj±ú+Ê«r^Â¥²Ø­§^r©ë^±éìz+nú+¶éÚµérv'gßÛp¢¹±©è}û!¢bßÛÞ~§vØZµëÞ'âºÇºÚ"µÍÔ]YJ    ÌÍÜÜ[   ][ÝÔÑSPÕÙQÓH[ÙÈÒTHÙ[YHHÜ[    ][ÝÊB

that also comes back with exactly the same error

[clearguy]

For the error in fact it is about the

_MySQLConnect($UN,$PW,$DB,$SVR)

.

The function can simply not connect to the server with your infos, check it.

[pcprint]

For the error in fact it is about the

_MySQLConnect($UN,$PW,$DB,$SVR)

.

The function can simply not connect to the server with your infos, check it.

that's why i'm thinking it won't work with my version of mysql (which i cant do anything about) as the username, password, database and server are all correct, and it doesn't matter whether i enter them directly or via variables same error.

[=sinister=]

Um, there's no _MySQLTableExists() again. =P I really need that UDF, it would be very usefull to me.

Keep up the great work,

=sinister=

[clearguy]

Try to use the basic _Query function to find out if a table exists...

Edited November 27, 2006 by clearguy

[NeoFoX]

I had a little problem with MySQL 5.xx to in the beginning..

Same with the _Query() so I tested a bit and came to the following:

_Query($sql,"SELECT * FROM `test` WHERE id=16")

The ` did the job for me..

Neo

[Guest JRowe_1]

Just as a side note, for anyone looking... it took me 2+ hours to discover this syntax.

When you want to assign a variable to a query, do this:

_Query($myConnectionObject, "SELECT someField FROM someTable WHERE someField = ' " & $myVariable & "' ")

The & concatenates the string, and the extra quotation marks tell the script that your variable is outside of the _Query parameter string, e.g. it substitutes the value of your $myVariable for the actual characters of $myVariable (which doesnt do anyone any good.)

If you try to pass $myVariable directly into the parameters, it will fail, and you're looking at a whole bunch of wasted time trying to figure it out. It's very easy

Next up, a simple explanation of how to convert the result of your SQL query into readable values, as opposed to the raw object returned.

The following snippet is fill in the blanks. It reads text from an input box, then tries to find that in the table/field location you specify:

(Note: This code isn't functional by itself, it's meant as an assisting reference)

;; this grabs the text from the input box with the handle $TextInputField
$myVariable = String(GUICtrlRead($TextInputField))

;;This is your connection definition
    $connect = _MySQLConnect('MyUsername', 'MyPassword', 'Database name', 'IP, Computer name, or URL')
;; This is your query definition
        $query = "SELECT someField FROM someTable WHERE someOtherField = '" & $myVariable & "' "

;; This is where the connection is actually used to run the query. The result is a raw MySQL object.
        $myQueryObject = _Query($connect , $query)


;; The $myQueryObject is raw data... it must be parsed into something readable for you to use it. The With keyword is Autoit's way of handling object variables easily.
;; $myQueryObject.Fields('someField').Value tells Autoit to look at the value of the someField variable in the Fields part of the $myQueryObject object.
 
    With $myQueryObject
        While NOT .EOF;; Runs hrough all available matching values until there are none left
            MsgBox(0, "", $myQueryObject.Fields('someField').Value);;displays the result of the previous SQL query in a message box

                ;;tells the script to move to the next matching value, if there is one, otherwise
            .MoveNext
            
        ;; the While loop ends and
        WEnd 

        ;; the With statement ends as well
    EndWith

This isn't functional, but it should be enlightening, at least a little bit helpful.

Thanks very very much for this UDF, cdkid, it's phenomenal.

[masonje]

The MySQL ODBC 3.51 DRIVER IS REQUIRED FOR THIS

it can be downloaded here: http://dev.mysql.com/downloads/connector/odbc/3.51.html

Below is a .ZIP called "ODBC_DRIVER_SETUP" it has the au3 for driver setup, read _ReadMe_.txt to see how to make it work.

Still working on getting this to work for me, but I thought I could contribute this much. The below code is a much more efficient way to install the ODBC drivers. The exe you download from the link provided by cdkid extracts the MSI into %temp%. Run the exe, don't complete the install but go to the temp dir and copy the msi into C:\ and compile the script below.

$MySQLvRead = RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\MySQL AB\MySQL Connector/ODBC 3.51", "Version")
$MySQLv = "3.5.12"

if $MySQLvRead < $MySQLv Then
    FileInstall("C:\mysql_odbc.msi", @TempDir & "\mysql_odbc.msi", 1)
    RunWait("msiexec /i " & @TempDir & "\mysql_odbc.msi /quiet")
    FileDelete(@TempDir & "\mysql_odbc.msi")
EndIf

You could create a <MySQLCheck.au3> include to the top of your MySQL scripts and you would be all set.

[mmavipc2]

very usefull

[mist3r]

Hey,

Really really like the work and effort you've put into making this possible. My question might be 1 many newbies like me wonder.

I'm mad about PHP and MySQL and love Autoit. I've started to develop a website within my company whereby I can keep track of several different routines.

Im a ICT tech support guy, we get calls all the while and it's logged in a paper book. Im wanting to make that available on a website internally. After discovering your UDF, i thought what a good idea to make a program using Autoit that will pull up the nitty gritty details of a client machine they are reporting the problem on and post it to us using that along side the website.

I was pretty stupid to not realise that the MySQL ODBC had to be installed on every machine. Is there any other way of doing this?

Im totally new to all this ODBC business and COM. I was following a few tutorials on the Microsoft Website on making ActiveX scripts. I copied and pasted the examples and none of them worked.

Im trying to find a good solution that would incoporate the use of the website and a program.

If I created a small Autoit Script that added a record and placed it on a network drive, then ran it from workstations around the site without ODBC being installed, would it work?

Sorry several questions there...

Thanks for any advice.

[GrimSage]

I tried your UDF and was Unable to get it to work with my setup. The reason being I have mysql is running on a different port. So I modified your script, and thought I would share it with everyone else incase they ran into the same issue. This might not even be nessicary. I might have done all this and there was an easier way, but here it is if it is needed.

CODE

Func _MySQLConnect($sUsername, $sPassword, $sDatabase, $sServer, $sPort = "3306", $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

SetError(2)

Return 0

EndIf

$ObjConn = ObjCreate("ADODB.Connection")

$Objconn.open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";PORT=" & $sPort & ";")

If @error Then

SetError(1)

Return 0

Else

Return $ObjConn

EndIf

EndFunc ;==>_MySQLConnect

[JoTalbot]

Please help. Not work.

$sql = _MySQLConnect('root','','jotalbot_script','localhost')
If $sql = 0 Then
    MsgBox(0, "Clipboard contains:", @error)
EndIf

$var = _Query($sql,"SELECT * FROM table")
With $var
    While NOT .EOF;
MsgBox(0, "Value:", .Fields('col1').value)
.MoveNext
WEnd
EndWith

_MySQLEnd($sql)

And then this:

I read all forum, install all step by step... But not working

p.s. _Query($sql, "INSERT INTO `table`(`col1`,`col2`,`col3`,`col4`) VALUES ('ddd', 'sss', 'vvv', 'ttt');") - This string works perfect.

[clearguy]

Please help. Not work.

$sql = _MySQLConnect('root','','jotalbot_script','localhost')
If $sql = 0 Then
    MsgBox(0, "Clipboard contains:", @error)
EndIf

$var = _Query($sql,"SELECT * FROM table")
With $var
    While NOT .EOF;
MsgBox(0, "Value:", .Fields('col1').value)
.MoveNext
WEnd
EndWith

_MySQLEnd($sql)

And then this:

I read all forum, install all step by step... But not working

p.s. _Query($sql, "INSERT INTO `table`(`col1`,`col2`,`col3`,`col4`) VALUES ('ddd', 'sss', 'vvv', 'ttt');") - This string works perfect.

Look i f you have really a table called "table", it is something wrong in your data, I got this same message when I typed wrong request - or - a wrong table name.

[zeroZshadow]

Looks nice, now i can finealy try something new again with autoit.

but there is a small problem.

I installed everything as u stated above, but my compiler returns this error

>Running:(3.2.1.14):C:\Program Files\AutoIt3\beta\autoit3.exe "D:\DEV\sqltest.au3"

C:\PROGRA~1\AutoIt3\beta\Include\mysql.au3 (27) : ==> The requested action with this object has failed.:

$Objconn.open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";")

$Objconn.open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";")^ ERROR

+>16:56:01 AutoIT3.exe ended.rc:0

+>16:56:02 AutoIt3Wrapper Finished

>Exit code: 0 Time: 1.722

I hope u guys have a solution of what i am doing wrong

Thanks, ZeroZ

p.s. here is the simple code i used

#include <mysql.au3>
$oSQL = _MySQLConnect("zerocore_test","nottellingyou","zerocore_database","http:\\www.zerocore.com")
if @error Then
    MsgBox(0,"ERROR","ERROR")
EndIf

[clearguy]

Try the inet adress without http://.

[zeroZshadow]

Sorry i didn't post it, but i found that out a week ago XD

now i have another problem. how can i count an amount of results found with a query ?

so if i do :

$sQuery="SELECT * FROM Users WHERE id = '"&$id&"' AND password = '"&$password&"';"

that would return nothing if the user and id arent in the database and would return the result if it is.

but i just want to know if its there

thanks in advance

[lsakizada]

if anyone has any suggestions for more functions i'd love to hear em

Thanks for the UDF.

Can you please add functions to use stored procedures?. I was looking for over the thread but can not find input.

Edited March 5, 2007 by lsakizada

[zeroZshadow]

reply on my own question..

just use "if $result.EOF =0 then" to check if it exists.

suggestions for the udf.

a list of properties for the raw mysql object. and maby a way to use it without the dll (even some external dll is fine, but now one i need to install)

[erikson]

hi

can you help me with this?

http://www.autoitscript.com/forum/index.php?showtopic=42311

[zeroZshadow]

i can help u with that.

u could OR use the UPDATE command with a query or just overwrite the like by storing the value's, then deleting and rewriting the line with the extra code