Sorted collection with db.search Att: IBM

HCL Domino Domino Forum
1

Me and colleagues frequently use the LS db.search function to query documents from a huge database for reporting purposes. The db.search function offers a SQL like collection of documents, which is great. The only disturbing part is that the function does not allow to sort the result like the SQL ORDERBY statement does.

Using the Notes SQL driver via ODBC would be a workaround, but is not the best way as the OS needs to be touched and a extra driver needs to be installed and configured for this purpose.

Creating extra sorting foldes is a bad workaround for databases with lots of documents and huge view indexes and multi database applications. But of cause possible as a quick and dirty workaround for small purposes.

Sorting a documentcollection by building huge arrays or running a bubble sort function is time intensive and a server performance killer.

On the whishlist is still the option to have an optional db.search parameter, which allows to simply enter an array of fieldnames in the desired sorting order. The array would be used to sort the documentcollection. As the Notes SQL driver already is able to handle sortings via ODBC, this shows it can’t be impossible with notes to manage this task.

This function would eleminate a very fundamental disadvantage against a relational database system and would make a lot of Notes developers happy. Please search the forum for persons requesting this feature again and again since a long time.

If this function is in conflict with planned DB2 strategies of IBM please force the DB2 implementation, but if not possible to get a relational system as the new ODS, keep this request in mind for Notes7 please. A lot of notes faithfully developers would very appreciate and would still move to DB2 at a later stage.

Thank you very much.

Frank Strunzkus

2

Subject: Sorted collection with db.search Att: IBM

While the functionality you describe would be nice, you can do what you want now with a tiny bit of extra work. Check out the new NotesView properties and methods in ND6. You could easily construct a view using script, add the columns to have the sorting you need, grab the AllEntries and process as need-be.

All it would take is one function that you could re-use and change as you see fit. The only real disadvantage would be having to remove the view after you were done processing. This could be handled via nightly agent, another function, or (if you handled the view as a class) a delete() method.

3

Subject: RE: Sorted collection with db.search Att: IBM

Thanks you. Sounds like a good temporary workaround. I will try to check if this works in praxis.

4

Subject: RE: Sorted collection with db.search Att: IBM

The idea is unfortunately not working properly in praxis.

I have create a new class libary which dynamicaly creates a new view with sorted columns and selection formula. The view exactly is created like desired and sorts all documents like expected. When looking at the view everything optically looks fine. Running a script afterwards does result the documents in order like expected.

I have intensively tested several options, but during runtime of the creation script the sorting does not work. The new created view is unsorted. All documents are properly choosen by selection formula, but in no sorting order. The script needs to finish and I have to leave the database to update the view. Even view.refresh() does not help.

The idea of using a temporary created and afterwards deleted view to sort documents seem unfortunally not to work.