04-20-2017 03:54 PM
Hello all,
I have been working with an access database thus far for my TestStand project. I am at a point where I am migrating over to a SQL server and have hit a(nother) minor bump.
So, as a background I am using the "MultiNumeric" step type extensively. I configure the number of measurements, measurement names, and measurement limits at runtime so that I can use the same subsequence in many different tests. To make things easier for me I leave all measurements as "GELE". If I want a single ended test I just set one of my limits to infinity. For example, if a value must be less than 2 I test it to be between -inf and 2. This has been working fine up until this point. The access database accepts -inf and inf as valid entries. However, when I run it under an SQL database I get an error telling me the value is "not a valid instance of data type float".
Is there anyway to modify that column's data type to be able to accept -infinity and infinity? Or must I accept that I can never use those again? It would be a shame if I can't use them because all the engineers in my group will have to remember not to use them even though TestStand recognizes them.
Thanks much,
Solved! Go to Solution.
04-21-2017 09:06 AM
Ok, so I have a bit of an update.
I have come to accept that in Microsoft's SQL 2014 server the "float" data type does not fully comply with the IEEE 754 definition of float in that it does not accept NaN, Infinity, or -Infinity and there is nothing I can do about that.
Two questions then:
1. Does anyone know if MS SQL 2016 might have fixed this? I can't find a solid yes or no on google, although MS engineers have posted in multiple forums that MS has considered adding support for it since 2008-ish.
2. Is there anyway to change the databaselogger plugin in TestStand to substitute NULL wherever it finds Infinity or -Infinity before it's sent to the SQL server?
Thanks,
04-21-2017 01:42 PM - edited 04-21-2017 01:43 PM
Hi Corey,
So as a quick thought, maybe you can add a precondition statement that checks for value and replaces it if it is "inf" or "-inf" with a "NULL"
The expression would look something like this:
Locals.Value = (Locals.Value == "Inf")?Null:Locals.Value
This is setting the variable "Locals.Value" to be NULL if true (equal to Inf) or remain the same if false. I am not 100% positive on the syntax of the infinity value (not certain it would be in quotes).
You could include this precondition statement in the specific item of the SQL schema by going to Configure>>Result Processing and click the configure icon (wrench and hammer) for the Database. On the schemas tab you could find the SQL schema that you are using and when you highlight the specific variable that you are writing you will notice a Precondition entry.
I have not tested this, as I do not have a SQL database connected, but it might be worth looking into.
04-23-2017 02:25 PM - edited 04-23-2017 02:27 PM
Hi Corey -
It appears that Microsoft SQL Server has never supported non-finite values for its FLOAT data type. Based on the response in this Microsoft Connect post from 2006, I doubt that they will ever support it.
TestStand numeric values can be non-finite (INF, -INF, IND, NAN). When such a value is passed to a SQL Server column of type FLOAT or passed to a FLOAT parameter of a stored procedure, the SQL Server client returns an error and the TestStand database logging will error.
For now, there are two basic options to mitigate:
1) Log NULL for non-finite values by changing the TestStand schema
2) Change the database table definition to store VARCHAR values (and make corresponding changes to TestStand schema)
We have opened a TestStand bug report on this limitation under ID 643628 and will investigate options. The below details and suggested options to mitigate may not be the final solution that TestStand implements.
The tables that present this problem are PROP_RESULT and PROP_NUMERICLIMIT.
PROP_RESULT
The default PROP_RESULT table defines the DATA field as BSTR(255), so this value will log properly for the Multi Numeric Limit step type, but not for the Numeric Limit step type because the schema data type for the column is specified as double-precision.
A) If you want to log NULL for non-infinite measurements, you can change the 'Precondition' expression for the following columns:
STEP_NUMERICLIMIT1.DATA
Logging.DatabaseOptions.IncludeOutputValues && Logging.StepResult.Numeric != NAN && Logging.StepResult.Numeric != INF && Logging.StepResult.Numeric != -INF && Logging.StepResult.Numeric != IND
PROP_MULTINUMERICLIMIT1.DATA
Logging.DatabaseOptions.IncludeOutputValues && Logging.PropertyResult.Data != NAN && Logging.PropertyResult.Data != INF && Logging.PropertyResult.Data != -INF && Logging.PropertyResult.Data != IND
To mitigate this for the "SQL Server Stored Proc" schema, you need to also change the data type of the corresponding parameter of the stored procedure.
B) If you want to log non-infinite values for the Numeric Limit step type, you should be able to change the schema data type to BSTR(255) for the STEP_NUMERICLIMIT1>>DATA column for the "Generic Recordset" and "Generic Insert" schemas in TestStand.
To mitigate this for the "SQL Server Stored Proc" schema, you will need to change the schema data type as mentioned above, but an additional change is required to the “InsertPropResult” stored procedure, specifically change the @pDATA parameter to “VARCHAR (255)”.
PROP_NUMERICLIMIT
The default PROP_NUMERICLIMIT tables defines its "limit" fields as FLOAT, and in TestStand 2016 there are 3 new related fields: NOMINAL_VALUE, UPPER_THRESHOLD, LOWER_THRESHOLD. An error will occur if a non-finite value is logged for any these fields.
A) To log NULL for non-finite limits, you must update the precondition expressions for the columns defined in the schema to return false if the value to be logged is non-finite. Here are the recommended changes:
STEP_NUMERICLIMIT2.HIGH_LIMIT
Logging.DatabaseOptions.IncludeLimits && Logging.StepResult.Limits.High != NAN && Logging.StepResult.Limits.High != INF && Logging.StepResult.Limits.High != -INF && Logging.StepResult.Limits.High != IND && (!PropertyExists("Logging.StepResult.Limits.Nominal") || (Logging.StepResult.Limits.Nominal != NAN && Logging.StepResult.Limits.Nominal != INF && Logging.StepResult.Limits.Nominal != -INF && Logging.StepResult.Limits.Nominal != IND ))
STEP_NUMERICLIMIT2.LOW_LIMIT
Logging.DatabaseOptions.IncludeLimits && Logging.StepResult.Limits.Low != NAN && Logging.StepResult.Limits.Low != INF && Logging.StepResult.Limits.Low != -INF && Logging.StepResult.Limits.Low != IND && (!PropertyExists("Logging.StepResult.Limits.Nominal") || (Logging.StepResult.Limits.Nominal != NAN && Logging.StepResult.Limits.Nominal != INF && Logging.StepResult.Limits.Nominal != -INF && Logging.StepResult.Limits.Nominal != IND ))
PROP_MULTINUMERICLIMIT2.HIGH_LIMIT
Logging.DatabaseOptions.IncludeLimits && Logging.PropertyResult.Limits.High != NAN && Logging.PropertyResult.Limits.High != INF && Logging.PropertyResult.Limits.High != -INF && Logging.PropertyResult.Limits.High != IND && (!PropertyExists("Logging.PropertyResult.Limits.Nominal") || (Logging.PropertyResult.Limits.Nominal != NAN && Logging.PropertyResult.Limits.Nominal != INF && Logging.PropertyResult.Limits.Nominal != IND ))
PROP_MULTINUMERICLIMIT2.LOW_LIMIT
Logging.DatabaseOptions.IncludeLimits && Logging.PropertyResult.Limits.Low != NAN && Logging.PropertyResult.Limits.Low != INF && Logging.PropertyResult.Limits.Low != -INF && Logging.PropertyResult.Limits.Low != IND && (!PropertyExists("Logging.PropertyResult.Limits.Nominal") || (Logging.PropertyResult.Limits.Nominal != NAN && Logging.PropertyResult.Limits.Nominal != INF && Logging.PropertyResult.Limits.Nominal != IND ))
Note: If you are using TestStand 2016, additional changes are required for the precondition expressions for the NOMINAL_VALUE, UPPER_THRESHOLD, and LOWER_THRESHOLD columns.
STEP_NUMERICLIMIT2.NOMINAL_VALUE
Logging.DatabaseOptions.IncludeLimits && Logging.StepResult.Limits.Nominal != NAN && Logging.StepResult.Limits.Nominal != INF && Logging.StepResult.Limits.Nominal != -INF && Logging.StepResult.Limits.Nominal != IND
STEP_NUMERICLIMIT2.UPPER_THRESHOLD
Logging.DatabaseOptions.IncludeLimits && Logging.StepResult.Limits.High != NAN && Logging.StepResult.Limits.High != INF && Logging.StepResult.Limits.High != -INF && Logging.StepResult.Limits.High != IND
STEP_NUMERICLIMIT2.LOWER_THRESHOLD
Logging.DatabaseOptions.IncludeLimits && Logging.StepResult.Limits.Low != NAN && Logging.StepResult.Limits.Low != INF && Logging.StepResult.Limits.Low != -INF && Logging.StepResult.Limits.Low != IND
PROP_MULTINUMERICLIMIT2.NOMINAL_VALUE
Logging.DatabaseOptions.IncludeLimits && Logging.PropertyResult.Limits.Nominal != NAN && Logging.PropertyResult.Limits.Nominal != INF && Logging.PropertyResult.Limits.Nominal != -INF && Logging.PropertyResult.Limits.Nominal != IND
PROP_MULTINUMERICLIMIT2.UPPER_THRESHOLD
Logging.DatabaseOptions.IncludeLimits && Logging.PropertyResult.Limits.High != NAN && Logging.PropertyResult.Limits.High != INF && Logging.PropertyResult.Limits.High != -INF && Logging.PropertyResult.Limits.High != IND
PROP_MULTINUMERICLIMIT2.LOWER_THRESHOLD
Logging.DatabaseOptions.IncludeLimits && Logging.PropertyResult.Limits.Low != NAN && Logging.PropertyResult.Limits.Low != INF && Logging.PropertyResult.Limits.Low != -INF && Logging.PropertyResult.Limits.Low != IND
B) If you want to log non-infinite values for the limit values you should be able to change the schema data type to BSTR(255), but this would be a much larger change. The above technically covers what needs to be done, but I did not have time to investigate this any further.
Note: All of the above mitigations were prototyped with the attached StoredProcedureMitigation.sql file for TestStand 2016. I changed the file version manually so you should be able to use in TestStand 2014.
04-24-2017 08:23 AM
Thanks Mada, I had not ever looked into schema precondition statements. Looks like that could do the trick.
04-24-2017 08:33 AM - edited 04-24-2017 08:34 AM
Scott,
I agree, it doesn't look like MS will support the full IEEE 754 float data type any time soon. I see at least one engineer at MS has been saying they have been considering it. However, some of the posts date back to ~2008 and there is still no full support for float. Guess I won't hold my breath.
Thank you very much for your in-depth answer. I had started looking into "option 1" before your reply as recommended by Mada in the previous post. However, I had not yet identified what the statements should actually be. Your help identifying those statements saved me a lot of time.
I would like to investigate option 2 further (changing column data type in schema) but I have other things to tackle and need something to show for my time spent sooner than later. Unfortunately working around this issue isn't "flashy", so it is more difficult to justify spending a lot of time on.
Hopefully an official change to TestStand to address this shortcoming in MSSQL is implemented, I'll keep my fingers crossed.
EDIT: FYI - I'm running TS 2016.
Thanks,
04-24-2017 08:44 AM - edited 04-24-2017 09:03 AM
Scott,
I just opened the .zip you attached. I will try and see how I can implement those at some time in the future. Unfortunately I am using the generic recordset schema for now. I had built my application around an access database initially so it was easiest to stay with the generic recordset for now. The unique GUID's for each table entry in the stored proc schema requires a pretty significant change. I currently sort a lot of things based on the "ID" column of each table. With stored proc I cannot do that and would have to figure out another way to do sorting.
EDIT: I manually added the preconditions you described to the Generic Recordset schema and re-processed my offending .tsr files and everything went smoothly. Thanks so much!!
04-24-2017 05:58 PM
Corey -
Thanks for the letting me know that the workaround is working.
04-09-2018 02:32 PM
If you are getting an unknown variable error:
An error occurred calling 'LogResults' in 'ITSDBLog' of 'zNI TestStand Database Logging'
An error occurred evaluating a database statement/column precondition.
Schema: Axcelis Schema With Preconditions
Statement: MEAS_NUMERICLIMIT.
Column: DATA
Property: Measurement.[Measurement 0]
Error in argument 1, 'Logging.StepResult.Numeric != NAN', in call to the expression function '#NoValidation'.
Unknown variable or property name 'Logging.StepResult.Numeric'.
Source: TSDBLog
Try this in PROP_RESULT table in STEP_NUMERICLIMIT1.DATA Precondition:
"Logging.PropertyResult.Data" instead of "Logging.StepResult.Numeric "
i.e:
Logging.DatabaseOptions.IncludeOutputValues && (Logging.PropertyResult.Data != NAN)