MATLAB: Can I specify a starting point to fetch data from a database using the Database Toolbox

Database Toolbox

I would like to specify a row number to the FETCH function to designate where data retrieval should start.

Best Answer

The ability to specify a starting record for the FETCH function is not available in Database Toolbox.

To work around this issue, use the ResultSet property of the cursor object that results from the EXEC call. For example, the following code can move the cursor 100 records into the result set without processing any data:

tmp = curs.ResultSet;
for i = 1:100
    tmp.next;
end

where "curs" is the cursor returned after running an SQL query using the EXEC function.

After you run the above code, the next fetch call starts retrieving at record 101.

Related Solutions

MATLAB: How to determine whether all records in the database have been retrieved using the Database Toolbox

This change has been incorporated into the documentation in Release 14 Service Pack 3 (R14SP3). For previous releases, read below for any additional information:

When all of the records associated with a cursor object have been retrieved, the cursor object's "Data" field is set to 'No Data'. Use this condition to terminate a processing loop after all records of a query have been processed, as illustrated in this example:

function data = dbtest()
% The "test" database has one table (Table1) with one numeric field
conn = database('test','username','password')
cur = exec(conn, 'select * from Table1');
% number of records to retrieve on each FETCH
fetchSize = 3;
i = 1;
cur = fetch(cur,fetchSize);
while ~strcmp(cur.Data, 'No Data')
      disp(['Fetch number ' num2str(i)])
      i = i+1;
      cur.Data{:}
      cur = fetch(cur,fetchSize);
end
close(cur)
close(conn)

MATLAB: Do I receive a NullPointerException when I try to retrieve information from the fetched result set using the Database Toolbox 2.2.1 (R13)

This enhancement has been incorporated in Release 2006b (R2006b). For previous product releases, read below for any possible workarounds:

This is a bug in the Database Toolbox 2.2.1 (R13) in the way that errors are handled when information is requested on a closed result set.

In order to retrieve information about a result set, the result set must be open. Note that upon closing the database connection via CLOSE, the result set, "curs" in this example, is also closed automatically.

Best Answer

Related Solutions

MATLAB: How to determine whether all records in the database have been retrieved using the Database Toolbox

MATLAB: Do I receive a NullPointerException when I try to retrieve information from the fetched result set using the Database Toolbox 2.2.1 (R13)

Related Question