Delphi调用SQL Server存储过程时多列唯一索引违例未捕获异常

环境与代码 #

Delphi端调用代码 #

使用Delphi 10.1 Berlin，通过TADOQuery组件调用SQL Server存储过程，代码如下：

try
  DM.OmegaCA_SS.BeginTrans;
  DM.Exec_SQL.Close;
  DM.Exec_SQL.SQL.Clear;
    
  Exec_SQL_Str := 'execute OMEGACA.P_ACC_POL_RULE_INS '
  ... params ... ;
    
  DM.Exec_SQL.SQL.Add(Exec_SQL_Str);
    
  DM.Exec_SQL.ExecSQL;
  DM.OmegaCA_SS.CommitTrans;    
except
  on E:EAdoError do
  begin
    DM.OmegaCA_SS.RollbackTrans;
    Messagedlg_alb(E.Message, 'Error', mtError, [mbYes], 0);
    statusbar1.SimpleText := '';
    Exit;
  end;
    
  on E:EDatabaseError do
  begin
    DM.OmegaCA_SS.RollbackTrans;
    Messagedlg_alb(E.Message, 'Error', mtError, [mbYes], 0);
    statusbar1.SimpleText := '';
    Exit;
  end;
    
  on E:Exception do
  begin
    DM.OmegaCA_SS.RollbackTrans;
    Messagedlg_alb(E.Message, 'Error', mtError, [mbYes], 0);
    statusbar1.SimpleText := '';
    Exit;
  end;
end;

SQL Server端存储过程 #

CREATE PROCEDURE [OMEGACA].[P_ACC_POL_RULE_INS] 
  (@p_policy_id int,
   @p_rule_name nvarchar(100),
   @p_rule_desc nvarchar(1000),
   @p_cond_eval int,
   @p_status_id int)
AS
BEGIN
     INSERT INTO OMEGACA.ACC_POL_RULE (policy_id, 
                                       rule_name, rule_desc, 
                                       cond_eval, status_id)
     VALUES (@p_policy_id,
             @p_rule_name, @p_rule_desc,
             @p_cond_eval, @p_status_id);
END;

多列唯一索引定义 #

CREATE UNIQUE NONCLUSTERED INDEX [ACC_POL_RULE_UN] 
ON [OMEGACA].[ACC_POL_RULE] ([POLICY_ID] ASC, [RULE_NAME] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
             SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
             DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
             ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

问题现象 #

• 输入值违反上述多列唯一索引时，Delphi代码中的异常未被捕获；向非空列插入NULL时也存在同样问题。
• 违反多列主键或单列唯一索引时，异常能被正常捕获。

解决方案 #

1. 修改存储过程，显式抛出致命错误 #

SQL Server对多列唯一索引违反默认返回警告级别错误，ADO不会触发异常。需在存储过程中捕获约束错误并抛出严重级别16的致命错误：

CREATE PROCEDURE [OMEGACA].[P_ACC_POL_RULE_INS] 
  (@p_policy_id int,
   @p_rule_name nvarchar(100),
   @p_rule_desc nvarchar(1000),
   @p_cond_eval int,
   @p_status_id int)
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRY
    INSERT INTO OMEGACA.ACC_POL_RULE (policy_id, rule_name, rule_desc, cond_eval, status_id)
    VALUES (@p_policy_id, @p_rule_name, @p_rule_desc, @p_cond_eval, @p_status_id);
  END TRY
  BEGIN CATCH
    -- 捕获唯一约束/索引重复错误（2601为唯一索引重复，2627为主键/唯一约束重复）
    IF ERROR_NUMBER() IN (2601, 2627)
    BEGIN
      RAISERROR('同一策略下规则名称不能重复', 16, 1);
      RETURN;
    END
    -- 重新抛出其他类型错误
    THROW;
  END CATCH
END;

2. 改用参数化调用存储过程（推荐） #

拼接SQL字符串易引发注入问题，且参数化调用的ADO错误反馈更可靠。修改Delphi代码如下：

try
  DM.OmegaCA_SS.BeginTrans;
  DM.Exec_SQL.Close;
  DM.Exec_SQL.SQL.Clear;
  
  -- 定义参数化存储过程调用语句
  DM.Exec_SQL.SQL.Add('EXEC OMEGACA.P_ACC_POL_RULE_INS :p_policy_id, :p_rule_name, :p_rule_desc, :p_cond_eval, :p_status_id');
  
  -- 绑定参数值
  DM.Exec_SQL.Parameters.ParamByName('p_policy_id').Value := ...;
  DM.Exec_SQL.Parameters.ParamByName('p_rule_name').Value := ...;
  DM.Exec_SQL.Parameters.ParamByName('p_rule_desc').Value := ...;
  DM.Exec_SQL.Parameters.ParamByName('p_cond_eval').Value := ...;
  DM.Exec_SQL.Parameters.ParamByName('p_status_id').Value := ...;
  
  DM.Exec_SQL.ExecSQL;
  DM.OmegaCA_SS.CommitTrans;    
except
  on E: Exception do
  begin
    DM.OmegaCA_SS.RollbackTrans;
    Messagedlg_alb(E.Message, 'Error', mtError, [mbYes], 0);
    statusbar1.SimpleText := '';
    Exit;
  end;
end;

3. 检查ADO连接属性 #

确认TADOConnection的Attributes属性包含xaCommitRetaining和xaAbortRetaining，CursorLocation可根据场景设置为clUseServer或clUseClient，确保ADO能正确接收SQL Server的错误信号。

原理说明 #

SQL Server对主键、单列唯一约束的违反会直接抛出严重级别14以上的错误，ADO会触发Delphi异常；但多列唯一索引违反默认返回低级别警告，ADO不会主动触发异常。通过存储过程的TRY/CATCH捕获错误并重新抛出高严重级别错误，即可让Delphi的异常处理逻辑正常捕获到。

内容的提问来源于stack exchange，提问作者altink