Saving Field Data to Your SQLite DB

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

This is the third and final part of this series. You can read part one here, and part two here.

In the last two installments I talked about ways to read and write LC fields into SQL database columns without having to specify the field or column names. The approach I used took advantage of the fact that if the LC field names and the SQL column names are the same, we can loop through the field names on the card and use that information to build up an SQL statement to save the data. We also did the same for buttons, but in a separate loop.

This time we are going to combine the button and field loops so that you can read or write both sets of LC data at the same time. Second, we will move the routine to the stack level allowing us to keep just one copy of the routine in the application. That way, if there are any changes required in the future, we only need to modify the code in one place.

Lets start with combining the field and button loops for the “save” data routine. Instead of looping though all of the fields and all of the buttons we can instead loop through all of the controls on the card and respond to only those controls we are interested in. The basic form of the loop would look like this:

set itemdelimiter to space 
repeat with y = 1 to the number of controls of this card 
      put word 1 of the abbrev name of control y into tType 
      if tType = “Button” or tType = “Field” then 
            if the cStoreMe of control y  <> “N” then 
                  put the short name of control y into tControlName 
                  switch tType 
                        case "field" 
                              -- do field stuff here 
                              break 
                        case "button" 
                              -- do button stuff here 
                              break 
                        default 
                  end switch 
            end if 
      end if 
end repeat

Beyond that there are only a few actions we need to perform depending if it’s a field or a button, and then we have the opening and closing lines of the routine which are identical for both buttons and fields. But before we fill those parts in, lets take a look at what will be required to move the routine to the stack level of the application.

First, we won’t know which card we are on so we can’t use the “of this card” syntax. We’ll need to pass the card ID to the routine and use that instead. Also, it may be the case (as it is in my application) that the fields and buttons on different cards are stored in different SQL tables. We could set up a function in memory that returns the name of the appropriate table based on the card ID, or we could pass the name of the SQL table to the routine along with the card ID. In this example I am going to opt for the latter approach and pass the name of the table into the routine. BTW, we will also need to give our routine a name and for the sake of clarity I’ve called it ‘SaveAllValues’. Since I automatically save all field and button values when the user leaves the card I put the call to this routine in the ‘closecard’ handler. You could also put it in a button or have it triggered by some other means.

on closecard 
      put the long ID of this card into tLongCardID 
      put "test" into tSQLTableName 
      saveAllValues tLongCardID, tSQLTableName 
end closecard

Combining the loop structure discussed above with the modifications necessary to move this routine to the stack level results in the following revised routine. This routine handles any set of fields or buttons on any card, saving them to a specified SQL table and you only need to have one copy of this routine in your application.

on saveAllValues pLongCardID, pSQLTableName 
      -- pass the long ID of the card you are calling this routine
   -- from
      -- and the name of the SQL table that you want to save the
   -- data to
      -- NOTE: I have implemented gConnectID and gID as global
   -- variables
      -- but you could also pass them as parameters if you prefer 
      put "UPDATE " & pSQLTableName & " SET " into tCmd  
      put 1 into x 
      set itemdelimiter to space 
      repeat with y = 1 to the number of controls of pLongCardID 
            put word 1 of the abbrev name of control y of pLongCardID \
            into tType
            if tType = "field" or tType = "button" then 
                  if the cStoreMe of control y of pLongCardID <> "n" then 
                        put the short name of control y of pLongCardID into \
                  tControlName
                        switch tType 
                              case "field" 
                                    if tControlName <> "Label Field" then -- skip \
                        label fields
                                          put quote & tControlName & quote & "=:" & \
                           x & comma & space after tCmd
                                          put the text of control y of pLongCardID \
                           into myArray[x]
                                          add 1 to x 
                                    end if 
                                    break 
                              case "button" 
                                    put the style of control y of pLongCardID \
                        into tStyle
                                    if tStyle = "checkbox" or tStyle = \
                        "radiobutton" then
                                          put quote & tControlName & quote & "=:" & \
                           x & comma & space after tCmd
                                          put the hilite of control y of pLongCardID \
                           into myArray[x]
                                          add 1 to x 
                                    end if 
                                    break 
                              default 
                        end switch 
                  end if 
            end if 
      end repeat 
      put space into char -2 of tCmd -- removes the last comma 
      put "where ID=" & gID 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 controls on this form: "\
 & the result as sheet
      end if 
end saveAllValues

Ok, that covers off saving fields and button values. What about reading them back in? Again we will apply the same modifications I’ve discussed above to the “read” routines I presented in the last issue. Since the looping structure is the same as the one above I won’t repeat it here. Instead, I’ll just focus on the differences. To start with, the opening statements are a bit different because we are now reading values instead of writing them:

on readAllValues pLongCardID, pSQLTableName 
   put "Select * FROM " & pSQLTableName & " where ID = " & gID \
         into tQuery
      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

if tControlName <> "Label Field" and the cStoreMe of fld \
      tControlName of pLongCardID <> "N" then
      try 
            put revDatabaseColumnNamed(theCursor, tControlName) into \
            fld tControlName of pLongCardID \
-- copy item from recordSet to field by control name
      catch theError 
            answer error \
            "There was a problem reading from the database: " \
& theError as sheet
            exit to top 
      end try 
end if

if the style of control y of pLongCardID = "checkbox" or the \
      style of control y of pLongCardID = "radiobutton" then
      try 
            put revDatabaseColumnNamed(theCursor, tControlName) into \
            temp -- copy item from recordSet by control name
      catch theError 
            answer error \
            "There was a problem reading from the database: "\
 & theError as sheet
            exit to top 
      end try 
      -- set the hilite of button tControlName 
      if temp is true then 
            set the hilite of button tControlName of pLongCardID to \
            true
      else -- for all other cases 
            set the hilite of button tControlName of pLongCardID to \
            false
      end if 
end if

The button-specific code is a tad longer because the “if-then” statement at the end is actually initializing the value of the button if it has never been defined before. We don’t need to do that for fields. Finally, we’ll call “readAllValues” in the same way we call “saveAllValues” the only difference being the command name. And I’ll just add a quick reminder that there is no code after the “end repeat” statement in “readAllValues” because the error checking is done inside the loop using the “try-catch” construct.

This completes the two modified routines. I’ve probably exceeded the amount of space Heather would have liked so, as I beat a hasty retreat, let me just add a quick thanks to all of the participants on the LC forums who were instrumental in helping to develop these routines.