Is it possible to extract data from a SQLite database with null values?

Question

J. Lucas McKay 2016-10-13

1
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/307112-is-it-possible-to-extract-data-from-a-sqlite-database-with-null-values

评论： Yulin Shi 2020-5-13

Hi there - I am new to databases and am running into some trouble with SQLite.

I have a SQLite database where I can retrieve data from variables with no null values successfully, but variables with empty entries throw an error in SqlDbConnector/fetchRows. I have dbprefs set with NullStringRead and NullNumberRead to 'null'; however, I get the error:

Error using matlab.depfun.internal.database.SqlDbConnector/fetchRows Unexpected NULL; (zero-based) column index: 0; details: initVecVarVecFromSqldbTypes().

% Isolate the walk of patient "McKay" 's first walk over the gaitmat. It
% should have two passes.
ID_i = num2str(ID{1})
% id is retrieved correctly
id = fetch(db, ['SELECT id FROM FootFall WHERE Gait_id = ' ID_i])
% variables with blank rows are retrieved incorrectly
PassNo = fetch(db, ['SELECT PassNo FROM FootFall WHERE Gait_ID = ' ID_i])
Blob1 = fetch(db, ['SELECT Blob1 FROM FootFall WHERE Gait_id = ' ID_i])

Is this a limitation in SQLite, or should I have dbprefs set differently? Any help appreciated.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

Prasad Mendu 2016-10-19

2
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/307112-is-it-possible-to-extract-data-from-a-sqlite-database-with-null-values#answer_239755

编辑：Prasad Mendu 2016-10-19

This could be because of a bug in the SQLite JDBC driver:

https://bitbucket.org/xerial/sqlite-jdbc/issue/81/the-method-getobject-in-rs-class-processes

MATLAB uses the wasNull() method to determine whether a null value was returned (such that we can replace it with the 'NullStringRead' value). wasNull() unexpectedly throws an exception here.

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

J. Lucas McKay 2016-10-21

Thanks Prasad - that sounds like it explains it.

请先登录，再进行评论。

Answer 2

Iliaz Miah 2018-9-11

4
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/307112-is-it-possible-to-extract-data-from-a-sqlite-database-with-null-values#answer_336334

在 MATLAB Online 中打开

Use Built-in function ifnull() in your query. example(For SQLite db) I would like to get 0 in return of a query if actual data is null.

MatLab code:

table2array(fetch(conn,'SELECT ifnull(column2,0) From Table1 WHERE ID=12346')
This will return 0 if column2 is empty/null for the selected row.

2 个评论
显示无隐藏无

Samuel 2018-12-4

Saved my day. :thumbs up:

Yulin Shi 2020-5-13

Quite Useful!

请先登录，再进行评论。

Answer 3

Tim Buschman 2017-6-5

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/307112-is-it-possible-to-extract-data-from-a-sqlite-database-with-null-values#answer_269548

The workaround I found was to use mksqlite package: http://mksqlite.sourceforge.net/index.html . Had no problems reading a SQLite database with NULLs.