Odbc Preview Sql Statement?

[jerzydziewierz]

Hello,

 

I am trying to set up logging of my 54-element numeric array that i load from OPC server,

 

i am using Oracle database through DSN. I have went through all the hoops related to selecting proper ODBC version (32bit), getting correct connection string, correcting SQL keywords and data types in the  "SQK Strings" box etc.

 

now i can see that DAQFactory correctly connects to the database, creates a table when it it not there (throws an error when it is) and creates fields with correct data types in the table.

 

I now get "C1012 Failed to access table for logging. Msg: ORA-00911: invalid character"

 

in other words, Oracle reports "ORA-00911" due to incorrectly formatted insert query.

 

I cannot imagine what invalid character this might be. Is there a way of previewing the SQL query that DAQFactory generates?

 

Regards,

 

Jerzy Dziewierz

[AzeoTech]

There isn't presently.  Is there an option in Oracle or the ODBC driver to log the queries?  What is the description of the table?

 

If it comes to it, we can add the sql command into the error message for you, if only temporarily.  I'm concerned that long queries will hide the error message for new users.

[jerzydziewierz]

Dear Guru,

 

Through trial and error i figured out that the problem might be with treatment of NaNs by DAQFactory and Oracle SQL

 

My OPC server does recognize when a thermocouple is unavailable and reports "NaN" (i have lots of thermocouples so i have to know which ones do not measure meaningful things).

 

When loading NaN value from the OPC server, the DAQFactory displays it as "-1.#J"  . This does not go well with my SQL server, which requires to use the term "BINARY_FLOAT_NAN" without parentheses (so, like it was a number and not a text in SQL).

 

I am writing a custom script to do this logging - but this begs a question, is there a good way to render NaN for example, in a "display variable value" box?

 

 

By the way,

Guru, do you have a name?

[AzeoTech]

Nan's are sent to the database as NULL.  "-1.#J" is the floating point form of NaN.  DAQFactory, however, does not use this form, mostly because its a pain to work with.  Instead, we consider internally any value over 1e300 as NaN.  But as I said, it will write a NULL to the database.  However, if the OPC server is returning a true NaN, it gets stored internally as a true NaN, and likely ends up in the SQL query as -1.#J which is why you get that Invalid character error.

 

There is no way to check for a true NaN in DAQFactory, so my first recommendation is to see if you can change the OPC server to output something else instead of NaN, such as -9e99, or better yet, 1e300 instead.  If you can get it to output 1e300 then you are probably done.  If not, but you can get it to output some other fixed number that is out of the range of reasonable values, you can easily use a Conversion to convert that to a DAQFactory NaN() and then NULL will go to the database.

[jerzydziewierz]

Dear Azeotech,

 

I have written the OPC server, so yes, i could modify it to produce 1e300. However.

With all due respect, but i think i will be better off with proper NaNs.

 

In your approach, for example, if someone decides to scale a vector containing good measurements and some NaNs by, say, 1e-3 (measurement reported in milivolts, display as volt fraction) - sudenly your 1e300 becomes "a real number". This is obviously not what end user would expect.

 

Secondly, if i go for the "special value" approach, i would have to propagate 'special behaviour" into rest of the system, which is outside of my controll. In fact, it is NaN that is such "special value" that is already treated with special behaviour .

 

For now, I have written my own logging script that treats NaNs and converts them into "BINARY_FLOAT_NAN" statement that Oracle likes.

 

Regards,

 

Jerzy Dziewierz