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? My responses in same order as your questions: It's not that I "needed" a 28-way union, it's that I "used" a 28-way union because it was the first query design that gave me acceptable results. Yes, there is one query for each day of the date range that you enter, so if you enter FROM DATE: 2/1/03, TO DATE: 2/28/03, you'll get a 28-way union (if you can offer a better query design that works, i'm always open to suggestions)! I can't use the systime (Unix timestamp) field because we keep track of dates in our own format in the trans_date (YYYYMMDD) field. And using the 'between' clause grinds the query to a halt with even a 2-day date range because the table being queried has 8 million records. My Platform/Version Information: ...(where i'm running my asp pages and odbc connections from)... -Microsoft Office 200 Pro -IIS 5.0 Web Server -Informix ODBC Driver: INFORMIX 3.80 32 BIT (3.80.0000 2.70.TC1) ...(where the Informix DB Server is running from)... Unix Version: SCO_SV opt1 3.2 5.0.5 i386 Informix Dynamic Server Version 7.31.UC7 ISQL: INFORMIX-SQL Version 7.20.UD7 I do not get errors when I run the sql via dbaccess, but I have to use VI or another editor to paste the large query onto the screen. Perhaps the problem is infact with MS, ODBC, and/or some ADO object? Searching... -->Nate (Thanks thus far...)