title: How do I execute a sql statement in lotusscript if the table has a timestamp field/column?
body: <p>I'm working to retrieve data from oracle 9i to lotus notes. I'm using Oracle 10g for testing since that's what I had. In the default HR database, there is an EMPLOYEES table. I've recently added a new column to get the last modified timestamp which is successful. The data is in the form like this: <strong>25-JUL-12 10.28.32.000000 AM</strong> </p>
  
  <p>The following is my lotusscript code:</p>
  
  <pre><code>Option Public
  Option Declare
  
  UseLSX "*lsxlc"
  
  %Include "lsconst.lss"
  
  Sub Initialize
      Dim s As New NotesSession, db As NotesDatabase
      Set db=s.Currentdatabase
  
      Dim lcs As New Lcsession
      lcs.Clearstatus
  
      Dim conUser As New Lcconnection("Oracle")
      Dim staffdoc As NotesDocument
      Dim fieldlistuser$
      fieldlistuser="EMPLOYEE_ID,FIRST_NAME,MODIFIED_AT"
  
      conUser.Server="localhost"
      conUser.UserID="hr"
      conUser.Password="hr"
      conUser.Connect
      conUser.MetaData="HR.EMPLOYEES"
      conUser.Fieldnames=fieldlistuser
  
      Dim fieldsUser As New LCFieldList
      Dim fieldUser As LCField
      Call conUser.Execute("Select * From HR.EMPLOYEES", fieldsUser)
  
      While conUser.Fetch(fieldsUser) &gt; 0
          Set staffdoc=New NotesDocument(db)
          staffdoc.Form="Staff"
          staffdoc.StaffID=fieldsUser.EMPLOYEE_ID(0)
          staffdoc.StaffName=fieldsUser.FIRST_NAME(0)
          staffdoc.DateJoin=fieldsUser.MODIFIED_AT(0)
          Call staffdoc.Save(True, True)
      Wend
      conUser.Disconnect
  End Sub
  </code></pre>
  
  <p>Previously everything was ok before I added the timestamp column in the EMPLOYEES table and I can export every row to a temporary lotus view. Now the error always stop at <code>Call conUser.Execute("Select * From HR.EMPLOYEES", fieldsUser)</code>. I thought it has something to do with the line <code>fieldlistuser="EMPLOYEE_ID,FIRST_NAME,MODIFIED_AT"</code> so I remove <code>MODIFIED_AT</code> from it and commented <code>staffdoc.DateJoin=fieldsUser.MODIFIED_AT(0)</code> but the error still occurs. The error is <strong>Error: Invalid data type for field 'MODIFIED_AT', Connector'Oracle', Method -Execute-</strong>. Can this actually be done at all? If can, what datatype in lotus should I store the timestamp value?</p>