Saving Field Data to Your SQLite DB

Saving Field Data to Your SQLite DB - Part Two
Cut out the tedium and errors of copying data from your fields into your database with this neat solution

Last time we looked at a way to write all of the field and button values from a LiveCode card to an SQLite file without having to specify either the field names or database column names. This time I’ll demonstrate the reverse operation, that is, reading all of the column values back into LiveCode fields and buttons without having to specify either the names of the fields or the columns. With these two routines in hand you can read/write any set of LiveCode fields/buttons to an SQLite file.

This might be a good time to mention an errata to the code provided in the last issue. In that issue, there was a line that read:

   if fld tFieldName <> "Label Field" and the cStoreMe of fld \
      tFieldName <> "N" then

   if tFieldName <> "Label Field" and the cStoreMe of fld \
      tFieldName <> "N" then

My apologies to anyone who wasted time trying to figure out why “Label Field” was trying to be saved to the database instead of being ignored.

This time we are going to read the values from a single SQL database record into corresponding fields on a LiveCode card. In this example we will use a recordset not because we are reading multiple records, but because the revDatabaseColumnNamed function allows us to read values out of the recordset by column name. And, since the column names and field names in our application are the same, by consecutively passing the name of each field to the revDatabaseColumnNamed function as a parameter we can extract each of the corresponding database values reliably. The only time this scheme should fail is when the field and column names are no longer in sync (for example, if you change the name of a field and forget to change its corresponding database column name, a mistake I’ve made on more than one occasion <g>).

By the way, the big advantage of reading and writing these values by name instead of position is that we can add or delete fields or modify the order of fields or column positions and, as long as the names of the LiveCode fields and the database column names remain synchronized, these routines will read and write the values properly. If they do get out of sync it’s generally not a big deal because the associated error message tells you immediately what you need to fix. Ok, on to the example:

put "Select * FROM myDBtable where UniqueID = " & UniqueID into \
      tQuery -- replace myDBtable with your SQLite table name
put revQueryDatabase(gConnectID, tQuery) into theCursor -- the \
      cursor is an ID that points to a recordset
if theCursor is not an integer then 
      answer error \
         "There was a problem reading from the database:\
 " & theCursor as sheet
end if

-- loop through all of the fields and attempt to read the
-- corresponding column value into the field, by fieldname
repeat with y = 1 to the number of fields of this card 
      put the short name of field y into tfieldname 
      if tFieldname <> "Label Field" and the cStoreMe of fld \
         tFieldName <> "N" then
            try 
                  put revDatabaseColumnNamed(theCursor, tfieldname) into \
               fld tFieldName -- copy item from recordSet to field by fieldname
            catch theError 
                  answer error \
               "There was a problem reading from the database:\
 " & theError as sheet
                  exit to top -- halts the current and all pending msg \
               handlers
            end try 
      end if 
end repeat

And that’s it. Just 16 lines of code. And as in the example I presented last time, you can do this for button values as well. You’ll just need to change 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 -- use if you \
            want to skip a button
                     put the short name of button y into tButtonName 
                     try 
                           put revDatabaseColumnNamed(theCursor, \
                  tButtonName) into temp -- copy item from recordSet\
by button name
                     catch theError 
                           answer error \
                  "There was a problem reading from the database:\
               " & theError as sheet
                           exit to top -- halts the current and all pending \
                  msg handlers
                     end try 
                     if temp is true then 
                           set the hilite of button tButtonName to true 
                     else -- for all other cases 
                           set the hilite of button tButtonName to false 
                     end if 
               end if 
         end if 
   end repeat

You may notice there is an ‘if-then’ statement to test if the value of ‘temp’ is true. This is because if you are reading from a record that has never been written to before, the value of Boolean fields will be undefined and this causes an error in LiveCode. By including the ‘if-then’ statement we can detect this situation and initialize the undefined values to ‘false’, which is what you want.

Now that you have routines to both read and write LiveCode fields (and buttons) to database columns without having to specify the names of either the fields/buttons or the columns (which was the original goal of this series) I was going to end right here. However, an astute reviewer suggested I could improve things by combining the field and button sections into a single loop and then move the whole thing up to the stack level of the application. So, next time we’ll do just that. Putting the routine at the stack level means reduced maintenance headaches should you decide to make any changes to it in the future (since you only have one copy of the routine in your application to modify). If you are looking for a challenge you may want to try this on your own. Until next time...