• LEI and ODBC

    By Jishan Dhruva 2 decades ago

    Hello



    I have Lotus Notes Client Applications that pushes the data to my oracle database via ODBC However i want to stop this and i want my Domino server to connect to oracle database. and transfer the data. Please help

    • code help

      By gary fc cronin 2 decades ago

      Here is agent code that will export data to Oracle via Lotus Connectors:

      You will still need an ODBC connection on the Server but you will not need in on the Clients.

      This code was not written by me. I came across it years ago and have found it very helpful.

      Source and destination datatypes must match.

      Using CHAR (Oracle) & text (Notes) is the easiest way to map.

      Pain & time is the best way to get this working.



      so the code



      'OPTIONS

      Option Public

      'Export Using Connectors



      Option Explicit

      Uselsx "*lsxlc"



      Public Const XFER_NAME_MAPPING = 2

      Public Const XFER_SESSION_NAME = "ANY TEXT HERE"

      Public Const XFER_SOURCE_CONNECTION = "notes"

      Public Const XFER_TARGET_CONNECTION = "oracle8"

      Public Const XFER_SOURCE_METADATA = "YOUR NOTES FORM NAME"

      Public Const XFER_TRUNCATION_FLAGS = LCFIELDF_TRUNC_PREC

      Public Const XFER_RECORD_COUNT = 1



      Public Const XFER_STATEMENT = "SELECT (Form = YOUR NOTES FORM NAME' & @Length(SOME FIELD)>3 & SOME_OTHER_FIELD ='1')"

      Public Const XFER_FIELD_EXT = 1





      'Each time I am moving data to Oracle I am overwriting it

      'you could set this to 0 so it will not over write

      'or just comment out the truncate command line of code

      Public Const XFER_OVERWRITE = 1

      Public Const XFER_CREATE = 0

      'end of options



      Sub Initialize

      On Error Goto LSErrorLabel<br/>
      Dim session As New LCSession( )     <br/>
      Dim record_count As Long<br/>
      Dim count As Long<br/>
      Dim src_namelist As String<br/>
      Dim targ_namelist As String<br/>
      Dim stream As New LCStream<br/>
      Dim fieldlist As LCFieldlist<br/>
      Dim mapped_fieldlist As LCFieldList <br/>
      Dim empid As New LCField(LCTYPE_TEXT, 1)<br/>
      Dim fullname As New LCField(LCTYPE_TEXT,1)      <br/>
      Dim staffnumber As New LCField(LCTYPE_TEXT,1)       <br/>
      Dim pdoc As notesdocument<br/>
      Dim db As notesdatabase<br/>
      Dim sess As New notessession        <br/>
      Set db=sess.currentdatabase     <br/>
      <br/>
      Dim src_connect As New LCConnection (XFER_SOURCE_CONNECTION) <br/>
      <br/>
      Dim targ_connect As New LCConnection (XFER_TARGET_CONNECTION)   <br/>
      <br/>
      <br/>
      targ_connect.server = &quot;YOUR ODBC DSN &quot;<br/>
          <br/>
      targ_connect.UserID = &quot;ORACLE USERNAME&quot;<br/>
      targ_connect.Password=&quot;ORACLE PASSWORD&quot;<br/>
      <br/>
      <br/>
      src_connect.server= &quot;YOUR NOTES SERVER&quot;   <br/>
      src_connect.database=&quot;YOUR NOTES FILENAME&quot;<br/>
      <br/>
      src_connect.metadata = XFER_SOURCE_METADATA<br/>
      targ_connect.metadata = &quot;YOUR ORACLE TABLE NAME&quot;  <br/>
      <br/>
      src_connect.Connect<br/>
      targ_connect.Connect    <br/>
      record_count = XFER_RECORD_COUNT<br/>
      If (record_count = 0) Then<br/>
          record_count = 1<br/>
      End If<br/>
      Set fieldlist = New LCFieldlist (record_count, XFER_TRUNCATION_FLAGS)   <br/>
      count = src_connect.Execute (XFER_STATEMENT, fieldlist)         <br/>
      If (XFER_OVERWRITE) Then    <br/>
          'COMMENT OUT THIS NEXT LINE IF YOU DO NOT WANT TI TO OVERWRITE  <br/>
          targ_connect.Action LCACTION_TRUNCATE<br/>
      End If      <br/>
      If ((count = 0) Or ((count = LCCOUNT_UNKNOWN) And (fieldlist.FieldCount = 0))) _ <br/>
      Goto ResumeLabel    <br/>
      If (XFER_NAME_MAPPING &lt;&gt; 2 ) Then     <br/>
          Set mapped_fieldlist = fieldlist<br/>
          If (XFER_NAME_MAPPING =1 ) Then<br/>
              <br/>
              targ_connect.mapbyname=&quot;1&quot;<br/>
          End If      <br/>
      Else<br/>
          src_connect.mapbyname=&quot;1&quot;<br/>
          targ_connect.mapbyname=&quot;1&quot;        <br/>
          <br/>
      <br/>
      


          src_namelist =&quot;NOTES_FIELD1, NOTES_FILED2&quot; <br/>
      


          targ_namelist =&quot;ORACLE_FIELD1 , ORACLE_FIELD2&quot;<br/>
          <br/>
          <br/>
          <br/>
      End If ' end of name mapping section    <br/>
      <br/>
      Set mapped_fieldlist = New LCFieldList(1000,1000)       <br/>
      Call mapped_fieldlist.MapName (fieldlist, src_namelist, targ_namelist)<br/>
      <br/>
      count = src_connect.Fetch (fieldlist, 1, record_count)<br/>
      While (count &gt; 0)        <br/>
          Call targ_connect.Insert(MAPPED_fieldlist, 1,count)<br/>
          count = src_connect.Fetch (fieldlist, 1, record_count)      <br/>
      Wend    <br/>
      <br/>
      targ_connect.Disconnect<br/>
      src_connect.Disconnect<br/>
      Goto ResumeLabel<br/>
      

      ' ———————————————————————————————

      ' here we handle any LotusScript errors

      LSErrorLabel:

      Dim MessageString As String<br/>
      MessageString = Error$ &amp; &quot; at line &quot; &amp; Cstr(Erl())    <br/>
      If (Session Is Nothing) Then<br/>
          'print MessageString<br/>
          <br/>
          'print &quot;LEI may not be correctly installed or configured.&quot;<br/>
      Else<br/>
          Dim Msg As String<br/>
          Dim MsgCode As Long<br/>
          session.GetStatus MessageString, MsgCode, Msg<br/>
          If MessageString=&quot;&quot; Then<br/>
              'print Error$<br/>
              strError = Error$<br/>
          Else<br/>
              'print MessageString<br/>
              strError = messagestring<br/>
          End If<br/>
      End If<br/>
      <br/>
      <br/>
      Resume ResumeLabel  <br/>
      

      ' ———————————————————————————————–

      ResumeLabel:

      <br/>
      resume next<br/>
      

      End Sub