These are not the errors you are looking for….

Recently the DBA made some database changes in my current project and we started seeing a new error in the Eurekalog reports from my Delphi front-end.  The interesting thing is that the error message was actually a warning message from SQL Server 2008 R2.  The message is ‘Warning : null value was eliminated by an aggregate or other SET operation’.  The DBA had found from previous experience that ADO doesn’t always report the actual error, but a preceding message output to the console in a stored proc.

We decided to write a test stored proc and call it from Delphi, confirming his past experience.  I modfied my code to look through the ADO Error collection, and found the source of the exception.  As a result, I have modified hcOPF to provide access to the error collection via the new Errors function on the IhcStoredProc interface.  I have populated this TStringList in the event of an error with the ADO error collection messages.  The original Exception is re-raised, but now you can catch it, and access the actual source of the exception.  I have also stubbed out the DBX implementation.

An additional note about ADO; due to a sporadic bug we encountered with ADO I use the AddParameter method to create and populate stored procedure parameters through hcOPF.  The parameters must be created in the same order as they are defined in the stored procedure since ADO seems to ignore the parameter name when populating the proc parameters.  Using the stored procedure Refresh method followed by SetParamValue calls resulted in sporadic “Parameter @XXX not found” errors.  This appears to be a known issue with ADO that you can read about here.  Since making this change we have not had a single such error report.

Tags: , ,

4 Responses to “These are not the errors you are looking for….”

  1. ObjectMethodology.com Says:

    Nice trace, good work!

  2. Warren P. Says:

    At a former employer we used a rare ADO alternative component set called Kamiak, that almost nobody else uses. We had to get really good at just this sort of spelunking at the low level ADO. This is fascinating stuff.

    And at my current spot, we’re still having some weirdness with regular TADOConnection and TADOQuery objects, with those parameter issues.

    Warren

  3. Paul Says:

    We to used Kamiak years ago but switched to TADOxxx. TADO is fairly stable but not always that fast. We have had a few curly ones mainly to do with locates.

    We are currently looking at SDAC from http://www.devart.com/sdac/
    Any thoughts on that?

    Paul

  4. Roland Bengtsson Says:

    Devart driver is good. My team use them for sql server. And we never use stored procedures and try to minimize use of sql. Those details is hided in our object persistance framework Bold for Delphi. By using that we get more done in less time.

Leave a Reply