sqlite in MATLAB

It turns out that MATLAB has sqlite builtin

• https://code.google.com/p/sqlite4java/
• http://almworks.com/sqlite4java/javadoc/index.html
• https://www.sqlite.org/c3ref/intro.html

% get or create a new database
db = com.almworks.sqlite4java.SQLiteConnection(java.io.File('sample.db'))
db.open % open database

% create a table called “person” with 2 columns, name and id
db.exec('create table person (id integer, name string)')

% add rows to “person” table
db.exec('insert into person values(1, "leo")')
db.exec('insert into person values(2, "yui")') 
db.dispose % dispose of db handle

% optionally close and reopen database to see it persists
db = com.almworks.sqlite4java.SQLiteConnection(java.io.File('sample.db'))
db.open

% create a prepared statement with ? wildcard
st = db.prepare('select * from person where id>?')
st.bind(1,0) % bind 1st ? wildcard to any number greater than 0

% binding the prepared statment also works for strings
% st = db.prepare('select * from person where name>=?')
% st.bind(1,'') % bind 1st ? wildcard
% note: all string are greater than or equal to ''

% step through matching rows
while st.step
  % returning the data type from the desired column
  st.columnInt(0) % get IDs from column 0
  st.columnString(1) % get name from column 1
end

% disposed of used up statement container
st.dispose
st.isDisposed

% ditto for db connection
db.dispose
db.isDisposed

% output
ans = 1
ans = leo
ans = 2
ans = yui

Although IMO xerial’s jdbc driver (with sqlite included) is much easier

• https://bitbucket.org/xerial/sqlite-jdbc
• http://docs.oracle.com/javase/tutorial/jdbc/index.html
• http://docs.oracle.com/javase/7/docs/technotes/guides/jdbc/

% https://bitbucket.org/xerial/sqlite-jdbc/wiki/Usage 
javaaddpath('C:\Users\mmikofski\Documents\MATLAB\sqlite\sqlite-jdbc-3.8.7.jar')
d = org.sqlite.JDBC
p = java.util.Properties()
c = d.createConnection('jdbc:sqlite:sample.db',p) % named file

% optional connections
% c = d.createConnection('jdbc:sqlite:C:/full/path/to/sample.db',p) % full path
% c = d.createConnection('jdbc:sqlite::memory:',p) % memory db
% c = d.createConnection('jdbc:sqlite:',p) % default
s = c.createStatement() % create a statement

% create a table, insert rows, etc.
% s.executeUpdate('create table person (id integer, name string)');
% s.executeUpdate('insert into person values(1, "leo")');
% s.executeUpdate('insert into person values(2, "yui")');

% execute query, get id and name
rs = s.executeQuery('select * from person')
while rs.next
    rs.getString('id')
    rs.getString('name')
end
c.close % close connection
c.isClosed

% output
ans = 1
ans = leo
ans = 2
ans = yui