How to Add Column Data to Existing Database With TestStand

Updated Jun 1, 2021

Environment

Software

  • TestStand

This article shows how to add/edit new schema to an existing database schema in the TestStand Database options. Specifically, this article:
  1. Differentiates how to copy existing schema sets and add additional columns or parameters
  2. Notes how adding new schema parameters to SQL stored proc requires a sever side change to correctly execute
  3. Gives context on the Validate… button to confirm that the database table has no errors
 
A more detailed article about creating Database schema from scratch can be found in the related links.

  1. Open TestStand
  2. Open the Database Options Dialog Box by going to Configure>>Result Processing>> Database>> Options from the TestStand toolbar.Database options screenshot.png
  3. Ensure that the options in the Logging/Database Options are correct including the database open connection string for the specific database and the type of database management system. We can also use the settings from a .udl file similar to the process for LabVIEWConnection string screenshot.png
  4. Select the Schemas tab at the top. It is recommended to simply copy the base schema that is compatible with the database plugin being used. If it is not copied, TestStand will overwrite these settings when TestStand is upgraded to futures. For example, copy and paste one of the Generic ... options for best compatibility with SQL or Microsoft Access databases and then give it a descriptive name. Copy schema set screenshot.pngPaste copied schema screenshot.png
    1. If using the SQL Server Stored Proc (NI), note that this option uses SQL procedures that are stored on the SQL server. They will need to be modified with new parameters to correctly add data to columns.
  5. To create a new column heading in the desired tables, right click and select New. For example, add additional data in the UUT_RESULT table.image.png
  6. Ensure that the Name, Type, Direction and Value to Log are set. Here are some additional notes on this:
    1.  The Value to Log can be set by using a hard coaded value, Station Global variable, or by taking advantage of callbacks such as the PostSequence callback and creating an AdditionalData container in the UUT variable. See additional article called Using Callbacks in NI TestStand.
    2. If using the SQL Server Stored Proc, this technically adds a new parameter and an update will need to be made to the stored procedure on the SQL server side.SQL parameter screenshot.png
  7. Select the newly copied Schema heading under the Schemas window, then select Validate... This will show any errors in the currently loaded table(based on UDL file or connection string).Validate screenshot.pngSchema Validation Results screenshot.png
  8. The Generate SQL button can be clicked to conveniently open the loaded database and run a query to add the missing column items to the correct tables. See Database Viewer screenshot below. Click the green run arrow to run the supplied query and add the new column to the desired table, else modify the query as needed. database viewer.png
  9. Exit the Database Viewer (The changes to the tables will automatically have saved)
  10. Select Revalidate on the Validation popup window to check again that there are no errors are warnings. Then select Done
  11. Select Apply to save all changes or OK to save changes and exit.
  12. Test the new database options with a simple test sequence such as with a single Message Popup step and a hard coded Value to Log. Then add the functionality of a station global or callback override to get correct values for logging to the database.

Common Errors
  1. If receiving an error about "unknown variable or property name", consider again that only Station Globals or data passed from overriding callbacks will be recognized by the LogToDatabase callback in the process model from the Database Options.
  2. Another common issue is to get "Datatype mismatch in criteria expression". Check that the datatype in TestStand correlates to an expected type in the database being used. For example, a BSTR(byte string) would work with a VARCHAR datatype in SQL.
  3. "The expression cannot be empty" comes from assuming that empty will be the same as a null value. Consider using the empty string instead for a nullable value.
 

Related Links

Other Support Options

Ask the NI Community

Collaborate with other users in our discussion forums



Request Support from an Engineer

A valid service agreement may be required, and support options vary by country.