fetch

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

Syntax

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

Description

example

curs = fetch(curs) imports all rows of data into the cursor object curs from the open SQL cursor object curs.

example

curs = fetch(curs,rowlimit) imports rows of data up to the maximum number of rows rowlimit.

example

curs = fetch(curs,Name,Value) imports rows of data using a scrollable cursor.

example

curs = fetch(curs,rowlimit,Name,Value) imports rows of data up to the maximum number of rows rowlimit using a scrollable cursor.

example

results = fetch(conn,sqlquery) executes the SQL statement sqlquery, imports all rows of data in batches for the open database connection conn, and returns the resulting data results.

example

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

Examples

expand all

Import All Data Using the Native ODBC Interface and Cursor Object

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.

Working with the dbtoolboxdemo data source, 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'

After finishing with the cursor object, close it.

close(curs)

Import All Data Using the Cursor Object

Working with the dbtoolboxdemo data source, use exec to select data in column City in the 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'

After finishing with the cursor object, close it.

close(curs)

Import Specified Rows Using the Cursor Object

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'

After finishing with the cursor object, close it.

close(curs)

Import Data Iteratively Using the Cursor Object

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'

After finishing with the cursor object, close it.

close(curs)

Import Data with an Absolute Position Offset Using the Scrollable Cursor

This example assumes you are connecting to a MySQL® database that contains a table called productTable. This table contains 15 records, where each record represents one product.

Connect to the MySQL database using the native ODBC interface. This code assumes you are connecting to a data source named MySQL with user name username and password pwd.

conn = database.ODBCConnection('MySQL','username','pwd');

Select all products from the productTable table and sort them in ascending order by product number. Create a scrollable cursor using the name-value pair argument 'cursorType'.

curs = exec(conn,'select * from productTable order by productNumber',...
            'cursorType','scrollable');

Import the last five products in the data set using the absolute position offset 11.

curs = fetch(curs,'absolutePosition',11);

Display the data for the five products.

curs.Data
ans = 

    [11]    [408143]    [1004]    [     11]    'Convertible'
    [12]    [210456]    [1010]    [     22]    'Hugsy'      
    [13]    [470816]    [1012]    [16.5000]    'Pancakes'   
    [14]    [510099]    [1011]    [     19]    'Shawl'      
    [15]    [899752]    [1011]    [     20]    'Snacks'     

The columns in curs.Data are:

  • Product number

  • Stock number

  • Supplier number

  • Unit cost

  • Product description

After calling fetch, the position of the cursor is located after the data set.

After finishing with the cursor object, close it.

close(curs)

Import Data with a Row Limit Using the Scrollable Cursor

This example assumes you are connecting to a MySQL database that contains a table called productTable. This table contains 15 records, where each record represents one product.

Connect to the MySQL database using the native ODBC interface. This code assumes you are connecting to a data source named MySQL with user name username and password pwd.

conn = database.ODBCConnection('MySQL','username','pwd');

Select all products from the productTable table and sort them in ascending order by product number. Create a scrollable cursor using the name-value pair argument 'cursorType'.

curs = exec(conn,'select * from productTable order by productNumber',...
            'cursorType','scrollable');

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

curs = fetch(curs,2,'absolutePosition',3);

Display the data for the two products.

curs.Data
ans = 

    [3]    [400999]    [1009]    [17]    'Slinky'       
    [4]    [400339]    [1008]    [21]    'Space Cruiser'

The columns in curs.Data are:

  • Product number

  • Stock number

  • Supplier number

  • Unit cost

  • Product description

Display the position of the cursor.

curs.Position
ans =

     3

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

After finishing with the cursor object, close it.

close(curs)

Import Data with Different Formats Using the Cursor Object

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

After finishing with the cursor object, close it.

close(curs)

Import Data Using the Database Connection Object

fetch imports data from the specified SQL statement when you pass a database object, conn, as the first argument. Use this example when using a JDBC/ODBC bridge or a JDBC interface. For the native ODBC interface, use curs as the input argument.

Using the dbtoolboxdemo data source that you access using the database connection object, conn, import the productDescription column from productTable. Set the data return format to 'cellarray' using setdbprefs.

setdbprefs('DataReturnFormat','cellarray')
sqlquery = 'select productdescription from productTable';

results = fetch(conn, sqlquery)
results = 

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

View the size of the cell array into which the results were returned.

size(results)
ans =

    10     1

Close the database connection.

close(conn)

Import Data with fetchbatchsize Using the Database Connection Object

fetch imports data from the specified SQL statement when you pass a database object, conn, as the first argument. Use this example when using a JDBC/ODBC bridge or a JDBC interface. For the native ODBC interface, use curs as the input argument.

Using the dbtoolboxdemo data source that you access using the database connection object, conn, import the productDescription column from the productTable by using the fetchbatchsize argument.

setdbprefs('DataReturnFormat','cellarray')
sqlquery = 'select productdescription from productTable';
fetchbatchsize = 5;

results = fetch(conn,sqlquery,fetchbatchsize);

fetch returns all the data by importing it in batches of five rows at a time.

Close the database connection.

close(conn)

Input Arguments

expand all

curs — Database cursordatabase cursor object

Database cursor, specified as an open SQL database cursor object created using exec.

conn — Database connectionconnection object

Database connection, specified as a database connection object created using database.

sqlquery — SQL statementSQL string

SQL statement, specified as an SQL string to execute.

Data Types: char

rowlimit — Row limitscalar

Row limit, specified as a scalar denoting the number of rows of data to import from the open SQL cursor object, curs.

Data Types: double

fetchbatchsize — Fetch batch sizescalar

Fetch batch size, specified as a scalar denoting 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. If fetchbatchsize is not provided, a default value of 'FetchBatchSize' is used. 'FetchBatchSize' is set using setdbprefs.

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: 'absolutePosition',5

'absolutePosition' — Absolute position offsetscalar

Absolute position offset, specified as a scalar to denote 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 Importing Data Using a Scrollable Cursor.

Data Types: double

'relativePosition' — Relative position offsetscalar

Relative position offset, specified as a scalar to denote 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 Importing Data Using a Scrollable Cursor.

Data Types: double

Output Arguments

expand all

curs — Database cursordatabase cursor object

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

results — Result datacell array | table | dataset | structure | numeric matrix

Result data, returned as a cell array, table, dataset array, structure, or numeric matrix as specified by 'DataReturnFormat' in setdbprefs.

Was this topic helpful?