LiveCode Forums.

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;

Script:		UPDATE `dishes` SET `dishname` = '" & mydishname & "'  WHERE `dishid` = " & mydishid
Resolved:	UPDATE `dishes` SET `dishname` = 'Pizza'  WHERE `dishid` = 42

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.
...

This is my private strong opinion.

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.

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.

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.

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