MySQL UDFs

[James]

Suggestion would be to have _MySQL_FunctionName() I personally prefer to keep things named nicely.

James

[Jat421]

Hi,

I am new to SQL and I am trying to connect to the MySQL. I have installed the ODBC-3.51.27 driver but whenever I try to connect to the database it throw a error.

Here is the code

#include <mysql.au3>
$sql = _MySQLConnect('sa','1234','mydb', 'MySQL')
_CreateTable($sql, 'testtable', 'tt_id')
_AddRecord($sql, 'testtable', 'tt_id', 1)
$count = _CountRecords($sql,  'testtable', 'tt_id', 1)
;this executes the query "SELECT * FROM `testtable` WHERE `tt_id` = 1 then counts the records
_DropTbl($sql,'testtable')
_MySQLEnd($sql)

Below is the error I am getting

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Documents and Settings\User\Desktop\SQL\New AutoIt v3 Script (4).au3"    
C:\Program Files\AutoIt3\Include\mysql.au3 (27) : ==> 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.516

What am I doing wrong? Thanks for any help!!

[Szymi2004]

Hi everyone!

It's my first post here, so I want to say hello - hello! Sorry in advance for any mistakes, but mainly I don't speak in English (I live in Poland). I have a large MySQL database, currently working for my website. I want to connect this script to my base, but I'm not sure how. When I start my application I would like to see (as a first window) form with logging into the database. The form would consist of two inputs (for login and password) and two buttons (for apply or quit). I've started with GUI and now I stuck with inputs. Here's my code (excluding the GUI):

$login = GUICtrlRead($Input1)
$password = GUICtrlRead($Input2)
$sql = _MySQLConnect("sa","sa","mydb","mywebsite.com")

Now I need a code to compare the inputs with datas from MySQL database. I was looking for help by myself, but I but haven't found a solution. Can someone help me with it? I would greatly appreciate. Thanks in advance.

Simon from Poland.

[JamesDover]

Spróbuj tego. To działa próbowałem go na mojej bazie danych.

expandcollapse popup

#include "MySQL.au3"
#include <ButtonConstants.au3>
#include <ComboConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
Dim $Database = "webdatabase"
Dim $MySQLServerName = "192.168.1.1"
Dim $TableName = "hardware"
$SimonRules = GUICreate("Simon's WebDataBase", 450, 89, 225, 293)
$Input1 = GUICtrlCreateInput("", 136, 8, 233, 25)
GUICtrlSetColor(-1, 0x000080)
$Input2 = GUICtrlCreateInput("", 136, 32, 233, 25)
$Login = GUICtrlCreateLabel("Login", 8, 8, 129, 20)
GUICtrlSetFont(-1, 12, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0x000080)
$Password = GUICtrlCreateLabel("Password", 7, 31, 115, 20)
GUICtrlSetFont(-1, 12, 800, 0, "MS Sans Serif")
$Apply = GUICtrlCreateButton("Apply", 368, 8, 75, 25, $WS_GROUP)
$Exit = GUICtrlCreateButton("Exit", 368, 32, 75, 25, $WS_GROUP)
GUISetState(@SW_SHOW)

While 1
    $Msg = GUIGetMsg()
    Select
        Case $Msg = $GUI_EVENT_CLOSE
            Exit
        Case $Msg = $Apply
            Apply()
        Case $Msg = $Exit
            Exit
        Case Else
            ;;;
    EndSelect
WEnd

Func Apply()
    $NameList = ""
    $SQLQry = "SELECT * FROM hardware" ; Change this to your query
    $UserName = GUICtrlRead($Input1)
    $Password = GUICtrlRead($Input2)
    $SQLInstance = _MySQLConnect($UserName, $Password, $Database, $MySQLServerName)
    $TableContents = _Query ($SQLInstance, $SQLQry)
    With $TableContents
        While Not .EOF
            $NameList &= .Fields ("ID").value & " " & .Fields ("NAME").value & @CRLF ;Change these "ID" and "NAME" to row names edit as needed
            .MoveNext
        WEnd
    EndWith
    MsgBox(0,"WebServerData",$NameList)
  _MySQLEnd ($SQLInstance)
EndFunc   ;==>Apply

Hi everyone!

It's my first post here, so I want to say hello - hello! Sorry in advance for any mistakes, but mainly I don't speak in English (I live in Poland). I have a large MySQL database, currently working for my website. I want to connect this script to my base, but I'm not sure how. When I start my application I would like to see (as a first window) form with logging into the database. The form would consist of two inputs (for login and password) and two buttons (for apply or quit). I've started with GUI and now I stuck with inputs. Here's my code (excluding the GUI):

$login = GUICtrlRead($Input1)
$password = GUICtrlRead($Input2)
$sql = _MySQLConnect("sa","sa","mydb","mywebsite.com")

Now I need a code to compare the inputs with datas from MySQL database. I was looking for help by myself, but I but haven't found a solution. Can someone help me with it? I would greatly appreciate. Thanks in advance.

Simon from Poland.

Edited October 15, 2010 by JamesDover

[mattschinkel]

Hey there, I'm using Autoit v3.3.6.1, I'm this error:

Only Object-type variables allowed in a "With" statement.:

With $var

With ^ ERROR

#include "mysql.au3"

$sql = _MySQLConnect("sa","sa","mydb","mywebsite.com")
$var = _Query($sql,"SELECT * FROM mytable WHERE user = 'username'")
With $var
While NOT .EOF
FileWriteLine("c:\test.txt",.Fields("user_name").value & @CRLF)
.MoveNext
WEnd
EndWith
_MySQLEnd($sql)

Please help, Thanks!

Matt

[russell]

ok im trying to stay calm but u was about to switch from autoit to C++ becuase this didnt exits. THANK YOU. I could hug you

[russell]

Hey there, I'm using Autoit v3.3.6.1, I'm this error:

Only Object-type variables allowed in a "With" statement.:

With $var

With ^ ERROR

#include "mysql.au3"

$sql = _MySQLConnect("sa","sa","mydb","mywebsite.com")
$var = _Query($sql,"SELECT * FROM mytable WHERE user = 'username'")
With $var
While NOT .EOF
FileWriteLine("c:\test.txt",.Fields("user_name").value & @CRLF)
.MoveNext
WEnd
EndWith
_MySQLEnd($sql)

Please help, Thanks!

Matt

Me too and i changed the mysql.au3 to {MySQL ODBC 5.1 Driver}, so i thought user and pass. Nope no change

[cueclub]

I have a silly and noobish question.

I have a website, with a DB on it. Now I have tried several things but still can not connect using

_MySQLConnect("sa","sa","mydb","mywebsite.com")

It keeps telling me it doesnt exist or cant connect. How do I get this to work if I cant get it to connect?

[Quanntum]

I have a silly and noobish question.

I have a website, with a DB on it. Now I have tried several things but still can not connect using

_MySQLConnect("sa","sa","mydb","mywebsite.com")

It keeps telling me it doesnt exist or cant connect. How do I get this to work if I cant get it to connect?

First enable remote access to your database....and make sure the firewall of the server AND your home is not blocking the 3306 port.

[Hashi]

Hey guys first off this UDF is exellent and was exactly what I needed to make my application work but I have run into a small problem, I wanted my application to login by checking against username / password but if it doesn't check some kind of 'already logged in' value then 100's of people can login using 1 username / password, is there anyway to make Autoit make a session or some kind of 'already logged in' vlaue it checks? I know I could make it set a value in a column but then how would that value change back to 0 if the program closes without sending a logout command? any help would be greatly appreciated!

[Leagnus]

> The requested action with this object has failed

IMHO server should be not 'MySQL' but 'localhost'.

For remote connection we should use plink.exe or any soft that uses plink.exe (for example SQLyogEnt.exe).

For SSH connection port can be 3310.

[dixonpete]

Do you have to have a specific ODBC driver installed? Times have changed and the current version is 5.1.8.

I had a look in my registry and it seems to be indicated as 5.1, not 5.1.8

Also, another gotcha. I want to distribute my work as an AutoIt self-executing exe script. If the computer the script is run on has an older (or newer) ODBC driver what's to be done? The user won't be able to edit the script and may not have the skills to discover what ODBC driver is installed.

[ProgAndy]

Do you have to have a specific ODBC driver installed? Times have changed and the current version is 5.1.8.

I had a look in my registry and it seems to be indicated as 5.1, not 5.1.8

Also, another gotcha. I want to distribute my work as an AutoIt self-executing exe script. If the computer the script is run on has an older (or newer) ODBC driver what's to be done? The user won't be able to edit the script and may not have the skills to discover what ODBC driver is installed.

If you cannot rely on ODBC, you should try to use my UDFs for libmysql or the simplified version EzMySQL.

[pocolo]

_CountRecords work only 1 field

How to count number records :

mysql_query("SELECT * FROM `abc` WHERE `a` = '123' AND `b` = '456' AND ...... ")

Edited September 28, 2011 by pocolo

[Ju72]

Thank you cdkid for this great work!!

[AtomR]

Really awesome UDF here.

I'm almost done coding my autoit script. Actually i'm in tests phase.

On the most recent test I decided to see if my script would run properly when MySql server was not running (to account for all possibilities). I would've thought that _MySqlConnect would try to connect and then return an error when it couldn't. But my program crashes with a

$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

What can I do so that my program does not crash and properly catch the error instead?

Thanks for your atention.

[water]

You need a COM error handler. Please see function ObjEvent in the help file for an example.

; COM Error Handler example
; -------------------------
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc") ; Initialize a COM error handler
; ... Your code goes here
Exit
; This is my custom defined error handler
Func MyErrFunc()
  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"    & @CRLF  & @CRLF & _
    "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
    "err.windescription:"    & @TAB & $oMyError.windescription & @CRLF & _
    "err.number is: "        & @TAB & hex($oMyError.number,8)  & @CRLF & _
    "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
    "err.scriptline is: "    & @TAB & $oMyError.scriptline   & @CRLF & _
    "err.source is: "        & @TAB & $oMyError.source       & @CRLF & _
    "err.helpfile is: "    & @TAB & $oMyError.helpfile     & @CRLF & _
    "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
Endfunc

[AtomR]

I have that but for some stupid reason decided to use _MySqlConnect before declaring the handler. Thank you for opening my eyes

[water]

I wish it always it that easy

[Gigli]

Hello all

I'am trying this UDF for the first time, it's really cool and simply

Is there something special to know about accent marks like é è ù ö ?

I try to insert through Autoit some records with frenchs words but it failed... If I try to insert "vélo" it just insert "v"

Is there something to do in autoit code, or it is in the MySQL base there is somenthing wrong. It is in UTF-8 Unicode (utf8)

Thanks you