fetch

(Not recommended) Import data into MATLAB workspace from database cursor

The fetch function with the cursor object is not recommended. Use the fetch function with the connection object or the select function instead. For details, see Compatibility Considerations.

The scrollable cursor functionality has no replacement.

Syntax

curs = fetch(curs)

curs = fetch(curs,rowlimit)

curs = fetch(___,Name,Value)

Description

curs = fetch(curs) imports all rows of data from an executed SQL query into the Data property of the cursor object. Use the cursor object to investigate imported data and its structure.

Caution:

Leaving cursor and connection objects open or overwriting open objects can result in unexpected behavior. After you finish working with these objects, you must close them using close.

curs = fetch(curs,rowlimit) imports the maximum number of rows of data, as specified in rowlimit, from an executed SQL query.

curs = fetch(___,Name,Value) specifies options using one or more name-value pair arguments in addition to the input arguments in previous syntaxes. For example, curs = fetch(curs,'AbsolutePosition',5); imports data using an absolute position offset in a scrollable cursor, whereas curs = fetch(curs,'RelativePosition',10); imports data using a relative position offset.

Examples

Import All Data Using cursor Object

Import product data from a Microsoft^® SQL Server^® database into MATLAB^® by using the cursor object. Then, determine the highest unit cost among products in the table.

Create an ODBC database connection to a Microsoft SQL Server database with Windows^® authentication. Specify a blank user name and password. The database contains the table productTable.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Check the database connection. If the Message property is empty, the connection is successful.

conn.Message

ans =

     []

Execute the SQL query using the exec function and the database connection. Then, import all the data from productTable.

sqlquery = 'SELECT * FROM productTable';
curs = exec(conn,sqlquery);
curs = fetch(curs)

curs = 

  cursor with properties:

         Data: [15×5 table]
     RowLimit: 0
     SQLQuery: 'SELECT * FROM productTable'
      Message: []
         Type: 'ODBCCursor Object'
    Statement: [1×1 database.internal.ODBCStatementHandle]

For an ODBC connection, the Type property contains ODBCCursor Object. For JDBC connections, this property contains Database Cursor Object.

Display the data in the cursor object property Data.

curs.Data

ans =

  15×5 table

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

     9               1.2597e+05     1003              13          'Victorian Doll'  
     8               2.1257e+05     1001               5          'Train Set'       
     7               3.8912e+05     1007              16          'Engine Kit'      
     2               4.0031e+05     1002               9          'Painting Set'    
     4               4.0034e+05     1008              21          'Space Cruiser'   
     1               4.0035e+05     1001              14          'Building Blocks' 
     5               4.0046e+05     1005               3          'Tin Soldier'     
     6               4.0088e+05     1004               8          'Sail Boat'       
     3                 4.01e+05     1009              17          'Slinky'          
    10               8.8865e+05     1006              24          'Teddy Bear'      
    11               4.0814e+05     1004              11          'Convertible'     
    12               2.1046e+05     1010              22          'Hugsy'           
    13               4.7082e+05     1012              17          'Pancakes'        
    14                5.101e+05     1011              19          'Shawl'           
    15               8.9975e+05     1011              20          'Snacks' 

Determine the highest unit cost in the table.

data = curs.Data;
max(data.unitCost)

ans =

    24

After you finish working with the cursor object, close it. Close the database connection.

close(curs)
close(conn)

Input Arguments

curs — Database cursor

cursor object

Database cursor, specified as a cursor object created using the exec function.

rowlimit — Row limit

numeric scalar

Row limit, specified as a positive numeric scalar that indicates the maximum number of rows of data to import from the database.

If rowlimit is 0, fetch returns all the rows of data.

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 quotes. You can specify several name and value pair arguments in any order as Name1,Value1,...,NameN,ValueN.

Example: curs = fetch(curs,'RelativePosition',10);

'AbsolutePosition' — Absolute position offset

numeric scalar

Absolute position offset, specified as the comma-separated pair consisting of 'AbsolutePosition' and a numeric scalar that indicates 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 a negative number to signify fetching data from the end of the data set. This name-value pair argument is available only when you create a scrollable cursor object using exec.

Example: 'AbsolutePosition',5

Data Types: double

'RelativePosition' — Relative position offset

numeric scalar

Relative position offset, specified as the comma-separated pair consisting of 'RelativePosition' and a numeric scalar that indicates 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 a negative number to signify importing data before the current cursor position in the data set. This name-value pair argument is available only when you create a scrollable cursor object using exec.

Example: 'RelativePosition',10

Data Types: double

Output Arguments

curs — Database cursor

cursor object

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

Tips

• If you have a native ODBC connection that you established using database, then running fetch on the cursor object updates the input cursor object itself. Depending on whether you provide an output argument, the same object gets copied over to the output. Therefore, only one cursor object exists in memory for any of these usages:

  % First use
  curs = fetch(curs)
  % Second use
  fetch(curs)
  % Third use
  curs2 = fetch(curs)

Compatibility Considerations

fetch function with the cursor object is not recommended

Not recommended starting in R2018b

The fetch function with the cursor object is not recommended. Use the fetch function with the connection object instead. Some differences between the workflows might require updates to your code.

There are no plans to remove the fetch function at this time.

Update Code

Use the fetch function with the connection object to import data from a database in one step.

In prior releases, you wrote multiple lines of code to create the cursor object and import data. For example:

curs = exec(conn,sqlquery);
curs = fetch(curs);
results = curs.Data;
close(curs)

Now you can import data in one step using the fetch function.

results = fetch(conn,sqlquery);

You can also import data in one step using the select function.

data = select(conn,selectquery);

The scrollable cursor functionality has no replacement.