New version of sqlite3 AddOn doesn't support NULL any more

sqlite3_new is the new version 3.0.0. In older versions the addon can handle NULL values in a table

>> a = sqlite3_new(f, 'SELECT CalibrationTable FROM CyclicValuesTableNames');
Error using sqlite3_new
unsupported column type
 
>> a = sqlite3(f, 'SELECT CalibrationTable FROM CyclicValuesTableNames');
>> a

a = 

  struct with fields:

    CalibrationTable: []

>> 

[Rik]

I will have a look at this as soon as I'm able to.

Note that a part of this change in version 3 is that I use a completely different source. This one I am able to compile myself, so I can support a much wider range of releases and operating systems with the exact same syntax. If this requires a change in the source code, you might need to provide a suggestion. I am not proficient enough in C to really make meaningful changes.

Answer 1

[Rik]

First the reproduction steps in a MWE:

% https://www.mathworks.com/matlabcentral/fileexchange/68298-sqlite3
db=[tempname '.db'];
sqlite3(db,'CREATE TABLE test (some_real REAL);')
sqlite3(db,'INSERT INTO test (some_real) VALUES (2)') % This does not make a difference.
sqlite3(db,'INSERT INTO test (some_real) VALUES (NULL)')
data = sqlite3(db,'SELECT * FROM test;');

The problem is with the C function below. The v.3.0.0 version of this function also has a mexErrMsgIdAndTxt in there, which prevents releasing the database file. The updated version fixes this by throwing an error later.

mxArray *get_column(sqlite3_stmt *stmt, int index)

{

int type = sqlite3_column_type(stmt, index);

if (type == SQLITE_TEXT) {

const char *text = sqlite3_column_text(stmt, index);

return WRAPPERmxCreateCharMatrixFromStrings(1, &text);

} else if (type == SQLITE_FLOAT) {

return mxCreateDoubleScalar(sqlite3_column_double(stmt, index));

} else if (type == SQLITE_INTEGER) {

mxArray *a = mxCreateNumericMatrix(1, 1, mxINT64_CLASS, mxREAL);

int64_t val = sqlite3_column_int64(stmt, index);

memcpy(mxGetData(a), &val, sizeof val);

return a;

} else if (type == SQLITE_NULL) {

// This should be changed to convert NULL to the required class.

return NULL;

} else if (type == SQLITE_BLOB) {

// This returns an SQLITE_FORMAT error status later.

return NULL;

}

// This should never happen.

return NULL;

}

This function is unable to tell whether the NULL should be a char, a double, or an integer. It then fails (and keeps the file locked, which is one of the bugs I'm trying to fix in v3.0.1).

If you have any practical suggestions for how to fix this, please feel free to suggest them.

[Rik]

If you expect a NaN when encountering a NULL, you could use return mxCreateDoubleScalar(mxGetNaN()); to fix this issue.

I am not sure what should be the expected behaviour when retrieving a BLOB type. If you have an opinion on this (or my proposal of returning NaN), feel free to post a comment

[Sebastian]

Thank you for your support. You bugfix solves the issue for me. Since I'm not a sqlite expert, I cannot make suggestions how the behaviour for a BLOB should be. I only recognized the error when updating to the new version and running my unit tests.

[Rik]

You're welcome. If I solved your issue, please consider marking it as accepted answer.

I will update the FEX submission when I get round to it. I want to merge this fix with a few other changes as well, so the update there may take a while.