BYU Home pageBRIGHAM YOUNG UNIVERSITY
  Office of Digital Humanities
Back     BYU LiveCode Lessons Gateway

DigHT 310
Managing and Integrating
Your Database in LiveCode

So far we've seen how to do basic database access from a LiveCode stack. Now it's time to build a useful application that integrates with your online database. We are going to build what's known as a "front end" to your database—a graphical user interface that makes it easy for an end user to do useful things with the data stored in your database.

What sort of things might we want our database management stack to do? Here is a basic list; you can probably think of lots of others.

With a few basic features defined, let's get started.

Step 1: Building the interface

This simple interface should be enough to allow us to build the display and editing tools mentioned in our list above.

LiveCode stack with controls for database management interface

The LiveCode datagrid object on the tool palette

Notice that we are using a type of control that you may be unfamiliar with; namely, a "data grid". The data grid is a relatively new addition to the LiveCode tool set. It is fundamentally different from other LiveCode controls in that it is a hybrid built of several other types of controls that are grouped into a non-editable group. Data grids are highly customizable and potentially quite complex, but using them to display tabular data, such as our database records, is very straightforward.

To create a data grid, just drag from the data grid icon on the tools palette onto your card, and resize to the desired size.

Data grid basic properties
Data Grid Basic Properties

Open its property inspector and name it just as you would any object. The basic properties for data grid in the example layout above are set as shown here.

Data grid column properties panel
Data Grid Column Properties

Data grids have another set of properties for setting up columns and column properties. Note that each column you create can have both a column name and a column label. In addition, each column width can be set to the precise width you want it to be. Columns may also be visible or hidden, a feature we'll use later on.

As LiveCode objects, data grids have properties just like fields and buttons do. Due to the hybrid nature of data grids, however, you have to refer to certain properties using the prefix "dg". The two properties we'll be using are the dgText and the dgHilitedLine. They are analogous to the text and the hilitedLine properties of fields.

Much more could be said about data grids, but this is enough for our needs. If you want to explore data grids more in depth, see the excellent on-line documentation for data grids.


Step 2: Scripting the interface

With the basic interface built, we are ready to start scripting. We'll start by scripting button "showAll" to display all of the records in the database table that we want to manage in our stack.

Using stack custom properties to store database connection informationThe first step is to script creating the connection to the database. And you may recall, the revOpenDatabase() function requires several arguments to connect successfully to the database server. Rather than typing them directly into the script, it's convenient to have them stored somewhere in the stack where they are easily accessed. Custom properties of the stack are well suited for things like this.

Make your job easier with "wrapper" commands. Even with the database connection information stored neatly in custom properties, it's tedious and repetitious to type out the revOpenDatabase() function in full every time we need to make a connection. A good practice is to create a "wrapper" handler that does the work for you in the stack script (or even in a library stack.) The wrapper handler might look something like this:

# in the stack script
global gConnID

on connectToDB
    put the dbhost of me & ":" & the dbport of me into tHost
    put revOpenDatabase(the dbtype of me,tHost,the dbname of me,the dbUser of me, the dbPasswd of me) into tConxn
    
    # it's a good idea, but not required, to check for a successful connection here
    if tConxn is a number then
        put tConxn into gConnID
        return empty
    else
        put "Unable to connect to database '" & the dbName of me & "': " & tConxn into tErrMsg
        answer error tErrMsg as sheet
        put empty into gConnID
        return tErrMsg
        exit to top
    end if
end connectToDB

Notice that we're saving the successful connection ID in a global variable, gConnID, so that it can also be accessed anywhere in the stack. Now, every time we need to make a connection to our database, we can just enter the command connectToDB in our handler. While we're at it, we can also simplify the process of disconnecting from the database with a wrapper handler in the stack script:

on closeDB pConnID
    revCloseDatabase pConnID
    
    # this ensures that you'll never have a left-over connection number in the global var
    if pConnID = gConnID then
        put empty into gConnID
    end if
end closeDB

Terminating a database connection can now be done with a simple call in our handler, closeDB gConnID. Remember to declare the global variable gConnID. Our open-close database framework in any handler is pretty simple now:

global gConnID

on mouseUp
    connectToDB
    
    # Do DB access stuff here
    
    closeDB gConnID    
end mouseUp

Step 3: Displaying the data

With our wrapper commands in place for easy opening and closing of database connections, we're ready to get to work on handlers for displaying and editing records in our tables. The first thing we may want to do is simply show all the records in a table. Let's say we're working with a table called 'people'. As mentioned before, the revDataFromQuery() function is an easy way to grab data from a database. Remember the SQL query to display all fields for all records?

SELECT * FROM people

Just send this query to the database like this:

  put "SELECT * FROM people" into tQuery
  put revDataFromQuery(,,gConnID,tQuery) into tRecords

It's always a good idea when working with database queries to make sure our query succeeded before proceeding. Many of the LiveCode database commands and functions, when they fail, return an error message that begins with the string revdberr. We can use that to do a quick error check before displaying the results of the query in the datagrid table:

  if tRecords begins with "revdberr" then 
      answer error "There was a problem accessing the people database: " & tRecords
      closeDB gConnID
      exit to top
  end if

Now all that's left is to display the records in the data grid, which we can do by setting the dgText property of the data grid. The mouseUp handler in button "showAll" should now look something like this:

global gConnID

on mouseUp
    connectToDB
    
    # get all of the records in your database
    put "SELECT * FROM people" into tQuery
    put revDataFromQuery(,,gConnID,tQuery) into tRecords
    
    # this part's not required, but a good idea to check for errors
    if tRecords begins with "revdberr" then 
        answer error "There was a problem accessing the people database: " & tRecords
        closeDB gConnID
        exit to top
    end if
    # end error check
    closeDB gConnID    
    
    set the dgText of group "employeeList" to tRecords
end mouseUp

The first piece of our database editing stack should be working now. A click on the Show All Records button should give this result:

A list of all records in the people table

This is almost perfect, but we need to fix one thing. One of the requirements was to make records easier to read, and we still only have id numbers listed for the departments. Instead, we want to display the name of the department. There are two ways to solve this—the LiveCode approach and the SQL approach. We'll look at both of them. First, however, let's modify the data grid and add a column, so there will be a column for the department ID number and a column for the department name:

Displaying department names: Method 1—The LiveCode approach

The LiveCode approach requires more LiveCode scripting but simpler SQL statements. Essentially, it involves:

We already have all the records for the people table, displayed in the data grid. Let's write a handler that will get all the records in the department table. We'll put the handler in the card script (since we might want to access it from more than just the "showAll" button.) You'll notice that it is almost identical to the handler that gets the people records:

# in the card script
global gConnID

on getDeptList
    connectToDB
    
    put "SELECT * FROM department" into tQuery
    put revDataFromQuery(,,gConnID,tQuery) into tDeptList
    # check for errors
    if tDeptList begins with "revdberr" then 
        answer error "There was a problem accessing the people database: " & tDeptList
        closeDB gConnID
        exit to top
    end if
    # end error check
    
    closeDB gConnID
    # store dept records in a custom property:
    set the deptData of me to tDeptList 
end getDeptList

We will also need a function that looks up the department numbers in this list so they can be added to the display in the data grid. We'll add that function to the card script

# added to card script

function deptName pDeptId
    set the itemDelimiter to tab
    put the deptData of me into tDeptData
    put lineOffset(pDeptId,tDeptData) into tLine
    if tLine<> 0 then
        return item 2 of line tLine of tDeptData
    else
        return empty
    end if
end deptName

Finally, let's modify the script for button "showAll" to use these new handlers to add the department name to the people records. Notice that we're just calling the new handlers we added to the card script:

global gConnID

on mouseUp
    connectToDB
    
    # get all of the records in your database
    put "SELECT * FROM people" into tQuery
    put revDataFromQuery(,,gConnID,tQuery) into tRecords
    
    # this part's not required, but a good idea to check for errors
    if tRecords begins with "revdberr" then 
        answer error "There was a problem accessing the people database: " & tRecords
        closeDB gConnID
        exit to top
    end if
    # end error check
    closeDB gConnID    
    
    # add department names
    getDeptList
    set the itemDelimiter to tab
    repeat with i = 1 to the number of lines in tRecords
        put tab & deptName(item -1 of line i of tRecords) after line i of tRecords
    end repeat
    set the dgText of group "employeeList" to tRecords
end mouseUp

Now the datagrid shows all of the people records, but with the proper department names added:

Displaying department names: Method 2—The SQL approach

You might think that the LiveCode approach is a lot of trouble for a simple thing. You would be right, but it is a viable option for someone comfortable with LiveCode scripting but a novice at SQL.

The SQL approach requires simpler LiveCode scripting, but more complicated SQL statements. In a nutshell, we use a SQL technique called a join, which allows us to join data from separate tables in a single query. SQL joins are powerful and potentially complex, so what we use here only scratches the surface. Here is the SQL statement that will get us the joined data we want in one query:

   SELECT people.*,department.name FROM people,department 
   WHERE people.dept = department.dept_id

In this kind of SQL join, we list two tables in the FROM clause, instead of just one. Because we're referring to more than one table, we need to qualify each column name by preceeding it with the table name followed by a dot, in this format: tablename.columnname. So people.* means "all the columns in the people table", and department.name means "the name column in the department table". The WHERE clause is basically saying "only include the records where the 'dept' column in table 'people' corresponds to a record in table 'department' with an equivalent value in the 'dept_id' column."

To use this new join query, just use it in place of the simple query we used earlier in the "showAll" button:

global gConnID

on mouseUp
    connectToDB
    
    # get all of the records in your database
    put "SELECT people.*,department.name FROM people,department " & \
        "WHERE people.dept = department.dept_id" into tQuery
    put revDataFromQuery(,,gConnID,tQuery) into tRecords
    
    # this part's not required, but a good idea to check for errors
    if tRecords begins with "revdberr" then 
        answer error "There was a problem accessing the people database: " & tRecords
        closeDB gConnID
        exit to top
    end if
    # end error check
    closeDB gConnID    
    
    set the dgText of group "employeeList" to tRecords
end mouseUp

By using this new SQL statement we get the people record list shown above, including the department names, without the need for extra LiveCode scripting!

Finishing touches

There is a minor cosmetic issue to fix. Even though the id numbers for the people records and the department records are important, they are meaningless to most users. Fortunately, data grid objects allow us to hide columns, while still keeping the underlying data intact. To do so, simply open the property inspector for the datagrid, choose the Columns panel, and uncheck the Visible checkbox for the columns you want to hide. Then adjust the width of the data grid and we end up with a much cleaner display:

data grid with 2 columns hidden

Remember, the data in the hidden columns is still there; it is just not showing. If we look at the dgText of group "employeeList", we can see it is still there, with tab-separated items on each line:

1	Milton	Chilton	801-123-2345	Room 101	1	Accounting
5	Mike	Nelson	801-345-6780	Room 40		1	Accounting
8	Jerry	Ball	322-5684	Room 220	1	Accounting
11	Fred	Farkel	801-345-5678	Room 111	1	Accounting
4	Judy	Johnson	801-656-8765	Room 130	2	Sales
3	Jane	Doe	801-567-7890	Room 160	2	Sales
6	Bob	Bower	345-0908	Room 203	3	Marketing
(etc.)

Knowing that the id numbers are still available to us as part of the data will become very important to us as we build tools to edit the database.

Continue to Part 2: Editing your Database Records


Back     BYU LiveCode Lessons Gateway
Maintained by Devin Asay.
Copyright © 2005 Brigham Young University