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

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);

Answer 1

[Florian Bidaud]

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');

[Monkey Coder]

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]

can you show the output of writing curs in the commmand window ?

[Monkey Coder]

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]

what if you do [curs1, curs2, curs3] =exec(conn, 'GetData'); ?

[Monkey Coder]

it throws Error using database.jdbc.connection/exec Too many output arguments.

[Florian Bidaud]

And with your first command do you get TableA or TableC as as the output ?

[Monkey Coder]

I get output of TableA. Thanks

[Florian Bidaud]

I think the only way to do it like you want is by using this method : https://uk.mathworks.com/matlabcentral/answers/96318-how-can-i-query-a-database-to-extract-data-from-multiple-tables

[Monkey Coder]

i see, it is ok. Thanks for checking.