cursor.fetch

Import data into MATLAB Workspace from cursor object created by exec

Alternatives

Retrieve data using Database Explorer (dexplore).

Syntax

curs = fetch(curs,rowLimit)
curs = fetch(curs)

Description

curs = fetch(curs,rowLimit) imports rows of data into the object curs from the open SQL cursor curs, up to the maximum rowLimit.

curs = fetch(curs) imports rows of data from the open SQL cursor curs into the object curs, up to rowLimit. Use the set function to specify rowLimit.

Data is stored in a MATLAB® cell array, table, dataset array, structure, or numeric matrix. It is a best practice to assign the object returned by fetch to the variable curs from the open SQL cursor. This practice results in only one open cursor object, which consumes less memory than multiple open cursor objects.

The next time fetch is run, records are imported starting with the row following the specified rowLimit. If you specify a rowLimit of 0, all the rows in the resultset are fetched.

If 'FetchInBatches' is set to 'yes' in the preferences using setdbprefs, cursor.fetch incrementally fetches the number of rows specified in the 'FetchBatchSize' setting until all the rows returned by the query are fetched, or until rowLimit number of rows are fetched, if rowLimit is specified. Use this method when fetching a large number of rows from the database.

Fetching large amounts of data can result in memory or speed issues. In this case, use rowLimit to limit how much data you retrieve at once.

    Caution:   Leaving cursor and connection objects open or overwriting open objects can result in unexpected behavior. Once you are finished working with these objects, you must close them using close.

Examples

expand all

Import All Rows of Data Using the Native ODBC Interface

Create a connection conn using the native ODBC interface and the dbtoolboxdemo data source.

conn = database.ODBCConnection('dbtoolboxdemo','admin','admin')
conn = 

  ODBCConnection with properties:

      Instance: 'dbtoolboxdemo'
      UserName: 'admin'
       Message: []
        Handle: [1x1 database.internal.ODBCConnectHandle]
       TimeOut: 0
    AutoCommit: 0
          Type: 'ODBCConnection Object'

conn has an empty Message property, which means a successful connection.

Use fetch to import all data into the database.ODBCCursor object, curs, and store the data in a cell array contained in the cursor object field curs.Data.

curs = exec(conn,'select productDescription from productTable');
curs = fetch(curs)
curs = 

  ODBCCursor with properties:

         Data: {10x1 cell}
     RowLimit: 0
     SQLQuery: 'select productDescription from productTable'
      Message: []
         Type: 'ODBCCursor Object'
    Statement: [1x1 database.internal.ODBCStatementHandle]

With the native ODBC interface, curs returns an ODBCCursor Object instead of a Database Cursor Object.

View the contents of the Data element in the cursor object.

curs.Data
ans = 

    'Victorian Doll'
    'Train Set'
    'Engine Kit'
    'Painting Set'
    'Space Cruiser'
    'Building Blocks'
    'Tin Soldier'
    'Sail Boat'
    'Slinky'
    'Teddy Bear'

Close the cursor object.

close(curs)

Import All Rows of Data

Working with the dbtoolboxdemo data source, use exec to select data in column City, for example, in table suppliers. Then, use fetch to import all data from the SQL statement into the cursor object curs, and store the data in a cell array contained in the cursor object field curs.Data.

curs = exec(conn,'select City from suppliers');
curs = fetch(curs)
curs =
 
        Attributes: []
              Data: {10x1 cell}
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select City from suppliers'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 sun.jdbc.odbc.JdbcOdbcResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 sun.jdbc.odbc.JdbcOdbcStatement]
             Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

View the contents of the Data element in the cursor object.

curs.Data
ans = 

    'New York'
    'London'
    'Adelaide'
    'Dublin'
    'Boston'
    'New York'
    'Wellesley'
    'Nashua'
    'London'
    'Belfast'

Close the cursor object.

close(curs)

Import a Specified Number of Rows

Working with the dbtoolboxdemo data source, use the rowLimit argument to retrieve only the first three rows of data.

curs = exec(conn,'select productdescription from producttable');
curs = fetch(curs,3)
curs =
 
        Attributes: []
              Data: {3x1 cell}
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select productdescription from producttable'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 sun.jdbc.odbc.JdbcOdbcResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 sun.jdbc.odbc.JdbcOdbcStatement]
             Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

View the data.

curs.Data
ans = 

    'Victorian Doll'
    'Train Set'
    'Engine Kit'

Rerun the fetch function to return the second three rows of data.

curs = fetch(curs,3);

View the data.

curs.Data
ans = 

    'Painting Set'
    'Space Cruiser'
    'Building Blocks'

Close the cursor object.

close(curs)

Import Rows Iteratively Until You Retrieve All Data

Working with the dbtoolboxdemo data source, use the rowLimit argument to retrieve the first two rows of data, and then rerun the import using a while loop, retrieving two rows at a time. Continue until you have retrieved all data, which occurs when curs.Data is 'No Data'.

curs = exec(conn,'select productdescription from producttable');
% Initialize rowLimit
rowLimit = 2
% Check for more data. Retrieve and display all data.
while ~strcmp(curs.Data,'No Data')
	curs = fetch(curs,rowLimit);
	curs.Data(:)
end
rowLimit =

     2


ans = 

    'Victorian Doll'
    'Train Set'


ans = 

    'Engine Kit'
    'Painting Set'


ans = 

    'Space Cruiser'
    'Building Blocks'


ans = 

    'Tin Soldier'
    'Sail Boat'


ans = 

    'Slinky'
    'Teddy Bear'

ans = 
    'No Data'

Close the cursor object.

close(curs)

Import Numeric Data

Working with the dbtoolboxdemo data source, import a column of numeric data, using the setdbprefs function to specify numeric as the format for the retrieved data.

curs = exec(conn,'select unitCost from productTable');
setdbprefs('DataReturnFormat','numeric')
curs = fetch(curs,3);
curs.Data
   ans =

    13
     5
    16

Close the cursor object.

close(curs)

Import Boolean Data

Import data that includes a BOOLEAN field, using the setdbprefs function to specify cellarray as the format for the retrieved data.

curs = exec(conn,['select InvoiceNumber, '... 
'Paid from Invoice']);
setdbprefs('DataReturnFormat','cellarray')
curs = fetch(curs,5);
A = curs.Data
A = 

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

View the class of the second column of A.

class(A{1,2})
ans =
logical

Close the cursor object.

close(curs)

Perform Incremental Fetch

Working with the dbtoolboxdemo data source, retrieve data incrementally to avoid Java® heap errors. Use cursor.fetch with the setdbprefs properties for FetchInBatches and FetchBatchSize to fetch large data sets.

setdbprefs('FetchInBatches','yes')
setdbprefs('FetchBatchSize','2')
conn = database('dbtoolboxdemo','','');
curs = exec(conn,'select * from productTable');
curs = fetch(curs);
A = curs.Data
A = 

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

cursor.fetch internally retrieves data in increments of two rows at a time. Tune the FetchBatchSize setting depending on the size of the result set you expect to fetch. For example, if you expect about a 100,000 rows in the output, a batch size of 10,000 is a good starting point. The larger the FetchBatchSize value, the fewer trips between Java and MATLAB, and the memory consumption is greater for each batch. The optimal value for FetchBatchSize is decided based on several factors like the size per row being retrieved, the Java heap memory value, the driver's default fetch size, and system architecture, and hence, can vary from site to site.

If 'FetchInBatches' is set to 'yes' and the total number of rows fetched is less than 'FetchBatchSize', MATLAB shows a warning message and then fetches all the rows. The message is Batch size specified was larger than the number of rows fetched.

You can exercise a row limit on the final output even when the FetchInBatches setting is 'yes'.

setdbprefs('FetchInBatches','yes')
setdbprefs('FetchBatchSize','2')
curs = exec(conn,'select * from productTable');
curs = fetch(curs,3);
A = curs.Data
A = 

    [9]    [125970]    [1003]    [13]    'Victorian Doll'
    [8]    [212569]    [1001]    [ 5]    'Train Set'     
    [7]    [389123]    [1007]    [16]    'Engine Kit'

In this case, cursor.fetch retrieves the first three rows of productTable, two rows at a time.

Close the cursor object.

close(curs)

More About

expand all

Tips

  • This page documents fetch for a cursor object. For details about using fetch, cursor.fetch, and database.fetch, see fetch. Unless otherwise noted, fetch in this documentation refers to cursor.fetch, rather than database.fetch.

  • cursor.fetch now supports the native ODBC interface.

Was this topic helpful?