TestStand 2016 On-The-Fly Reporting

[wesoli]

Hello

I'm wanting to try On-The-Fly database logging. The reason being that LabView sometimes crashes and then I'll loose all the completed test results. (That's another story...)

When I start the test sequence it jumps into the NI_DatabaseLogger.seq file and runs the step New UUT for Database Logging for OTF (in Model Plugin - UUT Start). However, when I query the UUT_RESULT table, no new entry has been written to the database. Since this hasn't been written, when a STEP_RESULT is written there is a runtime error, because it can't find the an entry in the UUT_RESULT table. 

Does anyone have insight to how the step New UUT for Database Logging for OFT tries to log to the database. I know it only writes some of the info and the balance when the test is completed. How does it write the initial data to the database? It clearly doesn't use a Store Proc. 

The only thing (I think) that may be an issue is that I have chosen to use a BigInt for all my primary keys and it's expecting a GUID. I have managed to get using the BigInt working well with the standard database logging. 

I've scratched around in the DBLog.vsxproj, but haven't found anything obvious and since there is a file missing I can't run and step through the code to see what happens. 

Any insights would be welcomed. 

Regards

Wesley

[BrandonC1]

@wesoli,

This is an interesting situation. Could you post a screenshot of the full run-time error you're experiencing when a step_result is written? It should provide more context that will help to figure this out.

Thank You,

Brandon C

Applications Engineer 

[wesoli]

Hi Brandon 

Here is a screenshot of the error:

Run-Time Error

Here is a screenshot of the STEP_RESULT[UUT_RESULT] column settings in Database Options:

As you can see, the column UUT_RESULT is populated by querying the ID column in the UUT_RESULT table. As mentioned in my first post, the step that is supposed to generate the partial UUT_RESULT table entry runs, but doesn't actually create a database entry. Below is a screenshot showing that the step has run:

I can only assume that the function called tries to create a new UUT_RESULT table entry, but fails without passing back any error message. This would be way the Run-Time error complains about the @pUUT_RESULT parameter, because it can't find an entry in the UUT_RESULT table. 

Kind regards

Wesley

[BrandonC1]

@wesoli,

I've been rooting around and talking with some other engineers and I found a piece of information that explains what we were experiencing.

"When you use on-the-fly database logging with a schema that uses a stored procedure or command statements that do not use the INSERT command, you cannot define constraints for foreign keys in step result statements that reference primary keys in UUT results. Defining constraints for these types of foreign keys generates an error because the on-the-fly database logger cannot execute the statement to create the record that contains the primary key before executing the statement to create the record that contains the foreign key."

I found that in

NI TestStand 2014 Help - On-the-Fly Database Logging: http://zone.ni.com/reference/en-XX/help/370052N-01/tsfundamentals/infotopics/database_otf/

Hopefully this clears up the behavior you were experiencing. 

Thank You,

Brandon C 

Applications Engineer 

[wesoli]

Hi Bradon

Thanks for the reply.

I've read that statement already 🙂 . In fact when you choose to use On The Fly Database Logging, TestStand warnings you about using Stored Procs or commands that don't use INSERT. However, I do use an INSERT to put values into the various tables (please see InsertUUTResult.sql attached). Just a comment on the file... I choose to use BigInt for all my Primary Keys. This why you will see the line "set @ipID = SCOPE_IDENTITY()" at the end. It's so I can pass back the BigInt value generated when the entry is made into the UUTResult database. 

So this is where I get a bit confused. The OTF Database Logging clearly uses the InsertStepResult stored proc when trying to log a step, but I don't know what stored proc (or other mechanism) it is using to try and write the initial UUTResult row entry. Since it can only write an partial entry into the UUTResult row, it must surely use an UPDATE command to fill in the balance of the info at the end of the test. Something like this:
1. Create UUTResult row. Populate serial number, Start Date Time, Station ID, etc
2. Pass back generated BigInt primary key. 

3. Do all the other StepResult, PropResult, etc entries for each executed step.

4. Update the UUTResult row at the end of the test with things like UUTStatus and UUTExecutionTime. 

I know it can be a pain trying to understand an issue without all the data. I'd be happy to put together a debugging package if you feel that would be helpful. I'll throw in the BigInt Primary Key stuff for free if anyone is interested 😉 . It makes looking at the tables so much easier - particularly when you know you're not going to have 100's of entries a day. 

Essentially, I just need to know what happens when ITSDGLog.NewUUT is called.

Hope you had a great weekend. 

Regards

Wesley

[RoxtheFox]

Hi Wesley, 

Could you share a screenshot of how you are setting the primary key in TestStand in UUT_RESULT? 

Thanks,

[wesoli]

Hi Roxana

Here is the screenshot as requested. 

Kind regards

Wesley

[BrandonC1]

Thanks for the picture Wesley,

One quick question - have you seen this behavior or error outside of using on-the-fly as your reporting style?

[wesoli]

Hi Brandon

No, this has only presented itself in On-The-Fly Reporting. 

Regards
Wesley

[BrandonC1]

Wesley, 

I appreciate your patience. The problem comes from a few places. 

The reason this problem is isolated to on-the-fly is because in that mode, step results are logged when they are obtained.

We're outputting the primary key from DB (shown in your screenshot on 11/01), so in order to generate a primary key, the stored procedure must run. The STEP result needs the primary key because its constrained with the foreign key, so TestStand tries to run the stored procedure, but, because we're using INSERT, it has to insert actual data because it can't update it later on.

So when it goes to insert UUT Results, TestStand tries to obtain the primary key by running the stored procedure since it's the output of the stored procedure - which throws the error.

I hope this all makes sense.