To test basic functionality

• Run a program that displays the results of a select query
• Click a button that connects to a database
• Click a button that starts a query
• Using the control panel
  • Stop Firebird
  • Start Firebird
• Click a button that connects to a different database
• Click a different button to run a different query

All the queries used the same IBQuery component.

The original code below worked fine until the server was restarted ... then it would only generate errors. The patched code (with lots of try..except blocks) runs without any errors.

I do not know if better code can be written (I always assume that it can), but as far as I know, it works.

Note: If you have the wrong IBX version, your Delphi program simply crashes and Windows terminates it. You must have a recent version for the following code to work.

procedure TBasic_DataModule.SetDatabase(Database: string);
begin
  if Database = '' then exit;

  if CompareText(IBDatabase_Basic.DatabaseName, Database) <> 0 then begin
    IBDatabase_Basic.Connected    := false ;
    IBDatabase_Basic.DatabaseName := Database;
    IBDatabase_Basic.Connected    := true;
  end;

end;   // SetDatabase


procedure TBasic_DataModule.ExecuteSelectSQL(s: string);
begin
    IBQuery_Basic.Close;

    if IBTransaction_Query_Basic.InTransaction then
       IBTransaction_Query_Basic.Rollback;

    IBQuery_Basic.SQL.Text := s;

      // stops error if the database is not found
    if IBDatabase_Basic.Connected then IBQuery_Basic.Open;

end;   // ExecuteSelectSQL

procedure TBasic_DataModule.SetDatabase(Database: string);
begin
  if Database = '' then exit;

  try
    if CompareText(IBDatabase_Basic.DatabaseName, Database) <> 0 then begin
      IBDatabase_Basic.ForceClose;           // this works after *Lost connection*
   // IBDatabase_Basic.Connected := false ;  // this fails after *Lost connection*

      IBDatabase_Basic.DatabaseName := Database;

    end;

    IBDatabase_Basic.Connected := true; // This fails if the network is still bad
  except
    on E: Exception do begin
      HandleIBErrors(e.Message, nil);
    end;
  end;
end;   // SetDatabase

procedure TBasic_DataModule.ExecuteSelectSQL(s: string);
begin
  try
    IBQuery_Basic.Close; // may generate *lost connection* error
  except
    on E: Exception do begin
      HandleIBErrors(e.Message, IBQuery_Basic);
    end;
  end;

  // by definition, each selectSQL must be a new transaction
  try
    if IBTransaction_Query_Basic.InTransaction then
       IBTransaction_Query_Basic.Rollback;
  except
    on E: Exception do begin
      HandleIBErrors(e.Message, IBQuery_Basic);
    end;
  end;

  try
    IBQuery_Basic.SQL.Text := s;
  except
    on E: Exception do begin
      HandleIBErrors(e.Message, IBQuery_Basic);
      IBQuery_Basic.SQL.Text := s;
    end;
  end;

  try
    IBQuery_Basic.Open;
  except
    on E: Exception do begin
      HandleIBErrors(e.Message, IBQuery_Basic);
      IBQuery_Basic.Open;
    end;
  end;
end;   // ExecuteSelectSQL

// This handles network failures
// *Display_Error* is provided to suppress the error dialog box for web servers

function TBasic_DataModule.Database_Connected(Display_Error: boolean): boolean;
begin
  Result := false; // needed to stop a compiler warning

    // *TestConnected* automatically disconnects if there is a problem
  if IBDatabase_Basic.TestConnected then begin
    Result := true;
  end else begin
    try
      IBDatabase_Basic.Connected := true;
      if IBDatabase_Basic.Connected then begin
        Result := true;
      end;
    except
      Result := false;
      exit;
    end;
  end;

  if (Result = false) and (Display_Error = true) then begin
    // this displays a modal dialog box that stops the application - for test only
    // must not display this with web pages (CGI and ISAPI files)
    Application.MessageBox( PChar('Database not available'
                  + '  ' + Database), 'Database Error', MB_OK);
  end;

end;  // Database_Connected

// This function helps to plug a design problem in the IBX components
// If there is either a temporary or permanent loss of database connection
// this sets *connected* to false (handle = nil) and fixes other problems

function TBasic_DataModule.HandleIBErrors(E_msg: string;
  ds: TIBCustomDataSet): boolean;
begin
  result := false;
  if    (E_msg = 'connection lost to database')
     or (E_msg = 'unavailable database')
     or (E_msg = 'unassigned code')
  then begin
    IBDatabase_Basic.ForceClose;
    try
      IBDatabase_Basic.Connected := true; // may or may not work
    except
      on E: Exception do begin  // eat a possible *Connected := true* exception
      end;
    end;
    result := true;
  end;

  if (    (E_msg = 'Dataset open')
       or (E_msg = 'unassigned code')) // these frequently appear with loss connection
      and (ds <> nil) 
  then begin
    try
      ds.Fields.Clear;
      ds.active := true; // Only way to set FOpen := False - this causes another exception
    except
      on E: Exception do begin  // eat the expected *ds.active := true* exception
      end;
    end;
    result := true;
  end;
end;  // HandleIBClientErrors

• I decided that it would be easier to pass a string instead of the exception object. Which strings were generated and how to handle them was determined by trial and error.

• Passing ds: TIBCustomDataSet makes the code more generic.

• When the IBQuery component generates a "Dataset open" error, the following line

  IBQuery.active := true;
  

  is the only way I know to set

  IBQuery.FOpen := False
  

  In every case, this causes another exception which is captured. (ForceClose does not clear this flag.)

• Without

  IBQuery.Fields.Clear;
  

  the query component can not be reused for a different query. The previous query will still work ... but not a new query.

• I was never able to determine what was causing the "unassigned code" error ... but it appears to occur with each of the other errors.

• If my program has a *real* error and I try to connect to a database that really does not exits ... there is no error. Perhaps "unavailable database" should not be handled.

URL: http:// cpcug.org / user / clemenzi / technical / Databases / Delphi / IBX_LC_Code.html