sqlitematlabnull

How to insert NULL values into a SQLite database table using MATLAB?


I am working with a SQLite database in MATLAB and trying to insert NULL values into some fields of a table. However, MATLAB throws an error when I attempt to insert NULL. Here's an example:

% Define the SQLite database file name
dbFileName = 'example.db';

% Delete the database file if it exists
if exist(dbFileName, 'file') == 2
    delete(dbFileName);
end

% Connect to the SQLite database
conn = sqlite(dbFileName, 'create');

% Create a sample table
createTableQuery = [ ...
    'CREATE TABLE example_table (' ...
    'id INTEGER PRIMARY KEY, ' ...
    'name TEXT, ' ...
    'age INTEGER DEFAULT NULL' ...
    ')'];
exec(conn, createTableQuery);

% Insert data with a NULL value for the 'age' field
insert(conn, 'example_table', {'id', 'name', 'age'}, {1, 'John', []});

% Close the database connection
close(conn);

When I run this code, MATLAB throws the following error:

Error using sqlite/insert
Inconsistent row counts for column 0 (1) and column 2 (0).
Error in test (line 22)
insert(conn, 'example_table', {'id', 'name', 'age'}, {1, 'John', []}); 

If I replace [] with 0, the insertion works, but this is not a true NULL value. How can I insert actual NULL values into a SQLite database table using MATLAB? Is there a specific syntax or workaround for this?


Solution

  • The limitations of using the MATLAB interface to SQLite are:

    Reference

    The possible workaround to use NaN

    insert(conn, 'example_table', {'id', 'name', 'age'}, {1, 'John', NaN});
    

    where NaN is an empty string in the database