Documentation

This is machine translation

Translated by Microsoft
Mouseover text to see original. Click the button below to return to the English verison of the page.

Note: This page has been translated by MathWorks. Please click here
To view all translated materals including this page, select Japan from the country navigator on the bottom of this page.

fetch

Import data into MATLAB workspace from database cursor or from execution of SQL statement

Syntax

curs = fetch(curs)
curs = fetch(curs,rowlimit)
curs = fetch(___,Name,Value)
results = fetch(conn,sqlquery)
results = fetch(conn,sqlquery,fetchbatchsize)
results = fetch(conn,sqlquery,rowlimit)

Description

example

curs = fetch(curs) imports all rows of data from an executed SQL query into the Data property of the cursor object. Use the cursor object to investigate imported data and its structure.

Caution:

Leaving cursor and connection objects open or overwriting open objects can result in unexpected behavior. After you finish working with these objects, you must close them using close.

example

curs = fetch(curs,rowlimit) imports the maximum number of rows of data from an executed SQL query.

example

curs = fetch(___,Name,Value) specifies additional options using name-value pair arguments to specify a scrollable cursor.

You can specify either an absolute or relative position offset. For example, curs = fetch(curs,'AbsolutePosition',5); imports data using an absolute position offset in a scrollable cursor. While curs = fetch(curs,'RelativePosition',10); imports data using a relative position offset.

example

results = fetch(conn,sqlquery) returns all rows of data after executing the SQL statement sqlquery for the connection or sqlite objects. fetch imports data in batches.

When you use the connection object as the input argument instead of the cursor object, running the exec function is unnecessary.

The fetch function imports data from a SQLite database file immediately using a sqlite object of the MATLAB® interface to SQLite.

example

results = fetch(conn,sqlquery,fetchbatchsize) imports all rows of data in batches of a specified number of rows at a time.

example

results = fetch(conn,sqlquery,rowlimit) imports the maximum number of rows from an executed SQL query using a sqlite object of the MATLAB interface to SQLite.

Examples

collapse all

Use the cursor object to import product data from a Microsoft® SQL Server® database into MATLAB®. Then, determine the highest unit cost among products in the table.

Create an ODBC database connection to a Microsoft® SQL Server® database with Windows® authentication. Specify a blank user name and password. The database contains the table productTable.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Check the database connection. If the Message property is empty, then the connection is successful.

conn.Message
ans =

     []

Execute the SQL query using the exec function and the database connection. Then, import all the data from productTable.

sqlquery = 'SELECT * FROM productTable';
curs = exec(conn,sqlquery);
curs = fetch(curs)
curs = 

  cursor with properties:

         Data: [15×5 table]
     RowLimit: 0
     SQLQuery: 'SELECT * FROM productTable'
      Message: []
         Type: 'ODBCCursor Object'
    Statement: [1×1 database.internal.ODBCStatementHandle]

With the native ODBC interface, the Type property of curs contains ODBCCursor Object. For JDBC connections, the Type property contains Database Cursor Object.

Display the data in the cursor object property Data.

curs.Data
ans =

  15×5 table

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

     9               1.2597e+05     1003              13          'Victorian Doll'  
     8               2.1257e+05     1001               5          'Train Set'       
     7               3.8912e+05     1007              16          'Engine Kit'      
     2               4.0031e+05     1002               9          'Painting Set'    
     4               4.0034e+05     1008              21          'Space Cruiser'   
     1               4.0035e+05     1001              14          'Building Blocks' 
     5               4.0046e+05     1005               3          'Tin Soldier'     
     6               4.0088e+05     1004               8          'Sail Boat'       
     3                 4.01e+05     1009              17          'Slinky'          
    10               8.8865e+05     1006              24          'Teddy Bear'      
    11               4.0814e+05     1004              11          'Convertible'     
    12               2.1046e+05     1010              22          'Hugsy'           
    13               4.7082e+05     1012              17          'Pancakes'        
    14                5.101e+05     1011              19          'Shawl'           
    15               8.9975e+05     1011              20          'Snacks'          

Determine the highest unit cost for all products in the table.

data = curs.Data;
max(data.unitCost)
ans =

    24

After you finish working with the cursor object, close it.

close(curs)

Use the cursor object to import a specific number of rows from a Microsoft® SQL Server® database table into MATLAB®. Then, determine the highest unit cost among the retrieved products in the table.

Create an ODBC database connection to a Microsoft® SQL Server® database with Windows® authentication. Specify a blank user name and password. The database contains the table productTable.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Check the database connection. If the Message property is empty, then the connection is successful.

conn.Message
ans =

     []

Execute the SQL query using the exec function and the database connection. Then, use the input argument rowlimit to retrieve only the first two rows of data.

sqlquery = 'SELECT * FROM productTable';
curs = exec(conn,sqlquery);
rowlimit = 2;
curs = fetch(curs,rowlimit)
curs = 

  cursor with properties:

         Data: [2×5 table]
     RowLimit: 0
     SQLQuery: 'SELECT * FROM productTable'
      Message: []
         Type: 'ODBCCursor Object'
    Statement: [1×1 database.internal.ODBCStatementHandle]
     Position: 1

Display data in the cursor object property Data.

curs.Data
ans =

  2×5 table

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

    9                1.2597e+05     1003              13          'Victorian Doll'  
    8                2.1257e+05     1001               5          'Train Set'       

Determine the highest unit cost in the table.

data = curs.Data;
max(data.unitCost)
ans =

    13

After you finish working with the cursor object, close it.

close(curs)

Use a loop with the cursor object to import inventory data from a Microsoft® SQL Server® database table into MATLAB®.

Create an ODBC database connection to a Microsoft® SQL Server® database with Windows® authentication. Specify a blank user name and password.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Currently, the data type for imported data is 'table'. Change the data type to 'cellarray' using the setdbprefs function.

setdbprefs('DataReturnFormat','cellarray')

Check the database connection. If the Message property is empty, then the connection is successful.

conn.Message
ans =

     []

Execute the SQL query using the exec function and the database connection. Specify retrieving two rows from inventoryTable at a time.

sqlquery = 'SELECT inventoryDate FROM inventoryTable';
curs = exec(conn,sqlquery);
rowlimit = 2;

Use a loop to import data using the fetch function

while ~strcmp(curs.Data,'No Data')
	curs = fetch(curs,rowlimit);
	curs.Data(:)
end
ans =

  2×1 cell array

    '2014-09-23'
    '2014-07-08'


ans =

  2×1 cell array

    '2014-05-14'
    '2013-06-08'


ans =

  2×1 cell array

    '2012-09-14'
    '2013-12-25'


ans =

  2×1 cell array

    '2014-08-06'
    '2011-06-18'


ans =

  2×1 cell array

    '2011-02-09'
    '2012-03-14'


ans =

  2×1 cell array

    '2012-09-11'
    '2010-10-29'


ans =

  cell

    '2009-05-24'


ans =

  cell

    'No Data'

Set the data type for imported data back to 'table'.

setdbprefs('DataReturnFormat','table')

After you finish working with the cursor object, close it.

close(curs)

Use a scrollable cursor object to import inventory data from a Microsoft® SQL Server® database table into MATLAB®. Then, determine the highest quantity in the table.

Create an ODBC database connection to a Microsoft® SQL Server® database with Windows® authentication. Specify a blank user name and password. The database contains the table inventoryTable.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Check the database connection. If the Message property is empty, then the connection is successful.

conn.Message
ans =

     []

Select all products from the inventoryTable table and sort them in ascending order by product number. Create a scrollable cursor object.

sqlquery = 'SELECT * FROM inventoryTable ORDER BY productNumber';
curs = exec(conn,sqlquery,'CursorType','scrollable');

Import data in the data set using the absolute position offset 10.

curs = fetch(curs,'AbsolutePosition',10);

Display the imported data.

data = curs.Data
data =

  4×4 table

    productNumber    Quantity    Price    inventoryDate
    _____________    ________    _____    _____________

    10                723        24       '2012-03-14' 
    11                567        11       '2012-09-11' 
    12               1278        22       '2010-10-29' 
    13               1700        17       '2009-05-24' 

After executing fetch, the position of the cursor moves after the data set.

Determine the highest quantity in the table.

max(data.Quantity)
ans =

        1700

After you finish working with the cursor object, close it. Close the database connection.

close(curs)
close(conn)

Use a scrollable cursor object and a row limit to import inventory data from a Microsoft® SQL Server® database table into MATLAB®. Then, determine the highest quantity in the table.

Create an ODBC database connection to a Microsoft® SQL Server® database with Windows® authentication. Specify a blank user name and password. The database contains the table inventoryTable.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Check the database connection. If the Message property is empty, then the connection is successful.

conn.Message
ans =

     []

Select all products from the table inventoryTable and sort them in ascending order by product number. Create a scrollable cursor object.

sqlquery = 'SELECT * FROM inventoryTable ORDER BY productNumber';
curs = exec(conn,sqlquery,'CursorType','scrollable');

Import data for two products in the middle of the data set. Use the row limit 2 to import data for two rows. Use the absolute position offset 3 to import data starting from the third row in the data set.

rowlimit = 2;
curs = fetch(curs,rowlimit,'AbsolutePosition',3);

Display the imported data.

data = curs.Data
data =

  2×4 table

    productNumber    Quantity    Price    inventoryDate
    _____________    ________    _____    _____________

    3                 356        17       '2014-05-14' 
    4                2580        21       '2013-06-08' 

Display the position of the scrollable cursor. The position of the cursor stays at the absolute position offset 3.

curs.Position
ans =

     3

Determine the highest quantity in the table.

max(data.Quantity)
ans =

        2580

After you finish working with the cursor object, close it. Close the database connection.

close(curs)
close(conn)

Use the cursor object to import invoice data as a cell array from a Microsoft® SQL Server® database table into MATLAB®. Then, determine the highest invoice number.

Create an ODBC database connection to a Microsoft® SQL Server® database with Windows® authentication. Specify a blank user name and password. The database contains the table Invoice.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Check the database connection. If the Message property is empty, then the connection is successful.

conn.Message
ans =

     []

Select the invoice number and paid data from the Invoice table using the exec function. The Paid column has data type of Boolean in the database. The cursor object contains the executed SQL query.

sqlquery = 'SELECT InvoiceNumber,Paid FROM Invoice';
curs = exec(conn,sqlquery);

Currently, the data type for imported data is 'table'. Specify the data type 'cellarray' using the setdbprefs function. Import the first five rows of data from the executed SQL query. Display the imported data.

setdbprefs('DataReturnFormat','cellarray')
rowlimit = 5;
curs = fetch(curs,rowlimit);
curs.Data
ans =

  5×2 cell array

    [ 2101]    [0]
    [ 3546]    [1]
    [33116]    [1]
    [34155]    [0]
    [34267]    [1]

Determine the highest invoice number by accessing the cell array and converting the numeric data to a numeric array using the cell2mat function.

invoices = curs.Data(1:5,1);
numinvoices = cell2mat(invoices);
max(numinvoices)
ans =

       34267

View the class of the second column in the imported data.

class(curs.Data{1,2})
ans =

    'logical'

Boolean data imports as a logical data type in MATLAB®.

Set the data type for imported data back to 'table'.

setdbprefs('DataReturnFormat','table')

After you finish working with the cursor object, close it.

close(curs)

Use the connection object to import all product data from a Microsoft® SQL Server® database table into MATLAB®. Then, determine the highest unit cost among products in the table.

Create an ODBC database connection to a Microsoft® SQL Server® database with Windows® authentication. Specify a blank user name and password. The database contains the table productTable.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Check the database connection. If the Message property is empty, then the connection is successful.

conn.Message
ans =

     []

Import all data from productTable using the connection object and SQL query. Display the imported data.

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

  15×5 table

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

     9               1.2597e+05     1003              13          'Victorian Doll'  
     8               2.1257e+05     1001               5          'Train Set'       
     7               3.8912e+05     1007              16          'Engine Kit'      
     2               4.0031e+05     1002               9          'Painting Set'    
     4               4.0034e+05     1008              21          'Space Cruiser'   
     1               4.0035e+05     1001              14          'Building Blocks' 
     5               4.0046e+05     1005               3          'Tin Soldier'     
     6               4.0088e+05     1004               8          'Sail Boat'       
     3                 4.01e+05     1009              17          'Slinky'          
    10               8.8865e+05     1006              24          'Teddy Bear'      
    11               4.0814e+05     1004              11          'Convertible'     
    12               2.1046e+05     1010              22          'Hugsy'           
    13               4.7082e+05     1012              17          'Pancakes'        
    14                5.101e+05     1011              19          'Shawl'           
    15               8.9975e+05     1011              20          'Snacks'          

Determine the highest unit cost for all products in the table.

max(results.unitCost)
ans =

    24

Close the database connection.

close(conn)

Use the connection object to import product data in batches from a Microsoft® SQL Server® database table into MATLAB®. Then, determine the highest unit cost among products in the table.

Create an ODBC database connection to a Microsoft® SQL Server® database with Windows® authentication. Specify a blank user name and password. The database contains the table productTable.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Check the database connection. If the Message property is empty, then the connection is successful.

conn.Message
ans =

     []

Import all data from productTable using the connection object and SQL query in batches of five rows at a time. Display the imported data.

sqlquery = 'SELECT * FROM productTable';
fetchbatchsize = 5;
results = fetch(conn,sqlquery,fetchbatchsize)
results =

  15×5 table

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

     9               1.2597e+05     1003              13          'Victorian Doll'  
     8               2.1257e+05     1001               5          'Train Set'       
     7               3.8912e+05     1007              16          'Engine Kit'      
     2               4.0031e+05     1002               9          'Painting Set'    
     4               4.0034e+05     1008              21          'Space Cruiser'   
     1               4.0035e+05     1001              14          'Building Blocks' 
     5               4.0046e+05     1005               3          'Tin Soldier'     
     6               4.0088e+05     1004               8          'Sail Boat'       
     3                 4.01e+05     1009              17          'Slinky'          
    10               8.8865e+05     1006              24          'Teddy Bear'      
    11               4.0814e+05     1004              11          'Convertible'     
    12               2.1046e+05     1010              22          'Hugsy'           
    13               4.7082e+05     1012              17          'Pancakes'        
    14                5.101e+05     1011              19          'Shawl'           
    15               8.9975e+05     1011              20          'Snacks'          

Determine the highest unit cost for all products in the table.

max(results.unitCost)
ans =

    24

Close the database connection.

close(conn)

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

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

dbfile = 'tutorial.db';

conn = sqlite(dbfile);

Import all 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 =

  5x5 cell array

    {[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

Database cursor, specified as a cursor object created using the exec function.

Database connection, specified as a connection object or sqlite object created using the database or sqlite functions.

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 this 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 rows of data.

Data Types: double

Fetch batch size, specified as a positive numeric scalar that indicates the number of rows of data to batch at a time. Use fetchbatchsize when importing large amounts of data. Retrieving data in batches reduces overall retrieval time.

Data Types: double

Name-Value Pair Arguments

Specify optional comma-separated pairs of Name,Value arguments. Name is the argument name and Value is the corresponding value. Name must appear inside single quotes (' '). You can specify several name and value pair arguments in any order as Name1,Value1,...,NameN,ValueN.

Example: curs = fetch(curs,'RelativePosition',10);

collapse all

Absolute position offset, specified as a numeric scalar that indicates the absolute position offset value. When you specify an absolute position offset value, fetch imports data starting from the cursor position equal to this value regardless of the current cursor location. The scalar can be a positive number to signify fetching data from the start of the data set. Or, the scalar can be a negative number to signify fetching data from the end of the data set. This name-value pair argument is only available when you create a scrollable cursor object using exec. For details, see Using Scrollable Cursors.

Example: 'AbsolutePosition',5

Data Types: double

Relative position offset, specified as a numeric scalar that indicates the relative position offset value. When you specify a relative position offset value, fetch adds the current cursor position value to the relative position offset value. Then, fetch imports data starting from the resulting value. The scalar can be a positive number to signify importing data after the current cursor position in the data set. Or, the scalar can be a negative number to signify importing data before the current cursor position in the data set. This name-value pair argument is only available when you create a scrollable cursor object using exec. For details, see Using Scrollable Cursors.

Example: 'RelativePosition',10

Data Types: double

Output Arguments

collapse all

Database cursor, returned as a cursor object populated with imported data in the Data property. You can specify the output data format in the Data property using the setdbprefs function.

Result data, returned as a cell array, table, dataset array, structure, or numeric matrix as specified by 'DataReturnFormat' in the setdbprefs function. The result data contains all rows of data from the executed SQL statement.

If conn is a SQLite connection, then results must be a cell array. 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.

Tips

  • The order of records in your database does not remain constant. Sort data using the SQL ORDER BY command in your sqlquery statement.

  • If you have a native ODBC connection that you established using database, then running fetch on the cursor object updates the input cursor object itself. Depending on whether you provide an output argument, the same object gets copied over to the output. Thus, there is always only one cursor object created in memory for any of these usages:

    • curs = fetch(curs)

    • fetch(curs)

    • curs2 = fetch(curs)

Alternative Functionality

App

The fetch function imports data using the command line. To import data interactively, use the Database Explorer app.

Introduced before R2006a

Was this topic helpful?