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.