cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

SQL database and "infinity"

Solved!
Go to solution

SQL database and "infinity"

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,


Corey Rotunno

0 Kudos
Message 1 of 9
(6,463 Views)

Re: SQL database and "infinity"

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,


Corey Rotunno

0 Kudos
Message 2 of 9
(6,402 Views)

Re: SQL database and "infinity"

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.

Adam
Message 3 of 9
(6,388 Views)
Solution
Accepted by topic author Corey.Rotunno
04-24-2017 06:24 AM

Re: SQL database and "infinity"

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.

 

Scott Richardson
National Instruments
Message 4 of 9
(6,375 Views)

Re: SQL database and "infinity"

Thanks Mada, I had not ever looked into schema precondition statements. Looks like that could do the trick.


Corey Rotunno

0 Kudos
Message 5 of 9
(6,366 Views)

Re: SQL database and "infinity"

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, 


Corey Rotunno

0 Kudos
Message 6 of 9
(6,364 Views)

Re: SQL database and "infinity"

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!!


Corey Rotunno

0 Kudos
Message 7 of 9
(6,360 Views)

Re: SQL database and "infinity"

Corey -

Thanks for the letting me know that the workaround is working.

Scott Richardson
National Instruments
0 Kudos
Message 8 of 9
(6,338 Views)

Re: SQL database and "infinity"

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) 

0 Kudos
Message 9 of 9
(3,601 Views)