• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

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.
0
goldengaming
Asked:
goldengaming
  • 4
  • 4
1 Solution
 
ThievingSixCommented:
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
 
ThievingSixCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
ThievingSixCommented:
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
 
ThievingSixCommented:

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
 
goldengamingAuthor Commented:
Thanks.. Tried this morning. Only thing I will have to change the TSToredProcs to ADOStoredprocs but that is fine for me...  
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now