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

2021 3 11
2 个回答
回答已采纳
更新时间：2023 7 1
9 次查看（30 天）

1 个投票

[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.
?

4 个评论
显示 2更早的评论 隐藏 2更早的评论

dpb
dpb 2021-3-11
编辑：Stephen23 2021-3-12
"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
Tim Darrah 2021-3-12
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
Stephen23 2021-3-12
@Tim Darrah: a bit more context (other code and processing) might help us to understand where/why this occurs.
Tim Darrah
Tim Darrah 2021-3-12
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.

请先登录，再进行评论。

请先登录，再回答此问题。

 采纳的回答

Rishik Ramena
Rishik Ramena 2021-3-18

0 个投票

Try setting the 'ToScalar' to true in table2struct .

5 个评论
显示 3更早的评论 隐藏 3更早的评论

Tim Darrah
Tim Darrah 2021-3-18
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
Rishik Ramena 2021-3-18
Can you share your code from the database connection to the connection close?
Tim Darrah
Tim Darrah 2021-3-18
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
Tim Darrah 2021-3-18
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
Tim Darrah 2021-3-24
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);

请先登录，再进行评论。

更多回答（1 个）

Tim Darrah
Tim Darrah 2021-3-18

0 个投票

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

5 个评论
显示 3更早的评论 隐藏 3更早的评论

Rishik Ramena
Rishik Ramena 2021-3-18
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
Tim Darrah 2021-3-19
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
Tim Darrah 2021-3-19
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
Tim Darrah 2021-3-22
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
Gordon Burns 2023-7-1
Using fetch() instead of select() solves this problem.

请先登录，再进行评论。

类别

在 帮助中心 和 File Exchange 中查找有关 Database Toolbox 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by