Jump to content

EzMySql UDF - Use MySql Databases with autoit

Yoriz

By Yoriz
in AutoIt Example Scripts

 Share

Recommended Posts

adriancs35 Seeker

adriancs35

Posted
Posted (edited)

Hi, I know this is super-old, but..

what am I doing wrong here..?

$em = _EzMySql_Query("SELECT email FROM accountss where id = '$idacc'")
MsgBox($MB_SYSTEMMODAL, "", "Required email is:" & @CRLF & $em)
Edited by adriancs35
  • Replies 61
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

Yoriz
Yoriz

This is a UDF called EzMySql for using a MySql database with autoit, it uses similar syntax as SQLite but for MySql, for a more comprehensive MySql UDF see MySQL UDF by ProgAndy. Credit goes to ProgAn

dmob
dmob

Gives me (crazy) idea to create app that uses SQLite for local (single user) access and MySQL for network / multi-user access.... hmmmm

willichan
willichan

Sorry to be resurrecting an old thread, but since I am not creating a new UDF, but just updating this one to be functional under v3.3.14.1, I thought it best to just post the update in the original th

Posted Images

jchd Universalist

jchd

Posted
Posted

Maybe this:

$em = _EzMySql_Query("SELECT email FROM accountss where id = '" & $idacc & "'")
MsgBox($MB_SYSTEMMODAL, "", "Required email is:" & @CRLF & $em)

Use quoting (doubling of single quotes) in case the variable $idacc may contain single quote(s).

  Reveal hidden contents

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

  • 3 weeks later...
  • 8 months later...
willichan Universalist

willichan

Posted
Posted

Sorry to be resurrecting an old thread, but since I am not creating a new UDF, but just updating this one to be functional under v3.3.14.1, I thought it best to just post the update in the original thread to be found more easily, and to render proper credit where due.  Again, no new functionality, but tweaked to work with the latest AutoIt versions.

  Reveal hidden contents

EzMySql.au3
 

  Reveal hidden contents

demo.au3
 

 

UserBarPurpleGeekNerd.GIF


My UDFs: Barcode Libraries, Automate creation of any type of project folder, File Locking with Cooperative Semaphores, Inline binary files, Continue script after reboot, WinWaitMulti, Name Aggregator, Enigma, CornedBeef Hash

 

  • 1 month later...
rony2006 Prodigy

rony2006

Posted
Posted

Ok, so I made this code and everything worked fine until I tried to send the data from the input "intrare" in my database:

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include "EzMySql.au3"
#include <Array.au3>



#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Form1", 172, 97, 192, 124)
Global $intrare = GUICtrlCreateInput("intrare", 32, 24, 121, 21)
Global $trimite = GUICtrlCreateButton("trimite", 48, 56, 75, 25)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

#include "EzMySql.au3"
#include <Array.au3>

If Not _EzMySql_Startup() Then
    MsgBox(0, "Error Starting MySql", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

$Pass = ""

If Not _EzMySql_Open("127.0.0.1", "root", $Pass, "ifm", "3306") Then
    MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf



While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE

            Exit

        Case $trimite ;mai jos scriu ce sa se intample in CAZ ca se apasa butonul trimite
            $datafromuser = GUICtrlRead($intrare)
            MsgBox (1, $datafromuser, "test")
            $sMySqlStatement = "INSERT INTO `piese_de_schimb` (`id`, `denumire`, `tip`) VALUES (NULL, "$datafromuser", 'ver4564de');"
            If Not _EzMySql_Exec($sMySqlStatement) Then
            MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
            Exit
            EndIf


_EzMySql_Close()
_EzMySql_ShutDown()
Exit
    EndSwitch
WEnd

 

 

If i use $sMySqlStatement = "INSERT INTO `piese_de_schimb` (`id`, `denumire`, `tip`) VALUES (NULL, 'pen'), 'ver4564de');"
            If Not _EzMySql_Exec($sMySqlStatement) Then
            MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
            Exit
            EndIf

 

is working ok, but if I remplace 'pen' with "$datafromuser" or $datafromuser I get an error.

What I do wrong? I want the data that I write in input box to be sent to my database in table piese_de_schimb.

BrewManNH Universalist

BrewManNH

Posted
Posted

Your variable is inside quotes, it's being read as a string containing the name of  the variable and not the contents of the variable. Remove the quotes and reformat the query line to put the quotes around the variable's contents instead of the variable.

Something like this:

$sMySqlStatement = "INSERT INTO `piese_de_schimb` (`id`, `denumire`, `tip`) VALUES (NULL, " & $datafromuser & ", 'ver4564de');"

You might have to play around with the quotes but that should get you started.

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

  Reveal hidden contents

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

rony2006 Prodigy

rony2006

Posted
Posted

Hello guys, 

Can you help me please with an example with how I can sent an array of data to a table?

 

For ex I have:

 

$name = "marian"

$age = "21"

$colour = "white"

$array [3] = [$name, $age, $colour]

 

How I can send $array [3] to mysql?

argumentum Universalist

argumentum

Posted
Posted
  On 11/23/2015 at 6:56 PM, rony2006 said:

How I can send $array [3] to mysql?

you don't. Not that you can't dump the 3 values with some separator, like "|" and rebuild the array after read but you don't misuse a database like that, is like, OMG.
do some reading on the evolution of putting data somewhere somehow.
https://en.wikipedia.org/wiki/Database could be a good start.

Follow the link to my code contribution ( and other things too ).
FAQ - Please Read Before Posting.
autoit_scripter_blue_userbar.png

  • 5 months later...
falcontechnics Seeker

falcontechnics

Posted
Posted

Hello there,

I'm trying to do something like the following. However, it fails ...No loops in the code.

Can you help me?

$sMySqlStatement = "INSERT INTO testtable (hostname,IP,RegKey,time) VALUES (" &
$sMySqlStatement = ^ ERROR

Code:

Local $sMySqlStatement = ""
  $sMySqlStatement &= "INSERT INTO testtable (hostname,IP,RegKey,time) VALUES (" & _
                        "'" & $hostname & "'," & _
                        "'" & $IP & "'," & _
                        "'" & $rtext1 & "'," & _
                        "'" & $time & "');"

 

If Not _EzMySql_Exec($sMySqlStatement) Then
MsgBox(0, "Error inserting data to Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())

..........

 

mLipok Universalist

mLipok

Posted
Posted

@falcontechnics

I think this error is not comming from code snippet which you post.

This code is correct: 

Local $hostname
Local $IP
Local $rtext1
Local $time

Local $sMySqlStatement = ""
$sMySqlStatement &= "INSERT INTO testtable (hostname,IP,RegKey,time) VALUES (" & _
        "'" & $hostname & "'," & _
        "'" & $IP & "'," & _
        "'" & $rtext1 & "'," & _
        "'" & $time & "');"

 

I think so because the code have & char and presented error have not.

  Quote

$sMySqlStatement &= "INSERT INTO testtable .......

Expand  

 

Signature beginning:
* Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
* ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Code * for other useful stuff click the following button:

  Reveal hidden contents

Signature last update: 2023-04-24

  • Developers
Jos Universalist

Jos

Posted
  • Developers
Posted
  On 5/21/2016 at 8:42 PM, mLipok said:

 

@falcontechnics

I think this error is not comming from code snippet which you post.

 

Expand  

Isn't that what I already said? ;)

SciTE4AutoIt3 Full installer Download page   - Beta files       Read before posting     How to post scriptsource   Forum etiquette  Forum Rules 
 
Live for the present,
Dream of the future,
Learn from the past.  :)

mLipok Universalist

mLipok

Posted
Posted

Oh, indeed.
The page was not refreshed, I had it open for the last two hours.
I worked on other things, and I came back to this page.
Sorry.

Signature beginning:
* Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
* ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Code * for other useful stuff click the following button:

  Reveal hidden contents

Signature last update: 2023-04-24

  • 5 weeks later...
maniootek Universalist

maniootek

Posted
Posted

Guys can you try insert to your mysql database this value  

#include "EzMySql.au3"
#include <Array.au3>

If Not _EzMySql_Startup() Then
    MsgBox(0, "Error Starting MySql", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf
Global $mysql_host = "localhost"
Global $mysql_user = "root"
Global $mysql_pass = "root123"
Global $mysql_name = "table"

$string = "aącćeęlłnńoósśzżzź"
$q_insert = "INSERT INTO `tablename`.`colname` (`col_1`) VALUES ('" & $string & "')"

If Not _EzMySql_Open($mysql_host, $mysql_user, $mysql_pass, $mysql_name, "3306") Then
    MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_Exec($q_insert) Then
    MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

_EzMySql_Close()
_EzMySql_ShutDown()
Exit

this all about charset to write $string value properly. At my sidem, when I read inserted entry, it look this way:

charset2.PNG

willichan Universalist

willichan

Posted
Posted (edited)

My first though would be to use mysql_real_escape_string().  (https://dev.mysql.com/doc/refman/5.7/en/mysql-real-escape-string.html)

SInce EzMySql does not have an implementation of mysql_real_escape_string(), you will either need to write one, or switch to the MySQL UDF that does have one.

I am far from the SQL expert here though, so someone may have a better solution for you.

Edited by willichan
Added Link

UserBarPurpleGeekNerd.GIF


My UDFs: Barcode Libraries, Automate creation of any type of project folder, File Locking with Cooperative Semaphores, Inline binary files, Continue script after reboot, WinWaitMulti, Name Aggregator, Enigma, CornedBeef Hash

 

jchd Universalist

jchd

Posted
Posted

@maniootek,

What is the column encoding specification?

  Reveal hidden contents

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

mLipok Universalist

mLipok

Posted
Posted

@jchd

here is answer to your question:

@maniootek 

I see you are trying to use second UDF.
As I see without success.

I know you know about my ADO.au3 UDF, so I ask directly:    Why you are not using my ADO.au3 ?

 

Signature beginning:
* Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
* ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Code * for other useful stuff click the following button:

  Reveal hidden contents

Signature last update: 2023-04-24

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
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...