I need to know how to restore connection to the database after a network failure.

I am forcing a network failure by unplugging my network cable and I get the following error. General SQL error ODBC SQL Server Driver [DBNETLIB] ConnectionWrite (send()) General Network error.  I have many BDE stored procs in my application and as soon as 1 is called after a network failure I get the error. I would like to know when and even how to trap it and how to restore the connection to the database. I have a ODBC connection to SQL Server using Delphi 6. I was wondering if anyone can help me. Thanks in advance.
goldengamingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dagan HooverDeveloperCommented:
You get that error right when you unplug the modem or when you try and send something?
0
goldengamingAuthor Commented:
When I do a storedproc1.open . I tried different stuff to see if I was still connected. But in all reality when I start the application I have not coded anything to connect to the database the firsttime. Here is my code

    sp_SearchbyStatsID.Close;
    sp_SearchbyStatsID.Params[1].asinteger := strtoint(edit2.text);
    sp_SearchbyStatsID.open;
    edit1.Text := sp_SearchbyStatsID.FieldByName('FirstName').AsString;

This works fine. If I unplug the cable then plug it back in it gives me the error after doing another OPEN. I need to know how to restore my connection. I guess also to check if I am able to connect as well. I mean as soon as I close the app and bring it back up it works fine. Thanks
0
Dagan HooverDeveloperCommented:
My guess is you can use a ping routine before the OPEN. You can ping the database IP and database port(differs) if it returns available then OPEN. You can also try including Try Except clauses around the code as well.
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

goldengamingAuthor Commented:
OK that is a good solution for finding out if the server is available. What about being able to restore the connectivity to it though. I just have those couple of lines of code. On startup it works. How am I able to reconnect to the database now. I don't have any code that connects to it in the first place.
Thanks
0
Dagan HooverDeveloperCommented:
Then you should do a connection, are you using ADO or BDE?
0
goldengamingAuthor Commented:
I am using BDE. I don't initially create a connection. Can you guide me on how to connect. Cause my StoredProc works without initially connecting. I guess the object does it on start up. I would appricaiate all the help I can get... Thanks
0
Dagan HooverDeveloperCommented:

unit Functions;
 
interface
 
uses DB, ADODB, Variants, ActiveX, ComObj, AdoInt, OleDB, Classes, Controls,
     Forms, DBTables, Windows, Dialogs;
 
function EnumSqlServers(AStrings : TStrings) : boolean;
function GetConnStr(ServerName: String; DatabaseName: String = ''; UserName: String = ''; Password: String = ''; WindowSecurity: Boolean = True): Widestring;
procedure DatabasesOnServer(ServerName: String; Databases : TStrings);
function DatabaseConnect(Query: TQuery; DatabaseName: String; UserName: String = ''; Password: String = ''): Boolean;
 
type
  TSQLConnection = record
    ServerName : widestring;
    DatabaseName : wideString;
    UserName : widestring;
    Password  : widestring;
  end;
 
type
  SQL = record
    Data : String;
  end;
 
implementation
 
var
  SC : TSQLConnection;
 
function EnumSqlServers(AStrings : TStrings) : boolean;
var
  oDmo, oApp, oServers : OleVariant;
  bResult : boolean;
  I : integer;
begin
  AStrings.Clear;
  try
    oDMO := CreateOleObject('SQLDMO.SQLServer');
    oApp := oDMO.Application;
    oServers := oApp.ListAvailableSQLServers;
    try
      AStrings.BeginUpdate;
      For I := 1 To oServers.Count Do
        begin
        AStrings.Add(oServers.Item(i));
      end;
    finally
      AStrings.EndUpdate;
    end;
    bResult := true;
  except
    bResult := false;
  end;
  oServers := Unassigned;
  oApp := Unassigned;
  oDMO := Unassigned;
  Result := bResult;
end;
 
procedure DatabasesOnServer(ServerName: String; Databases : TStrings);
var
  RS : _RecordSet;
begin
  Databases.Clear;
  With TAdoConnection.Create(nil) Do
    try
    ConnectionString := GetConnStr(ServerName);
    LoginPrompt := False;
    try
      Open;
      RS := ConnectionObject.OpenSchema(adSchemaCatalogs, EmptyParam, EmptyParam);
      With RS Do
        begin
        try
          Databases.BeginUpdate;
          While Not Eof Do
            begin
            Databases.Add(VarToStr(Fields['CATALOG_NAME'].Value));
            MoveNext;
          end;
        finally
          Databases.EndUpdate;
        end;
      end;
      Close;
    except
    end;
  finally
    Free;
  end;
end;
 
function GetConnStr(ServerName: String; DatabaseName: String = ''; UserName: String = ''; Password: String = ''; WindowSecurity: Boolean = True): Widestring;
begin
  SC.ServerName := ServerName;
  If DatabaseName <> '' Then
    begin
    SC.DatabaseName := DatabaseName
  end
  Else
    begin
    SC.DatabaseName := '';
  end;
  SC.UserName := UserName;
  SC.Password := Password;
  Result := 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;';
  Result := Result + 'Data Source=' + SC.ServerName + ';';
  If SC.DatabaseName <> '' Then
    begin
    Result := Result + 'Initial Catalog=' + SC.DatabaseName + ';';
  end;
  If WindowSecurity Then
    begin
    Result := Result + 'uid=' + SC.UserName + ';';
    Result := Result + 'pwd=' + SC.Password + ';';
  end
end;
 
function DatabaseConnect(Query: TQuery; DatabaseName: String; UserName: String = ''; Password: String = ''): Boolean;
var
  DBC : TAdoConnection;
begin
  Result := False;
  DBC := TAdoConnection.Create(nil);
  try
    DBC.LoginPrompt := False;
    DBC.ConnectionString := GetConnStr(SC.ServerName,DatabaseName,UserName,Password);
    If (SC.ServerName  = '') Or (SC.DatabaseName = '') Then
      begin
      MessageDlg('Select at least a Server and a Database!', mtWarning, [mbOK],0);
      Exit;
    end;
    try
      DBC.Open;
      DBC.Close;
      Result := True;
      Query.DatabaseName := DatabaseName;
    except
    end;
  finally
    If DBC.Connected Then
      begin
      DBC.Close;
    end;
    DBC.Free;
  end;
end;
 
end.

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
goldengamingAuthor Commented:
Thanks.. Tried this morning. Only thing I will have to change the TSToredProcs to ADOStoredprocs but that is fine for me...  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.