Documentation

This is machine translation

Translated by Microsoft
Mouse over text to see original. Click the button below to return to the English verison of the page.

fetch

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

Investigate the data and its structure by passing a database cursor object to fetch. Or, import and view data without running exec using the database connection object instead.

Import data from a SQLite database file immediately using a SQLite connection object of the MATLAB® interface to SQLite.

To import data interactively, use the Database Explorer app.

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

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
  • results = fetch(conn,sqlquery,rowlimit)
    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 and imports all rows of data in batches for the open database connection or SQLite connection conn. results contains the resulting data.

example

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

example

results = fetch(conn,sqlquery,rowlimit) imports rows of data up to the maximum number of rows rowlimit using the SQLite connection conn.

Examples

collapse all

Create a connection conn using the native ODBC interface and the dbtoolboxdemo data source.

conn = database.ODBCConnection('dbtoolboxdemo','admin','admin')
conn = 

  connection 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. Store the data in a cell array contained in the cursor object property curs.Data.

curs = exec(conn,'select productDescription from productTable');
curs = fetch(curs)
curs = 

  cursor 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 you finish working with the cursor object, close it.

close(curs)

Working with the dbtoolboxdemo data source, use the rowlimit argument to retrieve only the first three rows of data. The cursor object curs contains the executed query. Import the data from the executed query using the fetch function.

sqlquery = 'select productdescription from producttable';

curs = exec(conn,sqlquery);
curs = fetch(curs,3)
curs =

  cursor with properties:

        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 you finish working with the cursor object, close it.

close(curs)

Working with the dbtoolboxdemo data source, use the rowlimit argument to retrieve the first two rows of data. To retrieve two rows at a time, rerun the import using a while loop. Continue until you have retrieved all the data, which occurs when curs.Data returns 'No Data'. The cursor object curs contains the executed query. Import the data from the executed query using the fetch function.

sqlquery = 'select productdescription from producttable';

curs = exec(conn,sqlquery);

% 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 you finish working with the cursor object, close it.

close(curs)

Connect to the MySQL® database using the native ODBC interface. This code assumes that you are connecting to a data source named MySQL with user name username and password pwd. This database contains a table called productTable. This table contains 15 records, where each record represents one product.

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 you finish working with the cursor object, close it.

close(curs)

Connect to the MySQL database using the native ODBC interface. This code assumes that you are connecting to a data source named MySQL with user name username and password pwd. This database contains a table called productTable. This table contains 15 records, where each record represents one product.

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 you finish working with the cursor object, close it.

close(curs)

Import data that includes a BOOLEAN field. Specify the format cellarray for the retrieved data using the setdbprefs function. The cursor object curs contains the executed query. Import the data from the executed query using the fetch function.

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 you finish working with the cursor object, close it.

close(curs)

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

Working with the dbtoolboxdemo data source, import the productDescription column from the productTable 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)

Create a SQLite connection conn to an existing SQLite database file tutorial.db.

dbfile = 'tutorial.db';

conn = sqlite(dbfile);

Import the productDescription column from the productTable by using the rowlimit argument.

sqlquery = 'select productdescription from productTable';
rowlimit = 5;

results = fetch(conn,sqlquery,rowlimit);

results returns five rows of data.

Close the SQLite connection.

close(conn)

Related Examples

Input Arguments

collapse all

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

Database connection, specified as a database connection object or SQLite connection object created using database or sqlite.

SQL statement, specified as a character vector.

Data Types: char

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

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

Data Types: double

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

collapse all

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

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

collapse all

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 using setdbprefs.

Result data, returned as a cell array, table, dataset array, structure, or numeric matrix as specified by 'DataReturnFormat' in setdbprefs. The result data contains all rows of data from the executed SQL statement.

If conn is a SQLite connection, results is a cell array only. The cell array contains only one of these data types: DOUBLE, INT64, and CHAR. If NULLs exist in the result data, fetch returns an error. To avoid these limitations, connect to the SQLite database file using the JDBC driver. For details, see Configuring a Driver and Data Source.

More About

collapse all

Tips

  • The order of records in your database does not remain constant. Sort data using the SQL ORDER BY command in your sqlquery statement.

  • When working with a JDBC or JDBC/ODBC bridge connection, running fetch on the cursor object returns a new object of type cursor. The cursor object points to the same underlying Java® objects as the input cursor. For best results, overwrite the input cursor object. Overwriting this object means that only one open cursor object exists, which consumes less memory than multiple open cursor objects.

    curs = fetch(curs)
    Then, close this one object. Creating a different variable for the output cursor object creates two objects pointing to the same underlying Java statement and result set objects.

    With a native ODBC connection established using database.ODBCConnection, 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. Thus, there is always only one cursor object created in memory for any of the following usages:

    • curs = fetch(curs)

    • fetch(curs)

    • curs2 = fetch(curs)

Introduced before R2006a

Was this topic helpful?