Domino Code Fragment

Code Name*
Notes R4.5 LotusScript functions for synching an ODBC source with a Notes view.		Date*
04/19/2000		Source (or email address if you prefer)*
Eric Koeler ekoeler@panix.com
IP address:.
Description*
Notes R4.5 LotusScript functions for synching an ODBC source with a Notes
view.		Type*
LotusScript		Categories*
RDBMS Integration
Implementation:
Required Client:
Server:
Limitations:
Comments:
Files/Graphics attachments (if applicable): Code:

%REM
----------------------------------------------------------------------------
FILE:    EKODBC.LSS - Function Library
UPDATED: April 1997
AUTHOR:  Eric Koeler
        mailto:ekoeler@panix.com
        http://www.panix.com/~ekoeler
NOTE:    Copyright (C) 1997 by Eric Koeler, this file is protected by the
        GNU General Public License
----------------------------------------------------------------------------
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.
----------------------------------------------------------------------------
%ENDREM

' Declarations

Uselsx "*LSXODBC"

Dim dataSource  As ODBCConnection      ' ODBC data source driver class
Dim dataQuery   As ODBCQuery           ' ODBC query object
Dim dataResults As ODBCResultSet       ' ODBC result set

Const FLAG_NAME  = "flag"
Const FLAG_VALUE = "True"

'----------------------------------------------------------------------

Function DataConnectResults(dataName As String, _  
                           dataSQL  As String, _
                           maxNum   As Long,   _
                           logItem  As NotesRichTextItem) As ODBCResultSet
   
     ' This function connects to the specified data source, queries it
     ' with the specified SQL statement and returns an ODBCResultSet
     ' ready for processing.

      On  Error  Goto   ErrorTime
     Dim status As     Long    
   
     Set dataSource = New ODBCConnection      ' ODBC data source driver class
     Set dataQuery  = New ODBCQuery           ' ODBC query
   
     Set DataConnectResults = New ODBCResultSet
   
     '----- Try to connect to the specified ODBC Data Source
     status = dataSource.ConnectTo(dataName)
     If Not(status) Then
          Messagebox "Could not connect to the specified ODBC Data Source: " _
          & dataName & ".  Make sure it is properly set up in your Control Panel." _
          & "  Cannot continue...", 0, "Error!"
          Call LogMessage(logItem, "Could not connect to the specified ODBC Data Source: " _
          & dataName & ".")
          Goto TheEnd
     End If
   
     '----- Set up the query
     Call LogMessage(logItem, "Querying ODBC data source...")
     Set dataQuery.Connection = dataSource
     dataQuery.SQL = dataSQL
     Set DataConnectResults.Query = dataQuery
   
     '----- Set result set properties, must cache all of the results
      DataConnectResults.ReadOnly       = True
     DataConnectResults.CacheLimit     = maxNum
     DataConnectResults.FetchBatchSize = maxNum
     DataConnectResults.MaxRows        = 0
     Yield
   
     '----- Execute the query
     status = DataConnectResults.Execute
     If Not(status) Then
          status = DataConnectResults.GetError
          If Not(status = DBstsSUCCESS) Then
               Call LogMessage(logItem, DataConnectResults.GetErrorMessage(status))
          End If
          Goto TheEnd
     End If
   
TheEnd:
     Exit Function
   
ErrorTime:
     Call LogMessage(logItem, Error$ & "(" & Trim$(Str$(Err)) & ").")
     Set DataConnectResults = Nothing
     Resume TheEnd
   
End Function

'----------------------------------------------------------------------

Function DataNumResults(dataResults As ODBCResultSet,     _
                       logItem     As NotesRichTextItem) As Long

      ' This function connects to an ODBC source, executes a query and
     ' gets a result set.  The number of records in that result is
     ' returned.
   
     On  Error  Goto ErrorTime
     Dim status As   Long                ' For checking ODBC status
   
     DataNumResults = 0
     If (dataResults Is Nothing) Then Goto TheEnd
   
     '----- Count the number of records in the result set
     Call dataResults.LastRow
     DataNumResults = dataResults.CurrentRow
     If (dataResults.NumRows = DB_ROWSUNKNOWN And DataNumResults > 0) Then
          DataNumResults = 0
          status = dataResults.GetError
          If Not(status = DBstsSUCCESS) Then
               Call LogMessage(logItem, dataResults.GetErrorMessage(status))
          End If
          Call LogMessage(logItem, "A memory shortage truncated the " & _
          "result set.  Cannot continue...")
          Goto TheEnd
     End If
   
     '----- Log the number of records found
     Call LogMessage(logItem, "Found " & Trim$(Str$(DataNumResults)) & " records in source.")
     
TheEnd:
     Exit Function
   
ErrorTime:
     Call LogMessage(logItem, Error$ & " (line #" & Trim$(Str$(Erl)) &").")
     DataNumResults = 0
     Resume TheEnd
     
End Function

'----------------------------------------------------------------------

Function DataProcessBatch(     _
dataView As NotesView,         _          ' Notes view to use for field mapping
dataName As String,            _          ' Name of registered ODBC source
dataSQL  As String,            _          ' Simple SQL statement
safeNum  As Long,              _          ' Number of records to cache
newForm  As String,            _          ' Form for new docs ("" for no creation)
logItem  As NotesRichTextItem) As Long    ' For logging messages
   
     ' This function does all of the work.
   
     DataProcessBatch = 0
     Const thisMod    = "DataProcessBatch"
   
     On  Error      Goto   ErrorTime
     Dim sess       As New NotesSession        ' For accessing the environment
     Dim dataDoc    As     NotesDocument       ' Document being synchronized
     Dim nItem      As     NotesItem           ' For importing fields
     Dim varDat     As     NotesDateTime       ' For converting dates
     Dim flagItem   As     NotesItem           ' For flag field
     Dim status     As     Long                ' For checking ODBC status
      Dim varStr     As     String              ' For various conversions
     Dim varNum     As     Double              ' For converting numbers
     Dim noteName   As     String              ' Name of Notes field
     Dim odbcName   As     String              ' Name of ODBC  field
     Dim num, idx   As     Long                ' Index variables
     Dim count      As     Long                ' Total number of records
     Dim numProc    As     Long                ' Running count of the number processed
     Dim changed    As     Integer             ' Whether an item was changed on not
   
     '----- Make the ODBC connection, execute the query, and get the results
     Set dataResults = New ODBCResultSet       ' ODBC result set
     Set dataResults = DataConnectResults(dataName, dataSQL, safeNum, logItem)
     If (dataResults Is Nothing) Then
          Call LogMessage(logItem, "Error retrieving result set.  Cannot continue...")
          Goto TheEnd
     End If
   
     '----- Get the total number of records first
      count = DataNumResults(dataResults, logItem)
     If count = 0 Then Goto TheEnd
   
     '----- Set the row pointer
     status = dataResults.FirstRow
     If Not(status) Then
          Call LogMessage(logItem, "Could not position row pointer.  Cannot continue...")
          Goto TheEnd
     End If
     Yield
   
     '----- Loop through rows in data source up to the safe number
     For num = 1 To count
          numProc = numProc + 1
          Print "Processing record #" & Trim$(Str(numProc)) & " of " & Trim$(Str(count))
          idx = 0
         
          '----- Loop through the view columns to map data source fields to notes fields
          Forall X In dataView.Columns
               varStr = ""
               varNum = 0
             
               '----- Get the current note and data source item names
               noteName = X.ItemName
               odbcName = X.Title
             
               '----- Increment the item (column) index
               idx = idx + 1
               
               '----- Assume the first column is the key and use it so
               If idx = 1 Then
                   
                    '----- Get the key value of this record from data source
                    varStr = dataResults.GetValue(odbcName)
                   
                    '----- If the key is blank then skip this record
                    If Strcomp(Trim$(varStr), "") = 0 Then Goto NextRow
                   
                    '----- Find the notes document that matches this key
                    Set dataDoc = dataView.GetDocumentByKey(varStr)
                    If (dataDoc Is Nothing) Then
                       
                         '----- Log the missing document
                         Call LogMessage(logItem, "Cannot find note for record #" & _
                         Trim$(Str$(numProc)) & " (" & varStr & ").")
                       
                         '----- Make a call to the add record function
                         If Strcomp(newForm, "") Then
                               If DataCreateDoc(dataView, dataResults, newForm, logItem) Then
                                   Call LogMessage(logItem, "New document was created successfully.")
                                   changed = True
                              Else
                                   Call LogMessage(logItem, "New document was NOT created due to an error.")
                              End If
                         End If
                         Goto NextRow
                    End If
                   
               '----- Process this result row item
               Elseif Not(dataResults.IsValueNull(odbcName)) Then
                   
                    '----- Get the corresponding note item
                    If Not(nItem Is Nothing) Then Set nItem = Nothing
                    Set nItem = dataDoc.GetFirstItem(noteName)
                   
                    '----- If the item does not exist, then create it
                    If (nItem Is Nothing) Then
                          Set nItem = New NotesItem(dataDoc, noteName, dataResults.GetValue(odbcName))
                         If Not(nItem Is Nothing) Then nItem.IsSummary = True
                         changed = True
                         Goto NextRow
                    End If
                   
                    '----- Process this item based on its data type
                    Select Case (nItem.Type)
                       
                    Case TEXT, RICHTEXT, NAMES, AUTHORS, READERS, UNKNOWN, UNAVAILABLE
                       
                         '----- Get the source value and put into a temp string buffer
                         varStr = dataResults.GetValue(odbcName)
                       
                         '----- If the buffer value doesn't equal the item value, change the item value
                         If Strcomp(varStr, nItem.Text, 1) <> 0 Then
                              Set nItem = dataDoc.ReplaceItemValue(noteName, varStr)
                              changed = True
                          End If
                       
                    Case NUMBERS
                       
                         '----- Get the source value and put into a temp number buffer
                         varNum = dataResults.GetValue(odbcName, varNum)
                       
                         '----- If the buffer value doesn't equal the item value, change the item value                        
                         If nItem.Text = "" Or Not(varNum = Cdbl(nItem.Text)) Then
                              Set nItem = dataDoc.ReplaceItemValue(noteName, varNum)
                              changed = True
                         End If
                       
                    Case DATETIMES
                         
                         '----- Get the source value and put into a temp string buffer
                         varStr = dataResults.GetValue(odbcName)
                       
                         '----- If the buffer value doesn't equal the item value, change the item value        
                          If Strcomp(varStr, nItem.Text, 1) Then
                              Set varDat = New NotesDateTime(varStr)
                              Set nItem = dataDoc.ReplaceItemValue(noteName, varDat)
                              changed = True
                              Delete varDat
                         End If
                       
                    Case Else
                         Call LogMessage(logItem, "Unsupported data type for " & noteName)    
                       
                    End Select
               End If
          End Forall  
NextRow:            
          '----- If the current document has been changed
          If changed Then
             
               '----- Add the infamous flag field and save
               Set flagItem = New NotesItem(dataDoc, FLAG_NAME, FLAG_VALUE)
               If Not(flagItem Is Nothing) Then flagItem.IsSummary = True
               If Not(dataDoc Is Nothing) Then Call dataDoc.Save(True, True)
          End If
           changed = False
          If Not(dataDoc Is Nothing) Then Set dataDoc = Nothing
         
          '----- Move to the next row if appropriate
          If (dataResults.IsEndOfData) Then Goto TheEnd
          Call dataResults.NextRow
          Yield
     Next num
   
TheEnd:
     '----- Close the result set
     If Not(dataResults Is Nothing) Then Call dataResults.Close(DB_CLOSE)
     DataProcessBatch = numProc    
     Exit Function
   
ErrorTime:
     Call LogMessage(logItem, thisMod & ": " & Error$ & " (line #" & Trim$(Str$(Erl)) &").")
     Resume TheEnd
     
End Function

'----------------------------------------------------------------------

Function DataCreateDoc(dataView    As NotesView,         _
                       dataResults As ODBCResultSet,     _
                       newForm     As String,            _
                       logItem     As NotesRichTextItem) As Integer
   
     ' This function creates a new document in the specified database
     ' with the fields specified in the view.
   
     On Error Goto ErrorTime
     DataCreateDoc = False
   
     Dim tempDoc  As New NotesDocument(dataView.Parent)
     Dim formItem As New NotesItem(tempDoc, "Form", newForm)
     Dim tempItem As     NotesItem
     If Not(formItem Is Nothing) Then formItem.IsSummary = True
   
     '----- If there is no new form name, exit
     If Strcomp(newForm, "") = 0 Or (formItem Is Nothing) Then Goto TheEnd
   
     '----- Iterate through the view columns
     Forall X In dataView.Columns
          Set tempItem = Nothing
         
          '----- Create this item
          Set tempItem = New NotesItem(tempDoc, X.ItemName, _
          ProperCase(dataResults.GetValue(X.Title)))
         
          '----- Summary flag should be turned on
          If Not(tempItem Is Nothing) Then tempItem.IsSummary = True
     End Forall
     DataCreateDoc = tempDoc.Save(True, True)
   
TheEnd:
     Exit Function
   
ErrorTime:
     DataCreateDoc = False
     Resume TheEnd
   
End Function

'----------------------------------------------------------------------

Function DataBuildSimpleSQL(dataView  As NotesView,         _
                           dataTable As String,            _
                           logItem   As NotesRichTextItem) As String
   
     ' This function builds a simple SQL statement based on information
     ' in the Notes view.
   
     On Error Goto ErrorHandler
   
     DataBuildSimpleSQL = "SELECT "
     Dim count As Integer
     Dim keyV  As String
     count = 0
   
     '----- Iterate through the view columns
     Forall X In dataView.Columns
          count = count + 1
          If count > 1 Then DataBuildSimpleSQL = DataBuildSimpleSQL & ", "
         
          '----- Add this column's name
          DataBuildSimpleSQL = DataBuildSimpleSQL & X.Title
     End Forall
     DataBuildSimpleSQL = DataBuildSimpleSQL & " FROM " & dataTable
   
TheEnd:
     Exit Function
   
ErrorHandler:
     DataBuildSimpleSQL = ""
     Call LogMessage(logItem, Error$)
     Resume TheEnd
   
End Function