Our .Net app has been running for over a week now with its new “persistent database connection” architecture intended to reduce the # of IBM Universe licenses (see “Failing to Let Go” ).
The new design is having the desired effect, but it resulted in one new problem which looks like a bug in the UniOLEDB / .Net Provider for OLEDB stack. A tester found that “booking” (one of our application’s functions) a certain type of document without first saving it consistently resulted in a “Not all parameter markers have been resolved” error. This exception was thrown by the Fill method which ran a SELECT statement on this document’s record. The same action definitely worked fine with the previous design, where the .Net Data Provider would automatically open the database connection before running the Fill method, then close it again afterwards.
This bug was somewhat puzzling, since there had been no code changes specific to this SELECT statement, and the SELECT statement was pretty mundane: just one parameter, which was the key for the record. Running the statement in the debugger confirmed that the parameter was, indeed, being set.
When UniOLEDB errors occur for reasons that aren’t obvious from the coder’s viewpoint, it can be helpful to look at the problem from UniOLEDB’s viewpoint. This can be done by turning on the UniOLEDB trace facility, by setting the UniOLEDBTRACELEVEL and UniOLEDBTRACECATEGORY environment variables: I use settings of 4 and 4095, respectively. A DRITrace.txt file will then be written to the folder that the .Net application is running in, chock full of mostly cryptic information about UniOLEDB’s activities:
00022990 CArICommandTextImpl(73761420)::SetCommandText
00022991 CDRImCommandUCI::SetCommandText(SELECT …)
00022992 CArCommand(73761420)::Execute()
00022993 CDRImAdmin::ClearError
00022994 CArCommand(73761420)::InternalSetParameters()
00022995 CArCommand(73761420)::ParamData2DRIBuffer()
00022996 CDRImCommandUCI::Execute(SELECT …)
When I looked at the .Execute that failed,, I noticed that the trace entries which indicate a parameter (such as InternalSetParameters in the above example) were not present. Or, as some would, say the “parameter marker” hadn’t been “resolved”.
I went back and the looked at a previous parameterized SELECT statement that succeeded, to confirm that setting the parameter did result in a trace file entry. It did, but I noticed something else that I hadn’t expected: the previous SELECT statement was exactly the same as the one that had later failed. Same command text, same parameter. A check of my own log confirmed that it was the same parameter value too.
This turned out to be the root cause of the problem. The only thing that we had (intentionally) changed between versions was the point that the database connection was opened — it used to be closed after each SELECT statement, and re-opened for the next one. Now, it was left open between SELECTs. So, some piece of code in the stack (maybe the .Net Provider for OLEDB, maybe UniOleDB) wasn’t impressed that we were asking for the same thing twice, and ignored part of the 2nd request. Closing the database connection between these two requests solved the problem, and that’s the solution we went with for now.
In case you’re wondering, running the same SELECT statement twice wasn’t a bug on our part. Booking an unsaved document required that the .Net application 1) save the document 2) invoke a “Universe Basic API” (basically, a stored procedure) to fill in some missing fields in the document record 3) read the document record (the first SELECT) 4) invoke another Universe Basic API to “book” the document 5) read the newly booked document record (the second SELECT). Not the optimal way of handling this action, perhaps, but not such as unlikely series of events either.
I have a feeling that there are other parts of our .Net application which can run the same SELECT statement twice — if there aren’t, then there almost certainly will be some in the future. So, a more generalized solution is going to be required. I’m inclined to just do away with parameterized SELECT statements, embedding the parameter values into the SELECT statement instead. While this will, in theory, adversely affect both the performance and security of the application, conventional coding wisdom doesn’t necessarily apply when sailing the mostly uncharted waters of UniOLEDB development.