Hic sunt leones ("Here be lions")
Strange title for a post? Keep on reading...
(see a simple explanation from Wikipedia: Hic sunt leones)
As already stated in another post, I'm working on nsfdb2 using Domino R8 beta 3 for Windows, IBM DB2 v9.1 for Windows (local configuration), and I'm also testing nsfdb2 with federated data on i5 ( = iSeries= AS/400) to be used in Query Views (QV), both alone or mixed with DAV data.
I'm getting addicted to it! I like it
Ok, don't overuse nsfdb2, there are some best practices you should know (I will post about it soon, I hope), but consider it as a useful technology, just another "arrow for your bow".
After testing DAV and QV on both a Notes client and web browsers, I began doing two major tests:
1) using LotusScript (LS) statements to retrieve data provided by QV
2) programmatic Query Views, that is using LS to customize a QV to set a SQL statement (a string) defined at run-time by the programmer (note: a standard QV can be customized using @formula statements with @prompt() , etc. when openend by a Notes client user...)
To retrieve data from any QV, see the following LS sample that take advantage of classes called NotesViewEntryCollection and NotesViewEntry:
Set view= db.GetView("MYQUERYVIEW")
Set nvec= view.AllEntries
Set nve= nvec. GetFirstEntry
Do Until nve Is Nothing
sRow = ""
For i= 0 To n
sRow= sRow & " | " & nve.ColumnValues(i)
Set nve= nvec.GetNextEntry(nve)
The 2nd answers is tough, it involves 2 smart and willing IBM engineers.
First I read the docs but I could not find any info about LS and QueryViews, so I thought:
"maybe QV are just like classic Notes views... let's build a QV in the Designer client and later, at run-time, my LS code will modify the SQL statement (see below)
Set db= sess.CurrentDatabase
Set view= db.GetView("MYQUERYVIEW")
f= |SELECT * FROM WINSFA2.YSFCLS00F WHERE CSKCLZ='| & Ucase(sKey) & |'|
NO WAY! RUN-TIME ERRORS!
I used also used the LS editor auto-complete feature to see if some new R8 classes were available for QV, that was not printed the R8 Help for time sake....
I found view.IsQueryView property (read-only) but could NOT find any property to set SQL statements for QV: the editor is lying.
I posted a message in the R8 beta forum and a well-known IBM engineer, John Curtis, answered that I could use the property view.SelectionQuery (undocumented, I still believe).
I tried to use that new/unknown property:
f= |SELECT * FROM WINSFA2.YSFCLS00F WHERE CSKCLZ='| & Ucase(sKey) & |'| view.selectionQuery= f ' <- here the run-time error
my Notes R8 client crashed! I was disappointed but gave my feedback to John Curtis, both in the forum and by e-mail: unfortunately John is really busy but he answered that someone in the Domino team would help me.
After reading a very old (Jan/Feb 2006!) but informative article onTheView magazine (yellow cover), witten by Erin Dame and his colleague Gary Rheaume (IBM engineers) , I decided to write to Erin asking for help.
Today I received the right answer from Erin: at first I was doubtful about the tip, but I tried it as suggested by Erin Dame (IBM):
f= |"SELECT * FROM WINSFA2.YSFCLS00F WHERE CSKCLZ='| & Ucase(sKey) & |'"|
that is, putting double quotes before and after the SQL statement, as part of the string itself: the same way if I would do to define the SQL statement in the QV design element (using @FORMULA language) in the Designer client : IT WORKS!
I'm continuing asking info in the R8 beta forum about LS new classes for nsfdb2 feature:
to date, R8 beta 3 Designer Help does not mention any selectionQuery property for NotesView (please correct me whether I'm wrong, really).
Googling around (query-> notes selectionquery ) I found this:
- www.notesninjas.com , ninjas?! I'm really worried...
- my posts among R8 beta forum and blogs ;-)
- My R8 forum post so far on R8 beta 3 forum
- nsdb2faq (see comments too)