从 C# 运行 Sybase SQL 脚本的问题 - 参数太多 [英] Problems with running Sybase SQL Script from C# - too many parameters
问题描述
我在从 Sybase API 中的 AseCommand 对象运行 Sybase SQL 脚本时遇到问题.这是我的脚本的副本，它作为 sql 字符串参数传递到 C# 中
I'm having issues running a Sybase SQL Script from an AseCommand object in the Sybase API. Here are copies of my script, which is passed into the C# as the sql string parameter
SQL 脚本:
DECLARE @dt DATETIME
DECLARE @mx INT
SELECT @dt = getDate()
EXEC DATABASE_NAME..StoredProcedure1
@id_own_grd = 200,
@id_dom_grd = 'TEST VALUE',
@value = @mx OUTPUT
EXEC DATABASE_NAME..StoredProcedure2
@id_level = @mx, @id_level_pt=NULL, @id_obj1 = 58464819,
@id_typ_lvl='TEST', @am_test=20130916, @id_obj1_sub = 12949,
@dt_start_lvl = '9/16/2013', @dt_end_lvl = '9/16/2013',@ct_blah=0,
@id_abs=" ", @id_obj1_trans=0,@am_obj1_vol=NULL, @am_obj3=-0.311095,
@id_obj_test = NULL, @id_obj5_test = NULL, @id_is_valid = '0',
@nm_db_origin='ORIGIN_DB', @id_owner=200
调用以上 SQL 脚本的 C# 代码:
C# code that calls above SQL Script:
public Dictionary<int, Dictionary<string, object>> ExecuteSqlQuery(string sql)
{
lock (this)
{
try
{
using (AseCommand command = new AseCommand(sql, Connection))
using (AseDataReader reader = command.ExecuteReader())
{
Log.Info("Executing Query: [" + sql + "] on " + DbSettings);
command.CommandTimeout = 120;
var results = new Dictionary<int, Dictionary<string, object>>();
var columns = new List<string>();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
}
int row = 0;
while (reader.Read())
{
Dictionary<string, object> rowResults = new Dictionary<string, object>();
results.Add(row, rowResults);
foreach (string item in columns)
{
rowResults.Add(item, reader[item]);
}
row++;
}
Log.Info("Finished Executing Query: [" + sql + "] on " + DbSettings + " in " + timer.TimeTaken());
return results;
}
}
catch (Exception e)
{
Log.Error("An error occured executing query SQL: " + e.Message, e);
throw;
}
}
}
每当我尝试运行上述脚本时，都会抛出并捕获一个 Sybase.Data.AseClient.AseException，其中显示参数数量无效".此 C# 代码适用于不调用存储过程的任何其他代码.我知道 Sybase 有调用存储过程的具体步骤，尤其是输出参数，在此链接中有详细说明:
Whenever I try running the above script, an Sybase.Data.AseClient.AseException is thrown and caught which says "Invalid amount of parameters". This C# code works for any other code that does not call stored procedures. I am aware that Sybase has specific steps for calling a stored procedure, especially with output parameters, detailed in this link:
但是，我的用户希望能够在一个脚本中调用多个存储过程 - 我尚不知道该怎么做.有人有什么想法吗?
However, my users want to be able to call several stored procedures in one script - something I can't figure out how to do yet. Anyone got any ideas?
谢谢，
推荐答案
我和一位同事找到了答案.您必须更改 Sybase 连接字符串中的标志.将此添加到连接字符串
A colleague and I found the answer. You have to change a flag in the Sybase connection string. Add this to the connection string
;Named Parameters=false
这告诉 Ase 客户端不要检查传递给存储过程的每个参数.如果它爆炸了，它完全在呼叫者身上.您还需要为使用 OUTPUT 参数的任何时间设置 NamedParameters = false.
This tells the Ase Client to not check each parameter passed to the stored procedure. If it blows up, its entirely on the caller. You also need to set NamedParameters = false for anytime you are using OUTPUT parameters as well.
这篇关于从 C# 运行 Sybase SQL 脚本的问题 - 参数太多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
