Writing to a Database

Writing to a database equates to manipulating data within a database. Writing allows you to modify the contents of your database. This can mean gathering and storing new data, updating existing information, or deleting, among other things. This can be useful if through your testing you are gathering data from your application under test for further analysis.

One scenario where this could be useful is in the case of running a hotel, where you have a database containing information about guests, rooms, and beds that you want to manipulate by associating guests with appropriately-sized rooms. It can also be useful if you are using a database to gather data from your application under test, including the Eggplant Functional test results themselves, to store for future analysis (possibly via further automated testing).

Records vs. Property Lists

A record is different from an ordinary property list. It is a type of property list (see Property Lists) that has an active connection to a database. When a property list that corresponds to a row in a database is retrieved, it is known as a record. In addition to its visible properties, a record also contains hidden information about the database table from which it was fetched.

Simply put, a record is a property list that has a connection to a row in a database table. Changing the value of a property in a record directly updates the corresponding value in the database. A record may be assigned properties that do not correspond to columns in the database table, but if a property is assigned or changed that is not a column in the database, that value is only changed in the record object and the database is not affected.

Example:

This example accesses a record in a database table and then logs it to demonstrate what a record may look like in practice:

set myDB to {type:"odbc", DSN:"qaDB", user:"Ellen", password:"securePassword"} -- ODBC database connection definition

put table "film" of myDB into myTable -- Stores a reference to the "film" database table in the variable myTable

set Member to the record of myTable where title is "ACADEMY DINOSAUR" -- Accesses a specific record of the "film" table and stores it in the variable Member

Log Member -- Logs: {description:"A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies", film_id:"1", language_id:"1", last_update:"2017-06-28 15:47:10 -0700", length:"86", original_language_id:<missing value>, rating:"PG", release_year:"2006", rental_duration:"6", rental_rate:"3.99", replacement_cost:"20.99", special_features:"Deleted Scenes,Behind the Scenes", title:"ACADEMY DINOSAUR"}

Copying Records Between Tables

When a record is assigned to another variable, all of its properties are copied, but the copy does not keep a connection to the database. This is by design, to avoid unexpected consequences. The database connection can be retained in the copy by using the as record operator to indicate that the new copy should also be a record.

Note: The same effect could also be achieved for a single record by making a reference to the original record, but this technique will not work for a list of records. Using the as record operator is the preferred approach.

Example:

set member to the record of memberTable where memberNumber is desiredMemNum -- The member variable HAS a connection to the database

put member into newMember -- newMember has NO connection to the database

put member as record into member2 -- member2 HAS a connection to the database

The Is a Record Operator

The is a record, operator can be used to test whether a value is an actual record with a connection to a database, or simply a property list. This can be useful when troubleshooting SenseTalk scripts that work with a database connection.

Example:

Set myDB to {type:"Excel", file:"~/Desktop/ClubData.xlsx", Writable:yes} -- Define the database connection, whether Excel or ODBC.

put table "Members" of myDB into MemberTable-- Pull the reference information for the table you need to modify

put {NameFirst:"Heidi", NameLast:"Mullers", StartDate:"2019-01-02"} into MemberToAdd -- This could also be done recursively using Iterators, if adding numerous members to the database table

add record MemberToAdd to table "Members" of myDB -- Add the record to the table in the database

Put the Record of MemberTable where "NameFirst" is "Heidi" into member3-- Retrieve the record you just added and store it in a variable

if member3 is a record then log "Success!" -- Verify that the record is an actual record with an active database connection

Updating Values in a Database Record

To update any value within a database table, simply change the value in the record corresponding to that row in the table, and that value in the database gets updated automatically.

Note: This feature of automatically updating the database when values in a record are changed is turned on by default, and can be disabled if manual updates are preferred. See Manual Database Updates for more information. This feature may not work with some databases (such as MSSQL). In that case, disable manual updates.

Example:

Using a Where Expression to select a record, and then modifying one of the values (expirationDate) in that record:

set memberTable to table "member" of myDB -- Access the database and pull a table of data into a variable called memberTable

set member to the record of memberTable where memberNumber is 3152134 -- Using the where clause to select a record based on specific values, in this case the memberNumber

add 30 days to member's expirationDate -- This will update the value in the "expirationDate" column of that specific member record in the database

Example:

This example shows how you might make database table changes for member expiration dates using a repeat loop to iterate over multiple records:

set memberExpirationDates to table "memberInfo" of myDB -- Accesses a database and pulls the data from table "memberInfo" into a variable called memberExpirationDates

put [4260,1059,1685] into expirationstoUpdate -- Stores a list of member numbers in a variable to be iterated over

repeat with each Num in expirationstoUpdate -- Iterates over the list of member numbers using a repeat loop

set member to the record of memberExpirationDates where memberNumber is Num -- Retrieve the record for the particular member number being worked with each iteration

add 1 year to member's expirationDate -- Extend the expiration date for that member by one year

end repeat

Example:

This more detailed example shows how to update a database table with changes to colors and brightness:

set palette to table "colorpalette" of myDB -- Sets the variable 'palette' to a table object representing database table 'colorpalette'

set myColor to the record of palette where ID is 3 -- Sets the variable 'myColor' a record object for the corresponding record from the database

put "black" into myColor's color -- Updates the database record, changing the color value to "black"

log MyColor -- Logs {brightness:"9", color:"black", ID:"3"}

set myColor to the record of palette where Color is "green" and brightness is "14"

add 10 to myColor's brightness -- Updates the database record, adding 10 to the current brightness value

log myColor -- Logs {brightness:"24", color:"green", ID:"4"}

Adding New Records to a Database Table

To add records to a database table, first construct one or more property lists containing properties that match the columns in the table. Then use the add record or add records command to add those records to the table.

Add Record/ Add Records Command

Behavior: Adds a specified record to a specified table in the database. For adding multiple records, use the Add Records variation.

Parameters: A property list to be added to the database as a record, and the name of a table in the database where that record should be added.

Example:

set myTable to table "Members" of myDB -- Reads the table Members into a variable in the script, called myTable

set newMember to {firstName:"Fritz", lastName:"Geisler", memberNumber:4307}

add record newMember to myTable -- newMember must have all required values. myTable refers to the table Members of the myDB database.

Example:

The below example uses the result to obtain Eggplant Functional test result details and store them in a database using the Add Record command. For more on the commands and functions used below, see RunWithNewResults and the result.

//The below code writes the result of RunWithNewResults to a database table

set myDB to {type:"odbc", DSN:"mySQLDB", user:"carrie", password:"password123"}

RunWithNewResults "CreateWorkspaceTestCase"

put the result into myResult -- Stores the result property list in a variable to make referencing its different properties possible later

set newResult to {ExecutionTime:myResult.RunDate, TestName:"CreateWorkspaceTestCase", Result:myResult.status} -- Constructs the new record based on "the result." ExecutionTime, TestName, and Result are column names in the database table.

Add record newResult to table "results" of myDB -- Adds the new record to the table

Add Records Variation

Using the add records command, a long list of records may be added to the table with a single command.

Note: A database table may have various rules associated with it. For example, some columns may be optional and others be required to have a value assigned in every record. Or a column such as memberNumber might be required to be unique meaning that each record in the table must have a different memberNumber value. If you attempt to add a record to the database that violates one of these rules, it will be rejected, and an exception will be thrown in the script.

Deleting Records from a Database Table

Records can be deleted using either the Delete Record command (can also be plural , i.e. Delete Records). This command can be modified using a Where expression to specify qualities of the records to be deleted.

Delete Record/ Delete Records Command

Behavior: This command deletes a previously-fetched record from a database table.

Parameters: The name of the variable containing the previously fetched record or records to be deleted.

Example:

The delete record command is used to delete a previously-fetched record from a database table.

put the records of table "colorpalette" of myDB where ID is 2 into colortoDelete

delete record colortoDelete

Specifying Records Using a Where Expression

Where Expressions allow you to select desired records from a table when using the record or records function, the number of records function, or the delete recordscommand.

Example:

This example uses a where clause to specify which records need to be deleted , and then uses the delete records command to delete those records..

put table "colorpalette" of myDB into myColors

delete records from myColors where brightness is greater than 10

Example:

This example reads multiple records from the database using Where, and then uses the delete records command to delete those records.

put the records of myTable where state is "Colorado" into doomedRecords

delete records doomedRecords

Automatic vs. Manual Updates

Database updates are saved automatically by default. However, using the autoSaveDatabaseUpdates global property, you have the ability to change this setting so that database updates are manual. See Automatic vs Manual Updates for more information.

 

This topic was last updated on June 10, 2020, at 03:02:15 PM.

Eggplant icon Eggplantsoftware.com | Documentation Home | User Forums | Support | Copyright © 2020 Eggplant