Update SQL Query (Resolved)

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

Post Reply
EddieLee
Posts: 118
Joined: Thu Oct 25, 2018 1:06 pm

Update SQL Query (Resolved)

Post by EddieLee » Sun May 10, 2020 1:17 pm

Hi all,

Currently programming an android application and I need to update a record whenever a user makes a change to his details. Currently, I’m using a POST method to my server side scripts that allow user to view, insert. But I’m stuck at how to go about doing the update query and all the ways I tried have failed.

Server side

Code: Select all

<?lc
put $_POST["query"] into tSQLQuery 
put $_POST["dishname"] into dname
put revOpenDatabase("mysql","127.0.0.1","mydb","mydbusername","mydbpassword") into tDBID 
revExecuteSQL tDBID, tSQLQuery, "dname", "dishname"


revclosedatabase tDBID 

                                   
?>
App side

Code: Select all

 put "UPDATE `dishes` SET 'dishname' = '"& dishname &"' WHERE  `dishid` = '"& dishid &"'"into tSQLQuery
   put "query="&urlencode(tSQLQuery) into tSQLQuery
   post dishname to url "https://mywebsite/mysecretpage.lc"
   post tSQLQuery to url "https://mywebsite/mysecretpage.lc"
   answer it
For now the query cannot execute and no result was returned to me. Hope to get some help/advice.

Thanks!
Last edited by EddieLee on Thu May 14, 2020 4:43 pm, edited 2 times in total.
Eddie :D

sphere
Posts: 1145
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

Re: Update SQL Query

Post by sphere » Sun May 10, 2020 8:53 pm

Code: Select all

put "UPDATE `dishes` SET 'dishname' = '"& dishname &"' WHERE  `dishid` = '"& dishid &"'"into tSQLQuery
should be

Code: Select all

put "UPDATE `dishes` SET '"& dishname &"' WHERE  `dishid` = '"& dishid &"'"into tSQLQuery
this looks to much php style with all those quotes and double qoutes
so
else it is

Code: Select all

put "UPDATE `dishes` SET " & dishname & " WHERE  `dishid` = " & dishid into tSQLQuery
then it will work probably, your table name can not be dishname= variable holder, so you need to replace the tablename with the variableholder

EddieLee
Posts: 118
Joined: Thu Oct 25, 2018 1:06 pm

Re: Update SQL Query

Post by EddieLee » Sun May 10, 2020 11:36 pm

Hi sphere,

I don’t understand. Shouldn’t it be

Code: Select all

UPDATE ‘dishes’ (table) SET ‘dishname’ (column) = ‘“ & dishname & “‘ (variable)  WHERE ‘dishid’ (column) = ‘“& dishid & “‘ (Variable)
Because ‘dishname’ is a column in the dishes table and I only want to update that colum first before moving in to other columns. I wonder if my server side scripts are correct when receiving the query.

Thanks for your help!
Eddie :D

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: Update SQL Query

Post by AxWald » Mon May 11, 2020 9:56 am

Hi,
  1. Don't name your variables like your db fields. If you want to use a variable explicitly for a db field "dishname", call it "tdishname" or "mydishname" or thedishname" or such. This will save you a lot of headache.
    .
  2. Clean up your quote characters, you only use 3 of then:
    1. ' - single quotes: to quote string values in the SQL query;
    2. `- accent grave: to quote field or table names in the SQL query;
    3. " - double quotes: for the concatenation of LC strings used to construct your SQL query;
So, your query will look like this:

Code: Select all

Script:		UPDATE `dishes` SET `dishname` = '" & mydishname & "'  WHERE `dishid` = " & mydishid
Resolved:	UPDATE `dishes` SET `dishname` = 'Pizza'  WHERE `dishid` = 42
Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

golife
Posts: 103
Joined: Fri Apr 02, 2010 12:10 pm

Re: Update SQL Query

Post by golife » Mon May 11, 2020 10:55 pm

To make SQL statements easier to write, I am using the merge() function of LC which comes in handy here. For example consider INSERT, UPDATE or SELECT statements:

Code: Select all

 
 function selectAll pTableName
    # Returns all records from the specified table of the current database
    -- pTableName is a parameter containing the table name
    // Code snippet for an SQL SELECT statement
    // sDBID is the database connection id
    // pTableName is a variable containing the actual table name    
    local tSQL -- contains the SQL statement
    local tRecords -- contains the result string

      put merge ( "SELECT * FROM '[[pTableName]]'" ) into tSQL
      put revDataFromQuery( tab , return , sDBID , tSQL ) into tRecords
      -- insert a test result statement here...
      return textDecode ( tRecords, "UTF-8" )
      
   end selectAll
For anybody living outside of the US and Great Britain, please use textEncode and textDecode functions for any text supplied and retrieved from a database. Any non ASCII character will otherwise not render correctly.

And check the merge function in the dictionary. It merges the statement with the content of variables which need to simply be enclosed in double square brackets.

A few additional practical suggestions working with databases:

Just for consistency of statements, I am always enclosing table or column names supplied to the database with single quotes. To avoid conflicts with case sensitive naming rules and therefor upperToLower conversion functions, I am also using small case for database names (including the name of the database, tables, columns/fields, indices, etc.). As long as you have control over your database, you can make life much easier. The problems may start when you have to work on legacy databases or databases of third party developers.

Naming conventions and standards are very important, especially in the long run. And since it is considered bad practice in JavaScript to not declare all variables explicitly, it should be declared bad practice in LC as well. It is so much easier to understand them declaring them at the beginning of each function/command. And it should be enforced. You can enforce it yourself setting the *Strict Compilation Mode" in the Edit:Preferences:Script Editor of the IDE to true. The more complex an application becomes, the more you will be happy having enforced such rule. Many problems will just never appear. This is my private strong opinion. Also, this forces discipline in naming and using a consistent naming schema and forces to rethink. Bad naming leads to bad thinking and therefore to bad code.

And another hint to be succesful with SQL: Learn and understand the Normalization rules. Understand them fully and exercise them at least up to the so called 3NF (third normalized form). Most problems start in not thinking in terms of entities and relationships of classes of data. This type of thinking even helps in many other business and private domains, for example in organizing your office or home. It also affects the way user interfaces are created and maintained or functions/relations are written establishing the logic of any application.

Thierry
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 875
Joined: Wed Nov 22, 2006 3:42 pm

Re: Update SQL Query

Post by Thierry » Tue May 12, 2020 8:49 am

golife wrote:
Mon May 11, 2020 10:55 pm
....
The more complex an application becomes, the more you will be happy having enforced such rule. Many problems will just never appear. This is my private strong opinion. Also, this forces discipline in naming and using a consistent naming schema and forces to rethink. Bad naming leads to bad thinking and therefore to bad code.
...
Very much well said, golife.
This is my private strong opinion.
More than that,
quite a number of experienced developer, whatever the language they use, share this opinion.

Actually, I personaly run away from anyone not acting this way in my professional environment.


I would kindly suggest you copy-paste your thoughts in the database forums, so to be more visible.

Kind regards,

Thierry
!
SUNNY-TDZ.COM doesn't belong to me since 2021.
To contact me, use the Private messages. Merci.
!

sphere
Posts: 1145
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

Re: Update SQL Query

Post by sphere » Tue May 12, 2020 11:28 am

Yes, i do this too. I enforce Strict Compilation and declare all variables.
So i know it is always correct and no hassle afterwards. I just noticed a longer while ago that this is best practice.

golife
Posts: 103
Joined: Fri Apr 02, 2010 12:10 pm

Re: Update SQL Query

Post by golife » Tue May 12, 2020 2:38 pm

It is a good idea from Thierry to publish something about best practice and conventions in the database section of this forum. I will be thinking about it and how to keep it short. I hope Thierry and others will join in. And, of course, conventions and best practice apply to LC in general and any programming language. There are already some sources, for example from Richard or from André Garcia's book and from others. I would wish that such standards and best practices would officially be recommended for everyone when using LiveCode beyond the "Hello World" example.

-- Keep going -- ) Golife

Thierry
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 875
Joined: Wed Nov 22, 2006 3:42 pm

Re: Update SQL Query

Post by Thierry » Wed May 13, 2020 7:25 am

golife wrote:
Tue May 12, 2020 2:38 pm
It is a good idea from Thierry to publish something about best practice and conventions in the database section of this forum.
I will be thinking about it and how to keep it short.
Hi Golife,

Last week-end, I've been looking after all the DB commands in the dictionary, read the lessons and read the forums
to grasp as much as I could. I had to do so to refresh my memories as I didn't use any DB API with LC since a long time.
For most of my developments, I'm happy with formatted-encrypted text files.
So, no I'm not an expert on coding DBs within LC.

In this process, I found AxWald posts and yours very valuable.


In fact my thinking was to simply move or copy what you did explain here in the DB forum; nothing more really.
May be that will bring some more advices from DB experts; who knows...

Regards,

Thierry
!
SUNNY-TDZ.COM doesn't belong to me since 2021.
To contact me, use the Private messages. Merci.
!

EddieLee
Posts: 118
Joined: Thu Oct 25, 2018 1:06 pm

Re: Update SQL Query

Post by EddieLee » Thu May 14, 2020 8:03 am

golife wrote:
Mon May 11, 2020 10:55 pm
To make SQL statements easier to write, I am using the merge() function of LC which comes in handy here. For example consider INSERT, UPDATE or SELECT statements:

Code: Select all

 
 function selectAll pTableName
    # Returns all records from the specified table of the current database
    -- pTableName is a parameter containing the table name
    // Code snippet for an SQL SELECT statement
    // sDBID is the database connection id
    // pTableName is a variable containing the actual table name    
    local tSQL -- contains the SQL statement
    local tRecords -- contains the result string

      put merge ( "SELECT * FROM '[[pTableName]]'" ) into tSQL
      put revDataFromQuery( tab , return , sDBID , tSQL ) into tRecords
      -- insert a test result statement here...
      return textDecode ( tRecords, "UTF-8" )
      
   end selectAll
For anybody living outside of the US and Great Britain, please use textEncode and textDecode functions for any text supplied and retrieved from a database. Any non ASCII character will otherwise not render correctly.

And check the merge function in the dictionary. It merges the statement with the content of variables which need to simply be enclosed in double square brackets.

A few additional practical suggestions working with databases:

Just for consistency of statements, I am always enclosing table or column names supplied to the database with single quotes. To avoid conflicts with case sensitive naming rules and therefor upperToLower conversion functions, I am also using small case for database names (including the name of the database, tables, columns/fields, indices, etc.). As long as you have control over your database, you can make life much easier. The problems may start when you have to work on legacy databases or databases of third party developers.

Naming conventions and standards are very important, especially in the long run. And since it is considered bad practice in JavaScript to not declare all variables explicitly, it should be declared bad practice in LC as well. It is so much easier to understand them declaring them at the beginning of each function/command. And it should be enforced. You can enforce it yourself setting the *Strict Compilation Mode" in the Edit:Preferences:Script Editor of the IDE to true. The more complex an application becomes, the more you will be happy having enforced such rule. Many problems will just never appear. This is my private strong opinion. Also, this forces discipline in naming and using a consistent naming schema and forces to rethink. Bad naming leads to bad thinking and therefore to bad code.

And another hint to be succesful with SQL: Learn and understand the Normalization rules. Understand them fully and exercise them at least up to the so called 3NF (third normalized form). Most problems start in not thinking in terms of entities and relationships of classes of data. This type of thinking even helps in many other business and private domains, for example in organizing your office or home. It also affects the way user interfaces are created and maintained or functions/relations are written establishing the logic of any application.
Hi Golife,

Thanks for your very helpful input, will this way of doing this establish a connection to the database securely and not connecting the database through the app directly? And also will it reduce the amount of times i would have to write an SQL statement to prevent the app from getting laggy?

Thanks !
Eddie :D

golife
Posts: 103
Joined: Fri Apr 02, 2010 12:10 pm

Re: Update SQL Query

Post by golife » Thu May 14, 2020 1:51 pm

Well, thank you, but here I was trying to give ADDITIONAL best practice hints, not talking about specific database connection variants. I think, in this Forum such discussion is going on and has been addressed regarding connections methods.

For newbies to database development, I suggest to first be working with the local SQLite and get acquainted with SQL syntax and SQL standards. With SQLite, security issues do not play such an important role -- as long as you are working on your own machine.

EddieLee
Posts: 118
Joined: Thu Oct 25, 2018 1:06 pm

Re: Update SQL Query

Post by EddieLee » Thu May 14, 2020 3:17 pm

golife wrote:
Thu May 14, 2020 1:51 pm
Well, thank you, but here I was trying to give ADDITIONAL best practice hints, not talking about specific database connection variants. I think, in this Forum such discussion is going on and has been addressed regarding connections methods.

For newbies to database development, I suggest to first be working with the local SQLite and get acquainted with SQL syntax and SQL standards. With SQLite, security issues do not play such an important role -- as long as you are working on your own machine.
Hi golife,

I understood what you meant, however by using the merge function, does it reduce the amt of sql query that i need to write or does it decrease the amount of times that i need to connect to the database to retrieve the data? And also i tried using your codes to do a trial run but it doesnt work for me. It says that "Database error : No such table : "dishes" but there is a table that is name dishes in my database.

Code: Select all

 # Returns all records from the specified table of the current database
   -- pTableName is a parameter containing the table name
   // Code snippet for an SQL SELECT statement
   // tDBID is the database connection id
   // pTableName is a variable containing the actual table name    
   local tSQL -- contains the SQL statement
   local tRecords -- contains the result string
   put "dishes" into pTableName
   put merge ( "SELECT dishid FROM '[[pTableName]]'" ) into tSQL
   put revDataFromQuery( tab , return , tDBID , tSQL ) into tRecords
   return textDecode ( tRecords, "UTF-8" )
Thanks !
Eddie :D

golife
Posts: 103
Joined: Fri Apr 02, 2010 12:10 pm

Re: Update SQL Query (Resolved)

Post by golife » Sat May 16, 2020 12:15 pm

Sorry for late answer. I only receive messages from the Forum once a day.

The merge() function (please look up the Dictionary) is merging expressions/variable content with surrounding text. It makes it easier to combine variables and text. There is no other connection with the SQL and it does not have any influence on the database operation. All you can find in the lessons and dictionary and notes regarding the actual execution statement for SQL in this Forum still applies.

Test the result in putting the merge() result into the message box. You can see any error there.

If your execution of your SQL statements fails, please check if your database has been set up, if you point to the correct location of the database file (local or remote), if you have a VALID connection id, if the table and columns you are referring to are actually existing, if you have the user rights to access.... Always check "the result". (There is a nice tool out there called HeidiSQL which makes your life easier and it is donation based. There are other database tools as well for sqLite, and others. You can also test all your SQL statements using such tools.)

Also be watchful with single quotes (or also called simple apostrophes) as well as double quotes. FOR SQL I am also usally using a function that encloses strings with single quotes:

Code: Select all

// I am writing this as we are in the beginner's section -- even if redundant to similar information given here before.

on mouseUp
# A test script in a button. You could also just use the messages box without button to test.
   local tString
   put "This is my example string." into tString
   put sq ( tString ) into msg -- put the result into the msesages box
   put CR & dq ( tString ) after msg -- put this result after the first line into the messages box
 end mouseUp
 
function sq pString
   return "'" & pString & "'"
end sq

function dq pString
  return quote & pString & quote
end sq
Single or double quotes are already part of the example merge function. So, with merge() we often do not need these other help functions or more complicated concatenation strings that are difficult to read and construct.

But check that the strings themselves do not contain valid single or double quotes which otherwise must be escaped.

Generally, use the single quote (Unicode value 0027) and the double quote (Unicode value 0022) for enclosing strings according to SQL standards. They can easily be mistaken for other types of apostrophes or curly/special single/double quotes which are completely different characters. You could eventually add a conversion function in the sq or dq function to always return single/double quotes as expected (values 0027 or 0022).

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”