In the last post I discussed how to determine if an IDfCollection was empty, and how to process a generic IDfCollection (i.e., not knowing anything about its columns). This week I want to look at two ways to determine the size of an IDfCollection. As mentioned previously, the IDfCollection object does not have a method or attribute that identifies how many (if any!) rows it contains. The first method discussed below is easy to implement, but has several drawbacks. The second method is a little trickier to implement, but does not suffer from the same drawbacks as the first method.
Method #1
This method loops through the IDfCollection and increments a counter. The approach is simple, but will consume processing time if the query results are large. The code looks like this:
dql = "select r_object_id, object_name, r_creation_date, a_content_type, r_full_content_size, a_is_template from dm_document where folder('/Temp', descend)";
q = new DfQuery();
q.setDQL(dql);
col = q.execute(session, DfQuery.DF_READ_QUERY);
// count the rows in the collection
int cnt = 0;
while (col.next()) {
cnt++;
}
System.out.println("Collection = " + cnt + " rows");
// if there were results, re-run and process
if (cnt > 0) {
col = q.execute(session, DfQuery.DF_READ_QUERY);
processGenericCollection(col);
}
Notice the “gottcha” here. After counting the rows in the while() loop, the IDfCollection object’s pointer has been advanced beyond the end of the collection. There is no way to reset the pointer to the beginning of the IDfCollection short of re-running the query. This is a major deficiency in my book. This means you get one shot at processing your query results and you can only plow through them linearly.
Method #2
This second method offloads the task of counting the rows in the result set (i.e., the IDfCollection object) to the database. To accomplish this, you must issue two queries: the first returns the count of the database objects that meet the query criteria; the second returns the result set. The tricky part of this approach is correctly parsing the query and converting it to a ‘count’ query. Usually a simple string match will do the trick, but I have seen some queries blow up when the select variables are removed. Here is the basic code for this approach.
dql = "select r_object_id, object_name, r_creation_date, a_content_type, r_full_content_size, a_is_template from dm_document where folder('/Temp', descend)";
System.out.println("Results = " + countQuery(dql) + " rows");
q = new DfQuery();
q.setDQL(dql);
col = q.execute(session, DfQuery.DF_READ_QUERY);
processGenericCollection(col);
. . .
// convert query to count query and count results
private static int countQuery(String dql) {
IDfQuery q = new DfQuery();
IDfCollection col = null;
String cntDQL = "select count(*) as cnt ";
int cnt = -1;
try {
cntDQL += dql.substring(dql.indexOf("from"));
q.setDQL(cntDQL);
col = q.execute(session, DfQuery.DF_READ_QUERY);
col.next();
cnt = col.getInt("cnt");
col.close();
} catch (DfException e) {
e.printStackTrace();
}
return cnt;
}
This solution isn’t so much about the IDfCollection object as it is about the query. Still it seems a bit excessive and awkward just to determine how many rows are in an IDfCollection object.
There you have it, two ways to determine the size of an IDfCollection – neither of them ideal. I would love to hear from you if you have a different/better approach. Next week I’ll show you some code that does recursion using IDfCollections.
