mORMot Open Source

mingda

Member

Registered: 2013-01-04

Posts: 121

TSQlDBConnection Pool can enhance to recover breaked connection?

If external db serer down, connection can not recover after the db restart,
test code is below, when db is on, click button1 is ok, then shutdown db, click button1
will exception, this is ok, restart db, click button1, still exception, can enhance the connection pool to check the break connection then clear the pool or recover the connection, thanks!

  TForm3 = class(TForm)
    Memo1: TMemo;
    Memo2: TMemo;
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
  private
    { Private declarations }
    fProps: TSQlDBConnectionProperties;
  public
    { Public declarations }
  end;

var
  Form3: TForm3;

implementation

{$R *.dfm}

procedure TForm3.FormCreate(Sender: TObject);
begin
  fProps := TOleDBMSSQLConnectionProperties.Create('localhost', 'testdb', '', '');
end;

procedure TForm3.FormDestroy(Sender: TObject);
begin
  fProps.Free;
end;

procedure TForm3.Button1Click(Sender: TObject);
begin
  Memo1.Text := UTF8ToString(
    fProps.Execute('select * from test1', []).FetchAllAsJSON(TRUE)
  );
end;

Last edited by mingda (2014-02-25 02:16:56)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: TSQlDBConnection Pool can enhance to recover breaked connection?

I suppose this is this known ticket:
http://synopse.info/fossil/tktview?name=f024266c08

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

Website

Re: TSQlDBConnection Pool can enhance to recover breaked connection?

We discover and fix some additional issues during connection lost.
1) for Oracle we add missing ORA-01012 to e connection lost reason code. See commit [88bf7c3b89]
2) For OleDB we fix setting of a LastErrorMessage - see [88bf7c3b89]

But one problem we solve in very dirty way in our code (I cant't commit this):

For SynDBOracle  LastErrorMessage is defining during execution of NewStatementPrepared. But if connection is lost & statement is in the client-side cache then "prepare" do not raise a error - from OCI POV statement is prepared normally.
Actual error is raised in the ISQLDBStatement.ExecutePrepared, but in this method we can't modify TSQLDBConnection.fErrorMessage (it is private), so in our code we temporary add 

TSQLDBConnection
.....
property LastErrorMessage: RawUTF8 read fErrorMessage write fErrorMessage;

and modify corresponding TSQLDBConnection.LastErrorMessage directly

conn := props.ThreadSafeConnection();
Query := conn.NewStatementPrepared(...);
...
try
...
  Query.ExecutePrepared()
.. 
except
    on E: Exception do begin
      Query := nil;
      conn.LastErrorMessage := StringToUTF8(E.Message);
      raise;
    end;
  end;

How can this be applied to the mORMot?

Last edited by mpv (2016-02-19 15:13:04)