Re: Informix size limit on sql query through ODBC, ASP?

Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<3EDECE26.4060002@earthlink.net>... > nate wrote: > > Jonathan Leffler <jleffler@earthlink.net> wrote: > >>nate wrote: > >>>Can anyone tell me if there is a size limit on a sql statement sent to > >>>Informix via ODBC? > >> > >>In general, the limit is 64 KB (eg in ESQL/C). I don't know whether > >>ODBC has a smaller limit, but it shouldn't. > > > > If the limit is 64 KB, then I can't figure out why I am getting the > > error (I never posted the actuall asp error, so here it is)... > > Microsoft JScript runtime error '800a01fb' > > An exception occured > > ...The sql statement is only 16,599 characters long when I get the > > error for a date range of 02/01/03 - 02/28/03. But the sql statement > > is almost the same at 16,007 characters long when I run the report for > > a date range of 02/01/03 - 02/27/03, and I do not get the error? Could > > it be because my sql statement has 28 select...union clauses? What > > else could it be? > > Why did you need a 28-way union? Because you were doing queries for > February, and you need a different query for each day of the month? :-) > > I did chat with someone about this briefly (while on a con-call with > them and the other person was moving between borrowed conference > rooms), and he did mention that there might be a smaller limit - about > the 16 KB mark - maybe imposed by MS somewhere. > > I don't recall seeing any platform or version information in your > posting - I should probably have chided you on that before, but I > definitely need to do so now. And if your software isn't reasonably > current (say CSDK 2.8x), then an upgrade is probably in order and > might even fix the issue (but no promises on that). Which server > version are you using? Do you get errors when you run the SQL via > DB-Access? Sorry it took so long to reply (I actually forgot to hit submit yesterday and lost what I wrote. So here-goes-it round two! My responses to your questions in order: It's not that I "needed" a 28-way union, it's that I "used" a 28-way union because it was the first design that gave me acceptable results. Yes, there is one query for each day in the From Date/To Date, so a date range with a Start Date of 2/1/03 and an End Date of 2/28/03 would yield a 28-way union. I tried using the, 'between' clause and also, '[From Date] >= beg_date and [From Date] <= end_date' but both of these designs results in 2-20 minute query times-exceeding our asp script timeout. The activity table i'm querying has 8+ million records. The 28-way union query will run in 35 seconds for $750K worth of transactions (pretty darn good) so long as it does not exceed about 16K in size! But adding even the simplest report criteria such as 'store = 0001' (12 characters) to a 31 day query results in 12x31=371 more characters in the sql. My Version Information: (my computer-where i'm running the asp pages and ODBC connections from)... -Windows 2000 Prof. -IIS 5.0 -Informix ODBC Driver Version: 3.80 32 bit (computer where Informix is running)... -Unix Version: SCO_SV opt1 3.2 5.0.5 i386 -Informix Dynamic Server Version 7.31.UC7 -Informix-SQL Version 7.20.UD7 -I do not get errors when I run the SQL via dbaccess. It does seem that there is a limit to the size of the SQL somewhere between here and Informix DB? Still Searching...-->Nate