How to connect to a in-memory sqlite database using the Database Toolbox

According to the sqlite docs (https://www.sqlite.org/inmemorydb.html), it is possible to create an in-memory database using the :memory: file name. I attempted to create the database like :

conn = sqlite(":memory:", "create");

This did not work and returned the following error:

Error using sqlite

Database file :memory: was not created.

Error in index (line 2)

conn = sqlite(":memory:", "create");

Has anyone had any luck with in-memory databases or have any guidance?

Thank you!

Answer 1

[Kojiro Saito]

sqlite command only allows file name as input (like data.db), so you need to use JDBC interface instead.

SQLite JDBC for Windows and sqlite-jdbc.

You can use jdbc:sqlite::memory in the URL of databaseConnectionOptions.

%% Setup with JDBC driver
opts = databaseConnectionOptions("jdbc", "Other");
opts = setoptions(opts, ...
    'DataSourceName',"SQLite-jdbc-memory", ...
    'JDBCDriverLocation',"C:\Path\to\sqlite-jdbc-3.45.1.0.jar", ...
   'Driver', "org.sqlite.JDBC", "URL", "jdbc:sqlite::memory:");
username = "";
password = "";
saveAsDataSource(opts)

%% Connect to SQLite in Memory with JDBC
conn = database("SQLite-jdbc-memory", username, password);

%% Do something
% Write table to SQLite
LastName = ["Sanchez";"Johnson";"Zhang";"Diaz";"Brown"];
Age = [38;43;38;40;49];
Height = [71;69;64;67;64];
Weight = [176;163;131;133;119];
t =  table(LastName,Age,Height,Weight);
sqlwrite(conn, "mytable", t)

% Read from SQLite
t2 = sqlread(conn, "mytable");

%% Close connections
close(conn)