TADOStoredProc Parameter Returns wrong Value

The other day I was trying to make a stored procedure call using hcOPF and kept getting a ‘parameter object is improperly defined’ error.  It has to be the most frustrating ADO error I’ve ever encountered, and I’ve come across a few in my time.  What makes it so frustrating is that the error message gives no indication of what parameter is the source of the problem.

As a developer, I would recommend you be as specific as possible when raising exceptions.  Imagine a stored proc taking 30 parameters, and the time it would take to figure out all the possible reasons why any one of them might cause this error when you have absolutely no idea why an API call you have made a thousand times before without a problem, now fails.

What I ended up doing is making the call manually in SQL Management Studio (SMS) to verify the back end was functioning,  Then I wrote a test application in Delphi that simply made the same call using a TADOStoredProc component with the same parameters I used in SMS.  The call succeeded, but one of the input/output values was not what I expected.  After experimenting at length, and discussing this situation with a co-worker, I discovered that the input/output parameter in question was a string defined as a varchar(max) in SQL Server, and it was being truncated at the length of the string I initially populated it with as an Input.  Once I verified this hypothesis, I filed a QC report (101665).

I probably should also have filed a report to fix the generic exception I encountered initially but I had already spent enough time isolating and filing the first bug report.  At some point you have to actually get some work done instead of helping fix someone else’s bugs ;-)

Tags: ,

5 Responses to “TADOStoredProc Parameter Returns wrong Value”

  1. Cameron Says:

    We left ADO due to its inability to handle much of the 05+ SQL Server features. Fortunately SDAC in Native Mode is a very easy replacement, more stable and noticeably faster. The big bonus is supporting SnapShot Isolation modes.

  2. Stefan Glienke Says:

    The problem is that varchar(max) is not fully supported by ADO 2.8 and therefor it gets converted to another type. A quick google search for “ado varchar(max) output” reveals that. When assigning the input value for that parameter the size gets set to the length of that value. That is why your output value gets truncated if longer. To prevent that just set Size to MaxInt-1.

  3. Olaf Monien Says:

    I don’t think this is an ADO problem at all. The issues on Google appear to be oledb related, which shouldn’t be used with SQL Server anymore, SQL Server Native Client (NCLI) is recommended.
    Delphi simply seems to be to “shy” to put “maxint-1″ into the size property as would be required. If size is left at “0″ then size will be set to length(value).
    This might possibly be a feature to avoid an accidental 2GB string - instead the programmer has the burden to decide on the expected max size …

  4. Stefan Glienke Says:

    To my knowledge ADO does not use the NCLI directly but through the NCLI OLE DB provider (as explained here: http://msdn.microsoft.com/en-us/library/ms130978.aspx)

    And if you look at the second note of that article you see what I wrote in my first comment.

  5. LDS Says:

    ADO is “OLEDB without pointers”, it’s just a wrapper over OLEDB needed by languages that can’t use OLEDB pointer-based interfaces (like VB and scripting languages). Microsoft is going to desupport the whole OLEDB/ADO framework (it’s reverting to ODBC as a generic DB access interface), especially for SQL Server, thereby OLEDB/ADO will see very few improvements, if none at all.
    Time to look for alternatives…

Leave a Reply