'Double' data is loading as 'single' from database when database reports 'double'

[UPDATE] please see the response to @Stephen Cobeldick for more information on the problem.

[UPDATE 2] see the code snippet below for a complete example of how to reproduce this problem.

I cant figure out what the deal is with this, the picture says it all. I'm using postgres, ODBC, windows 10. In the advanced options I have the default for "Numeric" as 'double'. In my database, the column types are 'double precision' (not float), but MATLAB keeps loading them as single.

?

[dpb]

"MATLAB even recognizes that the datatype is double."

MATLAB presumes everything numeric is double unless told differently; you can't infer that the options object is anything but default settings.

If the data are actually returned as single, that would imply that that's what the ODBC connection did to it.

Are the data being interpreted as numerically correct? If so, what's the real problem; you can always cast to double() if you really, really want to.

[Tim Darrah]

Thanks for a quick reply. However, you can see from the table that ODBC is returning the data correctly as double type. The real problem is an error I'm getting regarding trying to assign a double value to a single value in a couple of my s-function blocks. Ultimately you are right in that I can go thru and cast everything to double() when I load it in, but there has got to be a better way because I have about 30 parameters that are doing this.

As I'm looking at the screenshot above, I think something is happening in the table2struct() function, but not certain.

[Stephen23]

@Tim Darrah: a bit more context (other code and processing) might help us to understand where/why this occurs.

[Tim Darrah]

Thanks @Stephen Cobeldick for your reply, let me add some more info.

Here is the error I'm getting. Notice it is on line 67 of that file. This is just one instance, because I have several parameters that this error relates to, it just happens to throw on the first one.

Here is that file, where battery is loaded from the database and then converted from a table to a struct.

Here is the output from databaseImportOptions, which shows the double data type.

Here is the variable itself, which shows single data type. The only thing that happens between these two screenshots is a call to the function table2struct().

So, a work around is of course to cast all of my parameters to double, parameter by paremeter, struct by struct, but I have a strong suspicion that there is a better way to go about this and I just dont have enough in depth knowledge of matlab to know what is happening, or how to address it.

I hope this helps, and thank you kindly for requesting more information.

Answer 1

[Rishik Ramena]

Try setting the 'ToScalar' to true in table2struct .

[Tim Darrah]

That did not change anything unfortunately. Actually I had aleady used that after I took the above screenshot. Any other ideas? Is there a bug in MATLAB that is forcing this conversion behind the scenes? because if it is loaded as a double in the table, it should stay a double when it is converted to a struct.

[Rishik Ramena]

Can you share your code from the database connection to the connection close?

[Tim Darrah]

So I actually noticed something. when I look at the variable options, it shows 'double', as in the screenshot above. BUT, when I go into the table and pull the actual value, it shows single! So it looks like the problem is not in table2struct(), but in the function to load the data from the database.

[Tim Darrah]

It looks like when I look at databaseImportOptions, this is actually querying the database itself, and not what was loaded from the database. So I am misrepresenting the problem then and need to update the description.

[Tim Darrah]

Thanks @Rishik Ramena, using sqlread did in fact result in reading the value types correctly, although I had to reimplement a few things (in the end, not much).

data = sqlread(conn,tablename);

Answer 2

[Tim Darrah]

conn = database('uavtestbed2', 'postgres', get_password('#4KRx39Dn@09'));

battery_sern = 'B001';
uav_sern = 'X001';
battery = select(conn, sprintf(db_api.get_battery, uav_sern, battery_sern));

opts = databaseImportOptions(conn, 'eqc_battery_tb');
opts.VariableOptions
% opts.VariableOptions shows 'double' as in the above screenshots
class(battery(1,"v").v)
% this shows single??? 
battery = table2struct(battery, 'ToScalar', true);
class(battery.v)
% this shows single too

[Rishik Ramena]

opts = databaseImportOptions(conn, 'eqc_battery_tb');
data = sqlread(conn,tablename,opts);

Try using sqlread to force the import options on the selected table.

[Tim Darrah]

Ha!! Thank you. This is perfect. Well when I included the opts parameter, I received a "column 'q' does not exist" error, but without using opts, sqlread correctly reads the values as double.

Thanks!!

[Tim Darrah]

Although, this pretty much breaks the current implementation, which is an API wrapper around select statements. So for example, what would be the equivalent sqlread() function that retrieves a table record based on an id column, such as

battery_sern = 'B001';
uav_sern = 'X001';
battery = select(conn, sprintf(db_api.get_battery, uav_sern, battery_sern));

where

db_api.get_battery = "select ebt.* from eqc_battery_tb ebt join uav_tb ut on ebt.uav_id = ut.id where ut.serial_number ilike '%s' and ebt.serial_number ilike '%s';";

?

I see the help page, but its not entirely clear how this should be converted.

[Tim Darrah]

It seems that sqlread() is a lot more limiting then using select(), and in order to perform a join operation I would first need to read both tables into memory and then use matlab functions such as strcmp() to filter by condition. But then what is the point of a database if I'm not able to use the functionality? This pretty much kills performance too.

Is this something that can be fixed in a future release of MATLAB? It seems like this is a bug (reading 'double' values as 'single') in MATLAB's implementation of select(), and the workaround is causing me to rewrite a lot of code that if the function just worked as expected, I wouldn't have to.

[Gordon Burns]

Using fetch() instead of select() solves this problem.