Overwriting Test Results Entries in Database

[DGM]

Hi.

My customer has a customized TestStand 2.0 installation. Test results are logged to an Access 2000 database. We have previously implemented a new UUT_DATA table which stores additional information about the unit under test (linked via a relationship to UUT_RESULT). The customer wants this table to be primary keyed by the UUT_NAME, rather than, at present, a GUID primary key.

My SQL is somewhat basic, and I can't figure out how to achieve this, despite many hours tinkering with the Database Options dialogues. In the first instance, how do I assign the value of the UUT_NAME (from Logging.UUT.SysInfo.UUT_Title) to the primary key? And, secondly, how do I specify that I either want to create a UUT_DATA instance with the UUT_NAME, or overwrite an existing one? INSERT INTO always seems to want to create a new instance (and objects when a record with the same unique key already exists).

Thanks.

[hannahW]

Hi there

Is the method you're using through the process model, or are you doing the logging some other way?
I'm assuming you're using the default process model (sequential) with the default database logging steps, since you've specified Logging.UUT.SysInfo.UUT_Title as one of the properties.
Can you please post you process model here so I can see what your schema is, and a small sequence that we can use.
Are you using TestStand 3.1? If so, then I can create the database from the database options as per the schema, otherwise, can you post an empty database to this thread so we can investigate further.

Thanks

Hannah
NI

[DGM]

Here are the installation files. It uses a customized version of the sequential model.

[DGM]

Ooops. Here's the second file ...

[Scott_Richardson]

To DGM -
Typically a UUT Name is already in the database if it is being retested. Is that the case here? If yes, does the customer expect the first set of results to be deleted before logging the new results? Does the customer expect to have a single UUT_DATA entry with two UUT_RESULT entries in the end?

As you may already know each primary key value can only exist once within a table. And if any other records in other tables reference the primary key, the primary key record cannot be deleted until the referencing records are first deleted. This is how the CONSTRAINT keywork works.

So either you have to use the existing UUT_DATA entry and create a new UUT_RESULT entry, or you have to delete the existing UUT_RESULT entry, delete the UUT_DATA entry, and then log the new UUT_DATA and UUT_RESULT entries.

What does your customer want?

[DGM]

Hi.

The customer is expecting a UUT_DATA entry to have a one-to-many relationship to one (or many) UUT_RESULT entries.

I guess I need to detect if there's a matching UUT_DATA entry already, and retrieve it from the database, before adding the new UUT_RESULT entry pointing to the existing UUT_DATA entry.

How would I best achieve this in TestStand 2.0?

[Scott_Richardson]

DMG -
What you are asking is to at run-time determine if the UUT_DATA record already exists. If it exists, acquire the primary key value in the UUT_DATA table for the record and assign it as the foreign key value for the UUT_RESULT. If it does not exist, create a new record in the UUT_DATA and use it as the foreign key value for the UUT_RESULT.

If the database that you were using supported complex stored procedures that can use IF statements (Access does not) the stored procedure could only log the UUT_DATA record if necessary, the UUT_RESULT statement would set its foreign key to the UUT number value directly.

Since TestStand 2.0 does not allow you to set a primary key value directly(TS 3.0 or 3.1 does), you would have to write to the primary key column like any other column, using an expression. The value written is just not available as a selectable foreign key for other tables.

Since stored procedures that use IF statements are not available to Access users, you need to conditionally create the UUT_DATA record. One way would be to use some external steps to query the UUT_DATA to determine if the record already exists. You would have to do this in the process model prior to logging the UUT results. In the database logging configuration, you would conditionally log the UUT_DATA statement using a precondition expression. The UUT_RESULT table would have to write its foreign key for the UUT_DATA table by value instead of referencing the primary key column from the UUT_DATA logging statement.