User`s guide

6 Using Database Toolbox Functions
6-52
Fetch Data Incrementally Using the Cursor Object
This example shows how to work with large data sets by retrieving data incrementally to
avoid Java heap errors.
Create the Database Connection
Create a connection conn using the native ODBC interface and the dbtoolboxdemo
data source.
conn = database.ODBCConnection('dbtoolboxdemo','admin','admin');
Retrieve Data in Batches
Use fetch with the setdbprefs properties for FetchInBatches and
FetchBatchSize to fetch large data sets. Select data from the productTable table.
setdbprefs('FetchInBatches’,'yes')
setdbprefs('FetchBatchSize’,'2')
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'
fetch internally retrieves data in increments of two rows at a time. Tune the
FetchBatchSize setting depending on the size of the resultset you expect to fetch. For
example, if you expect about 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 based on factors such as the: