Compare Lotus Domino Objects (COM) and Lotus NotesSQL ODBC

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.

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

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?

 

If you want directory data, why not use LDAP?

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

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

or

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

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?

"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:
http://www14.software.ibm.com/webapp/download/nochargesearch.jsp?cat=&q0=&pf=&k=ALL&pn=&pid=&rs=&S_TACT=104CBW71&status=Active&S_CMP=&b=&sr=1&q=notes+sql&ibm-search=Search

SQL defined + simple examples:
http://en.wikipedia.org/wiki/Structured_Query_Language

You have all info you need, you just have to put your mind to it...

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.

@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:
SELECT * FROM Person

Person is form, not view, so he should get all fields.

Not all: fields on (computed) subforms will not show up.

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.

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.

Since I am not the Lotus administrator, do I have access to check that information?  

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.  

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.

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.

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.

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

Image:

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

LOL

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!!

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    >:(

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.

*chuckle*

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):
http://www.experts-exchange.com/help.jsp
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.

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.

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.

> 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??