Tuesday, August 16, 2011

Import pictures from Domino database into Lotus Connections

Many organizations who buy IBM Connections already have a employee pictures database in Domino of some sort.

I do believe this would work for all kinds of domino databases, either if you are using the names.nsf or some other picture DB. You just have to find the correct fields in the domino db you are using.

In this scenario, the Iterator is a standalone pictures DB, called pictures.nsf. The field "bc_mapping" contains an email-address of the user. This is used as the link-criteria when doing a lookup to the employee table. The Employee table then gives us the "PROF_KEY" value for that profile/employee, which I then use in the link criteria in the Photo table.

The screenshots will show you some other IF statements and stuff. This is just some logic I used to get the date of when the picture in the domino database had changed, and later on compare it to the date stored in the Photo table in Connections. When the dominodate is newer than the one stored in the Photo table, then I overwrite the picture.

Give me a wink, and I'll blog about this aswell.

So here goes:

The Iterator looks like this:

As you can see, I'm fetching the $FILE field from the domino database.
The screenshot is a bit unclear, but the assignment is: "conn["$FILE"]"
This is the connector for looking up the employee table:


This is the link criteria for the lookup towards the Employee table in Connections:
Here are the fields I select to take with me furter into the assemblyline:
In the lookup error hook, I just type something out, and then do a skip:
This is the connector for the Write to the Photo table: It's in update mode remember. So that it will update the existing records in the db2 table, or create new ones if they don't exist.
And of course, the link criteria is the PROF_KEY field:



This is the fields I'm writing. "image/jpeg" is the value for the "PROF_FILE_TYPE".
And now, this is the important one. the "PROF_IMAGE" has to convert the picture that's saved in Domino into a DB2 BLOB data type:
Here's the entire script used:

try
{
    var item = work.getAttribute("jpegPhoto").getValue(0);
    var filename = work.getAttribute("jpegPhoto").getValue(0).getValueString();

    var maxread = 253600;
    var fis = item.parent.getAttachment(filename).getInputStream();
    var byteStream = new java.io.ByteArrayOutputStream;
    var bytes = new byte[maxread];
    var readCount = 0;
    var readCount = fis.read(bytes, 0, maxread);
    var result = readCount;
    task.logmsg("readcount " + readCount);
    fis.close();
  
    if(readCount >= maxread || readCount<1){
        bytes = null;
        print("Exceeded byte count");
        system.skipEntry();
    } else {
        byteStream.write(bytes, 0, readCount);
        print("got byte array");
        ret.value = new javax.sql.rowset.serial.SerialBlob(byteStream.toByteArray());
    }
}

catch(e)
{
    e.printStackTrace();
}


Feel free to comment if anything is unclear. And also comment if you find this useful.

DO REMEMBER that the picture should not be bigger than 45kb filesize. If it's bigger, then the import will fail!

13 comments:

Luis Benitez said...

Thanks for blogging about this. This looks great!

Unknown said...

No problem, and thank you.

The idea is to spread the loving TDI word when it comes to Connections and Domino integration. Tips and tricks in the ever so growing TDI community. Typically a Domino admin gets to be the Connections Admin aswell, with the need to learn TDI. Hopefully my blog will help them out.

@MattLSM said...
This comment has been removed by a blog administrator.
@MattLSM said...

Thanks for sharing !

George Araujo said...

Great!!! Very useful

Bruno Moraes said...

Great! I Will definitely try this sometime soon. How about that auto updating stuff!? Thanks!

Unknown said...

Hi. Glad to see that this is useful for you guys.

The auto-updating stuff? Do you mean the date-comparison functions? Yes, I can post this in a separate blog for you.

Sridevi said...

I was following your blog to import the photos from domino database to connections 301.

http://blog.robertfarstad.com/2011/08/import-pictures-from-domino-database.html

But it fails at the very last step with the following error. Have you experienced this or do you know how to fix it?

16:18:23,282 INFO - [write entry] CTGDIJ024I Executing update SQL statement modify an entry: UPDATE EMPINST.PHOTO SET PROF_IMAGE = 'javax.sql.rowset.serial.SerialBlob@787c787c' WHERE PROF_KEY='443cd7b0-6ed9-4f9a-bfb1-7c3f56206eca'.
16:18:23,282 INFO - [write entry] CTGDIS495I handleException , update, com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-408, SQLSTATE=42821, SQLERRMC=PROF_IMAGE, DRIVER=3.57.82
16:18:23,298 ERROR - [write entry] CTGDIS810E handleException - cannot handle exception , update
com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-408, SQLSTATE=42821, SQLERRMC=PROF_IMAGE, DRIVER=3.57.82
at com.ibm.db2.jcc.am.bd.a(bd.java:676)
at com.ibm.db2.jcc.am.bd.a(bd.java:60)
at com.ibm.db2.jcc.am.bd.a(bd.java:127)
at com.ibm.db2.jcc.am.km.c(km.java:2506)
at com.ibm.db2.jcc.am.km.d(km.java:2483)
at com.ibm.db2.jcc.am.km.b(km.java:1903)
at com.ibm.db2.jcc.t4.db.h(db.java:220)
at com.ibm.db2.jcc.t4.db.b(db.java:46)
at com.ibm.db2.jcc.t4.t.b(t.java:38)
at com.ibm.db2.jcc.t4.sb.g(sb.java:114)
at com.ibm.db2.jcc.am.km.db(km.java:1898

Unknown said...

Hi.

I´m sure you already googled the error "SQLCODE=-408, SQLSTATE=42821", right? It says that the data type is wrong, which is strange.
http://www.sqlerror.de/db2_sql_error_-408_sqlstate_42821.html

Which version of TDI are you on? And which version of DB2 server?
And are you using the correct db2 drivers from that exact same db2 server?

Unknown said...

And also, how big is the picture that you are trying to import? The filesize should not be over 45kb.

Sridevi said...

Thanks Robert for reverting. I changed PROF_IMAGE script in write_entry to below to fix it. I haven't checked though how big image it can handle now. It had no problems wit 40kb.

try
{
if ( work.getAttribute("jpegPhoto") != null ) {
var item = work.getAttribute("jpegPhoto").getValue(0);
var filename = work.getAttribute("jpegPhoto").getValue(0).getValueString();

task.logmsg("filename 11 "+filename);
var maxread = 1000;
task.logmsg("filename 222"+ item.parent.getAttachment(filename));
var fis = item.parent.getAttachment(filename).getInputStream();
var byteStream = new java.io.ByteArrayOutputStream;
var bytes = new byte[maxread];
var readCount = 0;


while(readCount >= 0) {
var readCount = fis.read(bytes, 0, maxread);
if(readCount > 0) {
byteStream.write(bytes, 0, readCount);
}
}
fis.close();
if(byteStream.size() > 0) {
task.logmsg('Byte Stream is:' + byteStream.toByteArray());
ret.value = byteStream.toByteArray();
}
}else
ret.value = null;
}

catch(e)
{ task.logmsg(" exception while writing the image " + e.getMessage());
e.printStackTrace();
}

@MattLSM said...

Hi again Robert,
I was wondering if you were get through the date-comparison functions, because I'm stuck since few days now on this. (trying to compare a Domino date format with the DB2 date format .. the only comparison which works is unequal lol )

If you did, would you share it with us on your blog ?

Unknown said...

Hi Matt.
Sorry for not seeing your comment on this before now.

I can blog about the date comparison, for sure. Just have to find available time to do it :-)