Reading from a Database
Retrieving data from a table within a database, commonly known as "selecting" or "fetching" data, is referred to as "reading" data when using databases with Eggplant Functional. This returned data can then be used to inform your testing, such as when doing Data Driven Testing with Eggplant Functional.
Reading Table Contents
Fetching the contents of a database table can be as simple as asking for all of its records.
Set the writable
database connection list property to No
if you do not need to modify the database, and instead just want to read from it.
Example:
The below code 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 key for each column in the table. For more on Records, see Records vs. Property Lists.
put the records of memberTable into Members
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 rather than a list of Records.
Reading all of the records of a large table into a variable as this example does is not recommended. Use a where
expression or iterate through the records as discussed in Iterating over Records of a Database.
Example:
This example uses the Get
command to 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.
get the records of memberTable where lastName is "Smith"
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"
Example:
The Get
command can also be used in conjunction with it (see All About "It"):
set myDB to {type:"odbc", DSN:"MyDB"} -- Define the database connection
put table "film" of myDB into myTable -- Stores referential data to a specific table in the variable myTable
get rental_rate of the record of myTable where title is "TERMINATOR" -- Accesses the rental rate of a specific film using the Get command
if it is "$9.99" then log "Rental rate is correct!" -- Uses the local variable It to verify that the rental rate is correct
Where Expressions
A database table may contain a large number of records (thousands, hundreds of thousands, or even more). Pulling an entire table of this size into an Eggplant Functional variable can affect local memory usage. 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.
Where
expressions are used to select the desired records from a table when using the record
or records
function, thenumber of records
function, or the delete record
command. If no where
clause is specified, all records of the table will be returned.
The where
clause discussed on this page is not to be confused with the where
clause used with Each Expressions. They do similar things, but are very different. The where
expression used with databases is more limited in its abilities but can be much more efficient when fetching data from a database.
A where expression consists of the where
clause followed by an expression. That expression includes one or more references to the table using the format columnName operator value. The name of the column must come first, followed by the operator and then the value. For example, in the expression "where size is at least 12", the column name is "size", the operator is "is at least", and the value is "12".
Example:
set mymovie to the record of films where title is "TERMINATOR"
If more than one record matches the criteria given in the where clause, when the term record
is used only the first such record is returned. Use records
to return all matching records.
Operators
When using a where
expression, the operator can be any of the following (or any of their synonyms or negative variants):
- is equal to
- is not equal to
- is less than
- is more than
- is less than or equal to
- is greater than or equal to
- begins with
- ends with
- contains substring
- is in list of values
- is between min and max
These operators can be used to test whether a column has a value assigned:
- columnName is null
- columnName is not null
And/Or
In addition, multiple conditions can be tested at once by combining them with AND
or OR
. In a complex where
expression it may be helpful to enclose subexpressions in parentheses in order to make the meaning clear.
Example:
delete records from billingTable where (dueDate is earlier than today) and (remainingAmountDue is 0)
Example:
//Displays the full name of the club member whose member number is 12345
put 12345 into desiredMemNum
set clubDBto (type:"odbc", DSN:"ClubData", user:"root", password:"")
put table "Members" of clubDB into memberTable
set member to the record of memberTable where memberNumber is desiredMemNum
put "Name: " & member's firstName && member's lastName