Undocumented Matlab
  • SERVICES
    • Consulting
    • Development
    • Training
    • Gallery
    • Testimonials
  • PRODUCTS
    • IQML: IQFeed-Matlab connector
    • IB-Matlab: InteractiveBrokers-Matlab connector
    • EODML: EODHistoricalData-Matlab connector
    • Webinars
  • BOOKS
    • Secrets of MATLAB-Java Programming
    • Accelerating MATLAB Performance
    • MATLAB Succinctly
  • ARTICLES
  • ABOUT
    • Policies
  • CONTACT
  • SERVICES
    • Consulting
    • Development
    • Training
    • Gallery
    • Testimonials
  • PRODUCTS
    • IQML: IQFeed-Matlab connector
    • IB-Matlab: InteractiveBrokers-Matlab connector
    • EODML: EODHistoricalData-Matlab connector
    • Webinars
  • BOOKS
    • Secrets of MATLAB-Java Programming
    • Accelerating MATLAB Performance
    • MATLAB Succinctly
  • ARTICLES
  • ABOUT
    • Policies
  • CONTACT

Using SQLite in Matlab

December 27, 2017 5 Comments

MathWorks invests a huge amount of effort in recent years on supporting large distributed databases. The business case for this focus is entirely understandable, but many Matlab users have much simpler needs, which are often served by the light-weight open-source SQLite database (which claims to be the most widely-used database worldwide). Although SQLite is very widely used, and despite the fact that built-in support for SQLite is included in Matlab (for its internal use), MathWorks has chosen not to expose any functionality or wrapper function that would enable end-users to access it. In any case, I recently came across a need to do just that, when a consulting client asked me to create an interactive data-browser for their SQLite database that would integrate with their Matlab program:

SQLite data browser

In today’s post I will discuss several possible mechanisms to integrate SQLite in Matlab code, and you can take your pick among them. Except for the Database Toolbox, all the alternatives are free (open-source) libraries (even the commercial Database Toolbox relies on one of the open-source libraries, by the way).

sqlite4java

sqlite4java is a Java package by ALM Works that is bundled with Matlab for the past several years (in the %matlabroot%/java/jarext/sqlite4java/ folder). This is a lightweight open-source package that provides a minimalist and fast (although not very convenient) interface to SQLite. You can either use the package that comes with your Matlab installation, or download and use the latest version from the project repository, where you can also find documentation.
Mark Mikofski exposed this hidden nugget back in 2015, and you are welcome to view his post for additional details. Here’s a sample usage:

% Open the DB data file
db = com.almworks.sqlite4java.SQLiteConnection(java.io.File('C:\Yair\Data\IGdb 2017-11-13.sqlite'));
db.open;
% Prepare an SQL query statement
stmt = db.prepare(['select * from data_table where ' conditionStr]);
% Step through the result set rows
row = 1;
while stmt.step
   numericValues(row) = stmt.columnInt(0);    % column #0
   stringValues{row}  = stmt.columnString(1); % column #1
end
% Cleanup
stmt.dispose
db.dispose

% Open the DB data file db = com.almworks.sqlite4java.SQLiteConnection(java.io.File('C:\Yair\Data\IGdb 2017-11-13.sqlite')); db.open; % Prepare an SQL query statement stmt = db.prepare(['select * from data_table where ' conditionStr]); % Step through the result set rows row = 1; while stmt.step numericValues(row) = stmt.columnInt(0); % column #0 stringValues{row} = stmt.columnString(1); % column #1 end % Cleanup stmt.dispose db.dispose

Note that since sqlite4java uses a proprietary interface (similar, but not identical, to JDBC), it can take a bit of time to get used to it. I am generally a big fan of preferring built-in components over externally-installed ones, but in this particular case I prefer other alternatives.

JDBC

JDBC (Java Database Connectivity) is the industry standard for connectivity to databases. Practically all databases nowadays have at least one JDBC connector, and many DBs have multiple JDBC drivers created by different groups. As long as they all adhere to the JDBC interface standard, these drivers are all equivalent and you can choose between them based on availability, cost, support, license, performance and other similar factors. SQLite is no exception to this rule, and has several JDBC driver implementations, including xerial’s sqlite-jdbc (also discussed by Mark Mikofski) and sqlitejdbc. If you ask me,
sqlite-jdbc is better as it is being maintained with new versions released periodically.
The example above would look something like this with sqlite-jdbc:

% Add the downloaded JAR library file to the dynamic Java classpath
javaaddpath('C:\path\to\sqlite\sqlite-jdbc-3.21.0.jar')
% Open the DB file
jdbc = org.sqlite.JDBC;
props = java.util.Properties;
conn = jdbc.createConnection('jdbc:sqlite:C:\Yair\Data\IGdb 2017-11-13.sqlite',props);  % org.sqlite.SQLiteConnection object
% Prepare and run an SQL query statement
sqlStr = ['select * from data_table where ' conditionStr];
stmt = conn.createStatement;     % org.sqlite.jdbc4.JDBC4Statement object
rs = stmt.executeQuery(sqlStr);  % org.sqlite.jdbc4.JDBC4ResultSet object
% Step through the result set rows
rows = 1;
while rs.next
   numericValues(row) = rs.getLong('ID');
   stringValues{row}  = rs.getString('Name');
end
% Cleanup
rs.close
stmt.close
conn.close

% Add the downloaded JAR library file to the dynamic Java classpath javaaddpath('C:\path\to\sqlite\sqlite-jdbc-3.21.0.jar') % Open the DB file jdbc = org.sqlite.JDBC; props = java.util.Properties; conn = jdbc.createConnection('jdbc:sqlite:C:\Yair\Data\IGdb 2017-11-13.sqlite',props); % org.sqlite.SQLiteConnection object % Prepare and run an SQL query statement sqlStr = ['select * from data_table where ' conditionStr]; stmt = conn.createStatement; % org.sqlite.jdbc4.JDBC4Statement object rs = stmt.executeQuery(sqlStr); % org.sqlite.jdbc4.JDBC4ResultSet object % Step through the result set rows rows = 1; while rs.next numericValues(row) = rs.getLong('ID'); stringValues{row} = rs.getString('Name'); end % Cleanup rs.close stmt.close conn.close

Database toolbox

In addition to all the above, MathWorks sells the Database Toolbox which has an integral SQLite connector, in two flavors – native and JDBC (the JDBC connector is simply sqlite-jdbc that I mentioned above, see a short discussion here).
I assume that the availability of this feature in the DB toolbox is the reason why MathWorks has never created a documented wrapper function for the bundled sqlite4java. I could certainly understand this from a business perspective. Still, with so many free alternatives available as discussed in this post, I see not reason to purchase the toolbox merely for its SQLite connector. Then again, if you need to connect to several different database types, not just SQLite, then getting the toolbox might make sense.

mksqlite

My personal favorite is actually none of these Java-based connectors (surprise, surprise), but rather the open-source mksqlite connector by Martin Kortmann and Andreas Martin. This is a native (Mex-file) connector that acts as a direct Matlab function. The syntax is pretty straight-forward and supports SQL queries. IMHO, its usage is a much simpler than with any of the other alternatives:

% Open the DB file
mksqlite('open', 'C:\Yair\Data\IGdb 2017-11-13.sqlite');
% Query the database
results = mksqlite(['select * from data_table where ' conditionStr]);
numericValues = [results.ID];
stringValues  = {results.Name};
% Cleanup
mksqlite('close');

% Open the DB file mksqlite('open', 'C:\Yair\Data\IGdb 2017-11-13.sqlite'); % Query the database results = mksqlite(['select * from data_table where ' conditionStr]); numericValues = [results.ID]; stringValues = {results.Name}; % Cleanup mksqlite('close');

Can it be any simpler than this!?
However, the main benefit of mksqlite over the other connectors is not its simplicity but the connector’s speed. This speed is due to the fact that the query is vectorized and we do not need to loop over all the separate data rows and fields. With the other connectors, it is actually not the loop that takes so long in Matlab, but rather the overheads and inefficiencies of numerous library calls to fetch one single value at a time from the result-set – this is avoided in mksqlite where there is only a single call. This results in lightning speed: A couple of years ago I consulted to a client who used a JDBC connector to an SQLite database; by switching from a JDBC connector to mksqlite, I reduced the execution time from 7 secs to 70 msecs – a 100x speedup! In that specific case, this made the difference between an unusable program and a highly interactive/responsive one.

Other alternatives

In addition to all the above, we can also use a .NET-based connector or a Python one – I leave these as an exercise for the reader…
Have I forgotten some important alternative? Or perhaps you have have some related tip you’d like to share? If so, then please leave a comment below.
Happy New Year everybody!

Related posts:

  1. Secure SSL connection between Matlab and PostgreSQL – It is tricky, but quite possible, to use SSL to connect Matlab to a PostgreSQL database. ...
  2. Speeding up Matlab-JDBC SQL queries – Fetching SQL ResultSet data from JDBC into Matlab can be made significantly faster. ...
  3. Matlab toolstrip – part 5 (icons) – Icons can be specified in various ways for toolstrip controls and the app window itself. ...
  4. Sending HTML emails from Matlab – Matlab's sendmail only sends simple text messages by default; a simple hack can cause it to send HTML-formatted messages. ...
  5. Types of undocumented Matlab aspects – This article lists the different types of undocumented/unsupported/hidden aspects in Matlab...
  6. Converting Java vectors to Matlab arrays – Converting Java vectors to Matlab arrays is pretty simple - this article explains how....
Database Java JDBC Performance
Print Print
« Previous
Next »
5 Responses
  1. Andrea Carignano December 12, 2019 at 10:12 Reply

    Thank you very much!
    I am using the first method of this article: sqlite4java.
    I have just a little problem. After every operation on database, I get some red messages in the command line like this:

    dic 12, 2019 8:56:09 AM com.almworks.sqlite4java.Internal log

    INFORMAZIONI: [sqlite] DB[2]: opened

    how can I turn off these messages?

    • Yair Altman December 12, 2019 at 10:21 Reply

      Andrea – sqlite4java is an open-source project (https://code.google.com/archive/p/sqlite4java). You can modify its code and/or submit an issue on that project’s repo (https://bitbucket.org/almworks/sqlite4java/issues).

  2. Philip Howard January 29, 2020 at 16:16 Reply

    Yair,

    Thank you for your post. Is it possible to use Matlab functions inside the SQL query for method using sqlite-jdbc? I see here that you can create custom callback functions in Java: https://stackoverflow.com/questions/3192443/writing-user-defined-sql-functions-for-sqlite-using-java-or-groovy. And Matlab let’s call it’s functions from Java: https://www.mathworks.com/help/matlab/matlab_external/execute-matlab-functions-from-java.html. So my idea was to try and call a Matlab function inside the custom Java callback function. However, I’m not sure how to interact with the JDBC via Matlab besides the standard SQL queries. Any advice would be appreciated!

  3. Alexander Eder January 30, 2023 at 16:03 Reply

    Unfortunately Matlab stopped shipping sqlite4java starting with R2021(b?)

    • Yair Altman February 5, 2023 at 11:43 Reply

      @Alexander – this is correct. Matlab stopped including sqlite4java in R2021b (it was still included in 21a). You can download the open-source sqlite4java project from https://code.google.com/archive/p/sqlite4java or https://bitbucket.org/almworks/sqlite4java/src
      Note that sqlite4java is not actively maintained: the last commit was in 2019, and the downloaded binaries use a rather outdated version of the sqlite database (3.8.7).

      When using sqlite in Matlab I usually prefer using the mksqlite utility (http://mksqlite.sourceforge.net), which is actively maintained and works [extremely well] with the latest sqlite versions.

Leave a Reply
HTML tags such as <b> or <i> are accepted.
Wrap code fragments inside <pre lang="matlab"> tags, like this:
<pre lang="matlab">
a = magic(3);
disp(sum(a))
</pre>
I reserve the right to edit/delete comments (read the site policies).
Not all comments will be answered. You can always email me (altmany at gmail) for private consulting.

Click here to cancel reply.

Useful links
  •  Email Yair Altman
  •  Subscribe to new posts (feed)
  •  Subscribe to new posts (reader)
  •  Subscribe to comments (feed)
 
Accelerating MATLAB Performance book
Recent Posts

Speeding-up builtin Matlab functions – part 3

Improving graphics interactivity

Interesting Matlab puzzle – analysis

Interesting Matlab puzzle

Undocumented plot marker types

Matlab toolstrip – part 9 (popup figures)

Matlab toolstrip – part 8 (galleries)

Matlab toolstrip – part 7 (selection controls)

Matlab toolstrip – part 6 (complex controls)

Matlab toolstrip – part 5 (icons)

Matlab toolstrip – part 4 (control customization)

Reverting axes controls in figure toolbar

Matlab toolstrip – part 3 (basic customization)

Matlab toolstrip – part 2 (ToolGroup App)

Matlab toolstrip – part 1

Categories
  • Desktop (45)
  • Figure window (59)
  • Guest bloggers (65)
  • GUI (165)
  • Handle graphics (84)
  • Hidden property (42)
  • Icons (15)
  • Java (174)
  • Listeners (22)
  • Memory (16)
  • Mex (13)
  • Presumed future risk (394)
    • High risk of breaking in future versions (100)
    • Low risk of breaking in future versions (160)
    • Medium risk of breaking in future versions (136)
  • Public presentation (6)
  • Semi-documented feature (10)
  • Semi-documented function (35)
  • Stock Matlab function (140)
  • Toolbox (10)
  • UI controls (52)
  • Uncategorized (13)
  • Undocumented feature (217)
  • Undocumented function (37)
Tags
ActiveX (6) AppDesigner (9) Callbacks (31) Compiler (10) Desktop (38) Donn Shull (10) Editor (8) Figure (19) FindJObj (27) GUI (141) GUIDE (8) Handle graphics (78) HG2 (34) Hidden property (51) HTML (26) Icons (9) Internal component (39) Java (178) JavaFrame (20) JIDE (19) JMI (8) Listener (17) Malcolm Lidierth (8) MCOS (11) Memory (13) Menubar (9) Mex (14) Optical illusion (11) Performance (78) Profiler (9) Pure Matlab (187) schema (7) schema.class (8) schema.prop (18) Semi-documented feature (6) Semi-documented function (33) Toolbar (14) Toolstrip (13) uicontrol (37) uifigure (8) UIInspect (12) uitools (20) Undocumented feature (187) Undocumented function (37) Undocumented property (20)
Recent Comments
  • Nicholas (6 days 22 hours ago): Hi Yair, Thanks for the reply. I am on Windows 10. I also forgot to mention that this all works wonderfully out of the editor. It only fails once compiled. So, yes, I have tried a...
  • Nicholas (6 days 22 hours ago): Hi Yair, Thanks for the reply. I am on Windows 10. I also forgot to mention that this all works wonderfully out of the editor. It only fails once compiled. So, yes, I have tried a...
  • Yair Altman (7 days 5 hours ago): Nicholas – yes, I used it in a compiled Windows app using R2022b (no update). You didn’t specify the Matlab code location that threw the error so I can’t help...
  • Nicholas (8 days 2 hours ago): Hi Yair, Have you attempted your displayWebPage utility (or the LightweightHelpPanel in general) within a compiled application? It appears to fail in apps derived from both R2022b...
  • João Neves (11 days 6 hours ago): I am on matlab 2021a, this still works: url = struct(struct(struct(struct(hF ig).Controller).PlatformHost). CEF).URL; but the html document is empty. Is there still a way to do...
  • Yair Altman (14 days 5 hours ago): Perhaps the class() function could assist you. Or maybe just wrap different access methods in a try-catch so that if one method fails you could access the data using another...
  • Jeroen Boschma (14 days 8 hours ago): Never mind, the new UI components have an HTML panel available. Works for me…
  • Alexandre (14 days 9 hours ago): Hi, Is there a way to test if data dictionnatry entry are signal, simulink parameters, variables … I need to access their value, but the access method depends on the data...
  • Nicholas (14 days 23 hours ago): In case anyone is looking for more info on the toolbar: I ran into some problems creating a toolbar with the lightweight panel. Previously, the Browser Panel had an addToolbar...
  • Jeroen Boschma (18 days 6 hours ago): I do not seem to get the scrollbars (horizontal…) working in Matlab 2020b. Snippets of init-code (all based on Yair’s snippets on this site) handles.text_explorer...
  • Yair Altman (46 days 8 hours ago): m_map is a mapping tool, not even created by MathWorks and not part of the basic Matlab system. I have no idea why you think that the customizations to the builtin bar function...
  • chengji chen (46 days 14 hours ago): Hi, I have tried the method, but it didn’t work. I plot figure by m_map toolbox, the xticklabel will add to the yticklabel at the left-down corner, so I want to move down...
  • Yair Altman (54 days 7 hours ago): @Alexander – this is correct. Matlab stopped including sqlite4java in R2021b (it was still included in 21a). You can download the open-source sqlite4java project from...
  • Alexander Eder (60 days 3 hours ago): Unfortunately Matlab stopped shipping sqlite4java starting with R2021(b?)
  • K (66 days 14 hours ago): Is there a way to programmatically manage which figure gets placed where? Let’s say I have 5 figures docked, and I split it into 2 x 1, I want to place 3 specific figures on the...
Contact us
Captcha image for Custom Contact Forms plugin. You must type the numbers shown in the image
Undocumented Matlab © 2009 - Yair Altman
This website and Octahedron Ltd. are not affiliated with The MathWorks Inc.; MATLAB® is a registered trademark of The MathWorks Inc.
Scroll to top