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

nate wrote: > Jonathan Leffler <jleffler@earthlink.net> wrote: >>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. Well, I'm not clear why you aren't using a DATETIME YEAR TO DAY column; it would probably provide benefits. I'm also unclear why you run into atrocious performance, but I suspect a lack of an appropriate index and/or absence of UPDATE STATISTICS. And even given those, you should be able to use a condition such as: trans_date IN ('20030201','20030202',...) However, you should also be able to get trans_date BETWEEN '20030201' AND '20030228' to work sensibly too - the indexes that benefit the one benefit them all. Time to run with SET EXPLAIN ON and see what is going on - then ensuring that the correct indexes are in place and statistics are correctly upated. > 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. If DB-Access is OK, then it points towards the ODBC driver having a problem. If you're using 2.70.TC1 I-Connect or CSDK, there are a couple of later releases (2.80 and 2.81) to try. 28 copies of the same query with just trivial changes between must be obnoxious to edit. > Perhaps the problem is infact with MS, ODBC, and/or some ADO object? > > Searching... -->Nate (Thanks thus far...) -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/