c# - 具有多个 SET 的 Sybase "Not enough values for host variables"

Question

当使用多个用于设置局部变量的参数执行查询时，出现此错误:

iAnywhere.Data.SQLAnywhere.SAException (0x80004005): Not enough values for host variables

我在使用 OdbcConnection 或 SAConnection 时遇到同样的错误。

var connection = new SAConnection(connectionString);
connection.Open();
var transaction = connection.BeginTransaction();

string sql = @"
DECLARE @A int
DECLARE @B int
SET @A = ?
SET @B = ?
";

var command = new SACommand(sql, connection, transaction);

var param1 = new SAParameter();
param1.Value = 1;
command.Parameters.Add(param1);

var param2 = new SAParameter();
param2.Value = 2;
command.Parameters.Add(param2);

command.ExecuteNonQuery();

如果只有一个参数，查询执行得很好:

DECLARE @A int
DECLARE @B int
SET @A = ?
SET @B = 42

我在 Sybase Anywhere 12.0.1.3942 服务器上运行它。

更新:经过更多测试后，我注意到当我在同一个查询中有多个参数化语句时会发生这种情况。这也给出了错误:

SELECT ?
SELECT ?

Answer 1

显然有 some obnoxious restrictions在 SA 数据库引擎中:

Host variable references are permitted within batches with the following restrictions:

• only one statement in the batch can refer to host variables
• the statement which uses host variables cannot be preceded by a statement which returns a result set

一个可能的解决方法是在一个语句之前选择所有宿主变量(参数)到局部变量使用它们:

BEGIN
    DECLARE @id INT;
    DECLARE @name NVARCHAR;
    DECLARE @comment NCARCHAR;
    SELECT :id, :name, :comment INTO @id, @name, @comment;
END;