Sybase "Not enough values for host variables" with multiple SETs

Question

When executing a query with more than one parameter used to SET local variables, I get this error:

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

I get the same error using either OdbcConnection or 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();

The query executes fine if there is only one parameter:

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

I'm running this on a Sybase Anywhere 12.0.1.3942 server.

Update: After more testing, I notice it happens when i have more than one parameterized statement in the same query. This also gives the error:

SELECT ?
SELECT ?

Answer 1

There are apparently some obnoxious restrictions in the SA database engine:

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

One possible workaround is selecting all host variables (parameters) into local variables in one statement before using them:

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