MATLAB Answers

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

49 views (last 30 days)
J. Lucas McKay
J. Lucas McKay on 13 Oct 2016
Commented: Yulin Shi on 13 May 2020
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.

Accepted Answer

Prasad Mendu
Prasad Mendu on 19 Oct 2016
Edited: Prasad Mendu on 19 Oct 2016
This could be because of a bug in the SQLite JDBC driver:
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.

More Answers (3)

Iliaz Miah
Iliaz Miah on 11 Sep 2018
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.

Brian
Brian on 21 Mar 2017
Did you ever figure out a workaround with this? I am using 2016a and have the same issue. Is this fixed in a newer matlab?
  1 Comment
J. Lucas McKay
J. Lucas McKay on 21 Mar 2017
Hey Brian - unfortunately my workaround was to avoid SQLite. No idea whether it has been fixed in an update.

Sign in to comment.


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

Community Treasure Hunt

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

Start Hunting!