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.
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:
Reading Syntax Definitions
Syntax definitions for language elements use boldface to indicate words that must be typed exactly, and italics to represent expressions or other variable elements. Curly braces { } indicate fully optional elements, while elements enclosed in square brackets [ ] indicate required items with multiple options (where one or the other may be used, but not both). Vertical bars | are used to indicate alternative options.
So, for example, the following partial syntax definition:
{in} [ascending | descending] {order}
indicates that you may optionally use the word “in” followed by either the word “ascending” or the word “descending”, followed optionally by the word “order”. In other words, all of the following would be valid (as well as several other variations):
ascending
descending
ascending order
in descending
in ascending order
Example: Calling a Table Directly Using a Table Expression
This example uses a table expression to store the contents of a specific table in the variable memberTable:
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.
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.
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
Eggplantsoftware.com | Documentation Home | User Forums | Support | Copyright © 2022 Eggplant