Jump to content

[TOPIC: topicViewTemplate]
[GLOBAL: userSmallPhoto]
Photo

MySQL Update problem
Started by guidobono May 22 2019 01:33 PM

9 replies to this topic
mysql update

Best Answer SGS , 23 May 2019 - 12:42 PM

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

[TOPIC CONTROLS]
[/TOPIC CONTROLS]
[modOptionsDropdown]
[/modOptionsDropdown]
[reputationFilter]
[TOPIC: post.html]
#1

guidobono

[GLOBAL: userInfoPane.html]
guidobono
  • Observer

  • 1 posts
  • Corona SDK

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.

 



[TOPIC: post.html]
#2

Rob Miracle

[GLOBAL: userInfoPane.html]
Rob Miracle
  • Moderator

  • 25,656 posts
  • Enterprise

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



[TOPIC: post.html]
#3

nick_sherman

[GLOBAL: userInfoPane.html]
nick_sherman
  • Corona Geek

  • 1,787 posts
  • Corona SDK

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.

 

 
local query = [[UPDATE Table set Name = "]]..name.text..[[" where id = ]]..idSel

 

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. 



[TOPIC: post.html]
#4

SGS

[GLOBAL: userInfoPane.html]
SGS
  • Corona Geek

  • 2,080 posts
  • Corona SDK

  Best Answer

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



[TOPIC: post.html]
#5

nick_sherman

[GLOBAL: userInfoPane.html]
nick_sherman
  • Corona Geek

  • 1,787 posts
  • Corona SDK

....and best to use my approach in case the name has an apostrophe in it ;)



[TOPIC: post.html]
#6

SGS

[GLOBAL: userInfoPane.html]
SGS
  • Corona Geek

  • 2,080 posts
  • Corona SDK

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



[TOPIC: post.html]
#7

nick_sherman

[GLOBAL: userInfoPane.html]
nick_sherman
  • Corona Geek

  • 1,787 posts
  • Corona SDK

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



[TOPIC: post.html]
#8

richard11

[GLOBAL: userInfoPane.html]
richard11
  • Contributor

  • 406 posts
  • Corona SDK

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()

[TOPIC: post.html]
#9

SGS

[GLOBAL: userInfoPane.html]
SGS
  • Corona Geek

  • 2,080 posts
  • Corona SDK

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;
        }


[TOPIC: post.html]
#10

richard11

[GLOBAL: userInfoPane.html]
richard11
  • Contributor

  • 406 posts
  • Corona SDK

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 😏


[topic_controls]
[/topic_controls]

Also tagged with one or more of these keywords: mysql, update