Database Tables
Working with database tables is an integral part of using databases with your testing. Use table expressions and database table properties to access database table contents and manipulate them.
SenseTalk can interact directly with databases and excel files as documented in the following sections. However, if you prefer to manipulate your data using SQL, see Executing SQL Directly.
Table Expressions
In order to manipulate data in your database, you must specify a table to work with by its name. This is done using a Table expression. A database connection must also be defined. For information on how to define a connection to a database, see Database Connections.
Using a table expression establishes the connection to the database if necessary, and collects information about the table, but doesn't fetch any data from the table. Data is fetched when records of the table are requested.
The is a table operator can be used at any time to verify that what you are working with is in fact a table.
Table Expression
Behavior: A Table expression allows you to reference a table in a database using SenseTalk. There are two different ways to use a table expression: Directly and by storing it in a variable.
Syntax:
table tableName [of | in | from] {database} dbIdentifier {{with} properties}
Example: Calling a Table Directly Using a Table Expression
This example uses a table expression (table "Members" of myDB) to reference a database table, and a records expression (the records of) to fetch all of the records from the table and store them in the variable CurrentMembers:
put the records of table "Members" of myDB into CurrentMembers -- Retrieves all records of the table "Members" and stores them in the variable CurrentMembers.
Example: Storing the Table Object in a Variable
This example does the same thing as the above example, but starts off by storing the table object in a variable called memberTable. Note that memberTable is a Table object that can be used to access the information contained in the Members table of the database. In the first line of this example, none of the table contents have been fetched from the database yet; memberTable merely holds information about the table and its columns.
set memberTable to table "Members" of myDB -- Sets the MemberTable variable to be a table object which refers to the table "Members". Has not yet retrieved the records.
put the records of memberTable into CurrentMembers -- Retrieves all records of the table "Members" (referred to by the table object memberTable) and stores them in the variable CurrentMembers.
The second line of code here is actually retrieving records, while the first is storing a reference to the table object itself, and has not pulled any records yet.
Example: Adding in a Table Property
This example stores the table object in the memberTable variable, specifies using columns of interest using the columnsToFetch table property, and then retrieves all of the records and stores them in the CurrentMembers variable.
set memberTable to table "Members" of myDB -- Sets MemberTable to be a table object which refers to the table "Members".
set the columnsToFetch of memberTable to ["Name","ID Number","Expiration"] -- Uses the columnsToFetch table property to specify columns of interest.
put the records of memberTable into CurrentMembers -- Retrieves the specified columns (Name, ID Number, and Expiration) of all of the records in the "Members" table (referred to by the table object memberTable) and stores them in the variable CurrentMembers.
Database Table Properties
These table object properties can be set to control a variety of table behaviors. They can be set in the same way as any other property (see Properties), but specifically apply to database table objects.
The columnsToFetch Property
Value: Column name or list of names.
Default: Empty.
Behavior: Set columnsToFetch to a list of column names to only fetch those columns. Subsequent record retrieval will only retrieve the contents of the specified columns and will not return other columns. When columnsToFetch is empty (the default), all of the columns defined for the table by the database will be fetched.
Example:
set the columnsToFetch of myTable to ["name","memberNum"]
The recordPrototype Property
Value: Object.
Default: Empty. (No default prototype.)
Behavior: Set to an object to be the prototype for all fetched records. Each record will be created as an instance of the prototype object, the same way it would be created if you used a new object expression. This can be used to provide additional behavior for all of the fetched records. For more information on records, see Records vs. Property Lists.
Example:
set myTable's recordPrototype to AccountRecord
The autoSaveUpdates Property
Value: Boolean.
Default: On.
Behavior: Set to yes or no to override the autoSaveDatabaseUpdates global property.
Example:
set myTable.autoSaveUpdates to false
The useRecordIterator Property
Value: Boolean.
Default: Off.
Behavior: Set to true to return a record iterator when possible instead of all records at once. For more information, see Iterating Over the Records of a Database Table.
Example:
set the useRecordIterator of myTable to Yes
The Is a Table Operator
The is a table operator can be used to test whether a value is a database table. This can be useful when troubleshooting SenseTalk scripts that connect to a database.
Example:
set myExcelDB to {type:"excel",file:"~/Desktop/HotelData.xlsx"} -- Define the database connection by providing the Excel file path and database type in a property list stored in the myExcelDB variable
put table "Customers.2019.01" of myExcelDB into Customers -- Store the table's referential information in a variable called Customers
If Customers is a table then log "Correctly identified the Customers table!" -- Verify that the table is in fact an existing table in the specified database