Connections not terminating

[tcoffey]

I need a little help!
I have an application that uses ADO to connect to an Oracle 8.0 database. I open connections and close the connections. So for all practical purposes I should never have more than one connection open to the database.

Here is the strange part : When I check connections on the oracle side (using TOAD or Enterprise Manager) it shows a connection from the computer running the VB application for ever time I opened a connection.

After each close of the connection I am setting the object to "NOTHING."

When I shut down the application all threads are cleared.

Can someone please help understand what is happening? I really can not have 100+ connections to the database for one application!![/color red]

 

[culshaja]

Are you sing the ODBC driver or the OLE DB driver?
You might have a problem with ODBC connection pooling!
Try the same thing using Oracle Object for OLE (OO4O) which is installed as part of the standrad Oracle client software install. See if the problem still persists. If it does raise a support call with Oracle or look at their suppotrt site as this might be a known problem with Oracle rather than ADO.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[tcoffey]

Thanks for the information James.

I have tried both methods the create object and the set as adodb.connection and got the same effect.

I did read somewhere that there is a known leak with ADO/MTS objects and this fix was done in msado15.dll so I check the date on mine and it is is using 2.5 version but the date is older then the one that Microsoft is stating. According to MS the date should be 1/26/2000 with a size of 329KB.

I am going to see if I can get this version of the file on my system and then check the effects.

Question?
I am using ADO2.5 so I really can not understand why this would be an issue, can you help me understand?

Reference number in MSDN Q247757

Thanks again for your assistance.

 

[culshaja]

I would download the comcheck component from the Microsoft Data Access web-site. You could have a corrupt MDAC installation. This has happened to me 4 times. Its a pig of a problem on Windows 2000. You could try downloading MDAC 2.6 and then see if this solves the problem.

Did you try the OO4O route? If you are developing using Oracle then this is by far the better Data Access middleware for Oracle. Its more efficient and has greater power than ADO.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[tcoffey]

James, James, James you are the man!!!

When I upgraded to MDAC 2.6 it all worked. Before I did that I also got it to work with a modification to the connection string that turned off connection pooling.

I may not have been using the 0040 route correctly but it did not work for me. I you would like to send me a sample snipit of code to illustrate the method I know that others, as well as myself, would love to see it.

Thanks again.

Microsoft strikes again!!!!

 

[culshaja]

The following code is a simple runthrough.

Dim OraSession as Object
Dim OraDatabase as Object
Dim objRetrievedData as Object
Dim strInsertSQL as string

'Create a Session
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'open a connection to the database
Set OraDatabase = OraSession.OpenDatabase("database tns_name", "UserName/Password", 0&)

'insert some data into a table
strInsert = "INSERT INTO tblA (colA, colB) VALUES (1,2)"
lngSuccess = OraDatabase.ExecuteSQL(strInsertSQL)

'N.B. the ExecuteSQL command will do INSERT/UPDATE/DELETE commands

'retrieve some data
strSelectSQL = "SELECT colA, colB FROM tblA"
set oraRetrievedData = OraDatabase.CreateDynaset(strSelectSQL, 0&)

The above example covers everything you need to know to get started. The rest can be foound in the help files installed as part of the Oracle Client Connection Tools. No refernce needs to be set in VB and I have found that if you do set a reference to the .tlb, when you try to compile it falls over!!!

James
James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[tcoffey]

This is all good stuff but I have a question.

I like to use async processing when I can. Is there a way to do this with this method?


"The above example covers everything you need to know to get started. The rest can be foound in the help files installed as part of the Oracle Client Connection Tools. No refernce needs to be set in VB and I have found that if you do set a reference to the .tlb, when you try to compile it falls over!!!"


That explains why it did not work for me.


Thomas