ExamGuru IT/.Net Guru : ODBC Connection Pooling with ADO.NET and Oracle RDB on microsoft.public.dotnet.framework.odbcnet

.NETGURU

ODBC Connection Pooling with ADO.NET and Oracle RDB

Messages

Related Types

This message was discovered on microsoft.public.dotnet.framework.odbcnet.

Thomas Heder

Hi!

I believe I am not getting ODBC connection pooling in ADO.NET to work
correctly. I am connecting to an Oracle RDB database with Oracle RDB ODBC
driver 3.1.0.0.

With a test project in VB6 I start out with this call to enable pooling on
the ODBC level.

SQLSetEnvAttr(0&,SQL_ATTR_CONNECTION_POOLING,SQL_CP_ONE_PER_DRIVER,SQL_IS_IN
TEGER)

This enables connection pooling at driver level (one pool per machine), and
this is what I want, since we have quite a bit of processes active on each
machine. I do an ADO Open() and I see the DBConnection appear in RDB. When
doing Close(), the connection remains in the database for the amount of time
specified in the ODBC manager. So far so good.

Now... ADO.NET does not behave in this way. According to the documentation
though, ADO.NET "always uses pooling".

If I connect to the RDB database with OdbcConnection.Open(), I get a
connection, but when I call Close(), it immediately disappears in the
database. I see this as an indication that pooling is not working. I have
tried to call SQLSetEnvAttr() in the .NET aplication but it didn't help.
When enabling ODBC trace, I can see that ado.net magically calls
SQLSetEnvAttr with the SQL_ATTR_CONNECTION_POOLING (201) attribute, doing
some other stuff that I don't understand.

Can anyone explain this to me?

Regards!

//Thomas Heder

--------------------------------------------------------
My VB6 application gives me the following ODBC trace:

VB6 3380-32b8 ENTER SQLSetEnvAttr
SQLHENV 00000000
SQLINTEGER 201 <unknown>
SQLPOINTER [Unknown attribute 201]
SQLINTEGER -6

VB6 3380-32b8 EXIT SQLSetEnvAttr with return code 0
(SQL_SUCCESS)
SQLHENV 00000000
SQLINTEGER 201 <unknown>
SQLPOINTER [Unknown attribute 201]
SQLINTEGER -6

....and my .NET application gives me initially the same, but when the
connection is opened, it also logs this:

[....my first manual call......]

TestClient 21a0-25e8 ENTER SQLSetEnvAttr
SQLHENV 00000000
SQLINTEGER 201 <unknown>
SQLPOINTER [Unknown attribute 201]
SQLINTEGER -6

TestClient 21a0-25e8 EXIT SQLSetEnvAttr with return code 0
(SQL_SUCCESS)
SQLHENV 00000000
SQLINTEGER 201 <unknown>
SQLPOINTER [Unknown attribute 201]
SQLINTEGER -6

[.....other stuff.....]

[......ado.net calls sqlsetenvattr again.................]

TestClient 21a0-25e8 ENTER SQLSetEnvAttr
SQLHENV 08061540
SQLINTEGER 201 <unknown>
SQLPOINTER [Unknown attribute 201]
SQLINTEGER -6

TestClient 21a0-25e8 EXIT SQLSetEnvAttr with return code 0
(SQL_SUCCESS)
SQLHENV 08061540
SQLINTEGER 201 <unknown>
SQLPOINTER [Unknown attribute 201]
SQLINTEGER -6

Reply to this message...

Thomas Heder

Ok... I have solved a part of this... I had failed to actually enable
pooling on the connection. The column "Pool Timeout" in the ODBC Manager was
hidden, and the way of doubleclicking the drivername to setup pooling is
anything but intuitive, but now I at least get pooling to work.

Another question: Now it seems like every client gets its own pool. Is it
possible to override this and place the driver in "one pool per driver" mode
even under ado.net?

//Thomas

"Thomas Heder" <thomas.heder@n o s p a m.home.se> wrote in message
news:eXR8wGU$Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

System.Data.Odbc.OdbcConnection

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews

.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT

Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project

FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.

Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials

	Copyright © ExamGuru, Inc. 2001-2006
	Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification