Egad! Zounds! Can this be true?
SQLCODE = -674, SQLSTATE = 09W07, ERRORMSG() = Statement's size limit is invalid
"The size limit must be a constant integer greater than 0 and less than 32767."
...same thing in V12 docs.
Yes, I actually got the message... not sure yet what the TOP value actually was, still trying to get over the Doc Shock!
The problem in dbisqlc stems from the database server reporting that it "definitely knows the row count for this query will be 26" (or whatever value was in the TOP phrase) by reporting a positive value in the SQLCOUNT field of the SQLCA. dbisqlc is easily fixed by just never letting it trust the count from the server (in which case is does just a little bit of extra work); however, I also wonder if trusting the TOP clause represents an server bug too.
answered 22 Oct '10, 19:03
I did remember having hit that 32k limit myself too. In my db code (v9 compatible) I see this:
I think the limit was raised in V10 and upward.
As Mark has pointed out, that seems to be a doc mistake. I can use TOP and START AT values way bigger than 32.766 and get correct results, both when using constant values and variables.
I have tested with an 220.127.116.117 engine and a table with 2.353.744 rows. Column 1 is the PK.
However, when the sum of TOP and START AT seems to be larger than the table count, the effect seems wrong when using dbisqlc (18.104.22.1682 and 22.214.171.12466):
The incorrect empty result set is shown for any TOP value greater >= 26. (Interestingly enough, dbisqlc 126.96.36.19966 starts to show the empty set for TOP >= 25.)
In contrast, when using DBISQL (188.8.131.522 and 184.108.40.20666), any increate of TOP n does still show the result set with the first PK.
Resume: So the TOP problem seems to be restricted to dbisqlc.
answered 21 Oct '10, 09:21
It also works correctly for 220.127.116.1180. I've tried
answered 21 Oct '10, 15:47