Aspire has facilities to allow for paging in large result sets coming from database. Usually rows from a database are listed as a table on the html page. In Aspire parlance this table is populated using a loop tag in the html file. The rows for that loop are retrieved using a loop definition in a properties file. The java class that usually does this work is called "GenericTableHandlerX". The nature of this handler is that it will retrieve the rows using a database cursor. This class does not allow to retrieve rows using a seek/get approach to retrieve a window of the result set. Such a pattern is necessary for effective paging. This facility is provided a java class called RandomTableHandlerX. This document explains this process using the RandomTableHandler7.

Use RandomTableHandler7 for your loop in the properties file

#the loop of public items
request.BUFH.publicitemsloop.class_request.className=com.ai.htmlgen.RandomTableHandler7
request.BUFH.publicitemsloop.query_request.className=com.ai.db.DBRequestExecutor2
request.BUFH.publicitemsloop.query_request.db=reportsDB
request.BUFH.publicitemsloop.query_request.stmt=\
SELECT * \
FROM filed_items AS fi, \
	reports AS r, \
	folders AS f \
WHERE 1=1  \
	And fi.item_id=r.report_id \
	And fi.folder_id=f.folder_id \
	and f.public='Y' \
	and f.owner_user_id={ownerUserId.quote} \
ORDER BY r.last_updated_on DESC

This is the only change in the properties file.

The html file: loop definition

(!--RLF_TAG BGN_LOOP publicitemsloop --)

Your html segment

(!--RLF_TAG END_LOOP publicitemsloop --)

some more html

(script)
writeNext();
(/script)

As you can see the loop is no different than a regular loop. The next button is placed on the web page using a javascript function called writeNext(). Here is the code for writeNext()

function writeNext()
{
	available = "{{aspire.loops.publicitemsloop}}";
	if (available == "true")
	{
		document.write('<p><a href="javascript:gotoNext()">..More</a>');
		document.write('<hr>');
	}
}

This code checks to see if the loop identified by "publicitemsloop" has any rows by checking the variable for "true". If there are rows that are displayed, then it places a next button. If there are no rows then it will be skipped. This is somewhat flawed as this flag won't tell if there are any more rows pending for the next fetch. If such a control is needed then you can use a JSP page to walk through the list

The initial URL to invoke for the page

The URL for the above html will look like

../DisplayServlet?url=blogsURL&publicitemsloop_controlstring=1,30&ownerUserId=satya

The important thing to note is the "publicitemsloop_controlstring" argument. In this example it says 1,30. This means start fetching at row 1 and fetch the next 30 items. The next time around this string will look like 31,30. That means start at 31 and get the next 30 items. Java script can orchestrate this process. To demonstrate this here is an example javascript function:

function gotoNext()
{
	controlString = "{{publicitemsloop_controlstring}}";
	csArray = controlString.split(",");
	begin=csArray[0];
	span=csArray[1];

	iSpan = parseInt(span);
	iBegin = parseInt(begin);

	newBegin = iBegin + iSpan;
	newControlString = "" + newBegin + "," + span;
	
	url="/akc/servlet/DisplayServlet?url=blogsURL";
	url += "&ownerUserId={{ownerUserId}}";
	url += "&publicitemsloop_controlstring=" + newControlString;
	document.location=url;

}

Working with paging on a JSP page

Some of the presented code will vary if you were to use JSP tags as opposed to aspire tags. Because on a jsp page you can't use {{ for retrieving data. You have to use the JSP constructs to do it. If you are using JSP you also have the option of using the GenericTableHandler6 and managing the row window yourself. RandomTableHandler7 is a convenience when JSPs are used. RandomTableHandler7 is certainly required if you are using Aspire tags on the other hand.

What files is this facility implemented in

1. com.ai.htmlgen.RandomTableHandler6
2. com.ai.htmlgen.RandomTableHandler7

Required build numbers

RandomTableHandler7 is available starting with build 19.1. For earlier builds use RandomTableHandler6. 6 has a bug when used on a jsp page and accessed via ihds. It also has a bug where it requires the loop name to be all lower case. Both problems are addressed in 7.