How to get more than one SQL table data using MATLAB?

2023 8 16
1 个回答
更新时间：2023 8 16
8 次查看（30 天）

0 个投票

I have stored procedure which returns three tables output. Somehow MATLAB code ony shows first table output. Is there anyway I can retrieve the second and third table output?
SQL Store Procedure:
CREATE PROCEDURE [dbo].[GetData]
@ID int
AS
BEGIN
SELECT Top 10 * FROM TableA
SELECT Top 10 * FROM TableB
SELECT Top 10 * FROM TableC
END
MATLAB code:
setdbprefs('DataReturnFormat','cellarray');
curs = exec(conn, 'GetData');
curs = fetch(curs);
outputdata = curs.Data;
close(curs);

请先登录，再回答此问题。

回答（1 个）

Florian Bidaud
Florian Bidaud 2023-8-16

0 个投票

Hi,
First, exec is not recommended, you should use execute instead.
Plus, for select queries, it is better to use select function.
You can just loop over your commands with select:
letter = {'A' 'B' 'C'}
for i = 1:3
curs{i} = select(['SELECT Top 10 * FROM Table' letter{i}]);
end
But for running this script and get several outputs, you can use executeSQLScript.
results = executeSQLScript(conn, 'GetData.sql');

9 个评论
显示 7更早的评论 隐藏 7更早的评论

Monkey Coder
Monkey Coder 2023-8-16
Hi Florian,
Thanks for your answer.
Unfortunately, executeSQLScript is available since R2019a. We are currently using R2018a so it won't work. Is there any equivalent function in R2018a?
Florian Bidaud
Florian Bidaud 2023-8-16
can you show the output of writing curs in the commmand window ?
Monkey Coder
Monkey Coder 2023-8-16
sure.
curs =
cursor with properties:
Attributes: []
Data: {7×1 cell}
DatabaseObject: [1×1 database.jdbc.connection]
RowLimit: 0
SQLQuery: 'GetData'
Message: []
Type: 'Database Cursor Object'
ResultSet: [1×1 com.microsoft.sqlserver.jdbc.SQLServerResultSet]
Cursor: [1×1 com.mathworks.toolbox.database.sqlExec]
Statement: [1×1 com.microsoft.sqlserver.jdbc.SQLServerStatement]
Fetch: [1×1 com.mathworks.toolbox.database.fetchTheData]
Florian Bidaud
Florian Bidaud 2023-8-16
what if you do [curs1, curs2, curs3] =exec(conn, 'GetData'); ?
Monkey Coder
Monkey Coder 2023-8-16
it throws Error using database.jdbc.connection/exec Too many output arguments.
Florian Bidaud
Florian Bidaud 2023-8-16
And with your first command do you get TableA or TableC as as the output ?
Monkey Coder
Monkey Coder 2023-8-16
i see, it is ok. Thanks for checking.

请先登录，再进行评论。

类别

在 帮助中心 和 File Exchange 中查找有关 Import Data Programmatically 的更多信息

产品

版本

R2018a

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by