3

I'm trying to work at getting the same information from a couple different sources but have hit a wall in trying to use NotesSQL and SQL 2008. What I am trying to do is to retrieve info from a couple different views on Domino servers. One view is a default view, the other is a created one.

One method I have used is Powershell, where I get the database, then select the view, get the first document & then iterate through the rest of the view grabbing fields that I need. The view I have selected is the People view.

I was trying to replicate this same thing using SQL 2008, using the NotesSQL driver, setting up an ODBC connection, and then creating a linked server to that Notes database. I am using the following query to select from the People view:

select * from openquery(MyNotesServer,'Select * from People')

However, what is returned from this view isn't what I am able to see when I use Powershell & then iterate through the documents returned in that view. Powershell shows 100+ columns in it, while SQL only returns 5 columns. Additionally, they're named "_12", "17" etc. Some fields (which may be custom, I don't know) have a meaningful name. Of the fields shown, I can select them by name ("_12", etc) but cannot select anything else. The number of rows (SQL) is the same as the number of documents in the view (Powershell $view.Allentries.Count).

Querying the view that was created (3 fields):

select * from openquery(MyNotesServer,'Select * from MyCreatedView')

returns all the fields in that view, and they are named as they are in the view.

In T-SQL querying the People view, how do you get the names of the columns that I know are there as I discovered in my Powershell script? They don't appear to be named the same thing, so how do you retrieve more than the 5 returned when you select * from the view? I have read through the Notes documentation & examples, but haven't been able to figure out what is mapped to where.

The reasoning behind this is wanting to utilize SQL & a notes.id file instead of running a script. Also, I want to make use of an already existing global view instead of views that may be accessible only to their author.

steve_o
  • 1,243
  • 6
  • 34
  • 60
  • Is this the Domino Directory? If so, for a slightly left-field suggestion why not enable LDAP on the Domino server? You might be even able to create a linked server connection using LDAP provider, I'm speculating on this because I know you connect to AD in SQL Server. – booyaa Mar 14 '13 at 09:05

2 Answers2

1

You can use select * from Person. Person is a form name, not a view name. Notes and Domino are not relational. The NSF file is a document database. The views in it are pre-built indexes that already have an implicit select. I.e., the "People" view selects all documents created with the "Person" form.

The above query bypasses the use of a view and will give you all the fields for all documents created with the the Person form.

Actually, come to think of it, the better query would be select * from Person where type='Person'. That's because the People view in Domino uses type="Person" instead of form="Person" in its selection formula. It is theoretically possible to have a document created with the Person form but with the Type field set to a different value. This variation will insure that you always get the same list that you see in the Person view.

But: In either case, it will be inefficient. The NotesSQL driver will have to do a full database search instead of simply reading the index of an existing view. It's going to search using the Notes formula SELECT Form="Person" & Type="Person". I really cannot recommend this, unless you are querying against a small Domino Directory database.

The best practice is to create a view containing all the fields that you really need, and do your query against that view.

Richard Schwartz
  • 14,463
  • 2
  • 23
  • 41
  • I am finding out what both you & Mario have stated. Still trying though, to query the Person (select * from Person). Using that inside an Openquery command. What keeps getting returned though is the linked server returning a "[Lotus] [ODBC Lotus Notes] Program type out of range" message that I've been unable to get beyond. The SQL Message is 7330 & it says it cannot fetch a row from OLE DB provider "MSDASQL" for the linked server. – steve_o Mar 15 '13 at 15:46
  • Based on my reading of this http://support.microsoft.com/kb/238774, it seems like it could be a data type issue. Are you trying to select *? Or does this happen even if you only select the specific items that you really need? – Richard Schwartz Mar 15 '13 at 19:58
1

First, in the Lotus Notes documentation, you'll find "columns" when you read about views, not fields.

Second, the columns have configured an option called "Programmatic Name". In that option, you can put an "alias". Lotus Notes puts values by default as "$12", "$17", etc. The thing is NotesSQL changes "$" by "_". That's the reason why you see "_12", "_17, etc.

How do you get the original names? As far as I remember (I don't have Lotus Notes near to make a verification) you can't. But, you can create another view with the columns with the data you need, and put appropiate names. The easy way to do it is copy/paste the view, delete the columns you don't need, and change that you want.

Mario S
  • 1,914
  • 1
  • 19
  • 32
  • Just an FYI, the '$' fields are typically derived or calculated values, whilst the column names are usually the underlying field names. Since calculated values could be based on any combination of data, there is no specific/single original underlying 'field name' to return. The program name could be named anything, and may not always have a $ prefix. To determine if the column is a single field or a derived column, there are internal 'view properties' can can be queried, but probably not accessible via ODBC/NotesSQL. In NotesSQL, views in Notes map to SQL Tables and cols, map to SQL fields. – andora Mar 04 '21 at 23:30