fetchmulti

Import data from SQL queries

Since R2024b

Syntax

queryResults = fetchmulti(conn,sqlquery)

queryResults = fetchmulti(conn,sqlquery,Name=Value)

Description

queryResults = fetchmulti(conn,sqlquery) imports data into MATLAB^® from multiple SQL queries and returns an array of structures with fields Data and Metadata.

queryResults = fetchmulti(conn,sqlquery,Name=Value) specifies one or more name-value arguments. For example, specify DataReturnFormat="cellarray" to import data as a cell array.

Examples

Import Data with Multiple Queries

Create a stored procedure with multiple SQL queries and then use fetchmulti to import the data.

Create a database connection to a Microsoft^® SQL Server^® by using the odbc function. When the database requires authentication, the recommended practice is to store credentials in your MATLAB vault using setSecret instead of including them in your code. To connect to the database, specify the datasource and retrieve your credentials using the getSecret function.

Before R2024a: setSecret and getSecret are not available. Specify username and password using character vectors or strings.

setSecret("usernamesql");
setSecret("passwordsql");
datasource = "sqlserver";
conn = odbc(datasource,getSecret("usernamesql"),getSecret("passwordsql");

Create a stored procedure with multiple queries. In this example, the stored procedure contains queries for Country and SupplierName for a specified city.

CREATE PROCEDURE dbo.getSupplierInfo
(@cityName varchar(20))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

SELECT Country FROM dbo.suppliers WHERE City = @cityName;
SELECT SupplierName FROM dbo.suppliers WHERE City = @cityName
END

Use fetchmulti to execute and import the results of the SQL queries stored in the procedure.

queryResults = fetchmulti(conn,"{CALL getSupplierInfo('New York')}");

queryResults =

1×2 struct array with fields:

Data
Metadata

Display the country Data and Metadata for New York.

queryResults(1).Data
queryResults(1).Metadata

ans =

  3×1 table

         Country  
    _________________

    {'United States'}
    {'United States'}
    {'USA' }


ans =

  1×3 table

            VariableType    FillValue    MissingRows 
            ____________    __________   ____________
 
    Country   {'char'}      {0×0 char}   {0×1 double}

Import Data with Multiple Queries into Cell Array

Create a stored procedure with multiple SQL queries and then use fetchmulti with the DataReturnFormat name-value argument to import the data into a cell array.

Create a database connection to a Microsoft SQL Server by using the odbc function.

setSecret("usernamesql");
setSecret("passwordsql");
datasource = "sqlserver";
conn = odbc(datasource,getSecret("usernamesql"),getSecret("passwordsql");

Create a stored procedure with multiple queries. The stored procedure contains queries for Country and SupplierName for a specified city.

CREATE PROCEDURE dbo.getSupplierInfo
(@cityName varchar(20))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

SELECT Country FROM dbo.suppliers WHERE City = @cityName;
SELECT SupplierName FROM dbo.suppliers WHERE City = @cityName
END

Use fetchmulti with the name-value argument DataReturnFormat set to "cellarray" to execute and import the results of the SQL queries stored in the procedure into a cell array.

queryResults = fetchmulti(conn,"{CALL getSupplierInfo('New York')}",DataReturnFormat="cellarray");

Display the country Data and SupplierName for New York.

queryResults(1).Data
queryResults(2).Data

ans =

  3×1 cell array

    {'United States'}
    {'United States'}
    {'USA' }


ans =

  3×1 cell array

    {'Wonder Products'}
    {'ACME Toy Company'}
    {Aunt Jemimas' }

Input Arguments

conn — Database connection

connection object

Database connection, specified as an ODBC connection object.

sqlquery — SQL query

string scalar | character vector

SQL query, specified as a string scalar or character vector. You can specify the SQL query as a nested query or as a stored procedure.

For information about the SQL query language, see the SQL Tutorial on the W3Schools website.

Example: {CALL getSupplierInfo('New York")}

Data Types: string | char

Name-Value Arguments

Specify optional pairs of arguments as Name1=Value1,...,NameN=ValueN, where Name is the argument name and Value is the corresponding value. Name-value arguments must appear after other arguments, but the order of the pairs does not matter.

Example: queryResults = fetchmulti(conn,sqlquery,DataReturnFormat="structure")

DataReturnFormat — Data return format

"table" (default) | "cellarray" | "numeric" | "structure"

Data return format, specified as one of the following:

• "table"

• "cellarray"

• "numeric"

• "structure"

Use DataReturnFormat to specify the data type of queryResults.

Example: DataReturnFormat="cellarray" imports data as a cell array.

VariableNamingRule — Variable naming rule

"modify" (default) | "preserve"

Variable naming rule, specified as one of the following:

• "modify" — Remove non-ASCII characters from variable names when the fetchmulti function imports data.

• "preserve" — Preserve most variable names when the fetchmulti function imports data.

Example: VariableNamingRule="modify"

Output Arguments

queryResults — SQL query result

table (default) | cell array | structure | numeric matrix

SQL query result, returned as a table, cell array, structure, or numeric matrix. results contains all rows of data from the executed SQL query by default.

When the executed SQL query does not return any rows, queryResults is an empty table.

Version History

Introduced in R2024b