How can I execute a complex set of SQL commands at once with Database Toolbox?
9 views (last 30 days)
Show older comments
MathWorks Support Team
on 25 Apr 2012
Edited: MathWorks Support Team
on 9 Dec 2015
I am using Database Toolbox to retrieve data from a Microsoft SQL Server. My query is very complex and can not be accomplished with a single SELECT statement. I have written a batch which I can execute from Query Analyzer; however when I use the EXEC command in MATLAB, no data is returned.
As a simple example, the following command
s = 'declare @id as int;set @id = 2;select * from testtbl where id = @id'
e=exec(conn,s)
e.Message
results in the message ERROR: No ResultSet was produced
Accepted Answer
MathWorks Support Team
on 9 Dec 2015
The EXEC command only returns a result set if the first SQL command is SELECT or the name of a stored procedure. Since the above example began with a declaration, no result set was returned.
There are a number of ways to accomplish this task. Some of these options include:
1. Save the batch of SQL commands as a stored procedure in the database.
2. Use one EXEC command to prepare the data and store it into a temporary table. Then retrieve the data from the temporary table with a simple SELECT statement.
3. Use the database's built-in stored procedure for evaluating a string as SQL commands. For example, Microsoft SQL Server has a built-in stored procedure called sp_executesql. This function will execute any string and return the results of the command to Database toolbox. For example, in Microsoft SQL Server semicolons (;) are used to separate statements so you can execute a batch program as follows:
>> s = 'sp_executesql "declare @id as int;set @id = 2;select * from testtbl where id = @id"'
>> e = exec(conn,s)
>> e = fetch(e)
>> e.Data
4. Another possible workaround is to use 'runsqlscript' command. Create a .sql file with list of SQL statements separated by ';'. For example, .sql file can look like below.
/* .sql file begins */
declare @supplierNumber as int;
set @supplierNumber = 1000;
select * from suppliers where supplierNumber = @supplierNumber;
/* .sql file ends */
>> conn = database('test','<username>','<password>');
>> results = runsqlscript(conn,'test.sql')
>> close (results)
>> close(conn)
0 Comments
More Answers (0)
See Also
Categories
Find more on Database Toolbox in Help Center and File Exchange
Products
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!