How can I retrieve the output if I execute an INSERT query with an OUTPUT clause with the Database Toolbox?

6 次查看（过去 30 天）
MathWorks Support Team
回答： MathWorks Support Team 2022-2-1
I am working with Microsoft SQL Server (which uses the Transact-SQL dialect of SQL) and I am executing an INSERT statement like the following:
% conn = database(...)\nsqlquery = 'INSERT INTO myTable (price) OUTPUT (INSERTED.ID) VALUES (3.14)';\ncursor = exec(conn, sqlquery)
But whenever I go to get the returned information (from the OUTPUT clause), I see the following:
 
>> cursor.Data\nans =\n 0\n\n>> cursor.Message\nans =\n 'Invalid Cursor: A result set was generated for update.'
How can I get the data that is returned? Even though this isn't a SELECT query, I should be getting data back.

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

采纳的回答

MathWorks Support Team
You should use the 'fetch' command directly with your SQL query, rather than using the 'exec' function. For example:
% conn = database(...)\nsqlquery = 'INSERT INTO myTable (price) OUTPUT (INSERTED.ID) VALUES (3.14)';\nresults = fetch(conn, sqlquery)
After running this code, the "results" variable should contain the information that you expect. Note that the format of "results" depends on the 'DataReturnFormat'. For more information, see the documentation for the 'fetch' function:
https://www.mathworks.com/help/releases/R2021a/database/ug/database.odbc.connection.fetch.html#bt2sx81-4

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Database Operations 的更多信息

标签

产品


版本

R2021a

Community Treasure Hunt

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

Start Hunting!

Translated by