Be aware that there is NO type check in the Lotusscript for the REPLACE parameter. It is possible to pass i.e. 42 to the method, which would work without any hint that 42 is not a valid value for true or false.
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.
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.
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 _ZN11XmlDocument22createDocumentFragmentEv
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.
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.
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?
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.
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.
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
