| Issue 131 | March 23rd 2012 | Contact the Editor | How to Contribute |
|
| ||
|
Saving Field Data to Your SQLite DB Recently I got involved in a large database project that involves hundreds of fields on dozens of cards. For this project I am using the SQLite database supplied with LiveCode. While similar to other SQL implementations, this was my first foray into the world of SQL and I found the process of copying data from LiveCode fields into database columns extremely tedious and error prone. In this article I will provide a solution to that problem. I started by using a process I’ve seen in many LiveCode stacks which involves copying the contents of the field into a variable, and then referencing the variable in a revExecuteSQL statement. This means there are 3 named objects involved in the transfer of each value; the original field, the referenced variable and finally the destination column. I ended up with code that looked like this: Put fld “myField1” into myVariable1 Put fld “myField2” into myVariable2 Put fld “myField3” into myVariable3 etc, etc… put "UPDATE master SET " & merge("my_column_name1='[[myVariable1]]', \ my_column_name2='[[myVariable2]]',\ my_column_name3='[[myVariable3]]' ") & \ "WHERE recordID = " & tRecordID into tCmd As you can see, for 30 or more fields you can easily end up with 50 lines of code just to write the values out, and another 50 or more lines to read the data values back in and re-populate the fields. I was also generating a lot of errors trying to type all of the field, variable and column names correctly and get the format of the SQL statement right. There had to be an easier way. About the same time I was contemplating this problem, Peter Haworth, one of the many frequent contributors on the Livecode Users forums, posted a code example in answer to a question I had asked about dynamically creating revExecuteSQL statements. Peter’s example showed a way to generate a revExecuteSQL statement that never made direct mention of either the field name or the database column name and, while it was written for a different purpose, I could see how it could be adapted to write out any set of LiveCode fields to an SQLite file. So, with Peter’s example in hand and a bit of tweaking I arrived at the following generic routine which will save any number of fields from a LiveCode card to an SQLite file. I added code to skip field labels (since they are considered fields by LiveCode too). I also created a custom property called cStoreMe which can be used to skip over fields you don’t want written to the SQLite file. Just put the value “N” or “n” into the cStoreMe property of a field and it will be skipped. There is no need to define a custom property for fields that are being stored since the code only tests for the value “N”. The only two requirements for this routine to work are (a) the field names and database column names must be the same and (b) if you want to skip over labels they should have the default name “Label Field”. In my own experience including spaces or other kinds of punctuation in the field and column names has not been a problem. put "UPDATE myDBtable SET " into tCmd -- replace myDBtable \ with your SQLite table name put 1 into x repeat with y = 1 to the number of fields of this card put the short name of field y into tFieldName -- filter out label fields and fields that have their -- cStoreMe property set to "N" if fld tFieldName <> "Label Field" and the cStoreMe of \ fld tFieldName <> "N" then put quote & tFieldName & quote & "=:" & x & comma & \ space after tCmd -- build the SQL statement, fieldname by fieldname put the text of fld tFieldName into myArray[x] -- \ put the field value into an array add 1 to x end if end repeat put space into char -2 of tCmd -- removes the last comma put "where UniqueID=" & tUniqueID after tCmd – if you are \ updating just 1 record -- put return & tCmd after msg -- uncomment this line if you -- want to see what tCmd looks like revExecuteSQL gConnectID, tCmd, "myArray" if the result is not an integer then -- our write operation did not succeed answer error \ "There was a problem saving the fields on this form: " \ & the result as sheet end if And that’s it. Excluding comments its only 16 lines of code, and it will work for any number of fields on any card. As written you should place it in the card script or in a button on the card. You can also use the same approach to save the values of checkboxes and radio buttons to an SQLite file. All that needs to change is the repeat loop as follows: repeat with y = 1 to the number of buttons of this card if the style of button y = "checkbox" or the style of \ button y = "radiobutton" then if the cStoreMe of button y <> "N" then put the short name of button y into \ tButtonName put quote & tButtonName & quote & "=:" & x & comma & \ space after tCmd put the hilite of button tButtonName into \ myArray[x] add 1 to x end if end if end repeat And, of course, you should change the error message to refer to buttons instead of fields. Otherwise, everything before and after the repeat loop will remain the same. Apart from the tremendous amount of time you’ll save not having to write out individual field and variable names or debug the revExecuteSQL statement there is another benefit to this approach. Since it uses the “substitution” form of revExecuteSQL, there is no need to sanitize your inputs to prevent SQL injection, or to ‘escape’ characters that are causing your SQL commands to fail (like single quotes, for example). I’m afraid I’ve run out of space but before I close I would just like to thank all the people on the Livecode User forums who contributed ideas to the development of this as well as to those who reviewed earlier drafts of this article. Your contributions are very much appreciated. Next time I’ll show you how to do the reverse operation, that is, read all of the database column values back into your LiveCode fields, by fieldname. However, if you are looking for an SQL challenge, you may want to attempt this on your own. Until next time. Happy (live) coding.
| 
|
