如何获得对存储过程的直接SQL调用的结果? [英] How to get the results of a direct SQL call to a stored procedure?
问题描述
我正在通过X ++中的直接SQL调用存储过程,但我不知道如何从中获取整数返回值. 0是好的,-1是坏的.
I'm calling a stored procedure via direct SQL from X++, and I can't figure out how to get the integer return value from it. 0 is good, -1 is bad.
// Login to SQL DB
loginProperty = new LoginProperty();
loginProperty.setServer('localhost');
loginProperty.setDatabase('SQL_DB');
odbcConnection = new OdbcConnection(loginProperty);
statement = odbcConnection.createStatement();
/*
@in_customer_id INT
,@status INT
,@dlvmode NVARCHAR(25)
,@dlvmodedesc NVARCHAR(50)
,@tracking_id NVARCHAR(50)
,@note NVARCHAR(MAX)
,@modified SMALLDATETIME = null
,@override_email NVARCHAR(200) = null
*/
sqlStatement = strfmt(' EXEC pr_send_status_email ' +
' %1,' + // CustomerId
' %2,' + // Status
' %3,' + // DlvMode
' %4,' + // DlvMode description
' %5,' + // Tracking #
' %6,' + // Note
' %7' // DateTime
, 160308
, 2
, sqlSystem.sqlLiteral("FD1")
, sqlSystem.sqlLiteral("Fed Ex overnight")
, sqlSystem.sqlLiteral("1ZABCDEF")
, sqlSystem.sqlLiteral("Note Here")
, sqlSystem.sqlLiteral(DateTimeUtil::utcNow()));
sqlStatementExecutePermission = new SqlStatementExecutePermission(sqlStatement);
sqlStatementExecutePermission.assert();
//BP deviation documented
resultSet = statement.executeQuery(sqlStatement);
//info(strfmt("%1", statement.executeUpdate(sqlStatement))); // I Tried this too
CodeAccessPermission::revertAssert();
if (resultSet.next()) // Errors here
info(strfmt("Return: %1", resultSet.getInt(1)));
推荐答案
executeUpdate 返回更新的行数;否则为0,对于不返回任何内容的SQL语句.
The executeUpdate returns an updated row count; otherwise 0 for SQL statements that return nothing.
executeQuery 返回ResultSet类的实例,但是调用存储过程不是一个选择,因此您会违反合同.
The executeQuery returns an instance of the ResultSet class, but calling a stored procedure is not a select, so you break the contract.
不支持您尝试执行的操作.
What you are trying to do is not supported.
您可以将C#用作胶水代码或使用C#类型直接使用 .NET CLR互操作.
You may use C# as glue code or use the C# types directly using .NET CLR Interop.
这篇关于如何获得对存储过程的直接SQL调用的结果?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!