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(adSchemaTabl
Set rstColumnSchema = cn.OpenSchema(adSchemaColu
NotesTablesIndex = -1
Do Until rstTableSchema.EOF
If rstTableSchema!TABLE_TYPE = "TABLE" And IncludeTableInSql(rstTable
NotesTablesIndex = NotesTablesIndex + 1
Set NotesTables(NotesTablesInd
Call NotesTables(NotesTablesInd
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
ASKER
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.
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.
ASKER
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.