Link to home
Start Free TrialLog in
Avatar of jflanner
jflanner

asked on

Problems resolving table name in Notes via ODBC


Help:


I have a VB program that is used to copy Notes Databased down into SQL.  I am using the Notes Sql ODBC driver provided by IBM.  

I am discovering the tables in the Notes Database by doing:

   Set rstTableSchema = cn.OpenSchema(adSchemaTables)
   Set rstColumnSchema = cn.OpenSchema(adSchemaColumns)
   
   NotesTablesIndex = -1
   Do Until rstTableSchema.EOF
      If rstTableSchema!TABLE_TYPE = "TABLE" And IncludeTableInSql(rstTableSchema!TABLE_NAME) Then
         NotesTablesIndex = NotesTablesIndex + 1
         Set NotesTables(NotesTablesIndex) = New clsTable
         Call NotesTables(NotesTablesIndex).Initialize(rstTableSchema!TABLE_NAME, rstColumnSchema, Me)
      Else
      End If
     
      rstTableSchema.MoveNext
   Loop

I am discovering a table called "App_Form".  Further, I am discovering columns for this table.  When I select from the table, I get 0 rows.  (Thus, 0 rows returned.)  When I look at Notes through an ODBC tool, I see a table called "App Form" (notice no underscore.)  There is data in this table.  I do not discover "App Form" using my table discovery above.

Like I said, this was working and now is not working.  (I conclude: someone changed the notes database.)  Most tables in Notes continue to copy properly.  (It's not completely broken.)  

Any help anyone can give me will be extreamly appreciated.
ASKER CERTIFIED SOLUTION
Avatar of qwaletee
qwaletee

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jflanner
jflanner

ASKER

I agree totally with 1 and 2.  I am well aware of the duplicate data, column names that are reserved in SQL, column names that are not unique, etc.  It is horrible.  (And we did not even begin toi discuss normalization!)  

But the situation is, we have a number of applications out grow Notes.  (We need to integrate them with other applications.  "Notes Integration" - my favorite oxymoron.)  My goal is to produce a generalized application that understands this mess, and can reasonably dump notes into SQL.

I am using ADODB to access the Notes Database.
I have checked for Views, or copies I could leverage to get out oif this situation.  None are there.

Any other ideas??  I appreciate the help.
Why not get all the data out of Notes natively, using Lotus.NotesSession?  You can worm yoru way through all documents in the database, exposing their fields.  If you worry about conformity, you can first get NotesDatabase.Forms, get the field lists from them, and go.  Or you can use NotesDatabase.Form just to get the forms and fields (avpimg schema problems), and then use NotesSQL only for the data retrieval.
You were able to get the data out using the Domino object model?
Yup.  Though the best bet is to use the NotesSQL driver to set up an ODBC connection, then DTS to get the data out into SQL.  Raw notes is chaotic, to say the least.  
I woud disagree about raw Notes being any more or less chaotic. Sure, the "data model" can be onconsistent, because the document tag model is unrestricted.  But using NotesSQL only serves to show you a slice of the picture -- badly.  NotesSQL is itself doing the same things you would do using "raw Notes."

An example: I've seen people usng NotesSQL against two different views that cntained the same documents, notrealizing that they were doing twice the work.  I point it ot, they correct it to use teh form for retrieval instead, and end up having to manage data cllation themselves, because they didn't have a view index anymore.