How do I execute a sql statement in lotusscript if the table has a timestamp field/column?

Question

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: 25-JUL-12 10.28.32.000000 AM

The following is my lotusscript 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) > 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

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 Call conUser.Execute("Select * From HR.EMPLOYEES", fieldsUser). I thought it has something to do with the line fieldlistuser="EMPLOYEE_ID,FIRST_NAME,MODIFIED_AT" so I remove MODIFIED_AT from it and commented staffdoc.DateJoin=fieldsUser.MODIFIED_AT(0) but the error still occurs. The error is Error: Invalid data type for field 'MODIFIED_AT', Connector'Oracle', Method -Execute-. Can this actually be done at all? If can, what datatype in lotus should I store the timestamp value?

It looked like the Help docs said it handles DateTime values as Variants. I wouldn't think that would matter when using extended syntax to assign the value, but does saving MODIFIED_AT into a variant first help at all? — David Navarre
Also, since DateTimes in Notes are arrays, does specifying the index of MODIFIED_AT create problems? Does using fieldsUser.GetField or fieldsUser.Lookup work any better? Or is the error sprung when it hits the Execute? — David Navarre
The error sprung when it hits the execute so I guess there's no telling whether specifying the index of MODIFIED_AT create problems. — user1409217

Ken Pespisa Ken Pespisa · Accepted Answer · 2012-07-25T12:51:36

My first thought is does the select statement work fine at the server?

Assuming it does, this looks like a problem with the driver converting the timestamp to a Lotus Notes datetime field. As a workaround you could create a stored procedure that returns 'modified at' as a datetime instead of a timestamp type.

How do I execute a sql statement in lotusscript if the table has a timestamp field/column?

2 Answers