fetchmulti
Description
imports data into MATLAB® from multiple SQL queries and returns an array of structures with fields
queryResults
= fetchmulti(conn
,sqlquery
)Data
and Metadata
.
specifies one or more name-value arguments. For example, specify
queryResults
= fetchmulti(conn
,sqlquery
,Name=Value
)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 thefetchmulti
function imports data."preserve"
— Preserve most variable names when thefetchmulti
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
MATLAB Command
You clicked a link that corresponds to this MATLAB command:
Run the command by entering it in the MATLAB Command Window. Web browsers do not support MATLAB commands.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list:
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)