Sign in to follow this  
Followers 0
lyonsksd

MYSQL.AU3 and variables

8 posts in this topic

I had a quick question about _mysql_query and using variables. Is it possible to use variables in my _mysql_query command and actually get it to enter data into a table. I am using Apache as my server and MySql for a database (Xampp) running on my local PC. I am able to enter data into my tables, but only if I type everything out. What I would like to do is to use variables so I don't have to have a unique line every time I try to enter more data. It is for data collection on an automated testing application at work. Every time I try to run my script, nothing gets entered, however if I type everything in manually, it works. Any ideas on what I am doing wrong?

I am using MySQL.au3 from Prog@ndy.

#include <array.au3>
#include "mysql.au3"
#include <Date.au3>

_MySQL_InitLibrary("C:\Program Files\AutoIt3\AutoItX\libmysql.dll", "false")
If @error Then Exit MsgBox(0, '', "")
$MysqlConn = _MySQL_Init()
$res = _MySQL_Store_Result($MysqlConn)

$mydatabase = 'brn'
;$date = @Year & "-" & @Mon & "-" & @Mday & ","
global $date = "'2011-01-20'"
global $user = "'Joe Plumber'"
global $SN = "'123456'"
global $status = "'pass'"
$values = "INSERT INTO mydata (date, user, SN, status) VALUES("

$values2 = '"'& $values & $date & "," & $user & "," & $SN & "," & $status & ")" & '"'

MsgBox (0,"test2", VarGetType($values2) & $values2)
$a = StringToASCIIArray($values2)
_ArrayDisplay($a)



$connected = _MySQL_Real_Connect($MysqlConn, "localhost", "admin", "1admin", "test", 0, "", 0)
If $connected = 0 Then Exit MsgBox(16, 'Connection Error', _MySQL_Error($MysqlConn))

;_mysql_query($MysqlConn,"INSERT INTO mydata (date, user, SN, status) VALUES('2011-01-20', 'Joe Plumber', '123459', 'pass')")


_mysql_query($MysqlConn,$values2)




; Query share
_MySQL_Free_Result($res)

; VEnd connection
_MySQL_Close($MysqlConn)
; MYSQL end
_MySQL_EndLibrary()

As you can see, I even entered in _ArrayDisplay to see if there were any stray "null" characters. Any help would be appreciated.

Thanks,

Daryl

Share this post


Link to post
Share on other sites



Don't put literal double quotes around the assembled query:

$values = "INSERT INTO mydata (date, user, SN, status) VALUES("
$values2 = $values & $date & "," & $user & "," & $SN & "," & $status & ")"

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Yet be sure to escape (= double) any occurence of single quotes in the text litterals, or your insert will mean failure.


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)

Share this post


Link to post
Share on other sites

That's what I would try to use:

$values = "INSERT INTO mydata (date, user, SN, status) VALUES("

$values2 = $values & "'" & $date & "','" & $user & "','" & $SN & "','" & $status & "')"

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

That's not enough ErrMa.

Say that $user contains O'Connor and you have a problem due to the single quote inside the text.

What I use is this:

Func X($s)
    Return ("'" & StringReplace($s, "'", "''", 0, 1) & "'")
EndFunc   ;==>X

Func XX($s)
    Return (",'" & StringReplace($s, "'", "''", 0, 1) & "'")
EndFunc   ;==>XX

X and XX chosen as short names. Example (assuming all columns are text):

$values = "INSERT INTO mydata (date, user, SN, status) " & _
                    "VALUES (" & _
                                X($date) & _
                                XX($user) & _
                                XX($SN) & _
                                XX($status) & _
                            ")"
Edited by jchd

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)

Share this post


Link to post
Share on other sites

Hello jchd,

I use something like StringReplace($s, "'", "\'", 0, 1) like the Reference does.

' -> \'

MySQL Reference 5.5

Share this post


Link to post
Share on other sites

Sorry, I confused with the escape sequence used by other engines (simple doubling). My bad.


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)

Share this post


Link to post
Share on other sites

Thanks to everyone who replied. The answer turned out to be too easy.

I ended up doing this.

#include <array.au3>
#include "mysql.au3"
#include <Date.au3>

_MySQL_InitLibrary("C:\Program Files\AutoIt3\AutoItX\libmysql.dll", "false")
If @error Then Exit MsgBox(0, '', "")
$MysqlConn = _MySQL_Init()
$res = _MySQL_Store_Result($MysqlConn)


$mydatabase = 'mydata'
$date = @Year & "-" & @Mon & "-" & @Mday
$user = "Daryl O'Conner"
$SN = "123456"
$status = "pass"


$values = "INSERT INTO " & $mydatabase & " (date, user, SN, status) " & _
                    "VALUES (" & _
                                X($date) & _
                                XX($user) & _
                                XX($SN) & _
                                XX($status) & _
                            ")"

Func X($s)
    Return ("'" & StringReplace($s, "'", "\'", 0, 1) & "'")
EndFunc   ;==>X

Func XX($s)
    Return (",'" & StringReplace($s, "'", "\'", 0, 1) & "'")
EndFunc   ;==>XX



$connected = _MySQL_Real_Connect($MysqlConn, "localhost", "admin", "1admin", "test", 0, "", 0)
If $connected = 0 Then Exit MsgBox(16, 'Connection Error', 

_MySQL_Error($MysqlConn))

_mysql_query($MysqlConn,$values)

It works like a charm. Thanks for all your help. I never knew that creating my own function could be so easy. Thanks jchd and EnrMa for the example.

Daryl

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
Sign in to follow this  
Followers 0