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
Iterating Over Records of a Database Table
Iterating over the records of a database table is a common way to update records and manipulate data. You can do this by either using a Repeat Loop or a Record Iterator.
Using a Repeat Loop
In order to use a SenseTalk repeat loop to iterate over data, you must pull that data into your script by reading it from the database. Then you can use a repeat loop as you would in any other SenseTalk script to iterate over the data retrieved.
In scenarios that require iterating over records and making modifications during the iteration process, use Repeat with Each... By Reference, discussed in References to Containers.
Example:
The example below uses a repeat loop to iterate over user records fetched from a database in order to fill out an online form for each user:
set myDB to {type:"odbc", DSN:"mySQLDB", user:"root", password:"password123"} -- Declares the database connection information
put the number of records in table "userInfo" in myDB into numRecords -- Reads the total number of records in the table and shores that information in the variable numRecords
Log "We will be entering" && numRecords && "records into the system." -- Logs a message to declare how many records are available
put table "userInfo" of myDB into users -- Stores information regarding how to access the database table "userInfo" in the variable "users"
put the records of users into credentials -- Retrieves the records themselves from the "userInfo" table and stores them in a variable called credentials
Log "Entering the following new users: " & credentials -- Logs a message with the records themselves
repeat with each item user of credentials -- Iterates based on each record in credentials
WaitFor 10, "Form_SubmitButton"
Click "Form_UsernameField"
typetext user.username -- References the value for the username property of the current record and types the value into the SUT
Click "Form_PasswordField"
typetext user.password
Click "Form_SubmitButton"
Click "Form_AddNewUser"
end repeat
Example:
A property list is a list of values identified using keys (see property lists for more information). This example shows how property keys can be used to identify and manipulate different pieces of data. It is a stand-alone example that does not actually need to connect to a database, so you can try running it on your own system.
// Use item keys in column names
set dbResults to { {name:"Roger", age:17}, {name:"Arabella", age:32}, {name:"Francis", age:29}, {name:"Balthazar", age:92},
}
put the keys of item 1 of dbResults into columnNames
put columnNames
put columnNames joined by comma into header
put header & return into csvContents
repeat with each record of dbResults
repeat with each key in columnNames
if the counter > 1 then put comma after csvContents
put record.(key) after csvContents
end repeat
put return after csvContents
end repeat
put csvContents
The below is a shortened version of the previous example, and is also written to be run without connection to a database for demonstration purposes, so you can try running it on your own machine.
// Use item keys in column names
set dbResults to{ {name:"Roger", age:17}, {name:"Arabella", age:32}, {name:"Francis", age:29}, {name:"Balthazar", age:92},
}
put the keys of item 1 of dbResults joined by comma & return into csvContents
repeat with each record of dbResults
put values(record) joined by comma & return after csvContents
end repeat
put csvContents
Using a Record Iterator
Instead of retrieving a set of records from a table as a list, it is possible to get a record iterator that will iterate through a set of records one at a time. Treating the iterator as a list (such as when using the as a list operator) causes it to fetch all of the remaining records. A record iterator might be convenient in some situations, however it only provides a very small performance advantage over repeat loops, as all of the data is fetched from the database at once but only converted to record objects as requested. For more information on iterators in SenseTalk, see Iterators.
There are two ways to access a record iterator in SenseTalk:
Setting the useRecordIterator Property
Set the useRecordIterator property to true in order to return a record iterator when possible instead of all records at once.
set the useRecordIterator of myTable to Yes
Request an Iterator Directly
In order to request an iterator directly, use "iterate over the records of" within your database request, like this:
set memberIterator to iterate over the records of memberTable
The next record can then be obtained using the nextValue property of the iterator:
put memberIterator's nextValue into currentMember
Counting the Records in a Database Table
Counting the records in a database table is done using the Number of Records function.
Number of Records Function
Behavior: Returns the number of records in the table specified. Either Records or Rows can be used interchangeably.
Parameters: A table. Must be paired with a table expression or variable referencing a table.
Syntax:
{the} number of [rows | records] [of | in] table {where whereExpr}
Table in the syntax above can be either a variable that is set to reference a table, or a table expression (table tablename of database).
Example:
Asking directly for the number of records in a table sends a count request to the database. The count request returns the number, rather than the records, making this approach much more efficient than fetching the records and asking for the number of items in the returned list. This is a safe approach when working with tables containing a large number of records. This example uses number of records to find out how many records there are in a database table.
put the number of records of table "colorpalette" of myDB
Example:
Another way to find out how many records match a particular condition in a database table is to store the table reference in a variable, then apply the number of records function to the table variable along with an optional where clause (see Where Expressions) as shown in the following example.
put table "colorpalette" of myDB into colors -- Stores a reference to the table in a variable.
get number of records of colors where color is "green" --
Log "There are " & it & " record(s) in the table."
Example:
Another method which is appropriate for working with smaller tables. This method is simpler, but must be used with caution, or when you know you want to bring all of the records into SenseTalk.
put the records of table "memberinfo" of myDB into members
put the number of items in members
Example: Number of Rows variation
Using the number of rows variation of the Number of Records (Rows) function to find out how many records match a particular condition in a database table:
put the number of rows of table "colorpalette" of myDB where "color" is "black"
Example:
Using the number of records function to declare the number of records that will be added at the beginning of a test run:
set myDB to (type:"odbc", DSN:"mySQLDB", user:"root", password:"password123")
put the number of records in table "users" in myDB into numRecords -- Stores the total number of records in the table into variable numRecords
Log "We will be entering" && numRecords && "records into the system." -- Logs a message to declare how many records are available