MySQL UDFs

[DavidB]

I've been away from the whole development scene for almost 18 months so bear with me as I try and get my head round using AutoIt and MySQL.

I thought I would start simply by using cdkid's UDFs and as far as I can tell I've got everything installed and set up OK. Well that is if I don't try and use an AutoIt script to perform a simple connect and interrogate of a database.

You'll recognise the code as being one of cdkid's examples where certain details have been changed for obvious reasons.

CODE

#include "mySQL.au3"

$sql = _MySQLConnect('sa','sa','','localhost')

$dbs = _GetDbNames($sql)

For $i in $dbs

MsgBox(0,'',$i)

Next

_MySQLEnd($sql)

The trouble is that as simple as it is I'm getting an error in the _MySQLConnect function in mySQL.au3 on the line that begins $Objconn.open

The message box quotes the line number for the error - 27 - then the code line twice and finishes with Error: The requested action with this object has failed.

Apologies for not posting a picture but I haven't got the hang of doing that just yet.

Any help with this problem would be much appreciated as I feel that once I can get connections working OK then most other things will fall in to place.

Thanks in advance.

David John Bird

[ironmanexe]

Me luv u long time!

[PartieHonteuse]

I've been using (or trying to use) this UDF for a while now and am suddenly getting an error when trying other things than the examples listed on the first pages. I haven't seen any errors such as mine going through the 15 pages worth of posts. When going to the "tools" menu in scite and selecting "go" this is the message I receive in gold letters pretty much right after the inputbox

"Error in my_thread_global_end(): 1 threads didn't exit" I looked that error up online and a lot of people mention a fix for libmysql.dll but the hosting provider is on a linux box so therefore no reason to have libmysql.dll and also they said to test the error to see if you fixed it is to create a phpinfo() script and run it and that same message should appear but when I do the phpinfo() i do not receive that error in the output.

Here's my code to check to make sure I'm not doing something way wrong (which is very highly likely):

expandcollapse popup

#include <mysql.au3>
$objErr = ObjEvent("AutoIt.Error","MyErrFunc")

$sqldomain = "mydomain.com"
$sqldb = "mydb"
$sqluser = "username"
$sqlpass = "password"
$table_users = "db_users"
$table_config = "db_config"

$sqlcon = _MySQLConnect($sqluser,$sqlpass,$sqldb,$sqldomain)

;let's add a user to the DB.
$user = InputBox("User", "Please enter the username to add.", "", "", _
    -1, -1, 0, 0)
Exit

_Query($sqlcon, "INSERT into rfid_users (username) VALUE ('" & $user & "')")


_MySQLEnd($sqlcon)


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

Thanks in advance for any help or information.

PartieHonteuse

Edited January 17, 2012 by PartieHonteuse

[TheCreator]

Hi,

Looks a good start; I presume you have to have MySQL for this, and I presume not free?. Perhaps only 0.01% of those who read it? Don't be too despondent about replies; just work on if it is of benefit to you or others over time.

[Of interest rather than any utility, I wonder if you can convert the data to a table dump to csv with your UDF queries, and read/ write it to sqlite database with my functions?]

Best, Randall

Lots of presumptions. Mysql is free, but your first was correct, though, I shouldn't feel the need to tell you this, I don't know why I am.

[jokke]

A minor uppdate to cdkids _MySQLConnect() with server port.

Dont know if this is replied inn any other post ( was to much to read )

Replace these lines inn UDF:

Line 19 replace with this:

Func _MySQLConnect($sUsername, $sPassword, $sDatabase, $sServer, $sPort = "3306", $sDriver = "{MySQL ODBC 3.51 Driver}")


Line 27 replace with this:

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

Now you are able to connect with a syntax like this:

#include <Mysql.au3>

Dim $uName = "username", $uPass = "password", $uDatabase = "database",  $uServerip = "ServerIP", $uServerport = "ServerPORT"

_MySQLConnect("$uName","$uPass","$uDatabase","$uServerip","$uServerport")
...

[SuperFlyChetGuy]

I'm getting this:

[jokke]

I'm getting this:

..not beeing able to connect.

Download Mysql administration, then make sure you are able to connect to the server with one of its tools.

And btw, the default UDF does not support any other connection port, on page 15 iwe posted what you need to edit, to make UDF able to connet on another port.

[cdkid]

7/31/07 - made first update in a long time, added ability to use specific ports isntead of 3306

~cdkid

[Stormrage]

UP!

Great update,but with the newest MySQL ODBC Driver still gives error (number 2, which means script cant connect to ODBC, newest is 3.51.17, at least at me this occours, but im absoludly not sure if really this is the problem)

Edited July 31, 2007 by Stormrage

[DaleHohm]

Sorry if I'm being dense here, but I am not following the install instructions in the base post very well. This may well be answered here already, but I didn't look through all 16 pages of replies...

I get that I have to install the ODBC connector from mySQL. I did that. Then I see your message that says:

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

In that there is the readme file and a .au3 -- the readme says:

For this to work, you must download the driver EXE from the site (above in my post there is a link) Then move it to c:\, and rename it "driversetup.exe".

Then compile, I couldn't post the compiled version because with FileInstall it's 2.49 MB

~cdkid

When you talk about the "driver EXE" are you referring to the MySQL Connector/ODBC 3.51? I used an MSI file to install it... I didn't see a "driver EXE". You then say "Then compile" -- compile what? The au3 in the zipfile? Why?

As you can see, I'm pretty confused. I'm hoping you can help me understand.

thanks,

Dale

Edited August 25, 2007 by DaleHohm

[user52]

any way to use mysql_insert_id()?

mysql_insert_id();

http://www.php.net/mysql_insert_id

$query = "";

$oconnectionobj.execute($query);

thanks

[cdkid]

Sorry if I'm being dense here, but I am not following the install instructions in the base post very well. This may well be answered here already, but I didn't look through all 16 pages of replies...

I get that I have to install the ODBC connector from mySQL. I did that. Then I see your message that says:

In that there is the readme file and a .au3 -- the readme says:

When you talk about the "driver EXE" are you referring to the MySQL Connector/ODBC 3.51? I used an MSI file to install it... I didn't see a "driver EXE". You then say "Then compile" -- compile what? The au3 in the zipfile? Why?

As you can see, I'm pretty confused. I'm hoping you can help me understand.

thanks,

Dale

Dale, that setup is just to do an auto-setup, not really necessary. It's more for redistributing, I.E. if they don't have the setup, instead of sending them a link, just send that compiled au3 script and it'll set it up for em. and by Driver EXE yes, i did mean the MSI installer for ODBC. Sorry I never realized how poorly i wrote that . I hope this clears it up a bit.

and user52, i'm sorry i don't know.

~cdkid

Edited September 7, 2007 by cdkid

[cdkid]

I was thinking about this, and I've decided I'll probably put this into an SVN so me and anyone else who would like to can work on it. I'll be getting this ready to submit for an include into AutoIt soon. Have to rename some functions & parameters and write up the helpfiles. When I get the repository set up i'll stick the link up on here so anyone can get it. If you want to be able to commit, or want to help send me a PM. If anyone's wondering what SVN is it stands for SubVersioN as far as i know . If you want more info do a google search for "Tortoise SVN". (that link is the first result that actually points at the tortoise SVN site).

~cdkid

(sorry about the double post, seemed appropriate)

Edited September 7, 2007 by cdkid

[cdkid]

The SVN is set up. If you have installed tortoise SVN then make a new folder anywhere, right click it & click "SVN Checkout" put kan2.sytes.net/publicsvn/mysql in there. Then anytime you want to check if there's a new ver, just right click the folder and click 'svn update'. If you want to help me get it ready to submit for inclusion in standard UDFs send me a PM.

NOTE: Several functions have been renamed/had their parameters changed (in the SVN only, not in this thread yet) please look over the file and you'll see some examples of this. ex: _Query has been renamed _MySQLExec. _CreateTable has been renamed _MySQLCreateTbl and had some "yes" or "no" parameters changed to boolean values. All future updates will be to the SVN and not in this thread until I submit it for inclusion.

~cdkid

Edited September 8, 2007 by cdkid

[user52]

any way to use mysql_insert_id()?

mysql_insert_id();

http://www.php.net/mysql_insert_id

$query = "";

$oconnectionobj.execute($query);

thanks

can you translate this?

thanks

mysql_insert_id()

http://forums.mysql.com/read.php?38,98672,98868#msg-98868

CODE

After executing your Insert-Command (C#, Connection is stored in conDBConnection):

...

string strSQLSelect = "SELECT @@IDENTITY AS 'LastID'";

MySqlCommand dbcSelect = new MySqlCommand(strSQLSelect, conDBConnection);

MySqlDataReader dbrSelect = dbcSelect.ExecuteReader();

dbrSelect.Read();

int intCounter = Int32.Parse(dbrSelect.GetValue(0).ToString());

dbrSelect.Dispose();

conDBConnection.Close();

MessageBox.Show("Test: " + intCounter.ToString());

"SELECT @@IDENTITY AS 'LastID'"

[celtic]

Hie ! this is my first post here ! I Hope you'll answer...

The french AutoIt communauty is finding a new ISP...

At first : thanks to all of you ! AutoIt is great ! AutoIt is what I nead ! Autoit is my way for a long time !

Thanks cdkid for your functions with Mysql ! Should be on the official manual !

I encouter some problem with the error codes from <mysql.au3> :

$connexion = _MySQLConnect( $sUsername,$sPassword,$sServer,$sDatabase,$iPort,$sDriver)
If $connexion = 0 Then
    If @Error = 1 Then
        MsgBox (4096, "Erreur de connexion", $connexion)
        _MYSQLEnd($connexion)
        Exit
    ElseIf @Error = 2 Then
        MsgBox (4096, "Absence de Driver ODBC", $connexion)
        _MYSQLEnd($connexion)
        Exit
    EndIf
Else

I've tried that while disconnecting Mysql Server... It doesn't work !

Return Value(s): On success returns the connection object for subsequent functions. On failure returns 0 and sets @error

@Error = 1

Error opening connection

@Error = 2

MySQL ODBC Driver not installed.

Requirement(s): Autoit 3 with COM support, MySQL Driver used in the $sDriver parameter

Remark(s): Be sure to call _MySQLEnd at the end of your scripts to be sure you are disconnected.

Author(s): cdkid

Did I miss something ?

That the error output :

==> The requested action with this object has failed.:

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

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

Error in my_thread_global_end(): 1 threads didn't exit

->15:22:18 AutoIT3.exe ended.rc:1

+>15:22:19 AutoIt3Wrapper Finished

>Exit code: 1 Time: 7.777

I understand the error, I did it to test ! But how do I use "@Error" to inform my final user ?

PS : forget my poor English ! I can "read that fucking manual", it's the minimum !

Edited October 30, 2007 by celtic

[guwguw]

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

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

I know this is old, but just in case other people encounter a similar error: The SQLserver address should be entered here as an IP address, not as a website (http:\\...) address.

P.S. Sorry guys, I hadn't used this browser on this computer for months and just found that the cache had not been refreshed right now ...

Edited November 11, 2007 by guwguw

[Leagnus]

Everything's cool! Thanks for such amazing udf!

One thing's left: to have static IP to be permitted on remote server...

[barribar]

when I run this code I have :

(4) : ==> Only Object-type variables allowed in an "With" statement.:

#include 'MySQL.au3'
$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)

Any help?

[Xand3r]

well:

0. MAKE SURE THAT YOU HAVE INSTALLED THE ODBC DRIVER

1. make sure that the login data is correct...

2. make sure that the mysql server allows your pc to connect to it

3. make sure your mysql user is allowed remote connections

4. make sure that `mytable` and `user` really exist

that's about it