Passing TestStand Variable to Database Options field

[Justin Busby]

No it does not pass the proper value. The Id value is a number but our "ID" is a string. I tried "Logging.StepResult.TS.StepId" and the foreign key constraint fails, which was the main problem I was running into previously.

Attached is a screenshot of my TestStand sequence for the currently worked around problem. When it gets to the GraphParentTitle step, that logged step ID is the one which I need for the "post processing" type statement. Now one thing I've tried but TestStand won't let me do, is establish the column as a foreign key and use the statement from STEP_RESULT as we do for other tables when the column is not also a primary key. It won't let me do this because it is also a primary key.

[Justin Busby]

Lars,

Any update on a possible solution? Will TestStand 4.1 possibly give more access to the needed variables? 

[J_Stape]

Hi Justin,

I have a few questions to help clarify your question in order to understand the problem and provide an answer.

From your previous post, it appears that you have three tables,  (step_result, meas_mul_numericlimit, meas_mul_numericlimit_info) and you need to store the ID entry located in step_result as a foreign key in both  meas_mul_numericlimit and meas_mul_numericlimit_info tables.  Is this correct?

If you take a look at other similiar steps, you will see that you can retrieve the primary key of step_result and store that key as a foreign key in another table by checking the foreign key checkbox and then selecting the step_result from the dropdown.  This will add the ID field in step_result to the meas_mul_numericlimit and  meas_mul_numericlimit_info tables as a column that is a foreign key.

Is this not what you wish to acheive?   If not, please clarify.

Thanks,

Jessica

Message Edited by J_Stape on 11-20-2008 10:56 PM

[Justin Busby]

What I'm trying to achieve right now is setting the ID of step_result as a Primary & Foreign key in "meas_mul_numericlimit_info". If I check solely the Foreign key field, I get an error. I have tried the foreign key setting to see if it would fix the issue and it didn't work.

The error I get (Just tried it again):

Details: An error occurred calling 'LogOneResult' in 'ITSDBLog' of 'zNI TestStand Database Logging'
An error occurred executing a statement.
Statement: GRAPH_PARENT_INFO.

Source: TSDBLog

Error Code: -2147352567; User-defined error code.
Possible System Error: Exception occurred.

Location: Step 'LogResult' of sequence 'ProcessModelPostResultListEntry' in 'DAQTron BatchModel.seq'

The message I get when trying the StepID expression value is:

"An error occurred calling 'LogOneResult' in 'ITSDBLog' of 'zNI TestStand Database Logging'
An error occurred executing a statement.
Statement: GRAPH_PARENT_INFO.
[MySQL][ODBC 5.1 Driver][mysqld-5.0.66a-enterprise-nt]Cannot add or update a child row: a foreign key constraint fails (`jupiter3_0_b/meas_mul_numericlimit_info`, CONSTRAINT `FK_MNLT_info_STEP_RESULT` FOREIGN KEY (`STEP_RESULT`) REFERENCES `step_result` (`ID`) ON DELETE CASCADE)
Description: [MySQL][ODBC 5.1 Driver][mysqld-5.0.66a-enterprise-nt]Cannot add or update a child row: a foreign key constraint fails (`jupiter3_0_b/meas_mul_numericlimit_info`, CONSTRAINT `FK_MNLT_info_STEP_RESULT` FOREIGN KEY (`STEP_RESULT`) REFERENCES `step_result` (`ID`) ON DELETE CASCADE)
Number: -2147467259
NativeError: 1452
SQLState: S1000
Reported by: Microsoft OLE DB Provider for ODBC Drivers

Source: TSDBLog"

As for the meas_mul_numericlimit table, it has a STEP_RESULT column and an ID column, so this problem doesn't exist as the step_result is setup as a foreign key.

[J_Stape]

Hi Justin,

From you last post as well as an explanation you provided earlier, I understand that want the following set-up for those three tables:

Please let me know if this is incorrect.

TestStand does not appear to allow a column to be a primary and foreign key:

 

However, the setup of your tables is unusual as the data in meas_mul_numericlimit and meas_mul_numericlimit_info could exist in one table.  I do not see the benefit of placing them in two different tables as it does not remove redundancy.  I realize that the foreign key is useful to link two tables together but in this case the primary key will uniquely identify each row in both meas_mul_numericlimit and meas_mul_numericlimit_info, so I am not sure why those two tables are not combined.

In short, I do not think that you will be able to have a field be a primary and foreign key, but I can not think of a case of why this would be needed.   Please let me know if I misunderstood or you want to discuss your need for a primary/foreign key further.

Message Edited by J_Stape on 12-30-2008 02:24 PM

Message Edited by J_Stape on 12-30-2008 02:26 PM

[Justin Busby]

I agree with what you said to a certain degree. The database structure is not optimal but changing the structure is not an option anymore. But to answer the question as to why they are separate tables, it's because the meas_mul_numericlimit_info table is, in essence, misnamed and is shared between different types.

meas_mul_numericlimit_info table holds only graphing information (Label of X and Y axis, Units of X and Y axis) with a relationship to the proper step for displaying array based data in a graph or specifically tagged data in a graph.

meas_mul_numericlimit actually holds raw gathered data.

The problem is that TestStand does not allow me to do a foreign key setting for step_result in meas_mul_numericlimit_info as it sees step_result as a primary key. The easy solution is to have a primary key of "ID" in meas_mul_numericlimit_info and then have step_result.ID as a foreign key. But a database structural change is out of the question. Another solution would be to add an "active" field for which in the query box you do a query in a query to grab the ID of the active UUT for the necessary WHERE clause. But again, the database change can't be done.

So, we've made a change in our ID creation scheme which now has the ID stored in a variable and we can use that variable value as the primary key entry. This also fixed another database slowdown we were having for a completely different reason. If only the TestStand generated ID would be readily available in a variable.

So I guess this issue is no longer valid for us. I do appreciate the help in trying to get this working.