MySQL UDFs

[mko]

Hi together

Seems there is a Bug in ODBC 3.5.1 with mysql.au3

Environmet: odbc 3.5.1, mysql 5.1.14, 5.1.31

This runs fine...

$SQLCode = "SELECT count(b.eingang) AS anz  FROM  berechnet b" 
  $TableContents = _Query($SQLInstance,$SQLCode)
  MsgBox(0,"",$TableContents.Fields("anz").value )

If i want to use Mysql SUM function i got an error

$SQLCode = "SELECT sum(b.eingang) AS anz  FROM  berechnet b" 
  $TableContents = _Query($SQLInstance,$SQLCode)
  MsgBox(0,"",$TableContents.Fields("anz").value )

MsgBox(0,"",$TableContents.Fields("anz").value )

MsgBox(0,"",$TableContents.Fields("anz").value ^ ERROR

Functions which seems to be OK:

count, min, max, std, variance

Functions which are not working

sum, avg

I have changened now to MYSQL ODBC 5.1

• Installing Mysql ODBC 5.1
• Copy mysql.au3 to mysql51.au3
• Edit in Mysql51.au3 first Function declaration to

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

• Change include in mainprogram to mysql51.au3

Now Select with Mysql Functions like sum and avg are running fine Edited July 4, 2009 by mko

[blitzkrg]

Hopefully someone can help me with this.

All the functions work for me except the query function.

if i want a list of db's it works. or to get a list of table names.. that works.

so i know my connection data is right

and i've verified my queries are valid. yet every single time, i get an error returned and i cant figure out why..

here is the function from mysql.au3

Func _Query($oConnectionObj, $sQuery)
    If IsObj($oConnectionObj) Then
        Return $oConnectionobj.execute ($sQuery)
    EndIf
    If @error Then
        SetError(1)
        Return 0
    EndIf
    
EndFunc   ;==>_Query

and here is a simple query that returns an error (error 1)

$var = _Query($sql,"SELECT * FROM users")

Any ideas?

[Negat1ve]

Oh god !!!! This is AWESOME WORK ! On of best when i even saw ! VERY BIG THANKS =))))))))

[blitzkrg]

Oh god !!!! This is AWESOME WORK ! On of best when i even saw ! VERY BIG THANKS =))))))))

does your _query function work? if so what o/s are you running and what version of autoit?

thanks

[ilReverendli]

124 views and only one reply... anyone got some ideas on what else i should add or any other feedback?

cdkid,

Thanks man for posting this UDF, this thing rocks, its opening up new ideas. I have one request as for feedback however. I consider myself a noob at this still, even though I have made some pretty slick tools with autoit (Provisioning system) and since I am pretty much teaching myself as I go along, It would be extremely helpful if of you could go alittle deeper into the "_query" command, perhaps line by line comment, explaining what its doing. - I know that's alot to ask for but if you have the time, It would be much appreciated.

Thanks again dude.

-Dave

[MrMitchell]

Hopefully someone can help me with this.

All the functions work for me except the query function.

if i want a list of db's it works. or to get a list of table names.. that works.

so i know my connection data is right

and i've verified my queries are valid. yet every single time, i get an error returned and i cant figure out why..

here is the function from mysql.au3

Func _Query($oConnectionObj, $sQuery)
    If IsObj($oConnectionObj) Then
        Return $oConnectionobj.execute ($sQuery)
    EndIf
    If @error Then
        SetError(1)
        Return 0
    EndIf
    
EndFunc   ;==>_Query

and here is a simple query that returns an error (error 1)

$var = _Query($sql,"SELECT * FROM users")

Any ideas?

I had the same problem...did some tweaking and finally got it working. As far as I could tell there are two options for output, either a string or an array. Both worked for me but I decided to go with the string route. I had to modify one of the functions in the MySQL.au3 UDF, then add another. The code is messy right now and even though I got it working, I am still having trouble figuring out how exactly. I'll try to clean it up some more then get it posted.

It involves using the GetString (for string) or GetRows (for array) method against an ADODB.RecordSet. You open the connection then use one of those methods to dump the data into either a string or an array. My version isn't very versatile, but it gets the job done...for the most part.

[panlatv]

Hopefully someone can help me with this.

All the functions work for me except the query function.

if i want a list of db's it works. or to get a list of table names.. that works.

so i know my connection data is right

and i've verified my queries are valid. yet every single time, i get an error returned and i cant figure out why..

here is the function from mysql.au3

Func _Query($oConnectionObj, $sQuery)
    If IsObj($oConnectionObj) Then
        Return $oConnectionobj.execute ($sQuery)
    EndIf
    If @error Then
        SetError(1)
        Return 0
    EndIf
    
EndFunc   ;==>_Query

and here is a simple query that returns an error (error 1)

$var = _Query($sql,"SELECT * FROM users")

Any ideas?

I'm not sure, but, if i do this:

$sql = _MySQLConnect(...)

$var=1

    dim $namecol[2]
    $namecol[0]="actived"
    $namecol[1]=""
    dim $varcol[2]
    $varcol[0]=$var
    $varcol[1]=""

_AddRecord($sql, 'tabledb', $namecol, $varcol)
_MySQLEnd($sql)

that returns an error ( it's the same if i add "$var" or chr(34)&$var&chr(34) )

But if i do this (add string function):

$sql = _MySQLConnect(...)

$var=1

    dim $namecol[2]
    $namecol[0]="actived"
    $namecol[1]=""
    dim $varcol[2]
    $varcol[0]=String($var)
    $varcol[1]=""

_AddRecord($sql, 'tabledb', $namecol, $varcol)
_MySQLEnd($sql)

It's ok. (my column is a int)

Edited November 15, 2009 by panlatv

[lexty]

Im getting this error, will you please help me. TIA!

Happened when Im trying to add values to an existing table

dim $values[5]

$values[0] = "col1"

$values[1] = "col2"

$values[2] = "col3"

$values[3] = "col4"

$values[4] = "";make sure u have one extra blank element

$sql = _MySQLConnect("root", "password", "perfdb", "localhost")

_AddRecord($sql, 'mytable', $values, $values)

;this executes a query ("INSERT INTO mytable (col1, col2, col3, col4) VALUE (col1, col2, col3, col4)

;which adds a record where col1, col2, col3, and col4 have the value "col1", "col2" etc

;that executes a MySQL query ("DELETE FROM mytable WHERE username = 'user'

_MySQLEnd($sql)

ERROR Message:

C:\Program Files\AutoIt3\Include\mysql.au3 (132) : ==> The requested action with this object has failed.:

$oconnectionobj.execute ($query)

$oconnectionobj.execute ($query)^ ERROR

Edited December 2, 2009 by lexty

[blitzkrg]

Im getting this error, will you please help me. TIA!

I can't even get a simply query to work.

we need someone to make a new udf

[ProgAndy]

I can't even get a simply query to work.

we need someone to make a new udf

Then try this: LibMySQL for AutoIt

[yehia]

how can i get the result for query as "select now()"

Edited December 23, 2009 by yehia

[yehia]

ok just found it

if anyone is running through this use .Fields(0).Value

[hmobron]

Hello Guy's,

I have a question about the error trapping. I can make succefuly a connection, only when the wrong username/password is provided, the script is stopped whit a error here:

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

Error: \mysql.au3 (27) : ==> The requested action with this object has failed.:

How to trap this and popup a msgbox Wrong username/password

Thanks inadvanced,

Hans

fixed whit

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc") ; Install a custom error handler

Func MyErrFunc()

$HexNumber=hex($oMyError.number,8)

Msgbox(0,"","We intercepted a COM Error !" & @CRLF & _

"Number is: " & $HexNumber & @CRLF & _

"Windescription is: " & $oMyError.windescription )

$g_eventerror = 1 ; something to check for when this function returns

Endfunc

Edited January 5, 2010 by hmobron

[EndFunc]

Anybody know why this won't work?

Error in expression.:

$Result = .Fields('user_id').Value

$Result = .Fields('user_id')^ ERROR

I always get that or either the object failed message. I don't get an error on the connection but when I try to get the result is when I get an error. Any ideas?

[Steveiwonder]

I have similar issue as some other people are reporting.

Im getting this error when only try to execute a test command to the database. "SELECT * FROM USERS"

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Documents and Settings\sy019852\Desktop\New Text document.au3"    
C:\Program Files\AutoIt3\Include\mysql.au3 (49) : ==> The requested action with this object has failed.:
$odido = $oConnectionobj.execute($sQuery)
$odido = $oConnectionobj.execute($sQuery)^ ERROR
>Exit code: 1    Time: 3.634

I confirmed the connection is working fine as the _CountTables works just fine.

I've searched the forum also and found people with the same problem but nothing to resolve it.

Edited February 11, 2010 by Steveiwonder

[Steveiwonder]

Noone managed to find a solution to this issue?

[boy233]

I managed to select, update, delete and insert using MySQL connection!

Connection Sybase anyone know?

I'm not managing to do ..

Can anyone help make a simple connection?

[Steveiwonder]

Some nerd want to pick this back up? wtb support :S

[zackrspv]

Some nerd want to pick this back up? wtb support :S

it's by far easier and more reliable to use the libmysql UDF instead of this one. with libmysql, and the proper calls, you do not have to worry if the system has any ADO/DAO issues, as the dll handles it all. It's also quite quick.

[ionut]

Hello,

Can you suggest me the fastest way of exporting large csv files to MySQL using these UDFs? I have thought of 2 solutions:

1. export the content of .csv to arrays and then use _MySQLAddRecord to add the arrays into the db; I am afraid it will take a long time to export the values from CSV to arrrays

2. use the _MySQLExec to execute LOAD DATA INFILE directly into MySQL and just to put the headers for the csv fle into an array and use it for column names.

Other suggestions would be great. I would really appreciate some examples, too.

Thank you,

Ionut