Domino Code Fragment

Code Name*
Documentation and implementation notes.		Date*
04/19/2000		Source (or email address if you prefer)*
[email protected]
IP address:.
Description*
Documentation and implementation notes.		Type*
LotusScript		Categories*
(Misc)
Implementation:
Required Client:
Server:
Limitations:
Comments:
Files/Graphics attachments (if applicable): Code:
related Notes: MSC Design Library, MSC Design Library
%REM
----------------------------------------------------------------------------
FILE:    EKODBC.LSS - Documentation and Sample Usage
UPDATED: April 1997
AUTHOR:  Eric Koeler
        mailto:[email protected]
        http://www.panix.com/~ekoeler
NOTE:    Copyright (C) 1997 by Eric Koeler, this file is protected by the
        GNU General Public License
----------------------------------------------------------------------------
First of all, in order to use the functions in this library, you must also
include the functions from EKLIB.LSS.  In the Declarations section of the
script in which you plan to use these functions, add the following two
lines:

      %INCLUDE "FILEPATH\EKLIB.LSS"
     %INCLUDE "FILEPATH\EKODBC.LSS"

Where FILEPATH is the OS-specific, fully qualified path to the location
where you saved these two files.

Since the functions in this file are only guaranteed to work in Notes R4.5,
you can instead create new Script Libraries and File-Import this files into
them.  If you create separate Script Libraries for EKLIB and EKODBC, then
in the Options section of the EKODBC Script Library, you must include a
reference to the EKLIB Script Library with the Use statement:

      Use "EKLIB"

Where EKLIB is the name of the Script Library containing the code from
the EKLIB.LSS file.
----------------------------------------------------------------------------
The functions in the EKODBC library upload the data from a registered ODBC
data source into a NotesView.  The NotesView acts as a .COL file by mapping
the ODBC column names to the Notes fields.  The view column titles should
match up with the ODBC source column names, and the view column formulas
should specify the Notes field names. The first column in the NotesView
must be the sorted key of both the NotesView and ODBC column.

Please note that these functions UPLOAD data from the source to the target;
they do not synch it.  Records that are in the ODBC source, but are not in
the NotesView, get added to the NotesView's parent database; records that
exist in both are synched (field for field, as per the NotesView) from the
ODBC source; and records that exist in the NotesView, but not in the ODBC
source, are ignored.

The following is a sample script that can be copied and used as is; just
change the values of the variables in the "Configuration Information"
section to reflect your personal situation.  If you want to use these
functions with Notes R4, 4.1, or 4.11a, be aware that you may run into
memory problems because the ODBCResultSet object caches all of the data
returned by the query, which may be too much for your machine.  This bug
was fixed in R4.5.  The nSafeRecs variable, which is passed to the
DataProcessBatch() function, determines the number of records that are
cached, so if you run into memory problems, just lower this number).
----------------------------------------------------------------------------
%ENDREM

'Declarations
%INCLUDE "EKLIB.LSS"
%INCLUDE "EKODBC.LSS"

Sub UpdateDbFromODBC

     '-----------------------------------------------------------------------    
    ' Configuration Information
    '-----------------------------------------------------------------------
    Const nSafeRecs  = 250                    ' Number of records to cache
    Const taskName   = "UpdateDBfromODBC"     ' This sub's name (for msgs)
    Const dataName   = "ODBC_Source_Name"     ' Registered ODBC source name
    Const dataTable  = "ODBC_Table"           ' ODBC table name
    Const newForm    = "Form_For_NewDocs"     ' Form for new documents
    Const srvr       = "Notes_Server"         ' Server where db is
    Const dbase      = "Notes_Db_FileName"    ' Db filename where view is
    Const viewName   = "Notes_View_Name"      ' View for data mapping
    '-----------------------------------------------------------------------

    On Error       Goto   ErrorTime
    Dim sess       As New NotesSession
    Dim logItem    As     NotesRichTextItem   ' RT item to log messages to
    Dim config     As     NotesDocument       ' Document with configuration info
     Dim dataDb     As     NotesDatabase       ' Database to be updated
    Dim dataView   As     NotesView           ' Import view for field mapping
    Dim idx        As     Integer             ' Index variable
    Dim dataSQL    As     String              ' ODBC SQL statement
    Dim numProc    As     Long                ' Running processed count
    Dim count      As     Long                ' Total number of rows

     '----- We are not using a log item in this script
    Set logItem = Nothing
   
    '----- Connect to the specified target NotesDatabase
    Set dataDb = sess.GetDatabase(srvr, dbase)
    If Not(dataDb.IsOpen) Then Call dataDb.Open("", "")
   
    '----- Open the specified NotesView
    Set dataView = dataDb.GetView(viewName)
    If Not(dataView.Columns(0).IsSorted) Then
         Print "The first column in the specified view must be sorted..." & _
         " Cannot continue."
         Goto TheEnd
    End If
   
    '----- Build the SQL statement from the Notes view column information
    dataSQL = DataBuildSimpleSQL(dataView, dataTable, logItem)
    If (Strcomp(dataSQL, "") = 0) Then
         Print "Error building SQL query statement...  Cannot continue."
         Goto TheEnd
    End If
   
    '----- This function does all the work and returns the number of records
    '----- it processed, 0 means an error occurred, all errors encountered
    '----- are printed to the status bar or Notes Log
     numProc = DataProcessBatch(dataView,  _  ' View to be used for field mapping
                               dataName,  _  ' Name of source ODBC table
                               dataSQL,   _  ' Simple SQL 'SELECT FROM' statement
                               nSafeRecs, _  ' Number of records to cache
                               newForm,   _  ' Name of form for new docs
                               logItem)      ' For logging error messages
   
    '----- Print ending information and exit
    If numProc = 0 Then
         Print "An error forced " & taskName & " to abort."
    Else
         Print "Ended " & taskName & " (processed " & Trim$(Str$(numProc)) & ")..."
    End If
   
TheEnd:
    Exit Sub
   
ErrorTime:
    If (Messagebox(Error$ & " (line #" & Trim$(Str$(Erl)) & _
    ").  Click OK to continue, " & "or click CANCEL to end.", 1, "Error!") = 2)_
    Then Resume TheEnd
    Resume Next
   
End Sub