MySQL UDFs

[JonnyThunder]

Ello!

I think the UDF is great, but a little overcomplicated. Take the PHP implementation of mySQL for instance - although there are a stack of mySQL commands - you'd only use a handful of them....

mysql_connect

mysql_query

mysql_fetch_array

mysql_close

At a push, you may also use these too...

mysql_insert_id

mysql_num_rows

mysql_affected_rows

Like I said, I like your UDF - but I'd personally limit it to only the commands that are needed to perform operations. For instance, there's no real need for a delete function when this can be acheived with a query command.

[barribar]

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

all of this are correct for me

any thing else?

sorry for my bad language

thanx a lot

[weaponx]

EDIT: Problem is with MySQL server version. Error exists in 4.1.13, it works fine in 5.0.45.

_GetColNames is not working, an empty array is returned with the correct column count and number of elements. I also tried a manual query to get the column names using _MySQLExec. The table exists and the query was tested in MySQL Query Browser. Here is my code:

expandcollapse popup

#include "mysql.au3"
#include <array.au3>

;username, password, db, address
$SQLOBJ = _MySQLConnect("user","pass","192.168.1.10","housing_energy")

Switch @ERROR
    Case 0
        ;MsgBox(0,"","Connection successful")
    Case 1 
        MsgBox(0,"","Error","Error opening connection")
    Case 2
        MsgBox(0,"","MySQL ODBC Driver not installed.")
EndSwitch

;Retrieve array of all table names in database
$aTables = _GetTblNames($SQLOBJ)
If NOT @ERROR Then
    ;Dump array to console and show table count
    ConsoleWrite("########## TABLES (" & _CountTables($SQLOBJ) & ") ##########" & @CRLF)
    For $X = 1 to $aTables[0]
        ConsoleWrite($aTables[$X] & @CRLF)
    Next
Else
    MsgBox(0,"Error","Something Went Wrong!")
EndIf

ConsoleWrite(@CRLF)

;Retrieve array of all column names in table
$ret2 = _GetColNames($SQLOBJ, "hwap_dcf4")
If NOT @ERROR Then
    ;Dump array to console and show column count
    ConsoleWrite("########## COLUMNS (" & _GetColCount($SQLOBJ, "hwap_dcf4") & ")##########" & @CRLF)
    For $X = 1 to $ret2[0]
        ConsoleWrite($ret2[$X] & @CRLF)
    Next
Else
    MsgBox(0,"Error","Something Went Wrong!")
EndIf

;Retrieve array of all column names in table
$var = _MySQLExec($SQLOBJ, "SHOW COLUMNS FROM hwap_dcf4;")
If NOT @ERROR Then
    ;Dump column names to console and show column count
    ConsoleWrite("########## COLUMNS (" & _GetColCount($SQLOBJ, "hwap_dcf4") & ")##########" & @CRLF)    
    With $var
        While NOT .EOF; is equal to "Do this until we run out of records"
            ConsoleWrite(.Fields('Field').value & @CRLF)
            .MoveNext;Move to the next row
        WEnd
    EndWith
Else
    MsgBox(0,"Error","Something Went Wrong!")
EndIf

_MySQLEnd($SQLOBJ)

EDIT: I did some digging and it looks like the same problem is shown here:

http://bugs.mysql.com/bug.php?id=27409

The value isn't actually being returned empty, its just binary encoded. One solution says to add this to the connection string:

charset=utf8;

Unfortunately this did not work for me.

Edited April 24, 2008 by weaponx

[aNewLyfe]

hi, i have this problem and i dont know much about mysql,

### COM Error ! Number: 80020009 ScriptLine: 27 Description:[MySQL][ODBC 3.51 Driver]Host 'My IP' is not allowed to connect to this MySQL server

i wrote user name, pass right dont know what is the problem :S

any suggestion ?

[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

hi, i have this problem and i dont know much about mysql,

### COM Error ! Number: 80020009 ScriptLine: 27 Description:[MySQL][ODBC 3.51 Driver]Host 'My IP' is not allowed to connect to this MySQL server

i wrote user name, pass right dont know what is the problem :S

any suggestion ?

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

your host is not allowed to connect to the server...

[aNewLyfe]

thanks for the answer madman.

now its working well on web, but another problem, i cant connect to localhost.

I am running Microsoft sql server.

when i try to connect localhost it says :

### COM Error ! Number: 80020009 ScriptLine: 27 Description:[MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on '127.0.0.1' (10061)

any help ?

[Jango]

thanks for the answer madman.

now its working well on web, but another problem, i cant connect to localhost.

I am running Microsoft sql server.

when i try to connect localhost it says :

### COM Error ! Number: 80020009 ScriptLine: 27 Description:[MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on '127.0.0.1' (10061)

any help ?

This is NOT for Microsoft SQL Server but for MySQL. If you don't know what is MySQL do a google search like everyone.

By the way if i have author permission (if not i will remove), here is a modified version for Micro$oft SQL Server 2005

#cs
    Function Name:    _SQLConnect
    Description:      Initiate a connection to a MySQL database.
    Parameter(s):     $username - The username to connect to the database with.
    $password - The password to connect to the database with. $Database - Database to connect to.
    $server - The server your database is on.
    $driver (optional) the ODBC driver to use (default is "{MySQL ODBC 3.51 Driver}"
    Requirement(s):   Autoit 3 with COM support
    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.
    Author(s):        cdkid
    Modified :        jango
#ce

Func _SQLConnect($sUsername, $sPassword, $sDatabase, $sServer, $sDriver = "{SQL Native Client}")
    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)
    If @error Then
        SetError(1)
        Return 0
    Else
        Return $ObjConn
    EndIf
EndFunc   ;==>_SQLConnect

[aNewLyfe]

right, right

thanks, my bad

[dfHunter]

if database is offline, script, when try to connect to it, crashes and stops, how can i make it to continue work?

[Xand3r]

if database is offline, script, when try to connect to it, crashes and stops, how can i make it to continue work?

try to manually get a socket to the server and see what the server tells you

[Jango]

if database is offline, script, when try to connect to it, crashes and stops, how can i make it to continue work?

Use Objevent (see AutoIt Help file) there is an example

[Jango]

if database is offline, script, when try to connect to it, crashes and stops, how can i make it to continue work?

Use ObjEvent (see AutoIt Help file) there is an example

[m0nk3yI3unz]

hey cdkid - Again, thanks for these (not sure if ive posted in this thread before).

Quick question: I downloaded the ODBC 5.1.4 drivers a few minutes ago, but Im not sure how to implement them into your code. I noticed you can change the driver name in the connect sequence, but im not sure what to put.

Ill keep trying with different things, but in the event I dont find it, I need some help getting it to work

Thanks for everything!

-Monkeh

EDIT: WOW that was easy to find, actually - I just went into the connect function, went into the registry path that was in it, and found the driver info - it was:

{MySQL ODBC 5.1 Driver}

THANKS!!!

Edited July 2, 2008 by m0nk3yI3unz

[weaponx]

Look at the function call:

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

Just change $sDriver to the new version name.

[Mattraks]

I always get this when i try to run it

C:\Program Files\AutoIt3\Include\MySQL.au3 (30) : ==> 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
>Exit code: 1   Time: 2.728

Edit: I tried with the one posted and on the svn site and it always shows that

Func _MySQLConnect($sUsername, $sPassword, $sServer, $sDatabase, $iPort = 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=" & $iPort)
    If @error Then
        SetError(1)
        Return 0
    Else
        Return $ObjConn
    EndIf
EndFunc   ;==>_MySQLConnectoÝ÷ Ù8Z¶Ëayû§rب©Ý¶¬ÈhÂ&¦q©ex"µ«­¢+Ù}5åME1
½¹¹Ð ÅÕ½ÐíÕÍɹµÅÕ½Ðì°ÅÕ½ÐíÁÍÍݽÉÅÕ½Ðì°ÅÕ½ÐìÄÈܸÀ¸À¸ÄÅÕ½Ðì°ÅÕ½ÐíµÅÕ½Ðì¤

And it still gives the error. Not sure why. Anyone want to help?

Edited July 21, 2008 by Mattraks

[Mattraks]

Bump

Anyone?

[SiteMaze]

Try the inet adress without http://.

I am inserting thousands of records to the database and need fast insertion.

One technique is to use Prepared Statements so that the mysql server can cache the sql queries.

Have anyone tried to use AutoIT with prepared statements?

[blenkhn]

I have been looking at your mysql udf and it looks great.

Is there a way to read the results of the query into a 2d array? Is there a code snippet or link I could see?

[theimer]

Hello,

is there any way how to prevent of interupting of autoit script after error during _MySQLConnect?

I not use local MySQL DB, but DB on internet. I can test ping for DB server, but server can be ok, but MySQL servie can be down and in this case I get error (The requested action with this object has failed) and autoit script is terminated. I need to catch error and prevent terminating of autoit script.

Can anybody help me?

Thanks for any ideas

Libor

[mianz]

i'm using the udf from kan2.sytes.net/publicsvn/mysql and mysql-connector-odbc-noinstall-5.1.5-win32

i have change the mysql.au3

this line for version 5.1

-> Func _MySQLConnect($sUsername, $sPassword, $sServer, $sDatabase, $iPort = 3306, $sDriver = "{MySQL ODBC 5.1 Driver}")

i have this error.. can some1 help?

C:\Program Files\AutoIt3\Include\mysql.au3 (30) : ==> 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