I do use a TADOQuery component to call a stored procedure in SQL Server.

I'm using Delphi 10.1 Berlin and SQL Server 2019 Developer edition.

The code goes like this:

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;

On the SQL Server side, the procedure is as below:

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;

There is no explicit transaction begin or commit, no XACT_ABORT - and I would like to keep it this way (as I do in, ex., Oracle).

In the table ACC_POL_RULE I have an unique index, defined as:

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]

Problem: if the application user enters values that do violate this unique index, the exception I did in Delphi code is NOT caught.

(I also have the same problem when inserting NULL in a non-NUll column)

Remark:

With same (Delphi) error handling, I do get the exception in case of violation of a:

• Multi-column primary key
• Single-column unique index

Question: how can I get my exception when violating the multi-column unique index?

I do use a TADOQuery component to call a stored procedure in SQL Server.

I'm using Delphi 10.1 Berlin and SQL Server 2019 Developer edition.

The code goes like this:

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;

On the SQL Server side, the procedure is as below:

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;

There is no explicit transaction begin or commit, no XACT_ABORT - and I would like to keep it this way (as I do in, ex., Oracle).

In the table ACC_POL_RULE I have an unique index, defined as:

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]

Problem: if the application user enters values that do violate this unique index, the exception I did in Delphi code is NOT caught.

(I also have the same problem when inserting NULL in a non-NUll column)

Remark:

With same (Delphi) error handling, I do get the exception in case of violation of a:

• Multi-column primary key
• Single-column unique index

Question: how can I get my exception when violating the multi-column unique index?

• sql-server
• exception
• delphi
• unique
• tadoquery

Share Improve this question Follow edited Mar 12 at 9:29 GuidoG 12.2k66 gold badges5555 silver badges9797 bronze badges asked Mar 11 at 14:59 altinkaltink 37111 silver badge1313 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 4

How can I get my exception when violating the multi-column unique index ?

A couple of options to ensure the app code gets the SQL error and raises the exception:

1. Add SET NOCOUNT ON as the first statement in the the stored procedure to suppress DONE_IN_PROC (row count messages)

2. Consume all result sets returned by the stored procedure (the TDS exception message is returned after TDS row count messages.