Passing TestStand Variable to Database Options field

[Justin Busby]

I seem to be having an issue with a non-optimized database query possibly because of a TestStand limitation or my limited knowledge of TestStand, probably the latter. What is currently happening is a custom type logs its data through a statement setup in the Database Options. This statement has a primary key reference and grabs that primary key from a recordset returned by this query:

              "SELECT id FROM step_result order by date_time desc limit 1".

This query can take around 30 seconds or longer to run which is way too long for a database query returning 1 row.

What I'd like to do is change that query, inside of the Database Options, to utilize a StationGlobals variable which will drastically speed this query up.

I'm doing the same query, with proper WHERE clauses, in another place (as a work-around solution to this same problem, which may be reverted if I fix this problem) and it is laid out as followed:

"SELECT id  FROM step_result where uut_result=\""+ StationGlobals.UUT_Result_ID +"\"  order by date_time desc limit 1"

I need that query to be run, with that variable, in the database options statement. Is this possible to do in TestStand? The problem I've seen so far is that the "Command Text" entry for the "Get Value from Recordset" doesn't like variables and string concats. Am I missing something obvious?

Thanks in advance for any help.

[Lars_L]

Hey Justin,

What version of TestStand are you using? Also, if you use the Expression Browser to input your query, what happens when you click the Check Expression for Errors button (the red checkbox on the bottom right).  I've placed your query in the Expression Browser and I'm getting the "No Errors" dialog.

Lars

[Justin Busby]

This is on TestStand 4.0.1.

You're right, the query that I posted with the TestStand variable works no problem and has no errors. However, in the Database Options window and the Command field, where I need the query to access that variable, I don't have the ability to do the expression window, check whether an expression is valid/has errors or even do an expression from what I have tried.

This isn't a normal TestStand step, this is a field for getting a primary key value for a Database schema statement column.

[Lars_L]

Hey Justin,

Are you talking about the "Command Text" value in the Primary Key entry under the Columns/Parameters tab in Database Options? If so then you can actually change the Type of the Primary Key to "Use Expression Value" and you can then change the Expression field to your command. If this isn't the field that you are trying to change then could you clarify which one you are interested in?

Lars

[Justin Busby]

That is the field that I am trying to change but I didn't think I could do that. If I change to "Use Expression Value" and then edit the expression field, is there a way to run the query and then extract the results? My understanding was the Get from record set selection ran the query and selected the first item in the set for you and I'm not 100% sure how to do that same action as an expression.

[Lars_L]

Hey Justin,

I think I now understand what you are trying to do. In essence you are trying to combine the "Get Value from Recordset" option with the "Expression" option. When you select "Get Value from Recordset" you are actually sending an SQL Query out the database, but when you select "Use Expression Value" then TestStand completely evaluates that expression down to a number and thus cannot implement the query.

I'm a little confused as to why you are doing your "SELECT id FROM step_result order by date_time desc limit 1" query in the first place. Could you explain how this fits into your application?

Also,  where are you implementing this other workaround that you mentioned? Is this in your sequence file or in your process model?

Lars

[Justin Busby]

The way our database is designed, this involves 3 different tables:

step_result - ID Primary kefield

meas_mul_numericlimit - ID primary key, foreign key of step_result ID field

meas_mul_numericlimit_info - Primary and foreign key for step_result ID field

A "parent" row is added into the step_result table, if the step type is of MultipleNumericLimitTest, a row is added into the m_m_n_i to hold graphing information and the actual data is inserted into the m_m_n table.

The MNLT step type uses the query to get the ID of the last entered step_result for the m_m_n_i table to setup the proper relationship. The problem is we need a specific s_r ID for everything to work properly. Obviously the Type definition is a "post processing" method as it can get the current step's ID from the database. The problem is that query isn't optimized and sorting 500k rows is incredibly slow.

The other problem, which I used a workaround on, dealt with a similar yet different situation.

In our Main sequence file, we had a sequence which would establish graphing features for tests whose measurements are spread across different "parents"/steps. We would establish the m_m_n_i information and then associations for a graph_parent field. What I did in that case was, create the association, then using normal SQL steps, gather the ID field and then update the ID for the graph_parent. Since this process was done pre-tests/measurements, I had the luxury to be able to do these steps outside of the database options field. In the other case though, its mid-test and based on measurements so I can't do the same workaround.

One thing I was wondering also is, in the processing of the custom type, is there a way to access the ID of the currently being processed step? If there was a variable for that, then I could just use the expression field.

Does this help/explain this a little better?

[Lars_L]

Hey Justin,

Are you referring to TestStand's unique ID for each step. If this is the case then I made a simple example that grabs the unique ID for each step and displays it. Would this work for your application?

Lars

Message Edited by Lars_L on 10-30-2008 03:21 PM

Message Edited by Lars_L on 10-30-2008 03:23 PM

[Justin Busby]

Yes the Unique ID generated for a step. Would that variable "Parameters.Step.TS.ID" be available while the type is being processed (i.e. when the database statements are being run)?

[Lars_L]

Hey Justin,

In order to access it from the database statements you should be able to use Logging.StepResult.TS.Id instead. Since I don't have a database to test this on at the moment I can't be sure. Can you test this and see if it returns the expected value?

Lars