Usage of disconnect type event

[former_SQLA_member1694896]

Hello, all I need to log durations and several other properties of all connections to the DB. I thought I would be able to use event type "disconnect", but it does not have parameter login time. Accessing the properties of the disconnected connection is, obviously, not possible.

Can someone tell me how I can do it?

Thank you Arcady

[VolkerBarth]

Here's a simple snippet, it just lists the according event parameters:

create event EV_Disconnect
type "Disconnect"
handler
begin
   declare nConnId int;
   declare strUser varchar(128);
   declare strDisconnectReason varchar(128);
   set nConnId = event_parameter('ConnectionID');
   set strUser = event_parameter('User');
   set strDisconnectReason = event_parameter('DisconnectReason');

   message 'EV_Disconnect for connection ' || nConnId || ' with user ' || strUser
      || ' and reason ' || strDisconnectReason || '.';

   -- eventually log the user's disconnect time and reason in a user-defined table 

end;

We have used something like that in a combination with a login_procedure (you could also use a Connect event here) to log the connecting and disconnecting of users in the system by storing both in a user-defined table.

[former_SQLA_member1694896]

Would this event be triggered on pooled connection as well? I have a situation where all non pooled connections are registered correctly and the pooled are not registered at all or are registered after longer than a minute, whereas the client succeeded in 5-6 operations between these times.

Arcady

[VolkerBarth]

What kind of connection pooling do you use - SQL Anywhere's builtin facility via connection parameter CPOOL or via API (say, .NET)?

At least for the builtin pooling, according to the docs, disconnect and connect events also are fired for pooled connections (as expected because pooling should be transparent for the client). - I don't know whether this applies to pooling via API.

[former_SQLA_member1694896]

According to the ODBC logs, the pooling parameter is there: //------------------------------------- Attempting to connect using: UID=g4_api;PWD=**;DBN=G4_center6;ServerName=G4_center6;CON=G4_WebService_API(G4_IsSalesChannelConnectedToCustomer);INT=NO;LOG=C:\\Logger\\ODBC\\center6\\odbc.txt;CPOOL='YES(MaxCached=100)';Host=10.10.100.64

//--------------------------------------