I am in the process of creating an app that migrates data from a Lotus Notes database over to SQL.
I am able to use an ODBC connection to connect to the Lotus Notes database.
Is there a way to query the data set to get the list of tables or views and their schema structure? The reason for this is that I have run into a scenario of the source table changing and the destination table needs to be updated.
What I am looking to do is pull in the source table/view, the number of columns and the data type of the column. Store that information either in a temp table or temp dataset. From there, do a compare with the destination SQL tables. If there is a difference, drop the destination table and re-create it with the new column names and data type. Then proceed to do the data transfer.
Strictly speaking, Notes is schemaless. If you're dealing with Notes views, I suppose the view design could be considered a sort of schema. I don't think, though, that there's a way to query the view design via ODBC. You'd have use the Notes classes for that. You can get the list of views with the NotesDatabase.Views property and then use the NotesView class to get information about the columns - but beware: the NotesDatabase.Views property is known to not work when calling the Notes classes on Win64 with 64 bit Notes installed. (The classes are officially supported only on 32 bit. They mostly work, but the properties that return arrays of design elements don't.)