Display number of rows returned by Custom SQL

[RICHARD RUTA]

In a grid, is there a way to get the number of rows returned by a Custom SQL?

Thanks,
Richard

[DaveLClarkI]

If no other answer is forthcoming, you can at least use the following to find out the sequence number for the last data row in the grid. Note that "wob" is the name of one of our skins and, as such, you would change that to suit your need. The only issue would be when to call this. You may need to experiment a bit to find that out as I created this from a different working function and don't have any custom SQL situations with which I can test this. Good luck.

Code: Select all

/*	================================================================================
	countNumberOfGridRows()	returns the sequence number of the last data row for the
							specified grid.  The arguments are the grid id and name
							of a bound field associated with a widget in that grid.
*/
wob.countNumberOfGridRows = function(gridId, boundFieldName)
{
	var fld, seqno = 0, mygrid = getObj(gridId).grid;	// get reference to grid object
	if (mygrid)								// if grid exists
	{
		while (fld = mygrid.getDataValue(seqno + 1, boundFieldName)) // loop on grid rows
		{
			++seqno;							// increment row number
		}
	}
	return seqno;							// return last sequence no. to caller
}

[Alex]

This will work for a grid that is populated by RPG code. However, it will not work for a database-driven grid populated by Custom SQL.

In our next update, we will add a JavaScript API that will return the total number of records in the grid, regardless of the grid type. However, the API can only be used once the grid has finished rendering. So, if you're trying to show the total number of records on the screen, this new API may not be the best fit. I would recommend having RPG code calculate the total number of records and output that value to the screen, instead.

[DaveLClarkI]

This will work for a grid that is populated by RPG code. However, it will not work for a database-driven grid populated by Custom SQL.

Is this because such a grid uses widgets that are bound directly to database columns and does not use widgets that are bound to RPG fields? If so, then that makes perfect sense. I guess I didn't make it real clear that the code I posted is for widgets bound to RPG fields. ;-) I did not stop to think about just what was meant by grids built via Custom SQL.

[Alex]

Yes. First of all, the field name passed to the getDataValue() function has to be an RPG field name. Therefore, this function will not work with a data-bound grid.

But, secondly, with a data-bound grid, the server does not send the entire set of records to the client. Instead, the grid keeps fetching server data as the user scrolls through the grid. Internally, however, it does know the total record count, since it has to use it to render the scroll bar properly.

[DaveLClarkI]

It is also true of the code I posted that not only is it only for grid widgets bound to RPG fields; but, it will also only represent the total rows available when it is a load-all subfile. Cheers.