enesual wrote:
I'm trying to hide exception (which means dup_val_on_index in Oracle) In Oracle usage I use null; to do nothing but i couldn't find what to do in Firebird?
...
begin
insert into mail_address(mail_address, name) values
(:i_mail_address, :i_name);
when sqlcode -2 do nothing....
end
end
Helen Borrie answers:
It is practically the same in Firebird - though of course Oracle's sqlcodes don't apply to Firebird.
For your example, sqlcode -803 encompasses (currently) two types of uniqueness violation, one for unique index violations and one for unique constraint violations (primary key or UNIQUE constraint):
Sqlcode -803 isc code 335544349 Symbol no_dup Message Attempt to store duplicate value (visible to active transactions) in unique index "@1" Sqlcode -803 isc code 335544665 Symbol unique_key_violation Message Violation of PRIMARY or UNIQUE KEY constraint "@1" on table "@2"
The sqlcode -803 will catch either exception:
...
begin
insert into mail_address(mail_address, name) values
(:i_mail_address, :i_name);
when sqlcode -803 do
begin
/* swallow any uniqueness violation */
end
end
...
However, the sqlcode is sometimes too blunt an instrument. You have the option to use the gdscode symbol directly instead to catch just one exception, leaving constraint protection to force an exception on the other one.
...
begin
insert into mail_address(mail_address, name) values
(:i_mail_address, :i_name);
when gdscode no_dup do
begin
/* swallow the exception */
end
end
...
Docs enumerating the error codes are available from the documentation index.