Does anyone have any experience connecting to a Lotus Notes database with Xojo? I am exploring the use of the Notes SQL 8.5.3 ODBC Driver for Domino servers. Suggestions or examples would be fantastic.
I have a little experience. I have installed the 8.5.3 driver but currently use the 9.0 driver. Both worked and I couldn’t see any difference between them.
This is from memory so it is a little vague.
I installed the driver as instructed and made the changes to the path environment variables as instructed. It’s worth noting that the lotus notes client must be installed on the computer. Just installing the ODBC Driver will not work. The driver needs the client to function properly. If I recall correctly, the driver installation mentions using some lotus driver manager but I did not use it. I just installed the drivers.
Here is the connection string I use:
Dim dbNotesECN As New ODBCDatabase
dbNotesECN.DataSource = "Driver={Lotus Notes SQL Driver (*.nsf)};database="+NotesDatabase+";"+_
"server="+NotesdbServer+";maxsubquery=20;maxstmtlen=15360;maxrels=20;maxvarcharlen=15360;"+_
"keeptempidx=1;maxlongvarcharlen=15360;showimplicitflds=0;mapspecialchars=1;threadtimeout=1200;"+_
"Uid="+NotesUserID+";"+_
"Pwd="+Cryptomb.StringValue(0,Cryptomb.Size)+";"
Cryptomb=Nil
If dbNotesECN.Connect Then
//proceed with database operations
lg.Write("Connect: Notes ECN database.")
lg.Write("Get Notes ECN.")
getNotesECN(dbNotesECN)
// We are done with database so drop connection
dbNotesECN.Close
lg.Write("Close: Notes ECN database.")
Else
lg.Write("Notes ECN Connection error: " + dbNotesECN.ErrorMessage)
lg.Close
Quit
End If
I pull data from the Notes database on a schedule using a scheduled task so I store all my connection data in a file and load it when the program starts. I also have a log file method that writes to a log file so I can see what’s going on.
In the above connection string, maxstmtlen, maxvarcharlen, and maxlongvarcharlen were set to smaller values. I found that occasionally the data returned would be truncated. I increased these values to solve the problem. Unless your data fields are very long, you probably will not need to change these. From what I have read, 15360 is the maximum for these values.
You will need to determine “database” and “server” to use in the connection string. Both can be determined by clicking the “Workspace” tab in the notes client and then right-clicking on the database of interest. In the context menu select Application>Properties and a small window will open. The first tab of the window should have “Server” and “Filename” on it. The string following “Server” will be used as the server in the connection string. The string following “Filename” will be used as the database in the connection string. “Pwd” will be the password that you use to log in to the Notes client. If I remember correctly, “Uid” was not simply my userid. It needed extra information. I describe where I found the proper “Uid” below.
In Notes, opening a database will usually present the documents that are stored in the database. I think these documents are also called forms. I’m not a Notes experts so don’t quote me.
If you right-click anywhere on a document and select “Document Properties” a small window will open. The fourth tab has an icon that looks like a door lock key. There is a label “Who can read this document”. Below the label is a scrollable text box that contains all the users that can read the document. I had to find my ID in the list and use it exactly as shown in “Uid” of the connection string. The second tab will have a small triangle on it and this tab will contain the fieldnames of the document. These are the fieldnames that will be used in the SQL query statements. In my case, one of the fields was called “Form”. The value in this field is was what I had to use as the table name in my SQL statements.
Be aware that Notes has a has what’s called a multi-value field (I think that’s what it’s called). The data type of the field is “Text List”. This appears to be a string with the values separated by a delimiter character. In my case, the delimited was normal windows end of line, but it can be changed.
Hope this helps get you started.
Thank you very much Duane. If you attend the conference in Houston I will gladly buy you dinner.
I appreciate the offer, but I am a hobbyist programmer (I’m actually a mechanical engineer) so I won’t be in Houston.
I actually forgot some important information that you may know, but hopefully will be useful for others. The “bitness” of the driver must match the “bitness” of the Lotus Notes Client not the operating system. For example, I run 64 bit windows, but my client is 32 bit so I had to install the 32 bit Notes ODBC driver.