Get sorted results from DQL queries

NotesDominoQuery by now does not support sorting of the results that come out of a query. John Curtis demoed at DNUG46 in Essen how you can get your results sorted. He showed the code only for a second, so I needed to rewrite it from scratch.

The method that he showed leverages from the new ‘maintainOrder’ property that has been added in V10 to the NotesViewEntryCollection class.

But let us first take a closer look at what is needed to make the code work.

I have a small application where I store code snippets. I can categorize them and also test code locally or against a server.

In my sample, I want to get all documents from that application that have “DEMO” in the subject and output the subject in ascending order.
Next I want to get the documents category in descending order.

Both columns, Subject and Category need to be prepared for sorting. They do not neccessarily have to be sorted initially.

To set the sorting programmatically, I use the ‘Resortview’ method from the NotesView class.
Be aware that the columnName must be the programmatic name of the column.
Here is a sample how to use the ‘ResortView‘ method

Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim vec As NotesViewEntryCollection

Set db = session.currentDataBase
Set view = db.getView("Samples")

' Sort By Subject
Call view.Resortview("Subject", true)
Set vec = view.Allentries

' Sort By Category
Call view.Resortview("category", false)
Set vec = view.Allentries

vec will now contain all view entries sorted ascending by Subject and after the second ResortView it will contail all view entries by Category in descending order.

You can use the following code to print the result to the console

Private Sub printIt(vec As NotesViewEntryCollection, itemName As String)
	Dim ve As NotesViewEntry
	Dim doc As NotesDocument
	Dim item As NotesItem
	Dim s As String
	Set ve = vec.Getfirstentry()
	While (Not ve Is Nothing)
		s = "- no value -"
		Set doc = ve.Document
		Set item = doc.Getfirstitem(itemName)
		If (Not item Is Nothing) Then
			If (item.text <> "") Then
				s = item.text
			End if
		End If
		MsgBox s
		Set ve = vec.Getnextentry(ve)
	Wend
End Sub

Now lets add some code to query for all documents that have “DEMO” in the subject.

By now, DQL does not have the capability to build a query that uses CONTAINS. This will be added in a future version of Notes and Domino.

Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView

Dim vec As NotesViewEntryCollection
Dim ve As NotesViewEntry
Dim col as NotesDocumentCollection
Dim doc As NotesDocument
	
Dim query As String
query = "Subject >= 'DEM' AND  Subject < 'DEN'"
Dim dql As NotesDominoQuery
	
Set db = session.currentDataBase
Set view = db.getView("Samples")

Set dql = db.CreateDominoQuery()
Set col = dql.Execute(query)

Call view.Resortview("Subject", true)
Set vec = view.Allentries
Call vec.Intersect(col, true)
Call printIt(vec, "Subject")

Call view.Resortview("category", false)
Set vec = view.Allentries
Call vec.Intersect(col, true)
Call printIt(vec, "category")

I put the code into an agent and ran that agent from the console. Here is the output.

Happy coding!


java.lang.UnsatisfiedLinkError: lotus/domino/local/Database.NcreateDQuery()J

UPDATE 05-FEB-2019: Issue is being tracked under SPR # VRARB94KAQ

When executing db.createDominoQuery(); in a Java agent on the server, I see the following error message on the Domino console:

te amgr run "ec11.nsf" 'dql.java'
[021963:000035-00007F2BE8DFD700] 02/03/2019 05:41:29 AM AMgr: Start executing agent 'dql.java' in 'ec11.nsf'
[021963:000037-00007F2BE816F700] 02/03/2019 05:41:29 AM Agent Manager: Agent printing: Version: Release 10.0.1 November 29, 2018
[021963:000037-00007F2BE816F700] 02/03/2019 05:41:29 AM Agent Manager: Agent printing: Db Title: singultus's Directory
[021963:000038-00007F2BE816F700] 02/03/2019 05:41:29 AM Agent Manager: Agent error: Exception in thread "AgentThread: JavaAgent"
[021963:000039-00007F2BE816F700] 02/03/2019 05:41:29 AM Agent Manager: Agent error: java.lang.UnsatisfiedLinkError: lotus/domino/local/Database.NcreateDQuery()J
[021963:000041-00007F2BE816F700] 02/03/2019 05:41:29 AM Agent Manager: Agent error: at lotus.domino.local.Database.createDominoQuery(Unknown Source)
[021963:000043-00007F2BE816F700] 02/03/2019 05:41:29 AM Agent Manager: Agent error: at JavaAgent.NotesMain(JavaAgent.java:19)
[021963:000045-00007F2BE816F700] 02/03/2019 05:41:29 AM Agent Manager: Agent error: at lotus.domino.AgentBase.runNotes(Unknown Source)
[021963:000047-00007F2BE816F700] 02/03/2019 05:41:29 AM Agent Manager: Agent error: at lotus.domino.NotesThread.run(Unknown Source)
[021963:000035-00007F2BE8DFD700] 02/03/2019 05:41:29 AM AMgr: Agent 'dql.java' in 'ec11.nsf' completed execution

According to John Curtis (HCL) “… We haven’t seen this to date.”.

I have tested on different OS.

serv02: 
Red Hat Enterprise Linux Server release 7.4 (Maipo)
Linux serv02.fritz.box 3.10.0-693.el7.x86_64 #1 SMP Thu Jul 6 19:56:57 EDT 2017 x86_64 x86_64 x86_64 GNU/Linux
IBM Domino (r) Server (64 Bit) (Release 10.0.1 for Linux/64) 02/03/2019 05:48:40 AM
serv02 has been upgraded from V10.0
serv03:
CentOS Linux release 7.6.1810 (Core)
Linux serv03.fritz.box 3.10.0-957.1.3.el7.x86_64 #1 SMP Thu Nov 29 14:49:43 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
IBM Domino (r) Server (64 Bit) (Release 10.0.1 for Linux/64) 02/03/2019 05:49:15 AM (Community server)
serv03 is "fresh" install and not upgraded from earlier Domino versions.

Here is the code I used

import lotus.domino.AgentBase;
import lotus.domino.Database;
import lotus.domino.DominoQuery;
import lotus.domino.Session;

public class JavaAgent extends AgentBase {

	public void NotesMain() {

		try {
			Session session = getSession();
			System.out.println("Version: " + session.getNotesVersion());
			
			Database db = null;
			db = session.getDatabase(null, "names.nsf");
			System.out.println("Db Title: " + db.getTitle());
			
			DominoQuery dql = null;
			dql = db.createDominoQuery();

			dql.recycle();
			db.recycle();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

As it works on Domino 10.0.1 on WINDOWS, I investigated nlsxbe.dll and liblsxbe.so

nlsxbe.dll contains the function in question

Java_lotus_domino_local_Database_NcreateDQuery 0x0000000180017cd0 0x00017cd0 732 (0x2dc) nlsxbe.dll W:\Domino\nlsxbe.dll Exported Function

liblsxbe.so is missing the function. At least, it is not exported.

nm -D liblsxbe.so | grep createD

0000000000096a03 T Java_lotus_domino_local_Database_NcreateDocColl
0000000000095587 T Java_lotus_domino_local_Database_NcreateDocument
000000000012bf7d T Java_lotus_domino_local_DateRange_NrecreateDateRange
00000000000e33e1 T Java_lotus_domino_local_DateTime_NrecreateDateTime
0000000000110ad2 T Java_lotus_domino_local_DbDirectory_NcreateDatabase
00000000000d5d6d T Java_lotus_domino_local_Session_NcreateDateRange
00000000000da091 T Java_lotus_domino_local_Session_NcreateDateTime
00000000000d6bd1 T Java_lotus_domino_local_Session_NcreateDxlExporter
00000000000d6d71 T Java_lotus_domino_local_Session_NcreateDxlImporter
00000000000957a0 T Java_lotus_notes_Database_NcreateDocument
0000000000110d7e T Java_lotus_notes_DbDirectory_NcreateDatabase
00000000000d5f40 T Java_lotus_notes_Session_NcreateDateRange
00000000000da360 T Java_lotus_notes_Session_NcreateDateTime
U _ZN11XmlDocument14createDocumentEv
U _ZN11XmlDocument22cr
eateDocumentFragmentEv

The version and date seem to be OK.

ls -al liblsxbe.so
-rwxr-xr-x. 1 root root 14510400 Nov 29 07:48 liblsxbe.so

Perhaps someone else can confirm this behaviour. I have already created case #TS001863705 with HCL.


NotesDominoQuery – Find the needle in the haystack (LS)

Think about a big database with lots of documents in it and you want to find only one particular document. You can do that with FTSearch, or you can use a db.search with some formula.

As of V10.0.x, you also have DQL and the new NotesDominoQuery class.
The class is available in Lotsscript and Java.
I want to demonstrate in this sample, how you can find the needle in the haystack with DQL in Lotusscript.

My database has about 12.500.000 Documents. It is one of our customers database at midpoints. The amount of documents was created by accident. Some call it a bug. Anyway, the database is a good playground.

The code is typical for a LS developer. It initiates objects and stuff, assigns variables like our dqlTerm (line 6 see the similarity to the @formula, you would probably use with db.search? ), does a check, if the target database is open (line 9 ) and also has some basic error handling (17, 20-21). We will come to that later.

I am running the sample on the client. As of today , you cannot run a query client / server. I will show in another post, how you can run the query on the server and work with the results on the client. But that is another story.

public Sub foo() 
	Dim session As New NotesSession
	Dim db As NotesDatabase
	Dim col As NotesDocumentCollection
	Dim dqlTerm As String
	dqlTerm = "form = 'frm.rules.device.rule' And rule_unid = '99A242AAB69B5BB9C1257FFC005DE6C4'"
	
	Set db = session.Getdatabase("","trul-big.nsf", False)
	If db.Isopen Then
		
		Dim dql As NOTESDOMINOQUERY
		Set dql = db.CreateDominoQuery()
		
		Dim parse_result As String
		parse_result = dql.parse(dqlTerm)

		If LCase(parse_result) = "success" Then
			Set col = dql.Execute(dqlTerm)
			MsgBox dql.Explain(dqlTerm)
		Else
			MsgBox parse_result
		End If
		
	End If
End Sub

Before you run the code and try to search such a huge amount of data, you need to set some notes.ini variables.

QUERY_MAX_DOCS_SCANNED=13000000 
QUERY_MAX_VIEW_ENTRIES_SCANNED=13000000

By now, there is no other way to increase the number of documents. There are setters in the NotesDominoQuery class, but those setters are broken. This is a known issue and HCL is working on a solution.

When we now run the code, we will get the following result

It took 51.3 secs to find 110642 documents that use the form “frm.rules.device.rule” and zero to none secs to find 1 document in that resultset.

Would it be faster or slower, if we modify our query to use only the rule_unid field and search over the entire set of documents?

dqlTerm = "rule_unid = '99A242AAB69B5BB9C1257FFC005DE6C4'"

Here is the result

32.2 secs to find the needle in the haystack. Use this kind of single field search, if you are sure that the field “rule_unid” is only used on one form.

But even in the case that the field is used on another form; to filter the resulting NotesDocument collection afterwards is much faster than the AND dqlTerm from the original code.

Maybe, you already have a view in your application where the rule_unid is in a sorted column.

Then we can use a modified dqlTerm to find the document in that view.

Again, let’s change our dqlTerm a little bit

dqlTerm = "'all'.rule_unid = '99A242AAB69B5BB9C1257FFC005DE6C4'"

And here is the result

THAT is pretty cool, isn’t it? 5.6 msecs to find the needle in the haystack.

Can it be even faster? I think no, but let us take another look at the code that does the query.

        Dim parse_result As String
        parse_result = dql.parse(dqlTerm)
 
        If LCase(parse_result) = "success" Then
            Set col = dql.Execute(dqlTerm)
            MsgBox dql.Explain(dqlTerm)
        Else
            MsgBox parse_result
        End If

We can safely remove all of our “Error handling”, that means lines 14-17 and 20-22. Why? Because dql.Excecute(dqlTerm) does the parse before executing the query.

So we end up with the following code

public Sub foo() 
	Dim session As New NotesSession
	Dim db As NotesDatabase
	Dim col As NotesDocumentCollection
	Dim dqlTerm As String
	
	dqlTerm = "'all'.rule_unid = '99A242AAB69B5BB9C1257FFC005DE6C4'"

	Set db = session.Getdatabase("","trul-big.nsf", False)
	If db.Isopen Then
		Dim dql As NOTESDOMINOQUERY
		Set dql = db.CreateDominoQuery()
		Set col = dql.Execute(dqlTerm)
		MsgBox dql.Explain(dqlTerm)
	End If
End Sub

When we run the code, we get

Keep in mind that the numbers vary a litte bit from run to run.

If we now modify our dqlTerm and add an error, we are shown a nice dialog box explaining the error in detail.

That’s all for today, I hope you find this information useful.


NotesDominoQuery sample

I have put together a small sample to demonstrate how to use NotesDominoQuery from LotusScript.

I created a new Class DQLWrapper. A little bit over the top, I know.

%REM
	Library 10010.dql
	Created Dec 30, 2018 by Ulrich Krause/singultus
%END REM
Option Declare

%REM
	Class DqlWrapper
%END REM
Public Class DqlWrapper
	
	m_query As String
	m_session As NotesSession
	m_db As NotesDatabase
	m_ndq As NotesDominoQuery
	
	%REM
		Sub New
	%END REM
	Public Sub New(strDbFilePath As String)
		Set me.m_session = New NotesSession
		Set me.m_db = me.m_session.Getdatabase("",strDbFilePath, False)
		If ( me.m_db.Isopen ) then
			Set me.m_ndq = me.m_db.Createdominoquery()
		Else
			' // do some error handling
		End if
	End Sub

	%REM
		Public function executeQuery()
	%END REM	
	Public function executeQuery() As NotesDocumentCollection
		If ( me.m_query <> "" ) then
			Set executeQuery = me.m_ndq.Execute(me.m_query)
		Else
			Set executeQuery = nothing
		End If
	End Function

	%REM
		Public Function explainQuery()
	%END REM	
	Public Function explainQuery() As String
		If ( me.m_query <> "" ) Then
			explainQuery = me.m_ndq.Explain(me.m_query)
		Else
			explainQuery = ""
		End If
		
	End Function
	
	%REM
		Public Function explainQuery()
	%END REM	
	Public Function parseQuery() As String
		If ( me.m_query <> "" ) Then
			parseQuery = me.m_ndq.parse(me.m_query)
		Else
			parseQuery = ""
		End If
		
	End Function	

	%REM
		Property query
	%END REM	
	Public Property Set query As String
		me.m_query = query
	End property
	
End Class

The query itself is executed from an agent that runs on the server. At the moment it is not possible to run a query client/ server.

Here is the code for the agent

%REM
	Agent dql.execute
	Created Dec 30, 2018 by Ulrich Krause/singultus
%END REM
Option Public
Option Declare
Use "10010.dql"

Sub Initialize
	Dim query As String
	Dim col As  NotesDocumentCollection
	query = "firstname = 'Ulrich' And lastname = 'Krause'"
	
	Dim dql As New DQlWrapper("names.nsf")
	dql.query = query
	
	If (  LCase(dql.parseQuery()) ="success" ) Then
		
		Set col = dql.executeQuery()
		MsgBox "QRY returns # docs: " + CStr(col.count)
		
		If ( col.count > 0 ) then
			Dim doc As NotesDocument
			Set doc = col.Getfirstdocument()
			MsgBox "UNID of first doc: " + doc.Universalid
		End if
	Else 
		
		MsgBox dql.explainQuery()
	End If
	
End Sub

You can now start the agent from the server console. You will get the number of documents for this query and the UNID of the first document found.

te amgr run "ec11.nsf" 'dql.execute'
[0DFC:001F-0FFC] 30.12.2018 13:49:10 AMgr: Start executing agent 'dql.execute' in 'ec11.nsf'
[0DFC:001F-0FFC] 30.12.2018 13:49:10 Agent Manager: Agent message: QRY returns # docs: 1
[0DFC:001F-0FFC] 30.12.2018 13:49:10 Agent Manager: Agent message: UNID of first doc: D8436D0F4E546BA3C12573FE0070AE88
[0DFC:001F-0FFC] 30.12.2018 13:49:10 AMgr: Agent 'dql.execute' in 'ec11.nsf' completed execution

If your query contains errors / is not understandable, you will see an output similar like this on your console

[0DFC:0020-11D0] 30.12.2018 13:59:45   Agent Manager: Agent 'dql.execute' error: Domino Query execution error:   Query is not understandable -  syntax error     - processing or expecting operator (=, <, <= …) token syntax    (Call hint: OSCalls::OSLocalAllc, Core call 
0) firstname = 'Ulrich' And lastname IS 'Krause' …………………………….^……….. ****
[0DFC:0020-11D0] 30.12.2018 13:59:45 AMgr: Agent 'dql.execute' in 'ec11.nsf' completed execution