How to pass input arguments from Database Toolbox R2015a to SQL script?

5 次查看（过去 30 天）
Robert
Robert 2015-6-23
评论： Robert 2015-6-25
Hello,
I don't know the easiest way in which to explain this but I would like to pass a date variable that is generated by Matlab into my SQL script. The SQL script itself declares variables so I have found it very difficult to create some sort of dynamic relation between the two. Let me post the scripts to hopefully better clarify my issue:
Matlab Script is below. The key is the date variable I am creating which is effectively 45 days less today along with the runsqlscript function.
% Set preferences with setdbprefs.Using Table format.
setdbprefs('DataReturnFormat', 'Table');
setdbprefs('NullNumberRead', 'NaN');
setdbprefs('NullStringRead', 'null');
% Make connection to database.
% Using JDBC driver.
connect = database('Business', 'Test', 'Test', 'Vendor', 'MICROSOFT SQL SERVER', 'Server', 'Database', 'PortNumber', 1433, 'AuthType', 'Server');
% Set the Date Variable
Date = datestr(today - 45);
% Read SQL Script RETURNS_BondABS.sql
results = runsqlscript(connect,'RETURNS_BondABS.sql');
returns= results.Data;
The SQL Script 'Returns_BondABS.sql' is rather long so I will post a snippet because the only thing I need to change is the line where @current = ',Date,' (or so I believe). You will see this variable declares quite a few variables but the only one that I need to change is @current. I would like to replace this with the Date variable matlab generates. The ultimate goal is create a loop in matlab on the date variable so as to run this sql script repeatedly for different dates.
DECLARE @current datetime,
@prior_1d datetime,
@prior_eom datetime,
@cyr datetime,
@prior_cy datetime
SELECT
@current = ',Date,'
SET @prior_1d = DATEADD(DAY,-1, @current)
SELECT
XXXX,
XXXX,
....
Let me know if I haven't been clear or if you require any more information. Running the script as is I generated the following error: 'Attempt to reference field of non-structure array' as well as this message in my 'results' cursor object: 'Conversion failed when converting date and/or time from character string.'

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

采纳的回答

Gitesh Nandre
Gitesh Nandre 2015-6-24
SQL script does not accept input arguments. It needs to be modified as an SQL stored procedure to pass input arguments to it.
Input arguments can be passed to stored procedures by using one of the below mentioned approaches depending on the requirement:
1. If there is no need to display the modified table after running the stored procedure, 'runstoredprocedure' can be used with input argument(s)
2. If the modified table has to be displayed on the MATLAB Command Window after running the stored procedure, 'exec' command can be used as described in the documentation at:
http://www.mathworks.com/help/releases/R2015a/database/ug/run-a-stored-procedure-that-returns-data.html

更多回答（0 个）

类别

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

标签

Community Treasure Hunt

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

Start Hunting!

Translated by