MS SQL is 'bullying' my Delphi routine?

[davidkennedy]

I am using a TRY, EXCEPT loop to convert my old Delphi tables and trying to capture non-conforming (orphan records) MSSQL records and place them into an error file called "ErrTable" (which is an SQL generated table). I am able to trap the error records into my ErrTable and generate my own error message to the user, BUT then the MS SQL engine throws an error message of it's own (describing this record violates the data integrity of the table(s) as the record in question has no parent record to legitimize it.) I am going to be converting thousands of records and can't stop for each error, delete the foul record, and start again.
Is there some type of command I can put in the EXCEPT section that takes the MS SQL error and posts it into the ErrTable table so the program execution can move on?
TRY
OutTable.Post;
EXCEPT
ErrTable.insert;
ErrTable.FieldByName('IntKey1').Value := KeyInt;
ErrTable.FieldByName('DateKey').Value := KeyDate;
ErrTable.FieldByName('RecDesc').Value := 'Tx Convert';
ErrTable.post;
MessageDlg('Cannot write record ' + 'InttoStr(KeyStr)', mtInformation, [mbOk], 0);
InTable.next;
END;

[Jackson]

You can use something like this:

Code: Select all

  try
    // your code
  except
    on E: EOLEDBError do begin
      for i := 0 to EOLEDBError(E).ErrorCount - 1 do
        if EOLEDBError(E).Errors[i].ErrorCode = DB_E_INTEGRITYVIOLATION then begin
          // your code
          break;
        end;
    end;
  end;

[davidkennedy]

Thank you Evgeniv,
Do I use "OLEDB" in the uses clause of my Delphi unit?
What type of variable to I create to catch this "E" instance of on E: (EOLEDBError)?
I tried creating a
var
E : EOLEDBError;
and it wouldn't compile
Are there any other common errors in a data conversion that I should code for besides "DB_E_INTEGRITYVIOLATION" ?
Thank you.

[davidkennedy]

For those following this thread I enclose answers to my last two questions;
uses - add these
OLEDB, OLEDBAccess

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. I've tried this in the IDE and as an .exe and it behaves the same.
Thank you.

[Jackson]

You don't have to declare E variable.
Include OLEDBAccess and OLEDBC units. You don't have to include OLEDB unit.
Try to use OLEDBErrorCode instead of ErrorCode member.
You can use construction like following:

Code: Select all

uses
  OLEDBAccess, OLEDBC;

procedure TForm1.Button1Click(Sender: TObject);
var
  i: integer;
  KeyViolation: boolean;
begin
  MSQuery1.Open;
  while not MSQuery1.Eof do begin
    MSQuery1.Insert;
    MSQuery1.FieldByName('ID').AsInteger := 1;
    try
      MSQuery1.Post;
      MSQuery1.Next;
    except
      on E: EOLEDBError do begin
        KeyViolation := False;
        for i := 0 to EOLEDBError(E).ErrorCount - 1 do
          if EOLEDBError(E).Errors[i].OLEDBErrorCode = DB_E_INTEGRITYVIOLATION then begin
            MSQuery1.Cancel;
            // insert record to DB
            MSQuery1.Next;
            KeyViolation := True;
            break;
          end;
        if not KeyViolation then
          raise;
      end;
    end;
  end;
end;