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, Jul 25 '12 at 16:26
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, Jul 25 '12 at 16:29
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, Jul 27 '12 at 07:35

score 0 · Accepted Answer · answered Jul 25 '12 at 12:51

0

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.

answered Jul 25 '12 at 12:51

Ken Pespisa

21,989
3
55
63

I haven't tested on server yet. Only working locally. I've changed the datatype of modified_at to Date instead of Timestamp and it worked but then I'd have to format/convert the date into the format that I wanted(like the timestamp) and I might lost the precision. As of now I'll just discuss with my boss if this is acceptable. Anyway I found LCTOKEN_STAMPFIELD while searching 'Oracle Connector Properties' in the lotus notes help but can't figure out if it has any use and how to use it. Anybody? – user1409217 Jul 25 '12 at 16:30

David Navarre · Answer 2 · 2012-07-25T14:48:16.150

0

Just looking at help docs and I'm wondering if captialization matters, as the LCConnection property I see listed is FieldNames, not Fieldnames.

edited Jul 25 '12 at 14:48

answered Jul 25 '12 at 13:51

David Navarre

1,022
10
27

I tried change Fieldnames to FieldNames with working sample just to verify but everything seems to be ok. LotusScript is case-insensitive most of the time by the way. – user1409217 Jul 25 '12 at 16:14
While I knew LS was case-insensitive, I wasn't sure if it passed the properties through that way or not. Always worth a try! – David Navarre Jul 25 '12 at 16:15

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

2 Answers2