I am trying to store variable length arrays of C++ doubles into a MySQL database.
I was initially storing each value in the array as a row of the form:
`pv_name` varchar(60) NOT NULL, `time_stamp` bigint(20) UNSIGNED NOT NULL, `array_index` bigint(20) UNSIGNED NOT NULL, `value` double NOT NULL, `alarm_status_id` tinyint(3) UNSIGNED NOT NULL, `alarm_severity_id` tinyint(3) UNSIGNED NOT NULL
time_stamp uniquely identified each array. The
array_index identified each element of the array.
The problem that I ran into is that the MySQL double type does not accept a 'nan' value. So my thought was to use a single blob type for the entire array. Would this be recommended? One advantage of doing so is that it allows me to store values of other types (tinyint, smallint, int, float, varchar) in this blob column in the same table, whereas before I needed a different table for each type. If this is appropriate, what is the best way to insert the array into the blob? I am currently casting it to a string using a format string of '%.17f' for each value and then using setString with a prepared statement (from the MySQL C++ connector (libmysqlcppconn)). Is it possible to use the '%g' format string instead without losing information? Is there an altogether different approach that may be more appropriate?