Integra for Notes Banner
  QuickReports Integra4Notes Integra Family of Products

Can I Integrate Notes data with non-Notes Databases?
    Yes. There are two ways to achieve this depending on what the business objective is.

    Data from Notes databases can be linked via Integra for Notes to read or write data in ODBC sources such as Oracle, Microsoft SQL, Microsoft Access, etc. Both Access and Lotus Notes support ODBC as a standard data access method and Integra for Notes leverages this.

    Integra profiles can be created using the @DbLookup and @DbColumn functions with ODBC parameters to read data from the ODBC data source and use this data in conjunction with Notes data for exporting to Word or Excel. Integra for Notes 4.1 includes a lookup wizard to allow this task to be accomplished more easily, especially by someone who is not an advanced developer.

    In addition, Lotus publishes a number of Connectors or LSX's for various databases. These include Oracle, SQL, Access, SAP, JD Edwards etc. These LSX's are LotusScript Classes which can be used to update the specific databases with data taken from a Notes database and these script libraries can be used from within Integra profiles to enable reading and writing of data to these database formats.
Using Notes Formula language for ODBC connections:
    In a simple scenario where a Notes document does contain a reference to an external datasource, for instance an account number, an Integra Excel profile can be created adding a Formula field to the Selected Field List in the Field tab of the Integra profile.

    This is an example of one of the Formula fields:

    res := @DbLookup( "ODBC" : "NoCache" ; "Integra ODBC Demo" ; "" : "" ; "" : "" ; "account.dbf" ; "max_cr":"" ; "account" ; accountcode ; "" : "" );
    @if(@iserror(res);"ODBC Error";res);
    This example shows a look-up from a FoxPro database table. However, it could be a lookup from any ODBC datasource, such as MS Access (see Access example), Oracle, etc. To establish the link to the ODBC datasource one needs to create an entry in the Windows ODBC Manager to tell the Notes Client where to look for the datasource and which ODBC driver it has to communicate with. The Windows ODBC Manager can be opened from the Windows Control Panel. Please refer to the Notes on-line help for ODBC functions and the Windows help for more details on how to create a link in the Windows ODBC Manager.

    In many cases this simple way of accessing external datasources might not give sufficient flexibility or functionality. This is where the Advanced scripting feature in the Integra profile comes into place:
Using Lotus Script for ODBC connections :
    Integra allows a profile developer to place almost any Lotus Script into the Script Callback field in the Advanced tab of the Integra profile. However, to use ODBC or any of the native drivers for external datasources, such as the Oracle LSX, one would need to include the Use statement into the declaration part of the code. As the Integra Callback Script is compiled at runtime, certain types of declarations are not permitted. The Use statement is one of them.

    So how can you include the necessary LSXs into the Integra Callback Script?

    Integra provides a Script Library called "CustomCode". The library is blank and can be used to add any customized code to the Integra database. Whatever subs, functions or LSXs are included into this library will be instantly available for use in the Integra Callback Script. That means that the CustomCode library is loaded with every execution of an Integra profile. This makes Integra highly customizable, however, there is one thing to keep in mind when adding code to the CustomCode library:

    A very important point is that customised code must not contain any declarations of Notes UI back-end classes, for instance NotesUIWorkspace. If any of the Notes UI classes are declared in the CustomCode library (or in any other included library), the scheduled agent functionality will stop working. It does not matter whether a particular function that contains such a declaration is not called in any of the scheduled profiles. Just the existence of such a declaration anywhere within the code is enough to produce this effect. This is a limitation of all Domino server releases prior to 5.0.8.
    The following screen shot shows how we use the CustomCode library to include a specific libraries for simple ODBC connections:

    The above is a best practice example: we actually do not add the code for particular purposes directly into the CustomCode library, but rather create what we refer to as IntegraAddin4xxx libraries and use the Use statement to include these libraries into the CustomCode library. In the above example we included only one library called IntegraAddin4ODBC. This library contains the basic functionality to simplify the scripting of any ODBC connection in the Integra Callback Script:

Related FAQ's:


FAQ# 0044
Bookmark and Share



   Shireburn Software Ltd. 2008 - 2022