MySQL UDFs

[AutoItKing]

You know, this would go great with the Web Based AutoIt thing! It'd be easier to write just about anything than in PHP.

[theguy0000]

You know, this would go great with the Web Based AutoIt thing! It'd be easier to write just about anything than in PHP.

@OP - see my signature for more info. I would love to add these if you want me to.

[AutoItKing]

Heck, I'd love it!

[Libor]

if anyone has any suggestions for more functions i'd love to hear em

Hi guys,

Great work you made here with this UDF, I have some suggestions how to improve _MySQLConnect function - to add more connection string parameters:

• server PORT selection if you don't want or can't use standard port 3306 (someone already mentioned here)
• OPTION connection string parameter - with this you can set the options which are available in MySQL ODBC driver (if you send data through VPN tunnels to mobile location, for example with GPRS connections, it's good to activate data compression option)
• STMT parameter in which you can write SQL query to be executed when you connect to the server. It is very usable for databases with non-english strings. For example I use SET NAMES cp1250 command here to instruct ODBC driver to translate my data between my client PC codepage (1250) and SQL database codepage (UTF-8).

All these addictions are very simple to implement and if they are implemented as optional parameters to _MySQLConnect function, they will not affect existing code which uses current version of UDF.

Have a nice (scripting) day

Edited March 31, 2007 by Libor

[Omar]

I love this udf, thanks so much it!

Here's a question how do I get this to work or is it even possible?

$var = _Query($sql, "SELECT DATE_FORMAT('" & $date & "', '%M %d, %Y')")

How would I read the results? I can't seem to figure it out.

Also why when I a retrieve a field that's a date do i get this:

20070331000000000000

It's supposed to be 2007-03-31

Any help would be greatly appreciated.

[guwguw]

Omar,

It's all in the manual.

All versions are here: http://www.mysql.org/doc/#refman

The part you asked about (assuming you are using version 5) -> http://dev.mysql.com/doc/refman/5.0/en/dat...-functions.html

[Alek]

have a little problem with _query

this is my what i got so far with it

$user = _GUICtrlListGetSelItemsText($List1)
$userdata = _query($db,"SELECT * FROM account WHERE username='" & $user[1] & "';")

lets say that $user[1] = admin

this is what is what it asks for

SELECT * FROM account WHERE username=' Admin ';

then it should return all the stuff that the table has where username = admin like password

but i get $g and some times ÿÿ

maybe im just useing it wrong

[guwguw]

have a little problem with _query

lets say that $user[1] = admin

this is what is what it asks for

SELECT * FROM account WHERE username=' Admin ';

then it should return all the stuff that the table has where username = admin like password

but i get $g and some times ÿÿ

maybe im just useing it wrong

For your syntax to give you a result, you would have to have an entry in your user table with spaces before and after, plus a capital "A" - do you have such an entry?

Assuming the correct entry is "admin" in username, your query should be

SELECT * FROM account WHERE username='admin';

Edited May 4, 2007 by guwguw

[Alek]

For your syntax to give you a result, you would have to have an entry in your user table with spaces before and after, plus a capital "A" - do you have such an entry?

Assuming the correct entry is "admin" in username, your query should be

SELECT * FROM account WHERE username='admin';

dident work, i still get $g or ÿÿ Edited May 4, 2007 by Alek

[guwguw]

dident work, i still get $g or ÿÿ

Do you have access to phpMyAdmin? I find that the best tool to test SQL queries.

From your description, it is hard to tell whether the problem is in the db structure, your SQL syntax or the use of AutoIt code. It might even be that the password is encrypted and cannot be read.

Try to test the plain query directly (like in phpMyAdmin) and if that works, carry it over into your AutoIt code.

[Alek]

the query works in phpMyAdmin and in php scripting :S

_querty should return 0 if error but i get $g or ÿÿ when i should get the users password and some other stuff.

[guwguw]

have a little problem with _query

this is my what i got so far with it

$user = _GUICtrlListGetSelItemsText($List1)
$userdata = _query($db,"SELECT * FROM account WHERE username='" & $user[1] & "';")

lets say that $user[1] = admin

this is what is what it asks for

SELECT * FROM account WHERE username='admin';

then it should return all the stuff that the table has where username = admin like password

but i get $g and some times ÿÿ

(I'm kind of new to AutiIt, but) can you tell us, what part of your Autoit code gives you "$g and some times ÿÿ"?

In other words, what code is running between your call to $userdata and the output?

What does $userdata[1] contain and how do you make it visible?

[Alek]

(I'm kind of new to AutiIt, but) can you tell us, what part of your Autoit code gives you "$g and some times ÿÿ"?

In other words, what code is running between your call to $userdata and the output?

What does $userdata[1] contain and how do you make it visible?

there are 3 things, first i it sets the password in an input in a gui, second a msgbox to test it and third a text file to test , the input and the msg says $g or ÿÿ (sometimes the input says TÞD) and the text file dosent say anything.

there are errors in the script but all of them are "$var possibly used before declaration" because i have the $var in functions.

[guwguw]

there are 3 things, first i it sets the password in an input in a gui, second a msgbox to test it and third a text file to test , the input and the msg says $g or ÿÿ (sometimes the input says TÞD) and the text file dosent say anything.

there are errors in the script but all of them are "$var possibly used before declaration" because i have the $var in functions.

If the input already gives you an unexpected result, why don't you fix that first? How can an input (which is entered manually by YOU, I assume?) show something else than you entered? If you are referring to that value when you are trying to display it, it's called an output. If that is the case (output of input value is wrong), then you either are using some codepage settings which don't match, or you are referring to the wrong value in your output statement (I would search for a wrong array reference first, being as blind as you keep us).

I'd like to help you, because I feel qualified to talk about MySQL, but how could I debug code without knowing it? What code are you using to generate the output (not interested in password input nor "text file to test" at this point) - just the syntax of your output statement after

$user = _GUICtrlListGetSelItemsText($List1)
$userdata = _query($db,"SELECT * FROM account WHERE username='" & $user[1] & "';")

[guwguw]

Now that it looks more like it's a coding problem, why don't you declare (DIM) the name-carrying $var at the beginning of your code?

It could be that $var gets called before it has a value assigned and just shows you whatever it finds in memory ... "DIM $var" should prevent junk from being displayed.

[Alek]

Now that it looks more like it's a coding problem, why don't you declare (DIM) the name-carrying $var at the beginning of your code?

It could be that $var gets called before it has a value assigned and just shows you whatever it finds in memory ... "DIM $var" should prevent junk from being displayed.

here is an example of what i got, this script does not work on my computer.

expandcollapse popup

#include <GUIConstants.au3>
#include <mysql.au3>
#include <GuiList.au3>

$Username = ""
$Password = ""
$DB = ""
$server = ""

$database = _MySQLConnect($Username, $Password, $DB, $server)
$Form1 = GUICreate("AForm1", 321, 87, 193, 115)
$List1 = GUICtrlCreateList("", 8, 8, 113, 71)
$Input1 = GUICtrlCreateInput("", 128, 8, 129, 21)
$Input2 = GUICtrlCreateInput("", 128, 32, 129, 21)
$Button1 = GUICtrlCreateButton("Look up user", 128, 56, 131, 21, 0)
$Label1 = GUICtrlCreateLabel("Username", 264, 8, 52, 17)
$Label2 = GUICtrlCreateLabel("Password", 264, 32, 50, 17)
_loadusers()
GUISetState()


While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            _lookupuser()
    EndSwitch
WEnd

Func _loadusers()
    $users = _GetColVals($database , "Account", "Username")
    for $x = 1 to $users[0]
        GUICtrlSetData($List1, $users[$x])
    Next
EndFunc

Func _lookupuser()
    $user = _GUICtrlListGetSelItemsText($List1)
    $query = "SELECT password FROM account WHERE username='"&$user[1]&"';"
    $userdata = _query($database,$query)
    GUICtrlSetData($Input1,$user[1])
    GUICtrlSetData($Input2,$userdata)
EndFunc
oÝ÷ Ú(£ºËg¢Ø¬yÛ(ëax"~'!jx$º¬z»hjëh×6
Func _lookupuser()
    $user = _GUICtrlListGetSelItemsText($List1)
    $query = "SELECT password FROM account WHERE username='"&$user[1]&"';"
    $userdata = _query($database,$query)
    if not $userdate = 0 then
        GUICtrlSetData($Input1,$user[1])
        GUICtrlSetData($Input2,$userdata)
    endif
EndFunc

then it doesent change the inputs (_query returns 0/error) and i know that $user[1] is something because i tested it with a msg that was showing me what $user[1] was, but that dosent explain why the query work with phpMyAdmin and not with _query. (even if i change $user[1] with a name i know exists in the database)

Edited May 5, 2007 by Alek

[guwguw]

One more hint: If you want to program, you have to be VERY precise!

Upper case and lower case are different values, especially in your SQL syntax - one time you write

$users = _GetColVals($database , "Account", "Username")

and another time you request

$query = "SELECT password FROM account WHERE username='"&$user[1]&"';"

My question: which is it? (= what are the column names?). Having a table "Account" is a totally different one than "account" and the field "Username" is not the same as "username".

Therefore, your function _loadusers() already fails ...

Also, in your last (modified) version of _lookupuser(), you are referring to

if not $userdate = 0 then
        GUICtrlSetData($Input1,$user[1])
        GUICtrlSetData($Input2,$userdata)
    endif

, although the variable userdate in the first line here has never been declared.

[Alek]

One more hint: If you want to program, you have to be VERY precise!

Upper case and lower case are different values, especially in your SQL syntax - one time you write

$users = _GetColVals($database , "Account", "Username")

and another time you request

$query = "SELECT password FROM account WHERE username='"&$user[1]&"';"

My question: which is it? (= what are the column names?). Having a table "Account" is a totally different one than "account" and the field "Username" is not the same as "username".

Therefore, your function _loadusers() already fails ...

Also, in your last (modified) version of _lookupuser(), you are referring to

if not $userdate = 0 then
        GUICtrlSetData($Input1,$user[1])
        GUICtrlSetData($Input2,$userdata)
    endif

, although the variable userdate in the first line here has never been declared.

i have change the account and username in so many ways and its still not working i used copy and paste to test it in phpmyadmin and it workd there but not in _query. but i was able to read from the database by modifying _GetColVals but i cant edit the database.

btw, i dont think its case sensetiv, i changed Account and Username in _loadusers to account and username and it dident change, still worked.

[theimer]

Can I use this UDF for DB placed on server on internet or only for local DB?

thanks

Libor

[guwguw]

Can I use this UDF for DB placed on server on internet or only for local DB?

thanks

Libor

It should work on any SQL database, assuming you use the proper login information. Often the SQL username and password is different from the regular site access password. Also, some servers are set up that one needs to use the MySQL IP address, rather than the domain name.

Here is an example of my (altered, naturally ) login info:

$Username = "arabeska"

$Password = "j3ir4bklt"

$DB = "mysql"

$server = "88.144.132.11"