12/07/2007

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...)


ANSWERS 1):
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 = ""
v= nve.ColumnValues

n= Ubound(v)

For i= 0 To n

sRow= sRow & " | " & nve.ColumnValues(i)

Next

Print sRow

Set nve= nvec.GetNextEntry(nve)
Loop


ANSWERS 2):
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")

sKey= "0B"

f= |SELECT * FROM WINSFA2.YSFCLS00F WHERE CSKCLZ='| & Ucase(sKey) & |'|

view.SelectionFormula= f

Call view.Refresh

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:
sKey= "0B"
f= |SELECT * FROM WINSFA2.YSFCLS00F WHERE CSKCLZ='| & Ucase(sKey) & |'| view.selectionQuery= f ' <- here the run-time error
Call view.Refresh


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!

Epilogue
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 ;-)


Stay tuned,
Cristian D'Aloisio



References
- My R8 forum post so far on R8 beta 3 forum
- nsdb2faq (see comments too)

3 comments:

nick wall said...

This is great stuff. I have not had time to work on the DB2 piece yet. If you have time, please continue to post your results. I would be very interested to follow your progress.

I do lots of integration between Domino and an iSeries (was an AS400 but we upgraded last week). currently I sync order data\ inventory, etc using LCLSX, but if I used the pieces you are testing, I think a lot of complexity could be removed. I have lots of remote sales reps who need the data off line...If you take a local replica of the database, and it's a database that has a queryview pulling data from DB2, does it pull "stub" documents locally, like virtual documents?

I am doing quite a lot with composite applications, so wrapping all this together makes ND8 pretty powerful.

Thanks again for sharing your efforts.

Tommy Valand said...

Thank you for this!

As with nick, I/the company I work at really want to test out NFSDB2, but we don't want to do the pioneering work. Stuff like this helps speed up the process.

Cristian D'Aloisio said...

@nick:
as far I know, local replicas are not supported, I mean data snapshots locally stored, you must be connected to Domino AND DB2 server.

Local data snapshots could be useless if you need "up-to-date info (old data...) or hard to manage if data are really huge:

myt advice, let your Notes users be connected to your company and be aware that QV are really fast only if your sql statement query narrows just the data your user really need ;-)
Of course you can use a web browser too to take advantage of nsfdb2-enabled Notes applications...