Domino Code Fragment

Code Name* Documentation and implementation notes.	Date* 04/19/2000	Source (or email address if you prefer)* Rlatulippe@romac.com 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:

,

%REM ---------------------------------------------------------------------------- FILE: EKODBC.LSS - Documentation and Sample Usage UPDATED: April 1997 AUTHOR: Eric Koeler mailto:ekoeler@panix.com http://www.panix.com/~ekoelerNOTE: 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