Using Director with Databases: Part 2

February 14, 2001
by Gary Rosenzweig

Last week I showed you how to make a simple database. This week, let's expand that project to include a way to save and load the database so that you don't need to start from scratch each time you run the program. I'll also show you how to export the information in the database in a format that can be read by other programs, like Microsoft Excel. While the program is running, the data is stored in a global called gDatabase. This is a list. It is actually a list of lists, with each sublist being a single record in the database. Here is how it looks in the Message window:

put gDatabase
-- [["Name": "Gary", "Street": "123 Clever Rd", "City": "Denver", "State": "CO", "Zip": "80210", "Phone": "720-555-1212", "Birthday": "2/29/69"], ["Name": "Natasha", "Street": "123 Clever Rd", "City": "Denver", "State": "CO", "Zip": "80210", "Phone": "303-555-5555", "Birthday": "5/1/99"]]

Director can't save lists directly into files, but it can convert the list to a string, and then save that. Converting to a string is as simple as using the string function. Here is the saveData function.

on saveData

-- convert list to a string
text = string(gDatabase)

-- send string to text file
saveText(text)

end

The saveText function is a standard one that I use for saving text files with the FileIO Xtra. It is from my Using Director 8 book (page 369). I also wrote about it in a previous article. I won't go into that code here, but you can find it in those two places, as well as the source file for this article.

Once you save the data, you should be able to look at it in SimpleText on the Mac and Notepad or Wordpad in Windows. It is just a straight representation of the list as a string.

Getting the data back into the program is only slightly more complex. For one thing, you want to take into account if the user presses the "Cancel" button when being asked for a file name. Also, if there is a problem with the file they select, you will want to tell them.

This is what the loadData function looks like. It uses the value function to attempt to convert the string to a list. It uses the listP function to determine if a list could be made.

on loadData

-- get contents of text file
text = openAndReadText ()

-- if text file is empty, could mean the dialog was cancelled
if text = "" then exit

-- convert string to list
list = value (text)

  -- did the conversion work?
  if listP (list) then
    -- yes, so set the database to this list
    gDatabase = list
  else
    -- no, must be a bas file
    alert "Error reading database."
  end if

end

There is a chance that the value function could fail, even though the user selected a valid file. This is because strings in Director can contain quote characters. So, if the user types in their name as "John "JS" Smith" then our code will save that out with all of the quotes involved: both the ones surrounding the name and the ones that are a part of the name. However, when the value function tries to interpret this, it will read all of the quotes as ones to interpret when rebuilding the list. This will create an error and the value function will give up, returning VOID as the value for the list.

There are several ways to avoid this. The one I like the most is to make sure that the input text members don't accept quotes at all. Or, they could accept quotes, but convert them to harmless single quotes. Another option is to convert the quotes in each item of the record to single quotes when it is first stored. You can find out more about limiting text input in a previous article.

The saveData and loadData functions provide a way for you to save and load the database. You can even save and load several different databases for whatever reason. However, these files cannot be read by another program. Lets create an export function that will save the data in a tab-delimiter format that can then be read by programs like Microsoft Excel.

The only trick here is to save each item in each record with a TAB character after it. You should also put a RETURN between records. The export function will even use the list properties to create a line of column titles. All of this is put into a text string. When the string is complete, it is saved with the same saveText function.

on export

  if gDatabase.count < 1 then
    -- if no data yet, then show message instead
    alert "No data."

  else
    -- create blank string
    text = ""

    -- add each of the column headings
    repeat with i = 1 to gDatabase[1].count
      put getPropAt (gDatabase[1],i) & TAB after text
    end repeat

    -- add each record
    repeat with i = 1 to gDatabase.count
      -- new line
      put RETURN after text

      -- add each field of each record
      repeat with j = 1 to gDatabase[i].count
        put gDatabase[i][j]&TAB after text
      end repeat
    end repeat

    -- send string to text file
    saveText (text)
  end if
end

The resulting text file can be looked at with SimpleText or Notepad. It is a little easier to read than the list-formatted text file.

Name Street City State Zip Phone Birthday Gary 123 Clever Rd Denver CO 80210 720-555-1212 2/29/69
Natasha 123 Clever Rd Denver CO 80210 303-555-5555 5/1/99

Programs like Excel allow you to open or import files and choose how the data should be read from those files. Usually this type of file will be called tab-delimited or something similar.

Note that there is no Shockwave version of this example movie. That is because it uses the FileIO Xtra, which does not come with Shockwave. Products like Shockwave need to be careful about what sort of things that the content can do. Imagine if Shockwave had the ability to create, modify, or even delete files off of the user's hard drive. Imagine the damage that a malicious Shockwave developer could do with that power. This is why Projectors have functions like FileIO that Shockwave does not. So download the example movie to try this one out.

Next week, we'll look at more ways to improve the database program.

A sample Director 8 movie is available for download in Mac or Windows format.

Gary Rosenzweig's two most recent books are:

Special Edition Using Director 8 -- The most comprehensive guide to Director ever, including tons of examples and demo movies. It's suitable for novices and experts alike.
Advanced Lingo for Games -- How to make games with Director 7 and 8. This book comes complete with full source code for more than 20 complete games.

More information about these books can be found at http://clevermedia.com/resources/bookstore/. They can be purchased there, or in your local bookstore.

Gary Rosenzweig is the Chief Engineer, founder, and owner of CleverMedia, a game and multimedia development company in Denver, Colorado. He is the author of ten books on Macromedia Director and Flash, including his latest, Special Edition Using Director MX.