Jan 20

Working with WebSQL in an HTML5 mobile app

I wrote some days ago that I now work on a BlackBerry 10 app using BlackBerry WebWorks, which is basically Apache Cordova (Phonegap).

The app needs to read data from JSON sources and store it locally, so that the app works offline. So I need local storage, and although the WebSQL specification came to an end, it’s supported in WebWorks, Chrome and other platforms.

Working with WebSQL is relatively straightforward because in the end it’s the well known SQLite in the backend. But, you have to deal with callbacks. For example, to write stuff you’re using this kind of code:

// size in bytes, for example 1024*1024*1
// params may be an array of data for the sql statement
var size = 1024*1024*1;
var db = openDatabase("mydb", '1.0', "mydb", size);
var f = function(tx) {
  tx.executeSql(sql, params, function(transaction, result) {
	// success, do something with the result
  }, function(tx, e) {
     // failure, error is in e.message

So there are two callbacks, one in case of success and one in case of error. If you want to execute multiple statements at once, you can put them all into one transaction. In that case you have to do some work to handle the results and errors. Something like this:

f = function(tx) {
	var hasError = false;
	var errors = [];
	var results = [];
	_.each(sql, function(singlesql) {
		tx.executeSql(singlesql, params, function(transaction, result) {
		}, function(tx, e) {
			// failure
			hasError = true;
	// do something with the result

(Note: _.each() is a method of underscore.js framework.)

Unfortunately this does not work reliable. Do you spot the error?

The function iterates though an array of sql statements, and each statement is being executed and in the result callback the result of that statement is added to a result array.

But what happens if you want to use the result array right after the _.each() loop? You’re using the result array before all the callbacks from the sql statements has been executed! So it may be null or contains only some entries, but not all.

You need to work on the results only when all sql statements have been executed:

f = function(tx) {
	var hasError = false;
	var errors = [];
	var results = [];
	var len = sql.length;
	for (var i = 0; i < len; i++) {
		tx.executeSql(sql[i], params, function(transaction, result) {
			if (i == (len-1)) {
				// do something with the result HERE!
		}, function(tx, e) {
			// failure
			hasError = true;

In a real world app that may get cumbersome when you do lot of work with WebSQL. Therefore I started to write a framework, just like I always do when things get complicated 🙂

What about this code:

b = new JBUDatabase("mydb");
// create table if it does not exist yet
db.createTable("tickets", [ {
	name : 'id',
	type : 'TEXT'
}, {
	name : 'title',
	type : 'TEXT'
} ]);
// create a demo array of data for 100 tickets
var data = [];
for (var i = 1; i < 100; i++) {
	data.push(["ID "+i,"ticket title "+i])
// write the data to the WebSQL database
db.writeEntries("tickets", data);

All the WebSQL stuff packaged in a simple to use API. Much more clear, isn't it?

Oct 21

First steps and issues with Titanium, DominoToGo and Blackberry 10

Today I started with Blackberry 10 development using Appcelerator Titanium and DominoToGo. Setup of the development environment was easy since there is a good guide in the Titanium documentation.

There are a few things to watch out for, though:

  • Make sure you follow the guide to create a debug token. That’s NOT the BlackBerry ID token created in the BlackBerry Deployment Setup Wizard in the Momentics IDE! See Momentics IDE – Properties – BlackBerry – Signing to create a debug token. It will create a *.bar file which you can use in Titanium’s Studio properties.
  • To access the file system of the BlackBerry simulator, use a ftp client and log into the IP that is displayed in the lower left corner of the simulator. Use “devuser” as user and passwort.
  • In BlackBerry’s file system the app data is stored in /accounts/1000/appdata/<app id>.
  • Do not switch off development mode in the simulator! If you ever switched that off, you need to enter a device password in order to enable development mode again. And if you did that, you cannot use the standard run command of Titanium Studio, since it doesn’t use the password you may have set in the run configuration.
    There is a workaround for that, too, using CLI in terminal. But it’s annoying, so better don’t touch the development mode switch in the first place.

The most recent version of DominoToGo has issues on BlackBerry 10 because Appcelerator simply didn’t implemented some features of the Titanium SDK for BlackBerry yet. One missing functionality is a file stream, which DominoToGo uses when data downloaded from Domino is huge (multiple megabytes). So far I’m using in-memory processing instead of file streams as a workaround.

The SQLite database implementation is missing the ‘rowsAffected’ property which I’m using in some database operations. I have to find a workaround for that, too.

I’m sure there will be more issues I will come across in the next weeks. But the good news is that a lot of important functionality is working just fine on BlackBerry 10, such as HTTP connectivity, reading and writing files and most database operations.


Feb 17

Adding a custom button in an iNotes appointment, not in the view – possible?

Note: this post has been migrated from another blog. Some links may be broken.

You might know you can create a iNotes/Forms85_x.nsf file which contains a subform named “Custom_JS_Lite”.
In that subform you can implement a JavaScript function as follows:

function yn() {
var selected_docs = API_GetSelectedDocs_Lite();
// do somthing with selected_docs

function Custom_Scene_Actions_Lite(s_MenuID) {
        // checkActionIDs( s_MenuID );
        if (s_MenuID == “e-actions-calendarview-regular” || s_MenuID == “s_Appointment”) {
                var a = [{title:”My custom button”, find_id: “trash”, id: “yn1″, before: true, action:”yn{}”, help_text:”call the yn() function” }];
                addActionsLite( s_MenuID, true, a );

Then you activate the Forms85_x.nsf by setting a notes.ini parameter in Domino server:

set configuration iNotes_WA_FormsFiles=iNotes/Forms85.nsf,iNotes/Forms85_x.nsf

and issue two commands on the console:

dbcache flush
tell http inotes flushforms

In my case, that works fine in the calendar view. Now the question: this has been possible for appointment documents in previous Notes releases, too. But it does not work anymore in 8.5, the menuID “s_Appointment” does not exist anymore.
The above code should add the button to appointment documents, too, but it does not.

Does anyone know if it’s still possible to add custom buttons to iNotes appointment documents? And if so, how?

Nov 09

Domino SVN Plugin just saved my life.

Note: this post has been migrated from another blog. Some links may be broken.

I just tried the SVN plugin for Domino Designer and some hours ago, it just saved my life:

I worked on an XPages app, from which I have several replicas on serveral other servers, and for sure Domino is included in the Backup. I changed some code and needed to restart Domino. Unfortunately, something made Domino hang during shutdown, so I killed it using nsd.
Then I started Domino again and while it was in the transaction log recovery phase we had a power outage! Unbelievable, last outage I know of was 10 years ago or so.

Neverthelless, that was a bad thing. Some seconds later the power was there again and the Domino server came back to live, but it had to do a fixup of my application and found some of my design elements corrupt and removed them. Ouch.

All of my replicas were some hours old and didn’t had my recent changes. So, my changes were lost.

At last that I thought. Until I remembered that I just installed the SVN plugin which creates an on-disk version of the project and syncs every change from the NSF to the on-disk project automatically… so I looked in the on-disk project using the Navigator view in Domino Designer and voilá, all my changed design elements were there!

I only needed to open them in a text editor, create the design elements new in the NSF and copy the code into them. Problem solved, no loss of work at all.

So, think of the SVN plugin as one more layer of backup, too.

Jul 23

Bug with encrypted documents and LotusScript NotesDocument.isEncrypted property – what do you think?

Note: this post has been migrated from another blog. Some links may be broken.

I found something by accident: let a collegue send you an encrypted mail.
Then write a LotusScript agent like this:

Dim session As New NotesSession
MessageBox “Selected document is encrypted: “+CStr(session.currentDatabase.Unprocesseddocuments.Getfirstdocument().isEncrypted)

Now perform the following two tests:

1.) Select the encrypted mail in the inbox and execute the agent. You will see that the .isEncrypted property is FALSE.

2.) Open the mail, then execute the agent against the open UIDocument. You will see that the .isEncrypted property is TRUE (as it should be).

From my point of view, this means:

– As soon as you access an ecrypted document with LotusScript and you got the handle to the document from a view, the document will be decrypted. If you save the document then, it will be saved DECRYPTED. You need to call .encrypt() by yourself again.

– You cannot test if a document is encrypted or not at all if you got the handle to the document from a view.

– The behaviour is different wether you got the document from the view or an open UIDocument.

I tested this with 8.5.1 and 8.5.2CD5. Can anyone confirm this with other clients? What do you think about this?

Jun 04

Why Flash is oldschool and HTML5 is the raising star. Cool Demo.

Note: this post has been migrated from another blog. Some links may be broken.

You know that Apple don’t like Flash anymore and doesn’t include it in the iPad. Personally, I never liked Flash in the first place because it needs a non-standard plugin in the browser, and I can never be sure that the visitor has the most recent version of that plugin. Same goes to Microsoft Silverlight; I know that it’s awesome and one can do incredible cool things with that technology, nevertheless, it needs a plugin, so I would never use it for public websites / webapps.

HTML5 seems to be the way to go. And Apple just published a demo what HTML5 can do.

Note, that demo only runs in the Safari browser. Most other browsers should be able to do the same, but I think Apple want to show how nice all that stuff works on the iPad and therefore made it Safari only.

Here is the demo: http://www.apple.com/html5/

May 25

Optimized an agent’s runtime from >30 minutes to less than 30 seconds

Note: this post has been migrated from another blog. Some links may be broken.

I had that agent in one application… the app contains about 300.000 documents, and some of them needs to be checked multiple times a day for events (for example, if they are expired). The agent doing this check needed over 30 minutes to run, and due to agent manager’s configuration was always terminated after 30 minutes.

Pretty bad performance, isn’t it? Well… not anymore, I’m down to less than 30 SECONDS now, only by re-engineering the logic and one view.

There were several performance killers in that agent, and all of them based on one very common problem: using time/date functions in a view or using the NotesDatabase.search() instead of a ftsearch().

Every Notes dev should know that using time/date functions in a view, either in the select or column formula, is a very bad idea. Granted, there are times where using that technique cannot be avoided, or where this does not cause severe performance problems. But in general, it’s a bad idea and if possible, code should be changed so that there is no time/date function used in a view.

In my case, the agent did the following:

1.) Using a NotesDatabase.search() with time/date functions three times (!) to search for a subset of documents, only to remove one item from them.

2.) Refreshing a view which uses multiple time/date functions, then iterating over all documents of that view.

The three NotesDatabase.search() calls alone took 20 minutes or so. Boy, what a bad architecture. Oh, and yes, I have to take all the shame on myself… I wrote that agent some years ago myself. In that time, the application didn’t contained many documents, and I was not aware that these techniques could create such a performance problem 🙂

So, I managed to remove all NotesDatabase.search() calls and all time/date functions from the view. Now, the agent just iterates over all documents in that view. For every documents some time/date checks are performed and then, when the conditions are met, the event is fired.

The view contains many more documents now, and the agent iterates over many more documents – but still that’s WAY faster than before.

Remember: iterating over lots of documents in a view is much faster than using time/date functions in that view.

May 10

Speed up performance when you do mutiple document.removeFromFolder() or document.putInFolder()

Note: this post has been migrated from another blog. Some links may be broken.

this should be a no-brainer for all LotusScript experts, but I just came across it and thought I can share it: I have an agent which runs through all documents of a view, and removes each document from a folder and puts it in another folder, depending on some criterias.

The loop basically looked like this:

while (not doc is nothing)
if (...) then
  call doc.removeFromFolder("somefolder")
  call doc.putInFolder("anotherfolder")
end if

works fine,  until you have more than a couple of hundred documents.
In my case, I was faced with over 100.000 documents, which caused the agent to take a loooooooong time to run (or to be exact: it was terminated by the amgr after 15 minutes).

Solution for this problem is to collect all documents that should be removed from a folder or put into a folder in a NotesDocumentCollection, and remove/put them all at once using NotesDocumentCollection.removeAllFromFolder() / NotesDocumentCollection.putAllInFolder().

The new loop likes like this:

Dim dc as notesDocumentCollection
set dc = session.currentDatabase.createDocumentCollection() 'works for Notes 8 and higher

while (not doc is nothing)
if (...) then
   call dc.addDocument(doc)
end if

call dc.removeAllFromFolder("somefolder")
call dc.putAllInFolder("anotherfolder)"

Pretty simple and runs so much faster.

P.S.: if you are working with really many document you may want to “flush” the DocumentCollection every 1000 documents or so, so that in case the agent breaks down you don’t loose all the work.

Jan 26

follow up to: sorted ftsearch results in XPages (with code!)

Note: this post has been migrated from another blog. Some links may be broken.

This is a follow up to my previous post about sorted ftsearch results, and it is inspired by ideas Tommy Valand wrote about.

The problem with my previous approach was, that I could not cache the result. So while using a pager, or on re-sorting, all the expensive work (doing the ftsearch, getting all NotesViewEntries) was done over and over again. I could not cache the result, since the TreeMap contained Notes objects which are recyled after the page is delivered to the browser.

Now I created a “fake” NotesViewEntry object which only stored Java objects. So after doing the ftsearch, I create one fake NotesViewEntry for each real NotesViewEntry, and store that in the TreeMap. Then I get the sorted list of values (as Array) out of the TreeMap and store that into the viewScope.

On pagination I simply return that cached Array, and on re-sort I use that Array to re-sort the values. The ftsearch and looping over the NotesViewEntries is done only exactly once during the lifetime of the page.

I didn’t implement the descending sort order yet, but that shouldn’t be hard since the TreeMap.values() collection can be reversed by one single method call.

So, here is my current code as RTF download (sorry for that, but when I tried to put the code in this blog post, I reached the size limit of blog posts…): ynNotesView code.
The code contains lots of comments and should be easy to understand.

I’m happy about any comments how to improve that code further.