fetch

Import data into MATLAB workspace using SQLite connection

Description

example

results = fetch(conn,sqlquery) returns all rows of data from an SQLite database file immediately after executing the SQL statement sqlquery by using the SQLite connection conn of the MATLAB® interface to SQLite.

example

results = fetch(conn,sqlquery,rowlimit) limits the number of rows for the data import.

Examples

collapse all

Use the MATLAB® interface to SQLite to import all rows of data into MATLAB® from a database table in an SQLite database file. Then, determine the highest unit cost among products in the table.

Create the SQLite connection conn to the existing SQLite database file tutorial.db. The database file contains the table productTable. conn is an sqlite object.

dbfile = 'tutorial.db';
conn = sqlite(dbfile);

Import all the data from productTable. The results output argument contains the imported data as a cell array.

sqlquery = 'SELECT * FROM productTable';
results = fetch(conn,sqlquery)
results=15×5 cell
    {[ 9]}    {[125970]}    {[1003]}    {[13]}    {'Victorian Doll' }
    {[ 8]}    {[212569]}    {[1001]}    {[ 5]}    {'Train Set'      }
    {[ 7]}    {[389123]}    {[1007]}    {[16]}    {'Engine Kit'     }
    {[ 2]}    {[400314]}    {[1002]}    {[ 9]}    {'Painting Set'   }
    {[ 4]}    {[400339]}    {[1008]}    {[21]}    {'Space Cruiser'  }
    {[ 1]}    {[400345]}    {[1001]}    {[14]}    {'Building Blocks'}
    {[ 5]}    {[400455]}    {[1005]}    {[ 3]}    {'Tin Soldier'    }
    {[ 6]}    {[400876]}    {[1004]}    {[ 8]}    {'Sail Boat'      }
    {[ 3]}    {[400999]}    {[1009]}    {[17]}    {'Slinky'         }
    {[10]}    {[888652]}    {[1006]}    {[24]}    {'Teddy Bear'     }
    {[11]}    {[408143]}    {[1004]}    {[11]}    {'Convertible'    }
    {[12]}    {[210456]}    {[1010]}    {[22]}    {'Hugsy'          }
    {[13]}    {[470816]}    {[1012]}    {[16]}    {'Pancakes'       }
    {[14]}    {[510099]}    {[1011]}    {[19]}    {'Shawl'          }
    {[15]}    {[899752]}    {[1011]}    {[20]}    {'Snacks'         }

Determine the highest unit cost of the products. Find the number of products by using the size function. Access unit cost data by looping through the fourth column of the cell array. data is a vector that contains numeric unit costs. Find the maximum unit cost.

rows = size(results);
for i = 1:rows
    data(i) = results{i,4};
end
max(data)
ans = int64
    24

Close the SQLite connection.

close(conn)

Use the MATLAB® interface to SQLite to import a limited number of rows into MATLAB® from a database table in an SQLite database file. Then, determine the highest unit cost among products in the table.

Create the SQLite connection conn to the existing SQLite database file tutorial.db. The database file contains the table productTable. conn is an sqlite object.

dbfile = 'tutorial.db';

conn = sqlite(dbfile);

Import five rows of data from productTable by using the rowlimit argument. results contains five rows of imported data as a cell array.

sqlquery = 'SELECT * FROM productTable';
rowlimit = 5;
results = fetch(conn,sqlquery,rowlimit)
results=5×5 cell
    {[9]}    {[125970]}    {[1003]}    {[13]}    {'Victorian Doll'}
    {[8]}    {[212569]}    {[1001]}    {[ 5]}    {'Train Set'     }
    {[7]}    {[389123]}    {[1007]}    {[16]}    {'Engine Kit'    }
    {[2]}    {[400314]}    {[1002]}    {[ 9]}    {'Painting Set'  }
    {[4]}    {[400339]}    {[1008]}    {[21]}    {'Space Cruiser' }

Determine the highest unit cost for the limited number of products. Access unit cost data by looping through the fourth column of the cell array. data is a vector that contains numeric unit costs. Find the maximum unit cost.

for i = 1:rowlimit
    data(i) = results{i,4};
end
max(data)
ans = int64
    21

Close the SQLite connection.

close(conn)

Input Arguments

collapse all

SQLite database connection, specified as an sqlite object created using the sqlite function.

SQL statement, specified as a character vector or string scalar. The SQL statement can be any valid SQL statement, including nested queries. The SQL statement can be a stored procedure, such as {call sp_name (parm1,parm2,...)}. For stored procedures that return one or more result sets, use fetch function. For procedures that return output arguments, use runstoredprocedure.

For information about the SQL query language, see the SQL Tutorial.

Data Types: char | string

Row limit, specified as a positive numeric scalar that indicates the maximum number of rows of data to import from the database.

If rowlimit is 0, fetch returns all the rows of data.

Data Types: double

Output Arguments

collapse all

Result data, returned as a cell array. The result data contains all rows of data from the executed SQL statement.

The cell array contains only one of these data types: double, int64, or char. If NULLs exist in the result data, fetch returns an error. To avoid these limitations, connect to the SQLite database file using the JDBC driver. For details, see Configuring Driver and Data Source.

Introduced in R2016a