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.
On this Page:
Reading Table Contents
Fetching the contents of a database table can be as simple as asking for all of its records.
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
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"
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.
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"
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.
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 / Number of Rows function.
Number of Records / Number of Rows 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:
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:
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 add the database records to a variable, then use the number of records of variable function 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
Eggplantsoftware.com | Documentation Home | User Forums | Support | Copyright © 2022 Eggplant