%% Import Data Using MATLAB® Interface to SQLite
% This example shows how to move data between MATLAB® and the MATLAB® interface to SQLite. Suppose that you have product data that you want to import into MATLAB®. You can load this data quickly into a SQLite database file. You do not need to install a database or driver. For details about the MATLAB® interface to SQLite, see Working with MATLAB Interface to SQLite. For more functionality, connect to the SQLite database file using the JDBC driver. For details, see Configuring Driver and Data Source.
% To access the code for this example, enter edit SQLiteWorkflow.m.
%% Create SQLite Connection
% Create a SQLite connection conn to a new SQLite database file tutorial.db. Specify the file name in the current working folder.

dbfile = fullfile(pwd,'tutorial.db');

conn = sqlite(dbfile,'create');

%
%% Create Tables in SQLite Database File
% Create the tables inventoryTable, suppliers, salesVolume, and productTable using exec. Clear the MATLAB® workspace variables.

createInventoryTable = ['create table inventoryTable ' ...
    '(productNumber NUMERIC, Quantity NUMERIC, ' ...
    'Price NUMERIC, inventoryDate VARCHAR)'];
exec(conn,createInventoryTable)

createSuppliers = ['create table suppliers ' ...
    '(SupplierNumber NUMERIC, SupplierName varchar(50), ' ...
    'City varchar(20), Country varchar(20), ' ...
    'FaxNumber varchar(20))'];
exec(conn,createSuppliers)

createSalesVolume = ['create table salesVolume ' ...
    '(StockNumber NUMERIC, January NUMERIC, ' ...
    'February NUMERIC, March NUMERIC, April NUMERIC, ' ...
    'May NUMERIC, June NUMERIC, July NUMERIC, ' ...
    'August NUMERIC, September NUMERIC, October NUMERIC, ' ...
    'November NUMERIC, December NUMERIC)'];
exec(conn,createSalesVolume)

createProductTable = ['create table productTable ' ...
    '(productNumber NUMERIC, stockNumber NUMERIC, ' ...
    'supplierNumber NUMERIC, unitCost NUMERIC, ' ...
    'productDescription varchar(20))'];
exec(conn,createProductTable)

clear createInventoryTable createSuppliers createSalesVolume ...
    createProductTable

% tutorial.db contains four empty tables.
%
%% Load Data into SQLite Database File
% Load the MAT-file named sqliteworkflowdata.mat. The variables CinvTable, Csuppliers, CsalesVol, and CprodTable contain data for export. Export data into the tables in tutorial.db using insert. Clear the MATLAB® workspace variables.

load('sqliteworkflowdata.mat')

insert(conn,'inventoryTable', ...
    {'productNumber','Quantity','Price','inventoryDate'},CinvTable)

insert(conn,'suppliers', ...
    {'SupplierNumber','SupplierName','City','Country','FaxNumber'}, ...
    Csuppliers)

insert(conn,'salesVolume', ...
    {'StockNumber','January','February','March','April','May','June', ...
    'July','August','September','October','November','December'}, ...
    CsalesVol)

insert(conn,'productTable', ...
    {'productNumber','stockNumber','supplierNumber','unitCost', ...
    'productDescription'},CprodTable)

clear CinvTable Csuppliers CsalesVol CprodTable

% Close the SQLite connection. Clear the MATLAB® workspace variable.
%

close(conn)

clear conn

%
% Create a read-only SQLite connection to tutorial.db.

conn = sqlite('tutorial.db','readonly');

%
%% Import Data into MATLAB®
% Import the product data into the MATLAB® workspace using fetch. Variables inventoryTable_data, suppliers_data, salesVolume_data, and productTable_data contain data from the tables inventoryTable, suppliers, salesVolume, and productTable.

inventoryTable_data = fetch(conn,'SELECT * FROM inventoryTable');

suppliers_data = fetch(conn,'SELECT * FROM suppliers');

salesVolume_data = fetch(conn,'SELECT * FROM salesVolume');

productTable_data = fetch(conn,'SELECT * FROM productTable');

%
% Display the first three rows of data in each table.

inventoryTable_data(1:3,:)

suppliers_data(1:3,:)

salesVolume_data(1:3,:)

productTable_data(1:3,:)

%
%% Close SQLite Connection

close(conn)

%
% Clear the MATLAB® workspace variable.

clear conn

% Copyright 2012 The MathWorks, Inc.