Author: Craig Stuntz
The error in the title comes from InterBase. Actually, the full error is:
[DataDirect][ODBC InterBase driver][InterBase]Dynamic SQL Error, SQL error code = -804, SQLDA missing or incorrect version, or incorrect number/type of variables.
I’ve seen this error before when the wrong InterBase client (gds32.dll) version was loaded by the application, or when the wrong number of variables is passed to a stored procedure. But yesterday I found a new way to get the error. It took me quite a while to debug this, so I’m putting the information out in case other people encounter it.
It seems that certain versions of InterBase server don’t respond well to passing a parameter of type ShortInt to a parameterized query that calls a stored procedure with that param as an integer argument. In particular, I found that doing this will fail on InterBase server version 7.5.1.162, but works fine on 7.5.1.80. Changing the type of the parameter to integer fixes the problem. I was doing this via ODBC; I don’t know if it makes a difference or not.
(Why was I using a parameter of type ShortInt in the first place? I wasn’t, exactly. But the value that I assigned to the Param.Value, a variant, was the constant 0. Delphi will treat the constant value 0 as a ShortInt.)
This is a good example of why unit testing can never be sufficient to completely test an application. Since this is a server error, a unit test can’t find it. Since it only happens on certain versions of the server, even basic integration testing might miss it. The only way to have a hope of finding such an error is to do automated testing on multiple environments. In this case, the error prevented a Crystal report from executing at all. We have a testing tool built into our applications that executes all of the reports and saves timing information to a log file. We run this with every QA releasewe do, and compare the timing information with previous releases. Sometimes we catch reports which won’t executed all, as with this case. In other cases, we find that the report has become much faster or much slower, due to a database metadata change.
The report profiler is part of a “debug console” which is built into the applications and provides testing and diagnostic information at runtime. I said before that testing is a valid use case, and this is one example. Another, report-related example is that whenever our applications modified the SQL in a report (to customize the result set for an end user), we have a way to display the final SQL before it is executed by the report. The reason is that a particular customer might make a particular selection that either performs badly or does not execute at all. (End users don’t directly edit SQL; instead they choose the data they want to see graphically, and we build SQL from their choices.) In order to reproduce their problem in a debug environment we need to know precisely what kind of choices they have made, and how the software handle those choices. Displaying the SQL before it is executed answer six questions.