cdkid

MySQL UDFs

379 posts in this topic

#241 ·  Posted

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.

Share this post


Link to post
Share on other sites



#242 ·  Posted

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

all of this are correct for me

any thing else?

sorry for my bad language

thanx a lot

Share this post


Link to post
Share on other sites

#243 ·  Posted (edited)

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:

#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 by weaponx

Share this post


Link to post
Share on other sites

#244 ·  Posted

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 ?


:alien: ~ Every Living Thing is a Code Snippet of World Application ~ :alien:

Share this post


Link to post
Share on other sites

#245 ·  Posted

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


Only two things are infinite, the universe and human stupidity, and i'm not sure about the former -Alber EinsteinPractice makes perfect! but nobody's perfect so why practice at all?http://forum.ambrozie.ro

Share this post


Link to post
Share on other sites

#246 ·  Posted

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 ?


:alien: ~ Every Living Thing is a Code Snippet of World Application ~ :alien:

Share this post


Link to post
Share on other sites

#247 ·  Posted

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

Share this post


Link to post
Share on other sites

#248 ·  Posted

right, right :)

thanks, my bad :)


:alien: ~ Every Living Thing is a Code Snippet of World Application ~ :alien:

Share this post


Link to post
Share on other sites

#249 ·  Posted

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

Share this post


Link to post
Share on other sites

#250 ·  Posted

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

Only two things are infinite, the universe and human stupidity, and i'm not sure about the former -Alber EinsteinPractice makes perfect! but nobody's perfect so why practice at all?http://forum.ambrozie.ro

Share this post


Link to post
Share on other sites

#251 ·  Posted

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

Share this post


Link to post
Share on other sites

#252 ·  Posted

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

Share this post


Link to post
Share on other sites

#253 ·  Posted (edited)

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

Monkeh.

Share this post


Link to post
Share on other sites

#254 ·  Posted

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.

Share this post


Link to post
Share on other sites

#255 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

#256 ·  Posted

Bump

Anyone?

Share this post


Link to post
Share on other sites

#257 ·  Posted

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?

Share this post


Link to post
Share on other sites

#258 ·  Posted

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?

Share this post


Link to post
Share on other sites

#259 ·  Posted

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

Share this post


Link to post
Share on other sites

#260 ·  Posted

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

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