MySQL UDFs

[raquien]

cdkid: thanks, you're right this UDF won't work on production release. I installed beta version and voila I can succesfully connect.. very sweett.. thanks man..

regarding the server ice, it's okay because im using it through LAN. that's the computer name of the server that i want to connect.

Thanks man..

[raquien]

@cdkid: is it possible to update the record? I used the code below but won't work. As of now I can't think of a possible workaround but to delete the existing record and add an updated one.

_Query($sql, "UPDATE `transaction` SET `txn_done` = 'yes' WHERE `txn_name` = $name LIMIT 1")

[raquien]

Okay, i've figured it out .. here's what i did..

$result = _Query($sql, "UPDATE `transaction` SET `txn_done` = 'yes' WHERE `txn_name` = " & $name & ' LIMIT 1;')

[thefluxster]

Now what we really need for all those interested in doing this but don't really understand how/why they need to install the ODBC drivers is a simple installation automation script. It is easy enough to install, but please hear me out.

I will probably be deploying a few MySQL based scripts to several people throughout the office and probably other offices as well. These people have varying technical skills at best and providing an all-in-one package would be preferable. Including instructions such as, go here, do this, just won't work. Also, if people see an install process flying by on the screen without any input from them, they'll probably find some way to break it as well. So, preferably, something that would install the ODBC quiet and fast would be best.

Anyone do this before or want to take the challenge of making the installation of the MySQL Connect ODBC silent? You're a braver person than I am, for sure, if you do.

[cdkid]

Hmm, good idea, fluxster, i'll se what i can come up with. Just so everyone knows, i'm converting all the functions to VB COM so they can be used in most any language

via objcreate

~cdkid

edit:

this install wont be hard at all, i'll just use WinSetState @SW_HIDE and ControlClicks, when i finish it, i'll post it up here.

Edited March 4, 2006 by cdkid

[kclteam]

Hmm, good idea, fluxster, i'll se what i can come up with. Just so everyone knows, i'm converting all the functions to VB COM so they can be used in most any language

via objcreate

~cdkid

Let me thank you again Cdkid for such nice UDF...

Yesterday I ran into a problem but corrected it after looking through your script! Your script's explanation of each function is marvellous. Thank you very much.

Edit: Spelling mistake

Edited March 4, 2006 by kclteam

[cdkid]

Well, it's not pretty, but it'll install the driver for you... I couldnt get it to run silently because it kept coming back from the WinSetState...So just inform your users that it's part of the setup process... The source, and EXE are included in this post, the au3 wont work cuz im using FileInstall.

*moved to first post in zip*

Edited March 29, 2006 by cdkid

[cdkid]

Attention people of AutoIt forums:

i need someone to test what i've gotten so far in the DLL im making, mostly just need to see if it will register. If anyone would be willing to help me out, maybe someone that can figure out why i cant RegSvr it, drop me a message on AIM or send me an email (aim: cdkid000 email: coll_147@hotmail.com)

or a PM

~cdkid

Edited March 5, 2006 by cdkid

[randallc]

Hi,

Sorry, don't know about the DLL

I eventually got the UDF working; but I acn't seem to concatenate commands [which work individually].

Is this a limit of the Object approach? - [not so with SQLite Obj...]?

eg

#include"MySQL.au3"

$sql = _MySQLConnect('root','PassWord','sampdb','HPLAP')

$s_Input1=& _

" BEGIN ;"& _

"DROP TABLE IF EXISTS files ;"& _

"CREATE TABLE files (file_id INTEGER PRIMARY KEY AUTO_INCREMENT,a VARCHAR(64), b INTEGER,c TEXT);"& _

"INSERT INTO files( a , b ,c) VALUES('valueA','2','valueC');"& _

"COMMIT ;"

_Query($sql,$s_Input1)

ConsoleWrite($s_Input1)

_MySQLEnd($sql)

Any ideas?

Best, Randall

Edited March 5, 2006 by randallc

[randallc]

Hi,

For making a new database, I can connect to "mysql" database first; as in this func;

func _MySQL_CreateDataBase($s_UserName,$s_PassWord,$s_MyDb,$s_ServerComputerName)

$s_MyDb_Mysql='mysql' ; assume always exists?

$sql = _MySQLConnect($s_UserName,$s_PassWord,$s_MyDb_Mysql,$s_ServerComputerName)

$s_Input1="CREATE DATABASE if not exists "&$s_MyDb&";"

_Query($sql,$s_Input1)

_MySQLEnd($sql)

Return _MySQLConnect($s_UserName,$s_PassWord,$s_MyDb,$s_ServerComputerName)

EndFunc

Best, Randall

[cdkid]

Hi,

Sorry, don't know about the DLL

I eventually got the UDF working; but I acn't seem to concatenate commands [which work individually].

Is this a limit of the Object approach? - [not so with SQLite Obj...]?

eg

Any ideas?

Best, Randall

Hi randall, i'm fairly certain that that is SQL syntax not MySQL go to dev.mysql.com and do a search for CREATE SYNTAX

~cdkid

[cdkid]

Wonderful, wonderful news... I have finally fixed the problem with registering the DLL, now i'm writing up the rest of the functions & the help file & the installer package... i'll post a link to it when i get it all finished.

if anyone would like to help me out with any of that send me an e-mail (coll_147@hotmail.com) or msg me on AIM (cdkid000) or MSN (coll_147@hotmail.com) or PM me

~cdkid

Edited March 5, 2006 by cdkid

[thefluxster]

Wonderful, wonderful news... I have finally fixed the problem with registering the DLL, now i'm writing up the rest of the functions & the help file & the installer package... i'll post a link to it when i get it all finished.

if anyone would like to help me out with any of that send me an e-mail (coll_147@hotmail.com) or msg me on AIM (cdkid000) or MSN (coll_147@hotmail.com) or PM me

~cdkid

I wait with baited breath. BTW, the installer worked great, thanks!

[GrungeRocker]

I wait with baited breath. BTW, the installer worked great, thanks!

yes it's all perfect

[kclteam]

Hi cdkid,

I have been using these MySQL UDFs of yours quite extensively and, as I have already mentioned in my previous posts, found them quite useful and handy.

Only yesterday I ran into a problem which has troubled me to no end.

I have made a script which connects to my MySQL database and counts the number of records for a particular condition and then stores that value in a global variable and closes the connection. Whole of this is in an infinite loop.

Here is the code:

While 1
;Do something with $last_visit

        $sql = _MySQLConnect($username, $password, $database, $server)
    $table = "phpbb_users"
    $column = "user_active"
    $condition = "0"
    
        If FileExists("notifier.ini") Then
        $last_visit = IniRead("notifier.ini", "record", "lastvisit", 0) 
    EndIf
    
    $number_inactive = _CountRecords($sql, $table, $column, $condition) 
    
    IniWrite("notifier.ini", "record", "lastvisit", $number_inactive)
    
    $new_reg = ($number_inactive - $last_visit)
    
    _MySQLEnd($sql)
WEnd

I am reading the values of $username, $password, $database and $server from an .ini file.

Most of the time, my script runs fine. But once in a while, an error pops up saying the connection with the object could not be established. When I run the COM error handeler, it gives me the following description error:

We intercepted a COM Error!!

err.description is: [MySQL][ODBC 3.51]Can't connect to MySQL server on '<my ip>' (10048)
err.windescription is: Unspecified error

err.lastdllerror is: 0
err.scriptline: 0
err.number is: 80020009
err.source is: Microsoft OLE DB Provider for ODBC Drivers
err.helpfile is:
err.helpcontext is: 0

Well, to avoid this error (i.e. to handle this error) what I did was put the connection method in a loop until a connection was established. So I modified my first line of the code as:

$sql = 0
Do
   $sql = _MySQLConnect($username, $password, $database, $server)
Until ($sql <> 0)

But this does not help, and I keep getting the same error.

Why is this happening?

But I know you are now busy with the installer thing, so don't worry too much about my problem. Only when you get some free time, please look into it.

Any help would be highly appreciated. Thanks!

[Gigglestick]

@kclteam

Just a few quick thoughts.

You might want to put the connect and end function calls outside the while loop. You don't actually have to disconnect and reconnect each loop, just stay connected and rerun your query.

Also, you don't need the FileExists() statement. The IniRead's default value will apply if the file doesn't exist.

Last, you might want to create an $old_number_inactive value and only IniWrite if the $number_inactive has changed instead of every loop.

Edited March 7, 2006 by c0deWorm

[kclteam]

thank you very much codeworm, I looked into my code after you recommended the changes and realized, yes, they were redundant....I have done the necessary changes...and am looking forward to it crashing...not because of the changes but because of the previous problem

thank you for bringing the "flaws" of my code to my notice...

[kclteam]

Hi,

I have been trying to handle the errors that MySQL UDFs return but apparently they haven't worked.

Like if I try connecting to a MySQL DB with an invalid connection details, then I wanna catch the error and display it to the user. I am doing this:

$sql = _MySQLConnect($username, $password, $database1, $server)

If ($sql == 0) Then
   MsgBox(0, "Error", "There is error in the Connect command")
Else
 ;Do something on DB
EndIf

But this doesn't work. The program crashes with COM error!

Ne suggestions?

[kclteam]

Ok, got it...it seems COM errors can't be handeled the way we handle other errors

[Reference from Help File]

Using COM without proper error handling can be very tricky. Especially when you are not familiar with the Objects in your script.

An AutoIt script will immediately stop execution when it detects a COM error. This is the default and also the safest setting. In this case you have to take measures in your script to prevent the error from happening.

...

You can only have ONE Error Event Handler active per AutoIt script.

Guess I need to look for some other method to handle the error.

[theguy0000]

great!

how about a function to get the names of all the databases in a specified server?