Trapping MS SQL DB_E_INTEGRITYVIOLATION
Posted: Tue 26 Sep 2006 04:09
I am tasked with converting many Delphi tables to MS SQL. Whenever I find an DB_E_INTEGRITYVIOLATION violation of trying to write an orphan record into the MS SQL database, it blows up with an error message and halts operation (in the IDE and running as an .exe). I posted a message last week that you were kind enough to describe the error checking code, but the problem is that when the execution blows in the TRY section attempting a RECORD.POST it never gets to the EXCEPT statement to 'gently' execute the error resolution routine. Here is the example,
var - add these;
E, i : integer;
The nerveracking part of trying to catch the MSSQL exception is that it doesn't ever move into the exception (EXCEPT) area. When I trace this through the code, the RECORD.POST throws the MSSQL exception in the TRY part, and doesn't drop out to the EXCEPT loop. The 'catch 22' part of all this is that I can't then put the exception code in the TRY part because the error exception objects only have scope in the EXCEPT area. Here's the code showing this phenomina;
try
OutTable.Post;
except
on E: EOLEDBError do
begin
for i := 0 to EOLEDBError(E).ErrorCount - 1 do
if EOLEDBError(E).Errors.ErrorCode = DB_E_INTEGRITYVIOLATION then
begin
ErrTable.insert;
ErrTable.FieldByName('IntKey1').Value := KeyInt;
ErrTable.FieldByName('DateKey').Value := KeyDate;
ErrTable.FieldByName('RecDesc').Value := 'Tx Convert';
ErrTable.FieldByName('StrKey2').Value := 'orphan';
ErrTable.post;
end;
break;
end;
I need to find a way to A: write the record, B: write the error record and C: move on to the next input record. Thank you.
var - add these;
E, i : integer;
The nerveracking part of trying to catch the MSSQL exception is that it doesn't ever move into the exception (EXCEPT) area. When I trace this through the code, the RECORD.POST throws the MSSQL exception in the TRY part, and doesn't drop out to the EXCEPT loop. The 'catch 22' part of all this is that I can't then put the exception code in the TRY part because the error exception objects only have scope in the EXCEPT area. Here's the code showing this phenomina;
try
OutTable.Post;
except
on E: EOLEDBError do
begin
for i := 0 to EOLEDBError(E).ErrorCount - 1 do
if EOLEDBError(E).Errors.ErrorCode = DB_E_INTEGRITYVIOLATION then
begin
ErrTable.insert;
ErrTable.FieldByName('IntKey1').Value := KeyInt;
ErrTable.FieldByName('DateKey').Value := KeyDate;
ErrTable.FieldByName('RecDesc').Value := 'Tx Convert';
ErrTable.FieldByName('StrKey2').Value := 'orphan';
ErrTable.post;
end;
break;
end;
I need to find a way to A: write the record, B: write the error record and C: move on to the next input record. Thank you.