Close ADO-connection

[nageswaragunupudi]

Please try this test program.
I am able to delete the file "soon" after closing the connection.

Code: Select all  Expand view  RUN

#include "fivewin.ch"
#include "adodef.ch"

function Main()

   local oCn
   local cDb := "test3.db"
   
   oCn   := CreateObject( "ADODB.Connection" )
   oCn:CursorLocation := adUseClient
   oCn:ConnectionString := "Driver=SQLite3 ODBC Driver;Database=" + cDb + ";"
   oCn:Open()
   ? oCn:State
   ? File( cdb )
   oCn:Close()
   oCn := nil
   ? file( cdb )
   ? ferase( cdb )
   ? file( cdb )

return nil
 

If the above code is working, why are you not able to erase the file in your program?

We see one variable oCn ( or whatever ) as the only variable referring to the connection object. But in a large application, it is highly likely that references to the connection object are still retained in some vars or object data which we can not see.
Unless all references in the application to this Object go out of scope, our application do not entirely give up the connection.

[James Bott]

Unless all references in the application to this Object go out of scope, our application do not entirely give up the connection.

Then, I assume, as long as you are not using PUBLICs, PRIVATEs, STATICs, and undeclared vars for connection objects, that this would not be a problem? If so, then this would be a good reason to clean up one's code.

I never use PUBLICs, PRIVATEs, or undeclared vars, and rarely use STATICs. This practice has worked well for me.

We could also try the opposite test--trying things that might prevent the database from closing.

Regards,
James

[Enrico Maria Giordano]

James,

Then, I assume, as long as you are not using PUBLICs, PRIVATEs, STATICs, and undeclared vars for connection objects, that this would not be a problem?

Unfortunately, not. Even "detached locals" can keep live reference to an object. As an example:

Code: Select all  Expand view  RUN

REDEFINE BUTTON...;
         ACTION MYFUNC( oRs )

EMG

[nageswaragunupudi]

Even "detached locals" can keep live reference to an object.

Very much true. But it is very rare that our programmers in normal course create a "detached local" pf the recordset.

As an example:

Code:
REDEFINE BUTTON...;
ACTION MYFUNC( oRs )
[/Code]

Please reconsider. This action clause creates a codeblock { || MYFUNC( oRs ) }.
The oRs is not detached. This codeblock always uses the current value of oRs in the calling program.

So there is no detached local in this case to retain a reference to the recordset.

A local (parameter) gets detached when the called program creates a codeblock using the parameter.

Example:

Code: Select all  Expand view  RUN

func callingfunction()

   local oRs, b
   // create oRs
   b := CalledFunc( oRs )
   Eval( b )
   oRs:Close()
   oRs := nil
   Eval( b )
   b := nil  // now the detached local also is released.  ActiveConnection also is closed
return nil

fun CalledFunc( oRs )
return { || MsgInfo( oRs:ActiveConnection:State }
 

Now the oRs get detached in the codeblock b. This detached local is live as long as the codeblock b is live. Even after the oRs in the called program is closed and set to nil, still Eval( b ) shows 1 ( meaining the activeconnection is still open). And when b is released by setting to nil or goes out of scope then the detached local ( referring to the recordset) also is released.

One can say XBrowse creates lots of codeblocks using the oRs.
Actually XBrowse does not create even a single codeblock using oRs received as parameter, but creates all codeblocks using ::oRs. Thereby there is not detached local referring to the recordset.

What I observed ( with xHarbour ) is this:
When oRs is assigned to the data of some Object, even after the Object goes out of scope, the reference to oRs in that Object's data is not released.

Example:
// oRs := Open Recset

o := TSomeClass()
o:oRs := oRs
...
...
later o := nil
still the reference of oRs in o:oRs is not released
This should have been explicitly released by some destructor method of the TSomeClass

Now thats what I have done.
TXBrowse's Destroy method sets ::oRs := nil
TDataRow class is not a control.
so I created DESTRUCTOR method and released oRs reference there.
Now I checked and the references to oRs are released cleanly

[Enrico Maria Giordano]

NageswaraRao,

Please check the sample below (change table and field name). You will see that LDB is automatically deleted only if you remove the BUTTON.

Code: Select all  Expand view  RUN

#include "Fivewin.ch"


#define adOpenForwardOnly 0
#define adOpenKeyset      1
#define adOpenDynamic     2
#define adOpenStatic      3

#define adLockReadOnly        1
#define adLockPessimistic     2
#define adLockOptimistic      3
#define adLockBatchOptimistic 4

#define adUseNone   1
#define adUseServer 2
#define adUseClient 3


FUNCTION MAIN()

    TEST()

    ? "2"

    RETURN NIL


STATIC FUNCTION TEST()

    LOCAL oDlg

    LOCAL oRs := CREATEOBJECT( "ADODB.Recordset" )

    oRs:CursorLocation = adUseClient

    oRs:Open( "SELECT * FROM Clienti ORDER BY Cliente", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=clienti.mdb", adOpenForwardOnly, adLockReadOnly )

    DEFINE DIALOG oDlg

    @ 2, 2 BUTTON "Test";
           ACTION TEST2( oRs )

    ACTIVATE DIALOG oDlg;
             CENTER

    oRs:Close()

    ? "1"

    RETURN NIL


STATIC FUNCTION TEST2( oRs )

    ? oRs:Fields( "Contatto" ):Value

    RETURN NIL

EMG

[Enrico Maria Giordano]

Or if you remove oRs from the TEST2() parameter.

EMG

[nageswaragunupudi]

After oRs:Close()
set
oRs := nil
Then immediately ActiveConnection also is closed

[Enrico Maria Giordano]

NageswaraRao,

After oRs:Close()
set
oRs := nil
Then immediately ActiveConnection also is closed

The point is that oRs can have live references elsewhere in the code. Setting it to NIL doesn't solve the problem, sorry.

EMG

[nageswaragunupudi]

The point is that oRs can have live references elsewhere in the code. Setting it to NIL doesn't solve the problem, sorry.

Mr Enrico
The points I am making are

#1) Once all references to the recordset are gone the active connection also is closed.
#2) ACTION clause does not create detached local. In the example you gave, the active connection was not closed "not because the ACTION codeblock" but because oRs has not still gone out of scope. Your inference from the example is not correct.
Once oRs either goes out of scope or set to nil, the active connection is closed.

I am not disputing your point that detached locals hold on reference to the object, but buttons ACTION clause does not.

[Enrico Maria Giordano]

NageswaraRao,

Mr Enrico
The points I am making are

#1) Once all references to the recordset are gone the active connection also is closed.
#2) ACTION clause does not create detached local. In the example you gave, the active connection was not closed "not because the ACTION codeblock" but because oRs has not still gone out of scope. Your inference from the example is not correct.
Once oRs either goes out of scope or set to nil, the active connection is closed.

Please look carefully at my sample. LDB file is still there even after the second message, when oRs has already gone out of scope.

EMG

[Rick Lipkin]

Enrico and Rao

I tested the oRs:CLose() followed up with oRs := nil and releasing the object did not close the .ldb for me .. however if I inserted oRs:ActiveConnection:Close() .. the .ldb went away.

My example was just a simple case of opening a table .. reading data and writing back a date value to the table and oRs:CLose() .. no browse, no other controls.

Rick Lipkin

[Rick Lipkin]

Enrico and Rao

I tested the oRs:CLose() followed up with oRs := nil and releasing the object did not close the .ldb for me .. however if I inserted oRs:ActiveConnection:Close() .. the .ldb went away.

I need to update this post and let you both know that oRs := nil DOES release the object as well as the connection .. the .ldb does in fact close when you nil out the recordset object!

I went back and looked at my example .. and I had an additional call to a verification table after the above first example.. It does appear that oRs := nil closed the connection.

Rick Lipkin

[Enrico Maria Giordano]

Rick,

I need to update this post and let you both know that oRs := nil DOES release the object as well as the connection .. the .ldb does in fact close when you nil out the recordset object!

I went back and looked at my example .. and I had an additional call to a verification table after the above first example.. It does appear that oRs := nil closed the connection.

Rick Lipkin

I have an application using a browse where setting oRs to NIL does not close LDB.

EMG

[nageswaragunupudi]

Enrico and Rao

I tested the oRs:CLose() followed up with oRs := nil and releasing the object did not close the .ldb for me .. however if I inserted oRs:ActiveConnection:Close() .. the .ldb went away.

I need to update this post and let you both know that oRs := nil DOES release the object as well as the connection .. the .ldb does in fact close when you nil out the recordset object!

I went back and looked at my example .. and I had an additional call to a verification table after the above first example.. It does appear that oRs := nil closed the connection.

Rick Lipkin

Mr Rick
Additional Hint:

If you have used XBrowse to browse the oRs then after closing the browse please do this:

Code: Select all  Expand view  RUN

oBrw:oRs := nil
oRs:Close()
oRs := nil
 

From the next release of FWH (13.08) XBrowse itself releases the reference and you do not have to release oBrw:oRs by setting to NIL in your code.

Actually I have made the modifications after testing. While testing I was continuously monitoring the number of connections remaining open with a timer after execution of each line of code.

[nageswaragunupudi]

Mr Rick

I again advise you to reconsider your practice of opening every recordset with connection string.

When you specify ConnString in the RecSet's open method, each time ADO opens a separate connection. Even though you release the connection when you close the recordset, ther fact is that you keep as many independent connections to the server open as the number of recordsets in use in your application.

This causes unnecessary strain on both the dataserver and the network. Better keep one connection open per one instance of the application. We are discussing about 2-tier (client-server) programming where our application directly connects to the dataserver, be it sql server or oracle or whatever. So for 2-tier applications like those we make, open connection once at the outset and use it for all recordsets and close the connection at the end of application.

There is another well known issue. Opening a connection takes some extratime, which is not necessary to spend every time we open a recordset. Opening recordsets using an already opened connection object is a lot faster.

I heard the concern expressed by some friends that if we keep a connection open all the time, disconnection with the server would crash the application. They think they can prevent this by opening recordset by specifying the conn string. But this is not a solution. This makes things even worse. Instead of keeping only connection open, you are keeping more connections open, because most the time the users are using some recordsets and so many connections are in an open state all the time.

The practice of opening recset with connstring is more in use in webbased programming. There it is state-less programming. Recset is open, data is used to prepare the web-page, close recset (and also connection) and serve the page. There the programmer is saving coding time and lines of code by specifying connstting in the recset open() method. And most important thing is that web development is always 3-tier programming ( dataserver --> appliction server --> application ). Application server handles connection pooling and for the dataserver application server is just one single user. The connection pooling greatly reduces the strain on the server and also avoids the time taken to open new connection.

Webdevelopment practices (3-tier appln) are not appropriate to our (2-tier) desktop applications.