Re: How to retrieve first 50 rows , next 50 rows and so on in Informix

Hi Guys Sorry no luck , my developer is trying to limit the no of rows resulted from query to db from the java code , for each session, so that if a big query returns 10000rows she wants to limit to 50 rows and next 50 rows etc, in this case even if more sessions are connected to db at the same time it will not blow up the memory. I read in informix http://www.geocities.com/SiliconValley/Bridge/4578/faq.html that by using web datablades it is possible to limit the number of rows by using some tags and attributes like MAXROWS and WINSIZE of MISQL. Anybody used these tags for the same or it is for different purpose ?? Researching on Web blades any Help?????????? Regards kalpana preetinder dhaliwal <preetinder.dhaliwal@dhl.com> wrote in message news:<biedsg$7n4$1@terabinaries.xmission.com>... > insert into temp ( as somebody already suggested ) , then fetch 50*,> then delete first 50 and then again 50* :), or select min and max rowid > and use an approximation like ( max - min )/50 ( divide in 50's ecah , > but ofcourse this set will have few rows ) and then select based on > rowids . If u want some other order, use any numeric key . > > We used something similar in checkpointing - query will be like - where > prod_id > l_prod_id and batch_num > l_batch_num order by prod_id, batch_num > > Rgds > Preetinder > > KalpanaPai wrote: > > >I know that > >Select first 50* from Table will get only 50 rows> >But i want to know from the application side , if the query returns > >more rows , i want to get them in 50's and display and next 50 etc, in > >that way the query will not kill the memory if more sessions are > >connected without bringing the whole data to the buffer . > > > >Any Solution ????????/ > >Thanks in advance > >Kalpana > > > > > > > >kalpanapai@hotmail.com (KalpanaPai) wrote in message news:<8b77f6f5.0308241906.21eb6f22@posting.google.com>... > > > > > >>Hi All > >> > >>Is there any equivalent command for SET ROWCOUNT or LIMIT in INFORMIX > >>to get the first 50rows, next 50 rows and so on if query returns 2500 > >>rows . > >> > >>So that the number of rows retrived for each page can be limited in > >>each session. > >> > >>Any clues would be appreciated. > >>Thanks in Advance > >>Kalpana > >> > >> > > sending to informix-list

I'm probably missing something here, but isn't this just fetch buffer size? OK, fbs isn't the same as the number of rows because different rows can have different lengths, but in any event, it sounds like you are more worried about the actual amount of memory used rather than the number of rows brought to the client at one time. All the Informix APIs, JDBC included, fetch the data from the server into a buffer transparently and just give the application a row at a time when the app makes a fetch call (unless you use one of the features like ESQL/C's fetch array size; not sure if Informix JDBC supports FetchArraySize). The buffer is refilled as needed by the API. I've seen were this actually caused some confusion because the driver continued to deliver a few rows after the network cable had been unplugged. This would apply if you are coding in the JDBC API; if you are coding in something above the JDBC level, using something that completely reads the ResulSet beyond your control, then the ability to use this feature is being bypassed. HTH, kalpanapai@hotmail.com (KalpanaPai) wrote in message news:<8b77f6f5.0308261835.e75d4a1@posting.google.com>... > Hi Guys > Sorry no luck , my developer is trying to limit the no of rows > resulted from query to db from the java code , for each session, so > that if a big query returns 10000rows she wants to limit to 50 rows > and next 50 rows etc, in this case even if more sessions are connected > to db at the same time it will not blow up the memory. > > I read in informix http://www.geocities.com/SiliconValley/Bridge/4578/faq.html > that by using web datablades it is possible to limit the number of > rows by using some tags and attributes like MAXROWS and WINSIZE of > MISQL. > Anybody used these tags for the same or it is for different purpose > ?? > > Researching on Web blades any Help?????????? > > Regards > kalpana > > > > preetinder dhaliwal <preetinder.dhaliwal@dhl.com> wrote in message news:<biedsg$7n4$1@terabinaries.xmission.com>... > > insert into temp ( as somebody already suggested ) , then fetch 50*,> > then delete first 50 and then again 50* :), or select min and max rowid > > and use an approximation like ( max - min )/50 ( divide in 50's ecah , > > but ofcourse this set will have few rows ) and then select based on > > rowids . If u want some other order, use any numeric key . > > > > We used something similar in checkpointing - query will be like - where > > prod_id > l_prod_id and batch_num > l_batch_num order by prod_id, batch_num > > > > Rgds > > Preetinder > > > > KalpanaPai wrote: > > > > >I know that > > >Select first 50* from Table will get only 50 rows> > >But i want to know from the application side , if the query returns > > >more rows , i want to get them in 50's and display and next 50 etc, in > > >that way the query will not kill the memory if more sessions are > > >connected without bringing the whole data to the buffer . > > > > > >Any Solution ????????/ > > >Thanks in advance > > >Kalpana > > > > > > > > > > > >kalpanapai@hotmail.com (KalpanaPai) wrote in message news:<8b77f6f5.0308241906.21eb6f22@posting.google.com>... > > > > > > > > >>Hi All > > >> > > >>Is there any equivalent command for SET ROWCOUNT or LIMIT in INFORMIX > > >>to get the first 50rows, next 50 rows and so on if query returns 2500 > > >>rows . > > >> > > >>So that the number of rows retrived for each page can be limited in > > >>each session. > > >> > > >>Any clues would be appreciated. > > >>Thanks in Advance > > >>Kalpana > > >> > > >> > > > > sending to informix-list