How to sort a NotesDocumentCollection in Lotusscript

17 Comments

This Lotusscript function sorts a document collection on one or multiple fields.

I have previously used several other algorithms that use a view to sort the collection, these however have the drawback that they become very inefficient (i.e. slow) as the number of documents in the view used for sorting grows. The solution presented below does not have this problem.

It has been developed and tested in Lotus Notes 6.5.3 but should work in all ND6 (release 6) and possibly earlier Lotus Notes releases too (if you test this successfully or unsuccessfully write a comment to this post and everyone will know).

Example of use:

Dim fieldnames(0 To 2) As String
fieldnames(0) = "SKU"
fieldnames(1) = "OrderDate"
fieldnames(2) = "Client"
Set collection = SortCollection (collection, fieldnames)

Function to sort DocumentCollection:

Function SortCollection(coll As NotesDocumentCollection, fieldnames() As String) As NotesDocumentCollection

' ------------------------------------------------
' --- You may use and/or change this code freely
' --- provided you keep this message
' ---
' --- Description:
' --- Sorts and returns a NotesDocumentCollection
' --- Fieldnames parameter is an array of strings
' --- with the field names to be sorted on
' ---
' --- By Max Flodén 2005 - http://www.tjitjing.com
' ------------------------------------------------

Dim session As New NotesSession
Dim db As NotesDatabase
Dim collSorted As NotesDocumentCollection
Dim doc As NotesDocument
Dim i As Integer, n As Integer
Dim arrFieldValueLength() As Long
Dim arrSort, strSort As String
Dim viewname As String, fakesearchstring As String

viewname = "$All" 'This could be any existing view in database with first column sorted
fakesearchstring = "zzzzzzz" 'This search string must NOT match anything in view
Set db = session.CurrentDatabase

' ---
' --- 1) Build array to be sorted
' ---

'Fill array with fieldvalues and docid and get max field length
Redim arrSort(0 To coll.Count -1, 0 To Ubound(fieldnames) + 1)
Redim arrFieldValueLength(0 To Ubound(fieldnames) + 1)
For i = 0 To coll.Count - 1
Set doc = coll.GetNthDocument(i + 1)
For n = 0 To Ubound(fieldnames) + 1

If n = Ubound(fieldnames) + 1 Then
arrSort(i,n) = doc.UniversalID
arrFieldValueLength(n) = 32
Else
arrSort(i,n) = "" & doc.GetItemValue(fieldnames(n))(0)
' Check length of field value
If Len(arrSort(i,n)) > arrFieldValueLength(n) Then
arrFieldValueLength(n) = Len(arrSort(i,n))
End If
End If

Next n
Next i

'Merge fields into list that can be used for sorting using @Sort function
For i = 0 To coll.Count - 1
If Not strSort = "" Then strSort = strSort & ":"
strSort = strSort & """"
For n = Lbound(fieldnames) To Ubound(fieldnames) + 1
strSort = strSort & Left(arrSort(i,n) & Space(arrFieldValueLength(n)), arrFieldValueLength(n))
Next n
strSort = strSort & """"
Next i

' ---
' --- 2) Sort array
' ---
arrSort = Evaluate("@Sort(" & strSort & ")")

' ---
' --- 3) Use sorted array to sort collection
' ---
Set collSorted = coll.Parent.GetView(viewname).GetAllDocumentsByKey(fakesearchstring)

For i = 0 To Ubound(arrSort)
Set doc = db.GetDocumentByUNID(Right(arrSort(i), 32))
Call collSorted.AddDocument(doc)
Next i

' ---
' --- 4) Return collection
' ---
Set SortCollection = collSorted

End Function

(This article is previously published and has been moved to this blog)

[Update: Instead of using Evaluate and @Sort in section 2 in the code you can of course use any of your favourite sort routines. Or search the Net for one, there are many out there.]

How to access MySQL from .NET (ASP.NET using VB.NET)

2 Comments

[Update: Go to this new post instead on how to use MySQL ADO.NET drivers]

This is a three step description of what you need to do to access a MySQL server from VB.NET.

I used Microsoft Windows Server 2003 with Microsoft Visual Studio 2003 Professional and MySQL Server 4.1.7.

It is possible to use ODBC, OLEDB and native .NET providers to access MySQL from .NET. However the ODBC driver (MyODBC) is the only official driver (ie from MySQL) provided for free. The drawback is that ODBC gives the worst performance of the three. More info on this can be found at dev.mysql.com/tech-resources/articles/dotnet.

Ok, so this is how I did it:

1) Download and install the MyODBC driver. Latest version can be found at dev.mysql.com/downloads/connector/odbc. Current version, used in this example, is 3.51.

2) Start a new or use an existing ASP.NET (using VB.NET) project in Visual Studio. You need to import the System.Data.Odbc namespace to the project. From the “Project” menu select “Properties”. Under “Common Properties” and “Imports” type “System.Data.Odbc” in the “Namespace” box and click “Add Import”.

3) Below is some example code to get started. More examples can be found at dev.mysql.com/doc/mysql/en/odbc-net-op-vb-cp.html.

Dim MyConString As String = _
"DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=localhost;" & _
"DATABASE=testdb;" & _
"UID=root;" & _
"PASSWORD=rootpassword;" & _
"OPTION=3"

Dim MyConnection As New OdbcConnection(MyConString)
MyConnection.Open()

Dim MyCommand As New OdbcCommand
MyCommand.Connection = MyConnection

Happy programming!

(This article is previously published and has been moved to this blog)

PHP5 with IIS6 on Windows Server 2003 in five easy steps

230 Comments

[Update: Follow this link if you are looking for an updated tutorial on PHP5 with IIS7 on Windows Server 2008]

How to install and configure PHP5 with IIS6 on Windows Server 2003 in five easy steps.

I thought installing PHP would be as easy as just using the install program. Well it wasn’t… But by learning from my mistakes it can be almost that easy.

Just follow these five easy steps on how to install and configure PHP 5 on a Windows Server 2003 running IIS – after trying the manual and several guides and tutorials found on the net (of which some were very long and very complicated and some were short and easy but partially incorrect) this is what it all came down to. Enjoy.

This is tested on PHP 5.0.4, IIS6 on Windows Server 2003 SP1. Be aware that IIS is not automatically installed with all editions of Windows Server 2003, this guide assumes that IIS 6 and Windows Server 2003 is already up and running on your server.

1. Download PHP at http://www.php.net/downloads.php. Make sure you dowload the “zip package”, the installer package won’t work.

2. Extract the downloaded zip file to a directory of choice on your harddrive. The rest of this guide will assume you are using C:\PHP

3. Add C:\PHP to your path. From the Start menu, right click My Computer and select Properties. From the Advanced tab click the Environment Variables button. Under System Variable find Path and click Edit. At the end of what is already present in Variable Value add a semicolon (;) and then C:\PHP.

4. Configure IIS. Open the Internet Information Services (IIS) Manager from Administrative Tools (found directly in the Start menu or in the Control Panel)

a) Web Service Extension. Click down to the Web Service Extension folder. Right click the folder and select Add New Web Service Extension. Set Extension Name to .PHP and add C:\PHP\PHP5ISAPI.DLL to Required Files . Check Set Extension Status To Allowed.

b) Web Sites. Click down to Web Sites. Right click the folder and select Properties. From the Home Directory tab click the Configuration button. Click Add to add an Application Extension. Enter C:\PHP\PHP5ISAPI.DLL as Executable and PHP as Extension. Leave the rest as default and click Ok.

5. All set! To test your PHP installation simply create text a file with the php extension, eg. test.php. Add the following three lines of text to it and then save it to your web site directory, eg. C:\INETPUB\WWWROOT. Then use your browser to read the file, eg. http://localhost/test.php

<?php
phpinfo();
?>

Other things you might want to consider:

– Some tutorials state that you need to restart the World Wide Web Publishing Service after having installed and configured php. I haven’t needed to (ie everything worked fine without restarting the service) but if you do it is found under Services in the Control Panel.

– As you start using PHP, in the not too distant future you will probably need to make some changes to php.ini. PHP works fine without the php.ini file but you really should have one and it should be in your Windows directory. Copy C:\PHP\PHP.INI-RECOMMENDED to C:\WINDOWS\PHP.INI

– If you are going to use MySQL you will need to make sure to uncomment the line “extension=php_mysql.dll” in php.ini and copy C:\PHP\LIBMYSQL.DLL to C:\WINDOWS\SYSTEM32 (Simply setting the PATH won’t work as this is apparently hard coded in PHP5). If you don’t follow these steps you will get an error message similar to this: “Call to undefined function mysql_connect”

– Read the manual at http://www.php.net/manual/en/install.windows.php

[Updated: If you have any problems, make sure you read the comments to this post. You will find both tips and questions there. If you know the answer to someone’s question please answer it.]

(This article is previously published and has been moved to this blog)

Programmer’s Frustration

No Comments

I just wanted to share this great story by Damien Katz, a former programmer at Iris/Lotus. I first read it about a year ago and came across again today. It’s about Damien as a novice C++ programmer rewriting one of the core components of Lotus Notes/Domino – the formula engine, originally written by Ray Ozzie.

I used to work a lot with Lotus Notes and remember when the new Formula engine was released, it was a big thing. If you have had any programming experience and ever tried to do something with someone elses code I think you will enjoy this – even if you’ve never touched Lotus Notes. It’s a kind of a programmer’s “hero story”. Make sure you read the comment by Ray Ozzie too.

Bonus: Here’s a Lotus interview – from back in the days – about the new formula engine.

Qtek 8310 Smartphone and Windows Media Player Sync

No Comments

I’ve had my Qtek 8310 Smartphone for a couple of months now and I thought I’d setup Windows Media Player (WMP10) synchronization to make my life a little easier.
It seemed easy enough to set it up, I just checked to sync Media in ActiveSync 4.1 and followed the on-screen instructions to complete the setup from the WMP Sync tab. However, on the sync tab I got a message saying “No Device has been Detected”. After some Googling I finally found this Microsoft Knowledge Base article that solved the problem. Basically you go in to Add/Remove programs, select ActiveSync and then do a repair and restart Windows.

While I was at it I decided to find out how to change the Device Name (ie my phones name) in Active Sync. I found this discussion thread that describes some tools to do it or that you go in to your phones registry and change the HKEY_LOCAL_MACHINE\Ident\Name entry. Since there is no Registry Editor included in WM5 I downloaded Mobile Registry Editor 1.1 which is one of the tools suggested in the thread. It worked like a charm. One thing I really liked about this tool is that there was no installation necessary, I don’t like installing stuff as it has a tendency to mess up my computer… In this case I just unzipped the three files to my Desktop and ran the exe from there.
Ok so that worked out well, but this should be way easier don’t you think? According to some of the info I came across while searching for this answer, you could do it from the UI in Pocket PC 2003.

I’m actually quite happy with my phone, only a few minor disappointments…
Before I bought it I didn’t realize that there are different editions of Windows Mobile 5 (WM5) – the Pocket PC and the Smartphone edition. The Smartphone edition is for devices with keyboard only (no pointing device/stylus) and smaller screen. There are some differences that do make sense and then there are those that don’t.
Into the latter category falls one thing that is very limiting to me: You cannot work with Tasks well in a Smartphone. First of all you can’t show them on your Home Screen (I tried some 3rd party tools but these did not work well). Second, if you create a Task on your phone you cannot set Due Date and Reminder. Third, Reminders don’t even show up on your phone (not sure if they do this in the Pocket PC edition either).
To end on a positive note, if you’re running Exchange with SP2 you can at least sync Tasks over the air (I cradle my phone once a week at the most).

Public Warning: Kiss DP-558 Video Recorder

1 Comment

I bought my Kiss DP-558 video recorder late in December last year and so far it has been a frustrating experience, even though I must admit it has not been all bad.

I made the decision to buy this player because
1) I wanted a home unit, i.e. a unit that is not noisy and always “on”, and a form factor that fit with the TV and my other VCR and Home theatre system. I did look at Microsoft Media Center that had just been released with Swedish EPG but there was no good hardware available.
2) I wanted the EPG. EPG is short for Electronic Program Guide and basically gives you the TV-listings and lets you easily program your recorder. This ruled out recorders from Sony, Pioneer etc. because none of these have EPG.

I’m not going to go thru all the problems I’ve had and still have with this machine but to sum up: It’s just not consumer ready. (If you’re a “hacker” you will probably love all the stuff you can do with it)

You don’t even get a proper manual on how to set up your machine and the on screen user interface and the remote control is all but intuitive.
Kiss has just been acquired by Cisco (and is now part of the Linksys division) so hopefully this will change in the future. And in all fairness I have to admit that when it does work well it has made my recording life much easier.

I would recommend that anybody thinking about buying this machine carefully search the net for more info on what does and does not work well with it. One good place to start is Hi-Jack’s Kiss DP-558 Forum. This forum has also helped me with figuring out a lot of issues, bugs and how to work some features.

Failed to update database "XXX.MDF" because the database is read-only

43 Comments

After a few hours of Googling and testing things I finally solved this problem so I thought I’d write the solution down both for my own and other’s benefit.

What I did to get the error:
I had just done some changes to one of my web sites using Visual Studio 2005 and ASP.NET 2.0 with VB.NET. I have previously used MySQL as a database for this site but thought I would test using SQL Server 2005 Express Edition for a new feature I just added.
As usual I did Build – Publish Web Site and then copied and pasted the published directory to my web server. When browsing any of the web pages that use the SQL Server 2005 database I get this error message: Failed to update database “XXX.MDF” because the database is read-only.

Solution:
From what I found on Google this seems to be a confirmed bug in the beta releases of Visual Studio but should be fixed in the released version, but obviously it is not. There seems to be many ways and suggestions on how to fix this, some very complicated.

This worked for me:
1) Make sure App_Data directory or any contained files does not have file system attribute Read-only set. (I had some files marked as Read-only)
2) Give user ASPNET and NETWORK SERVICE Modify control over the App_Data directory. (I had to add both these)
3) Run IISRESET to restart IIS to refresh its permissions.

Main part of this solution was found in this MSDN forum thread, specifically posts by Thongtap and Justin. Thanks guys!

Lotusscript: Body Above Signature in Memo

6 Comments

I got a library of Lotusscript functions and code examples that I find useful and reuse in projects every now and then. Some are very simple and some did require a little or a lot of research and work before I got them working. In this post is an example on how to disable the email signature from Lotusscript.

Problem:
When creating a memo from backend Lotusscript and then showing it to the user using the frontend/UI classes, the user email signature (if enabled) will be inserted at the top of the document, i.e. the body text will be below the signature instead of above it as expected.

Solution:
Disable the signature and then re-enable it as shown in the following code example.

Limitations:
This is only an example on how to disable the user email signature. If you want it inserted at the end of whatever your email contents is (in this example “Hello world!”) you can easily modify the example to read the signature from docProfile and then insert it.

Pre-requisites:
This has been tested in Lotus Notes 6.5.3 but it should work both in previous releases such as R5 and in the later ND7 release.
Write a comment if you have confirmed this working (or not working) in other releases.

Below is a code example that
1) creates a memo using Lotusscript backend classes,
2) disables user email signature,
3) shows the memo to the user using Lotusscript frontend classes, and
4) re-enables the user email signature

Please note that the NotesRichTextItem Update method used in this example is new in ND6.


' ------------------------------------------------
' --- You may use and/or change this code freely
' --- provided you keep this message in your code
' ---
' --- Example by Max Flodén 2005
' --- http://www.tjitjing.com
' ------------------------------------------------

Dim workspace As New NotesUIWorkspace
Dim dbMail As NotesDatabase
Dim docMail As NotesDocument
Dim rtitem As NotesRichTextItem

Dim docProfile As NotesDocument
Dim strProfileEnableSignature As String

'Open users mail database
Set dbMail = New NotesDatabase( "", "" )
Call dbMail.OpenMail
If Not dbMail.IsOpen Then
Call Messagebox("Could not open mail database.", 48, "Error")
Exit Sub
End If

'Create new memo from back end
Set docMail = dbMail.CreateDocument
docMail.Form = "Memo"
docMail.Subject = "Hello"
Set rtitem = New NotesRichTextItem( docMail, "Body" )
Call rtitem.AppendText("Hello world!")
Call rtitem.AddNewline(1)
Call rtitem.Update 'Update method is new in ND6

'Disable signature in users mail profile
Set docProfile = dbMail.GetProfileDocument("CalendarProfile")
strProfileEnableSignature = docProfile.EnableSignature(0)
If strProfileEnableSignature = "1" Then
docProfile.EnableSignature = ""
Call docProfile.Save(True,False)
End If

'Show memo to UI/front end
Set uidoc = workspace.EditDocument(True, docMail,,,True,True)

'Re-enable signature in users mail profile
If strProfileEnableSignature = "1" Then
docProfile.EnableSignature = "1"
Call docProfile.Save(True,False)
End If

Ray Ozzie and Microsoft’s New Services Strategy

No Comments

I just read this article in Fortune, headlined “Microsoft’s new brain”. It’s about Ray Ozzie but the real take aways for me were:

1) how Microsoft think about their new services strategy.

2) the report on how the new management at Microsoft work. I had understood that Ozzie played an important role but not that important.

Neither had I realized the vast amounts of money that needs to be put in to hardware and infrastructure investments. Some quotes:

“Just think about where there are windmills, dams, and other natural power sources around the world, and that’s where you’re going to see server farms,” he says.

“The people who could build a viable services infrastructure of scale,” he says, “are companies that have both the will and the capacity to invest staggering amounts of money – staggering amounts.” Think billions, many billions.

Google is rumored to have a million servers around the world and, according to a knowledgeable source, is already the top electricity user in at least one large U.S. state. Google would not comment.