A couple years back I was asked to develop an application for managing facility construction for an O&G firm. The previous incarnation of the application was written using custom domain objects against an Oracle 8 RDBMS. I agreed with the approach in using domain objects, but decided I would make my life easier and use an earlier version of what was to become hcOPF so I didn’t have to write the persistence logic and could use object binding.
Things proceeded well, but the SME (Subject Matter Expert) kept asking for more, and I ended up managing a pretty large object graph within the application. It was then that I got a mysterious error message saying I had exceeded the query limit. Me with my little application? It seemed ludicrous that Oracle couldn’t handle a few queries, until the DBA told me it was 100 simultaneous queries per connection. At first I couldn’t believe that I was using so many, until I did some investigation. At that time I was creating a query whenever I needed to load a list or a single object, so it didn’t take long with an object graph of any depth to use that many queries. I quickly implemented ThcQueryPool, and pretended that incident never happened.
That is, until quite recently. On my current project, I needed to support Stored Procs and executing queries directly, and wanted to do so in a database independent manner. That is, after all, one of the advantages of using an OPF. I implemented this support in the form of interfaces that could be implemented by the TQuery and TStoredProc type components in each supported DAL (data access layer). To speed up implementation, I created a couple methods on the ThcFactoryPool to return either a IhcStoredProc or IhcQuery. I assumed this interface would be reference counted, and the components would be destroyed at the end of the routine in which they were used. I was wrong, and just discovered this when SQL Server gave me a similar kind of message. All I can say is “OOPS, I did it again!”.
There is a good explanation of this issue on StackOverflow.
In order to fix this situation I have implemented _AddRef, and _Release methods in the ADO DAL objects so reference counting is now used. I will either have to propagate this solution to the other DALs, or re-factor to enable the use of Query and StoredProcedure object pools.