MYSQL.AU3 and variables

[lyonsksd]

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.

expandcollapse popup

#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

[PsaltyDS]

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 & ")"

[jchd]

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

[EnrMa]

That's what I would try to use:

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

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

[jchd]

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 January 23, 2011 by jchd

[EnrMa]

Hello jchd,

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

' -> \'

MySQL Reference 5.5

[jchd]

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

[lyonsksd]

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

I ended up doing this.

expandcollapse popup

#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