Using Databases in LiveCode pt. 2

DigHT 310
Managing and Integrating
Your Database in LiveCode (cont.)

Step 4: Editing database records

Now that we have the records from the people database displaying the way we want them, let's work on creating ways to add, delete and edit records.

Deleting records

Deleting records is the simplest database maintenance task to address, because it tracks perfectly with the straightforward SQL DELETE command. Remember, the syntax is:

   DELETE FROM tablename WHERE some condition is true

It is very important to always include a WHERE clause with a DELETE command; if you don't, you will delete every record in your database!

On the LiveCode side, your key command is revExecuteSQL, the command you use to execute all SQL commands except SELECT.

When we designed our acmeco database, we were careful to include a unique id number for each record. That means that if we execute the following command, it will delete one and only one record.

 DELETE FROM people WHERE person_id = 11

We wisely chose to include the person_id for each record when we wrote our SELECT query for the people table. The id number is always the first item of each record. Taking all of this information, we can pretty simply write the handler for our Delete Employee button:

# script of btn "delete"

global gConnID

on mouseUp
    put the dgHilitedLine of group "employeeList" into tLineToDelete
    if tLineToDelete is a number and tLineToDelete > 0 then
         
        # when deleting things, always give the user a way out
        answer warning "You are about to delete a person from your database. " &\
                "You cannot undo this!" with "Continue" or "Cancel" as sheet
        if it is "Cancel" then
            exit to top
        end if
        
        set the itemDelimiter to tab
        put item 1 of line tLineToDelete of the dgText of group "employeeList" into tID
        connectToDB
        
        # the next two lines do the database work
        put "DELETE FROM people WHERE person_id = " & tID into tCmd
        revExecuteSQL gConnID, tCmd
        
        # check for successful execution or error
        # successful queries return the number of rows affected; 
        # unsuccesful queries return an error string
        put the result into tRes
        if tRes is not a number then
            answer error "There was a problem deleting this record: " & tRes as sheet
        end if
        closeDB gConnID
        
        # finally, we want to refresh the list after a change is made, so...
        send "mouseUp" to btn "showAll"
    end if
end mouseUp

This may seem kind of long for a "simple" task. But if you study the handler you'll see that much of what we are doing is checking for valid data, presenting warnings, and checking for errors. When dealing with tasks that affect data integrity, caution is the order of the day.

Adding records

Adding records is also not difficult, but requires us to keep track of more pieces of data. We use the SQL INSERT command:

  INSERT INTO tablename (columnname1, columnname2, columnname3, ...)
  VALUES (value1, value2, value3, ...)

We don't need a WHERE clause here, because we are not affecting existing records.

Group "editRecordsGrp"

In our LiveCode stack, we're going to use the group "editRecordsGrp" to enter the data for the new record. Normally this group will be hidden, but we'll show it for Add and Edit operations. The script of button "add" will simply initialize and show the group:

on mouseUp
    set the label of btn "save" to "Add"
    initializeEditGrp # handler is in card script
    show group "editRecordsGrp"
    select the text of fld "fname" of group "editRecordsGrp"
end mouseUp

Initializing the edit group is important, and does the following:

empties out the fields;
re-labels the "save" button in group "editRecordsGrp" as "Add"
(we will be using the same group to change existing records, but in that case we will change the label of this button to something like "Save Changes")
populates the option menu button with the department names

Here is the initializeEditGrp handler. You should be able to understand what is happening here:

# in card script

on initializeEditGrp
    put empty into fld "fname" of group "editRecordsGrp"
    put empty into fld "lname" of group "editRecordsGrp"
    put empty into fld "phone" of group "editRecordsGrp"
    put empty into fld "office" of group "editRecordsGrp"
    getDeptList # remember this handler we wrote earlier?
    put the deptData of me into tDeptList 
    set the itemDelimiter to tab
    put "Choose:" into tDeptNames
    repeat for each line tLine in tDeptList
        put return & item 1 of tLine after tDeptIDs
        put return & item 2 of tLine after tDeptNames
    end repeat
    set the idList of btn "deptChoice" of group "editRecordsGrp" to tDeptIDs
    put tDeptNames into btn "deptChoice" of group "editRecordsGrp"
    set the menuHistory of btn "deptChoice" of group "editRecordsGrp" to 1
end initializeEditGrp

Once the user has filled in all of the data fields for the new employee record, our job is simply to gather the data and incorporate it into a SQL INSERT statement. Here is a handler that does the basics, in the script of button "save":

global gConnID

on mouseUp
   put fld "fname" into tFname
   put fld "lname" into tLname
   put fld "phone" into tPhone
   put fld "office" into tOffice
   put the menuHistory of btn "deptChoice" into tIDLine
   put line tIDLine of the idList of btn "deptChoice" into tDeptID
   connectToDB
   
   if the label of me is "Add" then
      put "INSERT INTO people (fname,lname,phone,office,dept) " &  \
            merge("VALUES ('[[tFname]]', '[[tLname]]', '[[tPhone]]', '[[tOffice]]', [[tDeptID]])") into tCmd
   else
      # this space will be used when saving an edited record
   end if
   revExecuteSQL gConnID, tCmd
   
   # check for successful execution or error
   put the result into tRes
   if tRes is not a number then
      answer error "There was a problem saving this record: " & tRes as sheet
   end if
   closeDB gConnID
    
   # refresh the list after a change is made
   send "mouseUp" to btn "showAll"
   hide group "editRecordsGrp"
end mouseUp

Even though this handler is perfectly functional, there are some a few issues that would need attention before releasing it for general use:

There should be a way to make sure the minimal required fields are completed before allowing the user to complete the add task. For example, we could disable the save button until the required fields are filled in; or we could check the fields in the mouseUp handler, and only allowing the save to go ahead if the required fields are completed.
Do you want to allow the user to do a delete when the Add task is open? It might be a good idea to disable the delete and edit buttons while the Add task is in progress.

Editing existing records

To edit an existing record we need to gather the data from the record, allow the user to change the fields they want, then save it back to the database. The SQL command for that is UPDATE, which has this syntax:

UPDATE tablename SET columnname1=value1, columnname2=value2, ...
WHERE some condition is true

Just as with the DELETE statement, it is imperative to always include a WHERE clause with an UPDATE command; if you don't, you will change the named columns for every record in your database!

The process for updating a record in our stack is very similar to that of inserting a record, with just two major differences:

We need to fill in the fields in the editRecordsGrp with data from the record we have chosen to edit.
We use UPDATE instead of INSERT to save the change to the database table.

Here is the script for button "edit":

global gConnID

on mouseUp
   put the dgHilitedLine of group "employeeList" into tLineToEdit
   if tLineToEdit is a number and tLineToEdit > 0 then
      set the label of btn "save" to "Save Changes"
      
      initializeEditGrp
      #fill in the fields in the editing form with data from the record you chose
      set the itemDelimiter to tab
      put line tLineToEdit of the dgText of group "employeeList" into tData
      # remember that item 1, the person_id, is hidden
      put item 2 of tData into fld "fname"
      put item 3 of tData into fld "lname"
      put item 4 of tData into fld "phone"
      put item 5 of tData into fld "office"
      put item 6 of tData into tDeptID
      set the menuHistory of btn "deptChoice" to lineOffset(tDeptID, the idList of btn "deptChoice")
      show group "editRecordsGrp"
      select the text of fld "fname" of group "editRecordsGrp"
   end if
end mouseUp

Once the edit form is open and filled in with data from the chosen record, the user can make changes. We have to modify the mouseUp handler in the "save" button now to do an UPDATE instead of an INSERT:

global gConnID

on mouseUp
   put fld "fname" into tFname
   put fld "lname" into tLname
   put fld "phone" into tPhone
   put fld "office" into tOffice
   put the menuHistory of btn "deptChoice" into tIDLine
   put line tIDLine of the idList of btn "deptChoice" into tDeptID
    
   connectToDB
   if the label of me is "Add" then
      put "INSERT INTO people (fname,lname,phone,office,dept) " &  \
            merge("VALUES ('[[tFname]]', '[[tLname]]', '[[tPhone]]', '[[tOffice]]', [[tDeptID]])") into tCmd
   else
      # when changing things, always give the user a way out
      answer warning "You are about to permanently change this record. " &\
            "You cannot undo this!" with "Continue" or "Cancel" as sheet
      if it is "Cancel" then
         exit to top
      end if
      set the itemDelimiter to tab
      put the dgHilitedLine of group "employeeList" into tLineToChange
      put item 1 of line tLineToChange of the dgText of group "employeeList" into tID
      put "UPDATE people SET " & \
            merge("fname='[[tFname]]', lname='[[tLname]]', phone='[[tPhone]]', office='[[tOffice]]', dept=[[tDeptID]]") & \
            " WHERE person_id = " & tID into tCmd
   end if
   revExecuteSQL gConnID, tCmd
   
   # check for successful execution or error
   put the result into tRes
   if tRes is not a number then
      answer error "There was a problem saving this record: " & tRes as sheet
   end if
   closeDB gConnID
    
   # refresh the list after a change is made
   send "mouseUp" to btn "showAll"
   hide group "editRecordsGrp"
end mouseUp

As before, even though this routine is working, we need to consider some usability issues before it is ready for release.

What if the user clicks on a different record in the list while the edit form is showing? Should the contents of the form change? Or should you just disallow clicking on other records when one is open for editing?
If nothing has changed in the edit form, should you still run the UPDATE? Or should you wait until at least one field has been changed before you even enable the "save" button?

Step 5: Creating customized reports

The ability to create customized lists and reports is one of the great strengths of database management systems like MySQL. In our fictional company a manager might want to get one of the following:

A list of everyone in the company with their phone numbers.
```
SELECT fname,lname,phone FROM people
```

A list of everyone in a certain department.

SELECT fname,lname,room FROM people WHERE dept = 2

An alphabetized list of everyone in the company.

SELECT fname,lname,phone,office FROM people ORDER BY lname

There are many other possibilities. Your challenge, now that you have seen how to merge your LiveCode stack with your database tables, is to come up with a way to create the controls and write the scripts to create your specialized report.

Assignment: Managing your Database in LiveCode