NaN and MySQL

[bruno_p]

Hi,

TestStand (3.1) can use NaN constant for NumericLimitTest.
But I log my results to a MySQL database (4.1.11) using the MySQL ODBC driver (3.51.12).

When TestStand logs the results to the database, it returns an error : the error message is very long but it says that I have an error in my SQL syntax for the STEP_NUMERICLIMIT statement.

I removed the NaN constant and it works.
I tried to use the NaN constant with an Access database and it works.

I found that MySQL doesn't manage the NaN constant. It is possible to use the 'NaN' value in SQL syntax but MySQL convert it to 0.
Can anyone confirm that ?

TestStand allows the use of the NaN constant, so I can't prevent the developpers from using it...
Has anyone a suggestion to solve my problem ?

Bruno

[Odd_Modem]

As I recall, Access logs NaN as zero. I think I saw that with Access 2000, ADO and LabVIEW. This is a mistake and a loss of information, IMO.

[bruno_p]

Sure this is a loss of information...
and a big problem for me and my customer.

If you saw that with Access, maybe did you find a work around that you could suggest to me ?

[SanaaT]

Hello bruno_p,

You will find, in the links below, documents that will sure interest you:

http://digital.ni.com/public.nsf/websearch/FE29E96ED392FA0286256D3500677F75?OpenDocument

http://digital.ni.com/public.nsf/websearch/6B392861F47A2F1286256A39004D4BDD?OpenDocument

Regards,

Sanaa T

[Scott_Richardson]

Bruno -
Since MySQL cannot handle the NAN value and if you are willing to convert the NAN value to a real value such as zero, I think you could use an expression in the configuration for the value being logged to the database.  Try to use the expression:

(Logging.StepResult.Numeric == NAN)?0:Logging.StepResult.Numeric

This might work.

[Odd_Modem]

log the numbers to text.

[bruno_p]

Thank you all.

Sanaa, I know how to use NaN constant with TestStand, but I don't know how to log it to a mysql database.

Scott, it is a good suggestion to avoid the errors. But I cannot use the zero value : this value has no sense when you generate the report again from the database.
I didn't think I could put an expression in the database configuration. It's a good information but I don't know wich value to use. It's difficult to find a value that is significant for all measurements of my customer.

Odd_Modem, the use of text results is a good suggestion. But the customer wants to keep the values in numeric format in ordrer to do some statistics.

Because mysql doesn't handle NaN value, I should find the "best" work around. So I keep all your suggestions to find the best way to answer the customer needs.

If you have other suggestions, give me them.
Thanks.
Bruno

[Scott_Richardson]

Bruno -
Other than using a predefined value to represent NAN, ie. -1.0 E-308 or zero, the only option is to create a new boolean field in the table for each numeric value.  The field could be called, VALIDDATA or NANDATA.  Add a new column to the statement in TestStand using the expression: Logging.StepResult.Numeric == NAN.

Any SQL queries that get the values must also read the new field to determine whether the data values are valid.

[drohacek]

Another idea: use the database NULL value to represent NaN.

To do this, specify a precondition for logging the DATA column that causes TestStand not to log the data if the value is NaN. The precondition would look like this:

Logging.DatabaseOptions.IncludeOutputValues && Logging.StepResult.Numeric != NAN

You specify the precondition in the Columns/Parameters tab of the Database Options dialog box. This requires that the column in the database be configured to allow NULL values.

[bruno_p]

Thanks for all your advices.

Finally TestStand uses other values than NAN that causes database logging errors : IND, +/-INF.

So I think I will use a new database column that indicates an invalid value.
I should compare the numeric result with the invalid values to log the data of this column (TestStand database configuration).
And the advice for the developpers of sequence files will be : don't use (as possible) the values NAN, IND and +/-INF.

Bruno