Reconnects

[yartoo]

Hi,

We are using roles to control user access to the database which works great, except when a server disconnect is resolved by the dbExpress driver. That seems to try up to 3 times to reconnect. The problem is that it does not fire the OnConnection event handler, so the roles are not set in the new connection. That results in a "no select access" message and we have to restart the application. We cannot see how to detect the reconnection and, therefore are unable to handle it.

Can you suggest a solution to this?

Cheers

Shaun

[AndreyZ]

Hello,

The TSQLConnection component does not have the OnConnection event handler, please specify the exact event handler you want to be called on connection reestablishment. Also, please specify the code you are using in this event handler.

[yartoo]

AndreyZ,

Sorry to take so long to get back on this. What we are doing now is...

//=================================================================
procedure tDbSupport.OnAfterConnect(Sender: TObject);

var
TheDb : TSQLConnection;

begin
TheDb := TSQLConnection (Sender);
if ((fDbType = dbtMSSQLServer)
and ((not fUseAdminAccount)
and (fMsSQLRoleName <> ''))) then
try
SetApplicationRole (TheDb, fMsSQLRoleName, fMsSQLRolePwd);

gLogEvent (lsLogOnly, etStnLocal, 'Database Role set');
except
on e : Exception do
begin
gLogEvent (lsError, etStnLocal,
Format ('Error setting database role. "%s"',
[e.Message]));
FreeAndNil (TheDb);
raise;
end;
end;
//=================================================================

What that does is set the users role in the connection that we use to control access to the database.

When the reconnect mechanism in the DbExpress driver executes we want it to call this OnAfterConnect event handler again or, better, would be to call a new event handler called OnAfterReconnect. That means you don't break any existing code and we know the event is being called due to a reconnect.

Thanks

[azyk]

The question is not related to the dbExpress driver for SQL Server functionality, since it can't control the TSQLConnection behavior. Please forward the question to the TSQLConnection component developers - Embarcadero, in order for them to change this component behavior.