Unable to connect to SQLite database

I am unable to use sqlite to connect to databases that have worked previously for me in Matlab and currently work in R. The command:

conn = sqlite(dbfile);

yields the error:

"Error using sqlite

Received exception upon attempting an operation. Exception: [Unable to open the database. (unable to open database file)]. Details:

[connect(~/*.db)]."

The command

isfile(dbfile)

confirms that the file exists.

I tried using the example tutorial SQLiteWorkflow.m, but received an error:

"Error using sqlite

SQLite file exists.

Error in SQLiteWorkflow (line 18)

conn = sqlite('tutorial.db', 'create');"

Any ideas on how to get sqlite working for me again?

[Walter Roberson]

What is the content of dbfile ?

As for the tutorial: it looks like you ran it before and the tutorial.db already exists. You could delete it to rerun the tutorial.

[Annette Hynes]

My 'dbfile' is a SQLite 3 .db file containing flow cytometry data. Files range from 10 - 100 MB.

The file 'tutorial.db' exists nowhere on my computer, but I still get the same error when trying to run the tutorial.

[Walter Roberson]

After you

openExample('database/WorkWithDataMATLABInterfaceToSQLiteExample')

what shows up for pwd() ? And when you ls() which files do you see there?

[Walter Roberson]

My question about dbfile was asking about what the character vector stored in the variable dbfile is -- the path to the database you are trying to connect to.

[Annette Hynes]

In another development, I used the answer from a post about importing a table from .db and it worked perfectly with my databases.

How can the following code work:

system('sqlite3 -header -csv my_dbfile.db "select * from sfl;" > results.csv');

when I am unable to make a connection using:

conn = sqlite('my_dbfile.db');

?

Answer 1

[Annette Hynes]

I am still unable to use 'sqlite' to connect to my .db databases (OSX High Sierra, R2018b). The one change I made in my Matlab setup between when I could connect and now is that I changed the MATLAB_JAVA environment to avoid chronic crashes. I still crash, but less frequently now.

My current workaround is to use 'system' to access my SQLite databases via a shell, export them to a .csv file, and import them into the Matlab workspace.

dbfile = fullfile([file_path, '/'], [this_cruise, '.db']);
sqlquery = 'SELECT * FROM sfl';
outfile = [file_path, '/', this_cruise, '_PAR.csv'];
cmd = ['sqlite3 -header -csv ', dbfile, ' "', sqlquery, ';" > ', outfile];
system(cmd);    

T = readtable(outfile);

Answer 2

[Annette Hynes]

pwd

'/Users/annettehynes/Documents/MATLAB/Examples/database/WorkWithDataMATLABInterfaceToSQLiteExample'

ls

WorkWithDataMATLABinterfaceToSQLiteExample.mlx

My .db file is stored locally on my machine so the path is simply a local path. On another note, why am I unable to copy and paste into these question boxes?

[Walter Roberson]

Unfortunately with the new editor, these boxes to enter in comments etc. do not show up as text boxes for the purposes of browsers, so browsers do not enable menu items for copying and pasting.

You appear to be using Mac. If you are not up to date on your Firefox, you might possibly be able to use command-V to paste into these boxes. However that will probably fail if you are up to date on Firefox. If you use Safari then command-V will work (at least for now...)

[Annette Hynes]

Thanks for the tip!