COM+ and Connection pool for Oracle

[ringwraith]

I recently began to develop a Com+ application using Oracle, i noticed that the connection pool with oracle is not working the right way, i need some source of information that helps me to configure these service on the transaction server, i'm using the microsoft provider for oracle, i dont know if i have to use the provider from oracle. Thanx...

[brucevde]

Are you using OLEDB or ODBC?

Pooling in the Microsoft Data Access Components

[ringwraith]

Im Using OleDb, i try to use the provider from Oracle but doesnt work, i dont know if i understand the com+ Architecture, i read that each transaction use a single connection to the data base, the attachment included here show the actual architecture of my application, i have a flow object, that calls three different bussiness objects, each bussiness class uses a generic Data Base Interface Object, but for each instance of the DBI it uses a connection, and i understand that these architecture uses a connection pool, and use the same instance of a connection object in the same transaction, do i understand the wrong way?

Thanx for your help

[deja]

hi,
(sorry, my english is not very well)
a few words about connection pooling.
either ODBC or OLEDB support connection pooling.
this pool is managed automatically behind the scenes by one of those interfaces (ODBC, OLEDB) and can't be control manually.
connection pooling is per process and per connection string, i.e. if u want to achive connection pooling u must use the same connection string (user, pass, data source etc.), and it make sense because u want a pool of connections to the same data source.
if u have two or more connections to various data sourcse, each one will have it's own pool.
when u open connection, then closing it, OLEDB (or ODBC) r not actually closing the connection, but only release it to the pool.
when u reopen connection, OLEDB searching the pool to find available connection (of course he checked the connection string to validate that the connection is already have a pool), if he find one he gives u that connection, if not, he will create new one.
connections in the pool r not live forever. if there r connection who idles for a period (60 sec), OLEDB will remove that connection from the pool.

now, i recommend u to use connection in your data access object per function.
do not use global connection in that class, because it can lead u to some problems.
when u mark one of your business component as transaction required (or supported), COM+ will actually create transaction only when he encountered connection opening in your code.
so every code before opening th connection (which belong to the data acces class) will not couse to create physical transaction through the DTC.
if u calling multiple times to the data access class inside youe transaction component, u can't be sure u r using the same session to the DB, becuase COM+ maybe use different connections (from the pool) each time, so rely on it.
i encountered that problem, when i added foreign keys to some of my tables.
when u update a child table (or delete, or insert), your DBMS must ensure that u have already that key (that u r trying to update with) in the parent table.
if he doesn't find one, u will get an error.
now, i tried to use transaction, that update both of the tables (parent and childs), when i updated the parent everything was fine, but i got an error when i tried to update the child, becuase the transaction commit only when the root object (of the transaction) deactivate (when i call SetComplete or when the function get to the end), but in the second update (to the child table), the key from the parent that i was updating didn't actually in the DB becasue there was no commit at all.
if COM+ would use the same connection, it wouldn't were a problem, because in the same session the DBMS can ensure that there r lready key (even if it not comitted).
that prove that COM+ uses different connections from the pool in the transaction.

hope that was helping.

[ringwraith]

Thanks Deja, dont worry my english is very bad too, i had a global connection on my DBI object, just like u said, but i changed, but stills doesnt working, when u refer that i should use a connection for each function what are u refering to? . A connection for every insert for example?. I have the same problem that U had with the constrainsts in the RDBMS, i am inserting on a table (the father) and when i try to insert in the child table, gives me a constraint error, because is not using the same connection, and when i monitor the connections i have a lot of sessions, , i have to make a transaction for each table, the transactions doesnt work with COM+?

Thanx for your help

[deja]

as i said, the best approach for data access is to use connection per function, for example:

Public Function RunSQLReturnRS(ByVal strSQL As String, Params As Variant, Optional strConn As String) As ADODB.Recordset

Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command

Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command

If IsMissing(strConn) Or strConn = "" Then
cmd.ActiveConnection = GetConnectionString()
Else
cmd.ActiveConnection = strConn
End If

cmd.CommandText = strSQL
cmd.CommandType = adCmdText

' call a function to pack the params inside the Command.Parameters collection
collectParams cmd, Params

rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenStatic, adLockReadOnly

Set RunSQLReturnRS = rs
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
Set rs.ActiveConnection = Nothing
Set rs = Nothing

Exit Function

errorHandler:
Set rs = Nothing
Set cmd = Nothing
RaiseError m_modName, "RunSQLReturnRS(" & Left(strSQL, 10) & ", ...)" & Err.description
End Function

the example above demonstrate using ADO to execute SQL statement on DBMS.
as u can see, we open the connection (through Command object), then closing it before leaving the function (Set cmd.ActiveConnection = Nothing).

this is very efficient since u don't have to hold connection open for the entire life cycle of the data access object.
must always remember that u r working with application server (COM+) that handle many concurrent users.

as for your second problem, about constraint violation, u can open connection from the transaction object for the lifetime of the transaction, but in that case you r breaking the layers concept, because u holding connection object in the bussiness object instead of the data access object. i don't like that idea but it works, because u r working with the same session to update your table set.
u can also write a stored procedure in the DBMS that will accept the data needed for the tables u r going to update, then control the whole transaction at the DBMS level (begin trans, end trans, commit etc.) this is much better approach the the former, but it require more invest in your code.
i didn't take neither of those solution, but instead, i wrote the constraints myself as a triggers.
for instance, a trigger that will initiate when u delete a record from the parent table, to delete it's descendants (in the chlid's tables).

i'm sure there r other solutions to that problem, but i didn't waste enough time to think about them.

best regards.

[ringwraith]

Thanx Deja, i will mantain the same connection open all the way of the transaction, but anyway Deja thanx for all your help, it helps me a lot. bye