Foreign Key to Custom MySQL Table GUID is Null

[hansf]

We are using TestStand 4.0 database reporting to write to a MySQL database.  We started with the standard TestStand database, modified some existing tables to add fields, and created some new tables to store data from some of our custom step types.  All is working well, with one exception.

When we try to use the ID field (a GUID) of our new step_testcase table as a foreign key called step_testcase in our new step_verifyimage table, the step_testcase column of the step_verifyimage table is always null.  The step_testcase table always has a valid ID field.  I've checked the Database Options settings and the MySQL tables and don't see anything wrong.

For reference, a JPEG image of the applicable portion of our database diagram is attached.  Most of the tables that aren't related to this problem aren't shown.

One thing that is a little different about the relationship between the step_testcase table and the step_verifyimage table is that we are trying to use a foreign key to relate rows that get result data from different sequence steps.  Our testcase step types are actually sequence calls to sequences that contain one or more visionverify step type instances.  That is different than the usual relationship, such as where one sequence step of testcase type is also a sequence call, both of which share one step result table entry.    

Is there some complication with using the GUID of one user created table as a foreign key in another user created table?  Or could the problem be caused by the fact that the tables that we're trying to relate with a foreign key collect data from different sequence steps?

Thanks,
Hans

[Evan P.]

Hans,

It sounds to me like a type mismatch issue. I would start by checking that the properties regarding the keys (Configure»Database Options) to ensure that the Columns/Parameters tab options are the same for step_verifyimage -> Step_Testcase and step_testcase -> ID fields. The exception would be that primary key option should be checked for the ID field, and foreign key option selected for the Step_Testcase field, referencing the step_testcase statement.

[hansf]

Hi Evan,

Thank you for your reply.  I double checked the types for the fields that you referred to - there isn't a mismatch.  See the attached JPEG showing the Database Option dialog screenshots.  I also checked the types of the fields in the database tables - all the step_testcase and step_verifyimage GUID fields and the fields that use them as a foreign key are CHAR(38).  These settings all match the foreign key and ID settings in all the other tables that get written to properly.

If there was a type mismatch, I'd expect TestStand to generate an error when writing to the database, but it does not generate any errors.  All the fields in all the tables are written to properly except for the STEP_TESTCASE foreign key field in the step_verifyimage table.  That's what makes me think that there is some complication with using a foreign key to a table that is written to by a different TestStand step.

Hans

[Scott_Richardson]

Hans -
The database logging feature maintains a stack of primary keys while TestStand recurses through the results. The types of foreign key relationships that TestStand logging supports are:

• A foreign key of a Statement of type Step Result can reference a primary key from a Statement of type UUT Result
• A foreign key of a Statement of type Measurement Result can reference a primary key from a Statement of type Step Result
• A foreign key of a Statement of a specific type can reference a primary key from a Statement of the same type that is higher in the list
• A foreign key of a Statement of type Step Result can reference a primary key from a Statement of type Step Result for the calling Sequence Call step

There is no provision to allow two different steps to reference each other unless one of the steps is a sequence call to a sequence that contains the other step.

Does this help explain the behavior that you are seeing because I am not sure I understand you use case?

[hansf]

Hi Scott,

I think we're on the right track to finding out what is going on.  I've created a stripped down sequence file example of what we're trying to do.  Please see the attached file.

The hierarchy of the sequences and steps are:

Main Sequence
   Contains one or more Instances of a TestCase step type (one instance in the attached example seq)
 
   Each TestCase Instance
      Contains one or more instances of VisionVerify step types (two instances in the attached example seq)

         Each VisionVerify Instance
            Contains one instance of a VerifyImage step type (in our actual use, the VerifyImage step type would be a LabVIEW VI call, but to simplify this example I set the adapter of the VerifyImage step type to None) 
   

The idea is that a Top Level sequence will have one or several TestCase sequence calls, which each can have one or more VisionVerify step calls, which each have one VerifyImage step call.  We want to record the results in our database.  We need to tie the VerifyImage step results to their parent TestCase result.

When I run this example sequence using the single pass entry point of our process model, I see the same problematic behavior - the foriegn key in the step_verifyimage table is null.  That prevents tying the VerifyImage results back to their parent TestCase result.

It seems like our desired foreign key relationship is:
A foreign key of a step of a specific type (VerifyImage) referencing a primary key of a sequence call step (of type TestCase) that calls a sequence (of type VisionVerify) that calls the VerifyImage type.

Our situation does not quite meet the above mentioned criteria that "There is no provision to allow two different steps to reference each other unless one of the steps is a sequence call to a sequence that contains the other step."  In our case, one of the steps in MainSequence is a sequence call to a TestCase sequence that contains a sequence call to a VisionVerify sequence that contains the other (VerifyImage) step.  We have an extra sequence call in the relationship.

Since our primary key should still be available in the stack, albeit another level up, I'm hoping there is a way for us to accomplish our goal.

Thanks for your help,
Hans

[hansf]

Oops, I attached the wrong file.  Sorry about that.  Here's the example sequence file.

Hans

[Scott_Richardson]

Hans -
I have reproduced the use case that you have described but I have not been able to get the database logging feature to include the key as you need. At this point all that I can do is log this as a suggested feature.

[hansf]

Hi Scott,

Thank you for your time and effort in reproducing my problem.  Now that I know that TestStand's database logging feature won't include the foreign key that I need through it's normal logging scheme, I need to create a workaround.

One idea:
Create a manually generated foreign key to store in a new column in the step_testcase table.  This new column would also be a GUID (could be called GENERATED_GUID) but it would be one that is generated just before the testcase step executes (or preferably in the pre-expression or pre step of the testcase step).  That generated GUID value would be stored in a TestStand variable so that the same GUID value could be written to the result containers for both the testcase step and the verifyimage step.  Then the step_verifyimage table would be modified in the MySQL database so that its foreign key points to the GENERATED_GUID column of the step_testcase table. 

In this scheme, I'm thinking that I have to tell TestStand that the step_verifyimage column that contains the value of GENERATED_GUID is a String(VarChar) of size 38 but is not a foreign key.

To try this approach, I need a way to generate a GUID, either in TestStand or LabVIEW.  How might I generate that GUID?

Thanks,
Hans 

[Scott_Richardson]

Hans -
There are SDK functions, so the C++ equivalent is:

GUID guid;
RPC_STATUS rStatus;
unsigned char * string;
rStatus = ::UuidCreate(&guid);
::UuidToString(&guid, &string);
::RpcStringFree(&string);

The Header Declared in Rpcdce.h; include Rpc.h.
Library Link to Rpcrt4.lib.
DLL Requires Rpcrt4.dll. 

[hansf]

Hi Scott,

Thanks for the reply but I'm not sure how to use that info.  I'm not using C++, C#, or LabWindows, so I'd really like to have something I can invoke directly from TestStand or LabVIEW.  Something we could call using ActiveX, directly from a existing DLL, or from an exe that runs from the command prompt would be much easier because I would not have to set up a whole additional development environment just to create a method generate a GUID.

Thanks,

Hans