To identify a particular table all that is needed is its name and the database connection info:
set memberTable to table "Members" of myDB
This sets the variable memberTable to be a Table object that can be used to access the information contained in the Members table of the database. None of the table contents have been fetched from the database yet; memberTable merely holds information about the table and its columns.
Reading Table Contents
Fetching the contents of a database table can be as simple as asking for all of its records:
put the records of memberTable into members
This will set the members variable to be a list of records, one for each row in the table. Each item in the list will be a Record object: a property list containing the values of that row in the table, with a property for each column in the table.
A database table may contain a large number of records (thousands, hundreds of thousands, or even more). For this reason it is often helpful to be able to ask for just the records that are of interest. A where clause achieves this, sending a request to the database for just the needed records, thus minimizing both the network traffic and the local memory usage.
get the records of memberTable where lastName is "Smith"
This will fetch only the data for the records where the lastName column is equal to "Smith". A where clause can include a variety of criteria for choosing records. To fetch all records for member names that begin with "S" but excluding the name "Smith" the request would look like this:
get records of memberTable where lastName begins with "S" and lastName isn't "Smith"
When a single record is all that is expected or needed, you can use record instead of records. This will return a single Record object for that record rather than a list of Records.
Here are some additional tools for reading and managing database tables:
- Iterating Over the Records of a Database Table: Read this information to understand how to fetch a set of records from a table as a list by getting a record iterator that iterates through a set of records one at a time
- Additional Database Table Properties: Read this information to understand how to set additional tool properties to assist you in managing database tables and records.