cursor.fetch - Import data into MATLAB workspace from cursor object created by `exec`

Alternatives

Retrieve data using Visual Query Builder. For more information, see Using Visual Query Builder.

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, 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 do not specify a RowLimit, fetch imports all remaining rows of data.

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.

Tips

This page documents fetch for a cursor object. For more information 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.

Examples

Example 1 — Import All Rows of Data

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

curs = fetch(curs)
curs =
    Attributes: []
          Data: {91x1 cell}
DatabaseObject: [1x1 database]
      RowLimit: 0
      SQLQuery: 'select country from customers'
       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]

Display data in curs.Data. Due to space constraints, only a portion of the returned data appears here.

curs.Data
ans = 
    'Germany'
    'Mexico'
    'Mexico'
    'UK'
    'Sweden'
    	.
    	.
    	.
    'USA'
    'Finland'
    'Poland'

Example 2 — Import a Specified Number of Rows

Use the RowLimit argument to retrieve only the first three rows of data.

curs = fetch(curs, 3)
curs =
        Attributes: []
              Data: {3x1 cell}
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select country from customers'
           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 = 
    'Germany'
    'Mexico'
    'Mexico'

Rerun the fetch function to return the second three rows of data.
```
curs = fetch(curs, 3);
```

View the data.

curs.Data
ans = 
    'UK'
    'Sweden'
    'Germany'

Example 3 — Import Rows Iteratively Until You Retrieve All Data

Use the RowLimit argument to retrieve the first 10 rows of data, and then rerun the import using a while loop, retrieving 10 rows at a time. Continue until you have retrieved all data, which occurs when curs.Data is 'No Data'.

% Initialize RowLimit (fetchsize)
fetchsize = 10
% Check for more data. Retrieve and display all data.
while ~strcmp(curs.Data, 'No Data')
	curs=fetch(curs,fetchsize);
	curs.Data(:)
end
ans = 
    'No Data'

Example 4 — Import Numeric Data

Import a column of numeric data, using the setdbprefs function to specify numeric as the format for the retrieved data.

conn = database('SampleDB', '', '');
curs=exec(conn, 'select all UnitsInStock from Products');
setdbprefs('DataReturnFormat','numeric')
curs=fetch(curs,3);
curs.Data
ans = 
    39
    17
    13

Example 5 — Import BOOLEAN Data

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

conn = database('SampleDB', '', '');
curs=exec(conn, ['select ProductName, '... 
'Discontinued from Products']);
setdbprefs('DataReturnFormat','cellarray')
curs=fetch(curs,5);
A=curs.Data
A = 
    'Chai'             [0]
    'Chang'            [0]
    'Aniseed Syrup'    [0]
        [1x28 char]    [0]
        [1x22 char]    [1]

View the class of the second column of A:
```
class(A{1,2})
ans =
logical
```

Example 6 — Perform Incremental Fetch

Retrieve data incrementally to avoid Java heap errors:

Data = cell(6400*4, 4); 
rowLimit = 6400; 
startRow = 1; 
endRow = rowLimit; 
conn = database('dname','','password'); 
cur = exec(conn, ['select field1, field2, '...
    'field3, field4 from fname']); 

while true 
    e = fetch(e, rowLimit); 
    if rows(e)==0 
        break 
    end 
     
    dbData = e.Data; 
    numFetchedRows = size(dbData,1); 
    if numFetchedRows < 6400 
        Data(startRow:endRow-...
          (6400-numFetchedRows), :) = dbData; 
    else 
        Data(startRow:endRow, :) = dbData; 
    end 
     
    startRow = startRow + rowLimit; 
    endRow = endRow + rowLimit; 
end