MySQL Update problem

Getting Started Newbie Questions
#1

Hi

I am making an app with MySQL connection. Everything work as it should, but when I try to update a record it doesn’t work. No errors, no warnings, it simply (apparently) ignores the order.

   

local query = “UPDATE Table SET Name = " …name.text…” WHERE id ="… idSel
db:exec( query )

If I send the query to the console (print), it seems well constructed.

Could it be a permission’s problem? I am working with a Mac but I’m mostly a windows user.

Any help will be useful.

#2

Hello and welcome to the Corona community forums. Corona doesn’t support MySQL out of the box. We support SQLite. It looks like you’re using Lua, but we are going to need to know a lot more about your setup and how you’re trying to talk to a MySQL database.

Rob

#3

If you actually mean SQLite, then the problem with your query is that you aren’t using any quotes around the string. You need to wrap the string in [[]] in order to put double quotes in it.

[lua]

local query = [[UPDATE Table set Name = “]]…name.text…[[” where id =]]…idSel

[/lua]

Secondly, db:exec returns a number. 0 upon success, 1 upon error. I don’t think you get any more detail than that. However if I have a problem I can’t figure out I just paste the query into my SQLite editor (replacing any variables with hard-coded values) and that will give me the error. 

#4

Your SQL is invalid

local query = "UPDATE Table SET Name = " ..name.text.." WHERE id =".. idSel db:exec( query )

it should be

local query = "UPDATE Table SET Name = '" ..name.text.."' WHERE id = ".. idSel db:exec( query )

as the Name field is a string you need to enclose this in quotes

#5

…and best to use my approach in case the name has an apostrophe in it :wink:

#6

Well of course I wouldn’t use adhoc strings like ever… paramaterised PDO queries for me.

But to be fair yours would also break if the input was o"leary

#7

That’ll upset my mate Steve’o O’’’’'Shauness’y

#8

I use :prepare and :bind_names to make sure everything is escaped nicely. It doesn’t matter what the variables contain then.

variable1 and variable2 in this example could be input field texts, for example:

(Sorry - posting from my phone and can’t format properly)

local sql = db:prepare( [[INSERT INTO tblTable ( fldField1, fldField2 ) VALUES ( :p_fldField1, :p_fldField2 );]] )
sql:bind_names({ p_fldField1=variable1, p_fldField2=variable2 })
sql:step()

#9

I find PDO much cleaner

 try { //connect require\_once('pdo.inc.php'); //run query $sql = "UPDATE table SET field1=? WHERE field2=?"; $stmt = $db-\>prepare($sql); $stmt-\>bindValue(1, $value1, PDO::PARAM\_STR); $stmt-\>bindValue(2, $value2, PDO::PARAM\_STR); $stmt-\>execute(); } catch(Exception $ex) { //error occured echo $ex-\>getMessage(); } finally { //clean up $stmt = null; $db = null; }
#10

Oddly, I don’t use PDO in PHP. I built my own wrappers years ago for the native mysql_ commands and use that for everything. Came in handy when the mysql_ functions were deprecated by mysqli_ functions and I only had to amend the one wrapper library rather than every individual command throughout every project :smirk:

#11

Hello and welcome to the Corona community forums. Corona doesn’t support MySQL out of the box. We support SQLite. It looks like you’re using Lua, but we are going to need to know a lot more about your setup and how you’re trying to talk to a MySQL database.

Rob

#12

If you actually mean SQLite, then the problem with your query is that you aren’t using any quotes around the string. You need to wrap the string in [[]] in order to put double quotes in it.

[lua]

local query = [[UPDATE Table set Name = “]]…name.text…[[” where id =]]…idSel

[/lua]

Secondly, db:exec returns a number. 0 upon success, 1 upon error. I don’t think you get any more detail than that. However if I have a problem I can’t figure out I just paste the query into my SQLite editor (replacing any variables with hard-coded values) and that will give me the error. 

#13

Your SQL is invalid

local query = "UPDATE Table SET Name = " ..name.text.." WHERE id =".. idSel db:exec( query )

it should be

local query = "UPDATE Table SET Name = '" ..name.text.."' WHERE id = ".. idSel db:exec( query )

as the Name field is a string you need to enclose this in quotes

#14

…and best to use my approach in case the name has an apostrophe in it :wink:

#15

Well of course I wouldn’t use adhoc strings like ever… paramaterised PDO queries for me.

But to be fair yours would also break if the input was o"leary

#16

That’ll upset my mate Steve’o O’’’’'Shauness’y

#17

I use :prepare and :bind_names to make sure everything is escaped nicely. It doesn’t matter what the variables contain then.

variable1 and variable2 in this example could be input field texts, for example:

(Sorry - posting from my phone and can’t format properly)

local sql = db:prepare( [[INSERT INTO tblTable ( fldField1, fldField2 ) VALUES ( :p_fldField1, :p_fldField2 );]] )
sql:bind_names({ p_fldField1=variable1, p_fldField2=variable2 })
sql:step()

#18

I find PDO much cleaner

 try { //connect require\_once('pdo.inc.php'); //run query $sql = "UPDATE table SET field1=? WHERE field2=?"; $stmt = $db-\>prepare($sql); $stmt-\>bindValue(1, $value1, PDO::PARAM\_STR); $stmt-\>bindValue(2, $value2, PDO::PARAM\_STR); $stmt-\>execute(); } catch(Exception $ex) { //error occured echo $ex-\>getMessage(); } finally { //clean up $stmt = null; $db = null; }
#19

Oddly, I don’t use PDO in PHP. I built my own wrappers years ago for the native mysql_ commands and use that for everything. Came in handy when the mysql_ functions were deprecated by mysqli_ functions and I only had to amend the one wrapper library rather than every individual command throughout every project :smirk: