I experiment with 3-tier model of client-server application. I use SQL Server 7.0 and DB2 as backend. I use VFP multi-threaded COM as middle tier to access to data and for bussines logic. I use VFP as frontend. I use ADO for access to data.
I create for each request (for each table, which I want to get) from frontend, new Recordset and Connection and Command objects and I return reference to recordset object to frontend. If frontend needn't to use any resource, I close this recordset and connection object. I use server cursor location for ADO object. I use OLEDB provider from MS for SQL Server and OLEDB Server/400 provider from Hit software for DB2. I try it with MTS.

A.
This model brings more opened concurrent connections for one frontend user, but this work properly with both SQL Server and DB2 backend.

I consulted any problems with OLEDB provider for DB2 with Hit software. I discussed about concurrent connections to AS400 with them and I got interested informations:
"What you are trying to do with your technology is a little dangerous working with AS400.
... opening 50 connection together to AS400 (to open 50 tables will kill the AS400 box. (Too much memory used and too many jobs started). A better idea is to keep the connection on the client machine (1 connection for machine) and use all the statement that you want."

Q: I want use my application for 10-100 users. If each users opens 3 connections to database, I get 300 connections to backend at a moment. Is any problems with many concurent connections (over OLEDB) to databases (MS SQL or DB2)? How troubles I can await?


B.
For this case, I consider about application model with one connection. I want to create only one connection object for all frontend requests. I don't know, whether it is good idea.
I want to create connection object in middle-tier during first request from frontend interface. I want to keep this connection object opened at all time, which user works with his application for. I want to share this one connection for all request from app to database.
Sample of app:
oData1 = createobject('MiddleTier.Data1Acces')
oRS1 = oData1.GetRSFromTable1
oData2 = createobject('MiddleTier.Data2Acces')
oRS2 = oData2.GetRSFromtable2
I want to use same Connection object in middle tier for both oRS1 and oRS2 recordset objects (filled in oRSx.ActiveConnection).
Q: How I can to keep reference to one connection object in middle tier for all app requests?

One solution comes to my mind only. I will create connection object for all recordsets in middle tier. I will get reference to this object to frontend. I will give back reference to this object to middle tier with each request for data from frontend ...

Is this way possible? Or Am I not right? Could anybody clear me it, if I'm misled?


Thanks in advance.
Jano