Egad! Zounds! Can this be true? Why? 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." http://dcx.sybase.com/index.html#1101en/saerrors_en11/errm674.html ...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! asked 20 Oct '10, 16:06 Breck Carter Volker Barth |
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 John Smirnios So this is a bug that will be fixed for dbisqlc in current versions? @John: "will not exceed 26" has more truthiness than "will be 26". @Volker: Maybe, under SAP's direction, dbisqlw.exe will be created... an actual Windows version of ISQL. I hate to say it, and I am not taking a shot at the developers, but as a corporate entity "Watcom Doesn't Give Good GUI". It's the only major exception to this rule that I know of: "Watcom Does Things The Way They Should Be Done." @Everyone: That was my Evil Twin speaking. My Good Twin is a big fan of the current dbisql.com/.exe... except that I never know which one to execute :) 2
@Volker: Yes, assuming I've made the final change it should be fixed in 12.0.1GA, 12.0.0.2602, 11.0.1.2514, and 10.0.1.4139. @John: Could you post the CR number here? - You know, we love more documentation:) 3
CR# 645986. It fixes the problem where dbisqlc may show a subset of the result set, fixes the reporting of DSNs in the Login tab (show DSNs from all SA verions, 64-bit used to not show any DSNs), and fixes the handling of connection parameters that did not have a short form (eg, the new "Server" parameter). More comments hidden
|
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. André answered 21 Oct '10, 09:18 ASchild Siger Matt |
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 11.0.1.2427 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 (11.0.1.2452 and 12.0.0.2566):
The incorrect empty result set is shown for any TOP value greater >= 26. (Interestingly enough, dbisqlc 12.0.0.2566 starts to show the empty set for TOP >= 25.) In contrast, when using DBISQL (11.0.1.2452 and 12.0.0.2566), 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 Volker Barth |
It also works correctly for 9.0.2.3480. I've tried answered 21 Oct '10, 15:47 Reimer Pods |
I think this is a mistake in the docs. I tried "select top 100000 * from systable" with SA12 and did not get an error. I checked the code and it would appear that at one time there was an upper limit check but it has been removed. I will double check and then get the docs fixed.
BTW: How did you get the error? What version/build & what tool did you use? The limit check was removed in 2003.
@Mark: I haven't had time to check, but I suspect it was SELECT @top with a variable that was NULL... that would do it, right? :)