【DM版本】:DM8
【操作系统】:centos
【CPU】:
【问题描述】*:MERGE SQL语句,多个同名参数绑定有问题,insert 多个同名参数绑定正常
【驱动版本】:DmProvider[1.1.0]
表结构:
SQL:
MERGE INTO SYNCTOOL.TABLE_1 A USING
(select count(*) co FROM SYNCTOOL.TABLE_1 WHERE COLUMN_1 =:COLUMN_1)
B ON(B.co<>0)
WHEN MATCHED THEN
UPDATE SET COLUMN_2 =:COLUMN_2
WHERE COLUMN_1 =:COLUMN_1
WHEN NOT MATCHED THEN
INSERT(COLUMN_1, COLUMN_2)
VALUES(:COLUMN_1, :COLUMN_2)
绑定参数:
IDataParameter[] cmdParms = new IDataParameter[2];
cmdParms[0] = new DmParameter(":COLUMN_1", "2");
cmdParms[1] = new DmParameter(":COLUMN_2", "2");
执行时报异常:
Parameter Index Out Of Range
at Dm.DmParameterCollection.CheckIndex(Int32 index)
at Dm.DmCommand.BindParameters(Int32& rowCount, DmDataReader rd, CommandBehavior behavior)
at Dm.DmCommand.do_ExecuteNonQuery()
at Dm.DmCommand.ExecuteNonQuery()
可以插入,例子如下
//测试MERGE插入
cmd.CommandText = "MERGE INTO SYSDBA.TABLE_11 A USING (select count(*) co FROM SYSDBA.TABLE_1 WHERE COLUMN_1 =:COLUMN_1) B ON(B.co<>0) WHEN MATCHED THEN UPDATE SET COLUMN_2 =:COLUMN_2 WHERE COLUMN_1 =:COLUMN_1 WHEN NOT MATCHED THEN INSERT(COLUMN_1, COLUMN_2) VALUES(:COLUMN_1, :COLUMN_2)";
var para1 = new DmParameter(":COLUMN_1", DmDbType.Int64);
cmd.Parameters.Add(para1);
var para2 = new DmParameter(":COLUMN_2", DmDbType.Int64);
cmd.Parameters.Add(para2);
para1.Value = 2;
para2.Value = 2;
cmd.ExecuteNonQuery();
请帮忙确认如下信息:
1:您的数据库版本:select id_code