Compare Lotus Domino Objects (COM) and Lotus NotesSQL ODBC

I have C# code that used Lotus Domino Objects to get the $People view, and I am trying to get the same information with the Lotus NotesSQL ODBC driver, but the data seems to be different.  I would like to know what the difference is between the COM session and the ODBC connection?
NotesSession session = new NotesSession();
        NotesDatabase database = session.GetDatabase(server, "names.nsf", false);
        NotesView people = database.GetView("$People");

Open in new window

LVL 96
Bob LearnedAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Sjef BosmanConnect With a Mentor Groupware ConsultantCommented:
Ah, okay. The "$People" view you're referring to is actually called "($People)". The parentheses make the view hidden for normal users. So try to open the table using the real name of the view: ($People). I have no idea if that works in ODBC...
Sjef BosmanGroupware ConsultantCommented:
Hi TL/1,

could you give us a little more info? We don't have enough to go on. What is it exactly that you try in ODBC?

I suppose differences between COM (local) and ODBC (network) don't need any explanation?
Bob LearnedAuthor Commented:
I am trying to get the same information from the NotesSQL ODBC driver that I got from the COM piece.  It seems that the information available from the $People in the NotesDatabase should be accessible.  The screen shot is from a connection to the Domino server's names.nsf.  I have looked through the tables, and I cannot find any one table or view that corresponds to the $People, so I was trying to find more technical details about the differences between the Lotus Domino Objects library and the ODBC driver, and how they get data from the Domino databases.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

ODBC is facility that provides access to data source. It can set to access local or remote data.
But NotesSQL (that sits on top of it) is notoriously unreliable.
There's no mean of tracing the SQL statements in case of error (windows ODBC trace mechanism does not work with NotesSQL, at least I wasn't able to make it work), so you're stuck with guessing what went wrong.

COM is convention that is used by applications to expose its classes on the OS level, so that other applications may use them.

Use COM wherever possible...
Bob LearnedAuthor Commented:
We are working with SSIS to get special employee identification data from the Lotus names.nsf, and I couldn't find a way to do that with COM, but I can attach with NotesSQL.  I wasn't happy using ODBC, but it seemed like a logical choice.  I didn't know about hidden views, so that is good to know *GRIN*.

I ran this query against the names.nsf database:

SELECT     *
FROM         "($People)"

That appears to get the same data as the __People_ view that is available from the Server Explorer.  It doesn't have the same information.

I got values like this:

CN=03Cas 03Cas/OU=AEB/O=Company/C=US,03Cas 03Cas,uid=03cas 03cas/o=InternalUsers/dc=company/dc=com

I also noticed that I am getting the NotesDocument:

        NotesDocument doc = people.GetFirstDocument();
        Array itemList = (Array)doc.Items;

Is that data available from NotesSQL?

Sjef BosmanGroupware ConsultantCommented:
If you want directory data, why not use LDAP?
mbonaciConnect With a Mentor Commented:
The value you're describing is probably content of $UpdatedBy field (NotesDocument internal field listing people who saved the doc).
To compare the results open document in Notes and open Document properties, second tab.

SQL you wrote returns all documents with their fields in the form of RDB table.

Try this function to get user doc from Names.nsf, I wrote it here so it's not tested:
Function GetUserDocumentFromNAB(ByVal userToFind As String, ByVal myPassword As String, ByVal serverName As String) As Object
        'Mb¤   The function returns the user document from server's NAB
        Dim sess As Object
        Dim Nab As Object
        Dim v As Object
        Set sess = CreateObject( "Lotus.NotesSession" )
	Call sess.Initialize( myPassword )
        Dim Nab = session.GetDatabase( serverName, "NAMES.NSF", False )
        If Nab Is Nothing Then Exit Function
        Set v = Nab.GetView( "($Users)" )
        If v Is Nothing Then Exit Function
        Set GetUserDocumentFromNAB = v.GetDocumentByKey( userName, True )
End Function

Open in new window

Check whether the user specified in the KeyFileName setting in the notes.ini file (that's the user ID that COM uses) has access to the server.

You should be able to access server names.nsf
Bob LearnedAuthor Commented:
I didn't want LDAP data, that is just what I saw as evidence that ($People) isn't the correct view data.  Can I access the same values from the NotesDocument with NotesSQL?  Is there a corresponding table or view defined for names.nsf?
You are creating your own virtual table by using SELECT statement.
What data do you exactly need?

If you need all people use:

SELECT FirstName, LastName FROM Person


SELECT FullName FROM "($People)"

First example uses form to act as a table, which is not very good if the database is very large (it builds view index at the SQL statement run-time).

Second example uses NotesView, which is an index by itself.
The problem with this view is that in NotesSQL:

The name of a field (column) in a view may be different from the field name specified on the form used to create this data.
To determine the name of the field, open the view in Domino Designer and select the Advanced tab of the column's properties. The "Name:" under "Programmatic Use:" is the field name returned to NotesSQL; this will typically be a name like $112.
Which means that only fields last name ($3) and FullName are available here (FullName is what you were getting - full hierarchy names).

To get the data from default view of Names.nsf (which you first see when you open it in client) use view named People (without $ and brackets).
To get the field names in that view use described procedure.

c u tomorrow...
Bob LearnedAuthor Commented:
I need a specific employee identification number that is only available through the Lotus Notes names.nsf.   I found it using code similar to that already shown (COM piece), but I need to be able to get that same data with NotesSQL (ODBC), and there doesn't seem to be a match between the data that I am getting with NotesDatabase/ NotesDocument and the Views and Tables that I can see from the ODBC connection.
Check data source in ODBC settings. Does it look at the right database?

If yes use:

SELECT YourFieldNameFromThePersonForm FROM Person

WHERE Person.FirstName = ...

Did you understand my previous comment where I explained why you cannot get that field from the view you used?
Bob LearnedAuthor Commented:
"Did you understand my previous comment where I explained why you cannot get that field from the view you used?"

No, I was tired when I read that, and frankly I just glossed over the words...

I am not pulling the usual data from Lotus, but a special timesheet code that is only accessible through Lotus.  It is available through the $People view from the NotesDatabase, but I can't find the same data element in all the views and tables.

Frankly, I don't understand how the two techniques are different in how they pull data from the names.nsf database.
It doesn't matter which data you're pulling, it works the same way.

NotesSQL is a tool that exposes domino data using ODBC.
That means that any other application can access Domino data using Structured Query Language - SQL.

NotesSQL exposes Domino data in two ways, through Forms and Views (the better way for you is to use Form).
Forms: temporary table is created whose columns are fields of the form.

You access the data by imagining that you have RDBMS table that has the same name as Lotus db Form and has attribute (column) for every form's field.
So you write SQL query like this:

SELECT formField1, formField2, formField3 FROM formName
WHERE <condition>

That's all you need to know to write SQL statement (which I already wrote for you in one of previous comments).

If you don't understand this, I suggest you read NotesSQL Help database which is shipped with the product.
If you don't have it, you can download it here:

SQL defined + simple examples:

You have all info you need, you just have to put your mind to it...
Sjef BosmanGroupware ConsultantCommented:
Marko, please have a look at TheLearnedOne's profile...

TL/1: there is actually a People view, without the parentheses and without the dollar, you might try that view to find out what data you *do* get via ODBC. By the way, that view should not be used in a permanent solution, because you're not guaranteed to get the same results every time: the user can modify its sorting behaviour.
Bob LearnedAuthor Commented:
@sjef_bosman:  Thank you for trying to protect my reputation!!

This may sound like I don't have any clue about running queries against Lotus data, but that is not the problem at all, so I am sorry if I gave anyone that impresson.  I have worked with Microsoft Exchange and Outlook for most of my career (20+ years), so my understanding of Lotus Notes and Domino is more limited than it should be.  My new job forced me back into the Lotus Notes arena.  I do not have any preference one way or the other about which one is better (to avoid any future squabbles).  

There seems to be a disconnect between the two different techniques to get data.  I work for a very large international company now, and finding a Lotus expert in the company is like trying to find a needle in a haystack.  I know where to turn, though, to attempt to find an expert *WINK*.  

1) NotesSQL People query:

             SELECT * FROM People gives me CompanyName, _12, _16, _17, _21

    None of these columns gives me that information.

2) If I look at all the items for a NotesDocument (COM), then I see that it has 190 in all.

INetPublicKey, Title, Suffix, preferredLanguage, MailAddress, ccMailLocation, InternetAddress, MessageStorage, EncryptIncomingMail, JobTitle, CompanyName, Department, EmployeeID, Location, Manager, OfficePhoneNumber, OfficeFAXPhoneNumber, CellPhoneNumber, PhoneNumber_6, Assistant, OfficeStreetAddress, OfficeCity, OfficeState, OfficeZIP, OfficeCountry, OfficeNumber, StreetAddress, City, State, Zip, country, PhoneNumber, HomeFAXPhoneNumber, Spouse, Children, PersonalID, notesmail, Cost_Center, saalagentdt_roles, saalvsc_roles, saalaric_roles, saalfedw_roles, saalmktdb_roles, disable_flag, password_data, log_history, as_roles, AMM_Roles, Cas_Roles, ACS_Roles, BugTrax_Roles, POE_Roles, Privacy_roles, RACF_ID, RACF_PWD, CCOmaint_Roles, RACF_ModelID, GID_Number, UID_Number, LoginShell, NT_ModelID, AssurnetDomain, Maps_Roles, ICS_Roles, Reacct, Finrep, CLIENTPIN, VScrpt_Roles, DWT_Roles, Comment, CalendarDomain, WebSite, AltFullNameSort, RoamCleanSetting, RoamCleanPer, LocalAdmin, AvailableForDirSync, CheckPassword, PasswordChangeInterval, PasswordGracePeriod, PasswordDigest, HTTPPasswordForceChange, Policy, Profiles, DocumentAccess, OU, PostalAddress, HomePostalAddress, Street, audio, businessCategory, carLicense, departmentNumber, employeeNumber, employeeType, initials, jpegPhoto, labeledURI, o, photo, roomNumber, x500UniqueIdentifier, userPKCS12, x121Address, registeredAddress, destinationIndicator, preferredDeliveryMethod, telexNumber, telexTerminalIdentifier, internationaliSDNNumber, seeAlso, Owner, Form, Type, MailSystem, PublicKey, FirstName, MiddleInitial, LastName, FullName, AltFullName, AltFullNameLanguage, ShortName, HTTPPassword, MailDomain, ccMailUserName, SametimeServer, x400Address, RoamingUser, RoamSrvr, RoamRplSrvrs, RoamSubdir, RoamAB, BkmksFile, JrnlFile, RoamMode, PasswordChangeDate, HTTPPasswordChangeDate, NetUserName, MailVerify, DeleteNTUserAccount, GroupName, OldMailFile, MailFile, OldMailServer, MailServer, ClientType, ClntMachine, ClntPltfrm, ClntBld, ClntDgst, ClntDate, MajVer, MinVer, QMRVer, QMUVer, HotVer, FixPVer, FlagsVer, ChangeRequest, LTPA_UsrNm, dspHTTPPassword, DB2UserName, OriginalModTime, DIVISIONCODE, DIVISIONDESC, LOCATIONCODE, DIVDESCLOCATION, MGMTCODE, PROCESSLEVEL, MANAGER_ID, LAWSON_NUMBER, HR_BUSINESS, HR_LOCATION, AdminpOldAltFullName, AdminpOldAltFullNameLanguage, ChangeRequestDate, AdminpOldFirstName, AdminpOldLastName, AdminpOldMI, AdminpOldFullName, AdminpOldOwner, AdminpOldInternetAddress, AdminpOldShortName, COMPANY, Comments, ENCRLAWSONID, AgentDeskTop_Roles, HTTPPasswordNotesSync, Attr,

3) The "real" question is "Can those NotesDocument items be found in the NotesSQL tables and/or views?"  What is the implication of a NotesDocument?  What is that object type?  Would I have to look for that information somewhere else, besides the names.nsf file?
Jesus Christ!!!
There's no room there for more certificates :)

What does this give you:
Sjef BosmanConnect With a Mentor Groupware ConsultantCommented:
Apparently, the SELECT will get you only the columns that are in the view. In the Domino Designer, these columns have names like $12, $16, $17, etc. and sometimes a real name like "CompanyName". So a SELECTpulls data from a view directly, not from the documents that are shown in the view.

This link may provide some clues:
It says: "Only the columns in a view are known in the context of a NotesSQL table"

Big question: how to get past that hurdle?

1. Create a new view
2. If you're not allowed to do that, create a new database and copy all documents in it, and create your new view
3. Some other way... difficult, hmmm...

By the way, I'd like to work for a big international company *WINK*  ;-))
Person is form, not view, so he should get all fields.
Sjef BosmanGroupware ConsultantCommented:
Not all: fields on (computed) subforms will not show up.
Bob LearnedAuthor Commented:
I get 572 columns from the Person table, but LAWSON_NUMBER is not one of them.  I ran some code to get the schema information for all the tables that I can see from the ODBC connection, and LAWSON_NUMBER is not found in any one of the tables.
Sjef BosmanGroupware ConsultantCommented:
Check if that field is created by an agent or subform (or maybe even DECS or LEI). Apparently, only fields that appear on a real form, in its list of fieldnames, are in the table.
Bob LearnedAuthor Commented:
Since I am not the Lotus administrator, do I have access to check that information?  
Sjef BosmanGroupware ConsultantCommented:
It requires the use of the Domino Designer client, which you need to inspect the design of forms and views, by you or by someone else in your company.  
Bob LearnedAuthor Commented:
I don't think that I have access to the designer, and getting that kind of access would be problematic.  This might be just the brick wall that I hate smacking into.
Sjef BosmanConnect With a Mentor Groupware ConsultantCommented:
Brick wall? It's just cardboard. The Suggested Retail Price for a User license is only USD $845.00, a days work for a consultant. ;-)

To use the Designer, you need the licence, and a database in which you have at least Designer rights in the ACL. If it's for inspection only, it's a bit expensive maybe, but you could start developing your own applications once you have it.
Bob LearnedAuthor Commented:
1) I am not a consultant

2) The company has very restrictive software policies

3) It maybe more of a gypsum board wall, but it is still a wall.

4) I am going to have to find a Lotus administrator who is willing to give up some information--extremely hard in this company.
Sjef BosmanConnect With a Mentor Groupware ConsultantCommented:
1/ I am, but that's not my fee, alas :-|

2/ Read my profile: "Is it a policy, or did they really think it over?"

3/ Put this on the wall:, get some momentum, aim well and get the opening. Or get/hire someone to do that for you.

4/ The larger the company, the haughtier the administrators: they create a kingdom inside the company, behind a fence of rules and regulations, and they conveniently forget what they're there for: to HELP their colleagues! On the other hand, I don't think they can do a lot for you. Unless you want them to modify the design of the database.... Aaaaargh! The sacrilege!

I think the differences between COM and ODBC are somewhat clearer now, to me anyway.
COM:   VB -> COM -> Notes client -> Domino server
ODBC: VB -> .Net -> ODBC -> NotesSQL -> Domino server

You get the differences because, unlike with COM, you are not addressing the Domino server directly when you're using ODBC. NotesSQL spoils the party for you.

Now, there are more ways to get data from a Domino server.
1/ Use Domino as web-server, and use HTTP to get the info out (requires login, most likely, so not practical)
2/ Use Java/CORBA/IIOP if you can interface with them from C# (requires the DIIOP task on the server, you'd need the Admins again...)
Bob LearnedAuthor Commented:
That information is available through the COM piece, so it is not being hidden, and I would think that I should be able to gain access to that piece of information.  This is more intriguing to learn why the COM provides one level of information, and the NotesSQL provides another.  

I think you hit the proverbial nail on the proverbial head when you mentioned "kingdom building" in large companies.  That is exactly the situation that I have to deal with on more than one occasion.  What look like security, is probably more about keeping control, and maintaining job security and not information security.

I won't hold my breath trying to find a Lotus admin that would give us that information, and I will have to learn more about DIIOP to see how it would help my situation.
Sjef BosmanGroupware ConsultantCommented:
I'm looking for an analogy, but I can't find a good one. Suppose you can interface directly with COM in your application. However, when you use NotesSQL, it is NotesSQL that interfaces with COM and your application with NoterSQL. It's the middle man who has his limitations, it just doesn't pass on every piece of information available in COM.

Heh... It's like you drive your car yourself, or you tell your wife how to do it... :-))  

Sorry ladies...
mbonaciConnect With a Mentor Commented:
NotesSQL doesn't interface with COM, it uses Lotus native access to design which is then translated to relational db table.
It is based on the Call-Level Interface (CLI) specifications from X/Open and ISO/IEC for database APIs.
Each ODBC driver (dll) links the application to a specific type of database.

NotesSQL consists of three DLLs and one executable:
When an ODBC connection is made, NSQL32.DLL is called by ODBC32.DLL, the ODBC Driver Manager. NSQL32.DLL is the DLL whose name appears in ODBC.INI.
NSQL32.EXE is called by NSQL32.DLL.
NSQLV32.DLL is called by NSQL32.EXE.

When configuring a NotesSQL data source, NSQLC32.DLL is called by the ODBC Administrator.
NotesSQL communicates exclusively with the Notes client or Domino server software. Notes and Domino handle all network traffic and security.

I don't know how fresh is your NotesSQL installation, but there is a new version available, v8.5 (since March/2009, I think).

Your LAWSON_NUMBER custom field (it's not in original design of Person form) is either "computed for display" (the field that only looks up its value and displays it while the document is opened) or it's physically not on the form (added only to documents by some other process).

Either way, you'll need that help from a Domino designer inside your company.

Or you can try to access the view that displays that field, but bear in mind this (maybe that's why your view access failed):

Because of this difference in syntax support, NotesSQL optionally maps certain characters to the underscore character ( _ ). This mapping can be turned on or off for each data source. By default, mapping is turned on. We recommend using the default unless it causes a form or view to be inaccessible. This can happen when two similar names (for example, "Employees$" and "Employees_") are mapped to the same name.
If you choose to map names, NotesSQL maps forms, views and their aliases, form fields, and view columns. The characters mapped are:
~ ` ! @ # $ % ^ & *
( ) - + = { } [ ] \
: ; " ' < > , . / ?
and the space character

Hope some of this helps,
Sjef BosmanGroupware ConsultantCommented:
> NotesSQL doesn't interface with COM...
Of course, but I didn't have the time (lame excuse) to draw the image like you did. I just wanted to make the point that, using NotesSQL, you're interfacing with Domino in a more distant way, with different/less capabilities.
mbonaciConnect With a Mentor Commented:
I spent all night drawing it.
When you're called to help you don't ask what time it is  :)
Sjef BosmanGroupware ConsultantCommented:
Bob LearnedAuthor Commented:
Wow!!  We are still using 6.5, by the way, which is another thorn in my side.  I hadn't used Lotus in a long time, and I was amazed to see that version (and frustrated!!).

Wish me luck trying to find somebody in Miami, Minnesota, or California (I am in Ohio), who would both know the answer, and be willing to give it up.  

I believe that I have enough information here to know that this is not going to be easily answered, and may require stepping back, and pick a different direction, rather than try to find a Lotus administrator in a very large haystack!!
Bob LearnedAuthor Commented:
There were some great comments.  I was hoping (against hope) for a solution that I could implement, but I still found what I came looking for (and more)--the different between Lotus COM and Notes ODBC.
I was clearly talking about NotesSQL versions, not Lotus (NotesSQL jumped from v3 to v8).

What did you do with the "view suggestion" from my last post?
I presume you have the field displayed in some view, if it's this important?

This is getting frustrating, no matter how much expert zones you have in your profile, I expect you to read every comment with care and, if it requires, respond to it.
Thank you for understanding my frustration, GRRrrr    >:(
Bob LearnedAuthor Commented:
I am talking this over with the other developer, and while your suggestions were instructional, they weren't very helpful in long run.  Somehow, I knew that I was going to have to find a Lotus administrator here to find a real solution.  

My friend, I am a very busy guy, and if you expect askers to read every little word and respond, then you are going to be frustrated a lot.
Sjef BosmanGroupware ConsultantCommented:

And I do wish you luck in finding a real Admin guy!
You're busy guy, LOL, we're all busy.
You are being disreputable to the guy who's trying to make you less busy.

Have you tried the view suggestion at all?
It may well solve your problem in the end. Maybe it wont, but consider the suggestion, you cannot say it's not relevant.
It's a custom (even - the rule) on EE to report back your finding, or if not, to say why you haven't even tried.

I hope you'll be able to force yourself into reading this (maybe you should read it out loud):
Asking Questions  -  How do I know when I get an answer?

Sjef, I expected you to react here, to stick with the principle and state your view on this.
I always react to these kind of "passive behavior", to say the least.
In the last four years there were maybe two or three manifestations of people who were asking questions and not (carefully) reading the responses.
So it doesn't happen so often. Luckily, there were only few of them.

But with one big difference.
All others accepted positive criticism.
Sjef BosmanGroupware ConsultantCommented:
But I did, didn't I? ;-))

IMHO you're overreacting. This is clearly an organizational problem, TL/I needs to dive in deep but there's no scuba gear available, or maybe it is but they won't let him have it. Hence, all the info you tell him about those beautiful tiny fish swimming at 100 ft. deep is rather useless. Can't you see he's doing everything he can, holding his breath not to shout it out??

So problem clearly defined, reasons behind the approach as well, we explained why it's different and why it will always be different, that he needs a Designer client to create or modify views or forms, and there's little we can do until he has one.

I humbly rest my case, your honour.
I don't get it, don't you think that, if he has that field displayed in a view, and follow my suggestions about mapping the view name - he can get the value without any diving?
It's not my intent to dwell on this, I gave very specific instructions.
Sjef BosmanGroupware ConsultantCommented:
That's just it: "IF he has that field displayed in a view", and  I assume the field isn't in any view. And since he can't change a view...
ts ts ts don't give me that . . . .
And who of three of us should've checked that?
Since it's added to the form and it's so important that it's wanted elsewhere, I supposed it was also added to a view...  The problem - solved...

If it's not there, good, fine, great!
I suppose a normal individual would just say it's not there and we move on...

If you don't have time to analyze the responses then don't ask questions!
Everything we're dealing with here on EE is somehow under the question mark.
IF you're on version 8 then...
IF you have set in server document param then...
IF you want to disable replication...
IF you have designer installed...
IF you run the code on a selected documents...
I posted the link to this question in Community Support.
Sjef BosmanGroupware ConsultantCommented:
> If it's not there, good, fine, great!
> I suppose a normal individual would just say it's not there and we move on...

How to verify it's not there without a Designer client??
With COM - to list views and columns (aren't view listed in the image in his first post? Whatever),
with SELECT * FROM particular_view,
or maybe the best approach would be to do it all in one query, using NotesSQL's universal relation table,

Well, when you don't know how to do something, I suppose you ask...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.