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 22.214.171.1247 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 (126.96.36.1992 and 188.8.131.5266):
The incorrect empty result set is shown for any TOP value greater >= 26. (Interestingly enough, dbisqlc 184.108.40.20666 starts to show the empty set for TOP >= 25.)
In contrast, when using DBISQL (220.127.116.112 and 18.104.22.16866), 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 22.214.171.12480. I've tried
answered 21 Oct '10, 15:47