Problem solve Get help with specific problems with your technologies, process and projects.

Transferring picture data between Notes and Oracle table

Good day. I have a problem transferring pictures data (jpg) from files between Lotus Notes and Oracle table with LC LSX. Can you help? And, possibly send example script?
The solution to your problem depends on how the pictures are stored. You can handle file attachments using the Notes Connector, but if the image is imported into a rich text field, it is not stored as a file, and the LC LSX does not know how to extract the image data from the rich text.

As far as copying attachments, I don't have a code sample to offer, but I can talk about how it's done. There is some code below, but here's my disclaimer: it hasn't been tested. I'm making it up as I go along.

Working with attachments in the LC LSX is not as straightforward as normal fields. You can't simply get the files as BLOB values and write them into the relational database. Instead, you must tell the LC LSX to extract them into a directory you specify, then use a different Lotus connector – the "File" connector – to load the contents into memory.

The Notes connector properties you need are:

LoadFile: Set TRUE to make Notes extract the files.

FilePath: Set to the path that you would like to files extracted to. Do not use the Notes data or executable directory (also don't leave this property blank, which has the same effect). You should create a temporary directory on the computer that will execute the LotusScipt code.

So you might initialize the Notes connector as follows:

Dim lcconNotes As New LCConnection("notes")
lcconNotes.LoadFile = TRUE
lcconNotes.Filepath = "C:\TEMP\notesattach"

You also need a File connector, which you would instantiate with a statement such as:

Dim lcconAttach As New LCConnection("file")

You can read about File connections in the help documents File System Connector Properties and File System Metadata in the ND6 Designer help (also found in the Lotus Connector LotusScript Extensions Guide and Connectors and Connectivity Guide, respectively).

Basically, the File connector treats a local file directory as if it were a relational table, one row per file. The Database property gives the pathname of the directory one level above the directory you want, and the Metadata property tells which subdirectory of the Database directory you're interested in. Continuing with the above example, you might write:

lcconAttach.Database = "C:\TEMP"
lcconAttach.Metadata = "notesattach"
lcconAttach.Binary = TRUE ' these are binary (JPG) files; default is to treat files as text

When you "fetch" a document from the Notes database, any files attached to the document will be automatically written into this directory. The results from Notes will include a field named FILE whose value is a multivalued text list of the filenames. Reading values out of multivalued fields is an art in itself, but you don't need to do it for your purposes, because you can also get the filenames by reading rows from the File connection. So after you fetch from Notes, the next step is to select from the File connector.

Do While lcconNotes.Fetch(flNotes) 
   ' At this point do your stuff with 
the "regular" Notes fields. 
   ' E.g. copy the Notes fields to the RDB. 
   Set flFile = New LCFieldlist 
   lcconFile.Select Nothing, 1, flFile 
   Do While lcconFile.Fetch(flFile) 
      'flFile fieldlist has Contents 
BLOB field you can insert in relational table. 
      ' The FileName property may also 
be useful.  
      ' At this point copy one attachment
 to the RDB 
   lcconFile.Action LCACTION_TRUNCATE '
 delete the extracted Notes attachments 

Do you have comments on this Ask the Expert question and response? Let us know.

Dig Deeper on LotusScript



  • iSeries tutorials

    Search400.com's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...