Close ADO-connection

[Rick Lipkin]

Rao

YES .. I have come around to your way of thinking and prior advice along those lines .. I worked a good bit yesterday trying to retro-fit my existing code and I came up with this simple solution ..

My existing code looks like this

Code: Select all  Expand view

xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
xSOURCE   := cDEFA+"\Groom.mdb"
xPASSWORD := "xxxxxxxxx"

IF xDATABASE = "A"  // ms access
   xCONNECT := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Jet OLEDB:Database Password='+xPASSWORD
ELSE
   xCONNECT := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD
ENDIF

...
...

oRsUser := TOleAuto():New( "ADODB.Recordset" )
oRsUser:CursorType     := 1        // opendkeyset
oRsUser:CursorLocation := 3        // local cache
oRsUser:LockType       := 3        // lockoportunistic

cSQL := "SELECT * From [Staff] Order by [Lname]"

TRY
  oRsUser:Open(cSQL,xCONNECT )
CATCH oErr
  MsgInfo( "Error in Opening Staff table" )
  RETURN(.F.)
END TRY
 

Retro-fit code

Code: Select all  Expand view

// defined at top of Main()

xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
xSOURCE   := cDEFA+"\Groom.mdb"
xPASSWORD := "xxxxxxxxx"

IF xDATABASE = "A" // access
   xSTRING := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Jet OLEDB:Database Password='+xPASSWORD
ELSE
   xSTRING := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD
ENDIF

xConnect := CREATEOBJECT( "ADODB.Connection" )

TRY
   xConnect:Open( xString )
CATCH oErr
   Saying := "Could not open a Global Connection to Database "+xSource
   MsgInfo( Saying )
   RETURN(.F.)
END TRY

...
...

// using the same Open() code
oRsUser := TOleAuto():New( "ADODB.Recordset" )
oRsUser:CursorType     := 1        // opendkeyset
oRsUser:CursorLocation := 3        // local cache
oRsUser:LockType       := 3        // lockoportunistic

cSQL := "SELECT * From [Staff] Order by [Lname]"

TRY
  oRsUser:Open(cSQL,xCONNECT ) // xConnect is now the connection object
CATCH oErr
  MsgInfo( "Error in Opening Staff table" )
  RETURN(.F.)
END TRY
 

As you can see .. I just re-defined xConnect to be the ( global ) connection object rather than the ( global ) connection string. This offered me the best solution without having to do major surgery and accomplishes using just ONE pre-established connection passed to open each recordset .. thus allowing me to only manage one connection which I can close at anyone time like when I need ( at runtime ) to execute the 'repair and compact' routine .. and at the close of that routine .. I just re-establish the global connection and keep on going.

I must admit, I am a bit concerned about maintaining a single ( application ) connection due to the possibility the workstation could temporally lose its network connection and the app would not be able to re-connect, however I have all my recordsets trapped between Try,Catch and EndTry .. and all the user would see is a connection message and the app would not necessarily crash with a run-time error.... but if the network goes down or 'hick-ups' more than my app would crash as well

I would presume all my legacy applications using Ms Sql would suffer from the same connection problem and curiously why none of the DBA's would have noticed an excessive amount of open connections ? .. and even how Ms Sql manages those as concurrent or not

Rick Lipkin

[James Bott]

Rick,

Ideally, if your app looses a connection, the app should save the recordset locally, then update the database when a connection is re-established. ADO has built-in capabilities to save and restore recordsets.

Persisting Data
http://msdn.microsoft.com/en-us/library/windows/desktop/ms675273(v=vs.85).aspx

I have never used this so I can't offer more info.

Regards,
James

[nageswaragunupudi]

Mr James

I shall soon post an example of how to do it. (We can do with xHarbour but not yet with Harbour). We shall also discuss about disconnected recordsets in that posting.

But Mr Ricks (and many others') problem is what happens if the connection with the server is broken while a user is browsing a recordset? How do we handle the situation? We shall be soon discussing these aspects also.

[Rick Lipkin]

James

I am not too concerned about data as I buffer all my fields with variables and I create my recordset with the local cache option .. I am not really 'detached' but not working from the database or the table ( as I understand it ) .. from my experience, if the workstation crashes it is not (as) catastrophic to the database especially if the database is on a network share or client\server so database corruption ( especially Ms Access ) is not that common.

Buffered variables ( add and edit ) are not written to the table until oRs:Update() so if the box crashes or the network 'hick-ups' .. the variables were never in a committed state .. nothing added or changed, your app may give you a run-time error but so will most of the other applications on that box ..

As far as buffering data ( un-written data ) as a transaction if the network loses connection, is important, don't get me wrong, I would rather have my app crash and let the user re-boot and try the application again than worry about an un-stable workstation and if the buffered transaction is really being captured intact to be written to the table at some future time....

Just one of those things you can blame on a 'disturbance in the force'

Rick Lipkin

[nageswaragunupudi]

Mr Rick

Glad you could switch to single connection object instead of single connection string so fast.

I must admit, I am a bit concerned about maintaining a single ( application ) connection due to the possibility the workstation could temporally lose its network connection and the app would not be able to re-connect, however I have all my recordsets trapped between Try,Catch and EndTry .. and all the user would see is a connection message and the app would not necessarily crash with a run-time error.... but if the network goes down or 'hick-ups' more than my app would crash as well

Even in your earlier method, if network connection is down while the user is using (mostly browsing or editing) a recordset, the situation is the same. In both approaches we have the same issue.

You say you are talking about losing connection when user has closed all recordsets and just doing nothing. Even then it is not an issue. If you fail to open recordset, just close and reopen the connection and try again.

We have better answers. Also please see my reply to Mr James' post. We shall discuss some of these issues in my future postings.
But we can not always avoid crashes when network fails which happens even when we are browsing a DBF residing on the server.

I would presume all my legacy applications using Ms Sql would suffer from the same connection problem

Yes.

curiously why none of the DBA's would have noticed an excessive amount of open connections ?

They should have. When an organisation has both Oracle and sql servers most DBAs concentrate more on oracle server activity and they pay least attention to sql servers.

Earlier I remember you were mentioning about the issue of licences. That would not be an issue. Even in case of user count based licences, the count applies to the number of client-pcs connected at any given point of time, but not to the number active sessions.