MySQL UDFs

379 posts in this topic

#321 ·  Posted

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

James

Share this post


Link to post
Share on other sites



#322 ·  Posted

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

Share this post


Link to post
Share on other sites

#323 ·  Posted

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.

Share this post


Link to post
Share on other sites

#324 ·  Posted (edited)

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

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

Share this post


Link to post
Share on other sites

#325 ·  Posted

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

Share this post


Link to post
Share on other sites

#326 ·  Posted

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


muppet hands are so soft :)

Share this post


Link to post
Share on other sites

#327 ·  Posted

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 :graduated: and i changed the mysql.au3 to {MySQL ODBC 5.1 Driver}, so i thought user and pass. Nope no change

muppet hands are so soft :)

Share this post


Link to post
Share on other sites

#328 ·  Posted

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?

Share this post


Link to post
Share on other sites

#329 ·  Posted

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.

Share this post


Link to post
Share on other sites

#330 ·  Posted

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!

Share this post


Link to post
Share on other sites

#331 ·  Posted

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

Share this post


Link to post
Share on other sites

#332 ·  Posted

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.

Share this post


Link to post
Share on other sites

#333 ·  Posted

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.

*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Share this post


Link to post
Share on other sites

#334 ·  Posted (edited)

_CountRecords work only 1 field

How to count number records :

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

Edited by pocolo

Share this post


Link to post
Share on other sites

#335 ·  Posted

Thank you cdkid for this great work!!

Share this post


Link to post
Share on other sites

#336 ·  Posted

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.

Share this post


Link to post
Share on other sites

#337 ·  Posted

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


My UDFs and Tutorials:

UDFs:
Active Directory (2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (NEW 2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

Share this post


Link to post
Share on other sites

#338 ·  Posted

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

Share this post


Link to post
Share on other sites

#339 ·  Posted

I wish it always it that easy :)


My UDFs and Tutorials:

UDFs:
Active Directory (2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (NEW 2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

Share this post


Link to post
Share on other sites

#340 ·  Posted

Hello all ;)

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

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

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