Represent Lotus Domino data in Oracle Database using the Oracle Data Cartridge Interface and the Domino Java API

Each of us faced the need to integrate different systems. I would like to talk about how to provide the ability to display data from Lotus Domino using an SQL select statement in Oracle Database. Perhaps this topic will not be interesting to many. But, in my opinion, these two commercial products deserve to write about the opportunities they provide.

Those wishing to understand the topic can preview two documents: Example: Pipelined Table Functions: Interface Approach, part of the Java Implementation of the ODCITable Methods and Java Access
Based on them, I created the following example, the purpose of which is to demonstrate the fundamental possibility of using data from a Domino address book in Oracle queries. It is assumed that both software products are installed in linux in the standard way, and the files and actions themselves are located and executed in the / home / user / java / oracle_domino directory. Required additional comments can be found in both documents.

For starters, domino_names_odci_mem.java:

 import lotus.domino.*; import java.util.*; import java.io.*; import oracle.sql.*; import java.sql.*; import java.math.*; import oracle.CartridgeServices.*; import java.util.Calendar; import java.text.SimpleDateFormat; public class domino_StoredCtx { //   ,   domino Vector vnames; } //   SQLData,          public class domino_names_odci_mem implements SQLData { private BigDecimal key; final static BigDecimal SUCCESS = new BigDecimal(0); final static BigDecimal ERROR = new BigDecimal(1); //  SQLData String sql_type; public String getSQLTypeName() throws SQLException { return sql_type; } public void readSQL(SQLInput stream, String typeName) throws SQLException { sql_type = typeName; key = stream.readBigDecimal(); } public void writeSQL(SQLOutput stream) throws SQLException { stream.writeBigDecimal(key); } // ODCI: ODCITableStart,ODCITableFetch,ODCITableClose (  ,   ) static public BigDecimal ODCITableStart(STRUCT[] sctx,String param) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); domino_StoredCtx ctx = new domino_StoredCtx(); StructDescriptor outDesc = StructDescriptor.createDescriptor("DOMINO_NAME", conn); Object[] out_attr = new Object[4]; //     10  int nrowsval = 10; ctx.vnames = new Vector(); try { //     domino       lotus.domino.Session session = NotesFactory.createSession("ip_of_domino_server:DIIOP_port","user","password"); lotus.domino.Database db = session.getDatabase("", "names.nsf"); lotus.domino.View vw = db.getView("People"); lotus.domino.Document doc = vw.getFirstDocument(); while(nrowsval>0 && doc != null){ String shortName = doc.getItemValueString("ShortName"); String altFullName = doc.getItemValueString("AltFullName"); lotus.domino.Name name = session.createName(altFullName); String commonName = name.getCommon(); String employeeID = doc.getItemValueString("EmployeeID"); out_attr[1-1] = (Object)new String(shortName); out_attr[2-1] = (Object)new String(commonName); out_attr[3-1] = (Object)new String(employeeID); out_attr[4-1] = (Object)new String(now()); ctx.vnames.add((Object)new STRUCT(outDesc, conn, out_attr)); nrowsval--; doc = vw.getNextDocument(doc); } } catch(Exception e) { e.printStackTrace(); } int key; try { //        ODCITableFetch key = ContextManager.setContext(ctx); } catch (CountException ce) { return ERROR; } Object[] impAttr = new Object[1]; impAttr[0] = new BigDecimal(key); StructDescriptor sd = new StructDescriptor("DOMINO_NAMES_ODCI_MEM",conn); sctx[0] = new STRUCT(sd,conn,impAttr); return SUCCESS; } public BigDecimal ODCITableFetch(BigDecimal nrows, ARRAY[] outSet) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); domino_StoredCtx ctx; try { //    ctx=(domino_StoredCtx)ContextManager.getContext(key.intValue()); } catch (InvalidKeyException ik ) { return ERROR; } // ODCITableFetch  ,      //..  ,     2 ,    ,    if(ctx.vnames==null) return SUCCESS; //     Object out_arr[] = ctx.vnames.toArray(); ArrayDescriptor ad = new ArrayDescriptor("DOMINO_NAMES_TABLE",conn); outSet[0] = new ARRAY(ad,conn,out_arr); // ,       ctx.vnames=null; return SUCCESS; } public BigDecimal ODCITableClose() throws SQLException { domino_StoredCtx ctx; try { ctx=(domino_StoredCtx)ContextManager.clearContext(key.intValue()); } catch (InvalidKeyException ik ) { return ERROR; } return SUCCESS; } //      public static final String DATE_FORMAT_NOW = "yyyy-MM-dd HH:mm:ss"; public static String now() { Calendar cal = Calendar.getInstance(); SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_NOW); return sdf.format(cal.getTime()); } } 
 

import lotus.domino.*; import java.util.*; import java.io.*; import oracle.sql.*; import java.sql.*; import java.math.*; import oracle.CartridgeServices.*; import java.util.Calendar; import java.text.SimpleDateFormat; public class domino_StoredCtx { // , domino Vector vnames; } // SQLData, public class domino_names_odci_mem implements SQLData { private BigDecimal key; final static BigDecimal SUCCESS = new BigDecimal(0); final static BigDecimal ERROR = new BigDecimal(1); // SQLData String sql_type; public String getSQLTypeName() throws SQLException { return sql_type; } public void readSQL(SQLInput stream, String typeName) throws SQLException { sql_type = typeName; key = stream.readBigDecimal(); } public void writeSQL(SQLOutput stream) throws SQLException { stream.writeBigDecimal(key); } // ODCI: ODCITableStart,ODCITableFetch,ODCITableClose ( , ) static public BigDecimal ODCITableStart(STRUCT[] sctx,String param) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); domino_StoredCtx ctx = new domino_StoredCtx(); StructDescriptor outDesc = StructDescriptor.createDescriptor("DOMINO_NAME", conn); Object[] out_attr = new Object[4]; // 10 int nrowsval = 10; ctx.vnames = new Vector(); try { // domino lotus.domino.Session session = NotesFactory.createSession("ip_of_domino_server:DIIOP_port","user","password"); lotus.domino.Database db = session.getDatabase("", "names.nsf"); lotus.domino.View vw = db.getView("People"); lotus.domino.Document doc = vw.getFirstDocument(); while(nrowsval>0 && doc != null){ String shortName = doc.getItemValueString("ShortName"); String altFullName = doc.getItemValueString("AltFullName"); lotus.domino.Name name = session.createName(altFullName); String commonName = name.getCommon(); String employeeID = doc.getItemValueString("EmployeeID"); out_attr[1-1] = (Object)new String(shortName); out_attr[2-1] = (Object)new String(commonName); out_attr[3-1] = (Object)new String(employeeID); out_attr[4-1] = (Object)new String(now()); ctx.vnames.add((Object)new STRUCT(outDesc, conn, out_attr)); nrowsval--; doc = vw.getNextDocument(doc); } } catch(Exception e) { e.printStackTrace(); } int key; try { // ODCITableFetch key = ContextManager.setContext(ctx); } catch (CountException ce) { return ERROR; } Object[] impAttr = new Object[1]; impAttr[0] = new BigDecimal(key); StructDescriptor sd = new StructDescriptor("DOMINO_NAMES_ODCI_MEM",conn); sctx[0] = new STRUCT(sd,conn,impAttr); return SUCCESS; } public BigDecimal ODCITableFetch(BigDecimal nrows, ARRAY[] outSet) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); domino_StoredCtx ctx; try { // ctx=(domino_StoredCtx)ContextManager.getContext(key.intValue()); } catch (InvalidKeyException ik ) { return ERROR; } // ODCITableFetch , //.. , 2 , , if(ctx.vnames==null) return SUCCESS; // Object out_arr[] = ctx.vnames.toArray(); ArrayDescriptor ad = new ArrayDescriptor("DOMINO_NAMES_TABLE",conn); outSet[0] = new ARRAY(ad,conn,out_arr); // , ctx.vnames=null; return SUCCESS; } public BigDecimal ODCITableClose() throws SQLException { domino_StoredCtx ctx; try { ctx=(domino_StoredCtx)ContextManager.clearContext(key.intValue()); } catch (InvalidKeyException ik ) { return ERROR; } return SUCCESS; } // public static final String DATE_FORMAT_NOW = "yyyy-MM-dd HH:mm:ss"; public static String now() { Calendar cal = Calendar.getInstance(); SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_NOW); return sdf.format(cal.getTime()); } }


Now the shell script that will prepare and display everything.
NCSO.jar in the case of a standard Domino installation can be taken from the directory / local / notesdata / domino / java
')

 export ORACLE_SID=MYDB #      sqlplus '/as sysdba' <<EOF drop user udomino cascade; / create user udomino identified by "pdomino"; grant connect,resource to udomino; grant create any directory,drop any directory to udomino; exec dbms_java.grant_permission( 'UDOMINO', 'SYS:java.net.SocketPermission', 'ip_of_domino_server:DIIOP_port', 'connect,resolve' ); EOF # Oracle Database      jar,   Domino,         #     , .. Oracle ,      loadjava -user udomino/pdomino -order -resolve -genmissing -verbose -resolver "((* UDOMINO) (* PUBLIC) (* -))" NCSO.jar #         sqlplus -S /nolog <<EOF connect udomino/pdomino drop type domino_names_odci_mem; drop type domino_names_table; drop type domino_name; drop function domino_names; drop java source domino_names_odci_mem_source; drop directory javadir / create type domino_name as object ( shn varchar(20), fio varchar(256), tab_n varchar(20), dt varchar(20) ); / create type domino_names_table as table of domino_name; / create or replace directory javadir as '/home/user/java/oracle_domino'; / create and compile java source named domino_names_odci_mem_source using bfile (javadir,'domino_names_odci_mem.java'); / show errors #   ODCITable create type domino_names_odci_mem as object ( key integer, static function ODCITableStart(sctx in out domino_names_odci_mem,param varchar2) return number as language java name 'domino_names_odci_mem.ODCITableStart(oracle.sql.STRUCT[],java.lang.String) return java.math.BigDecimal', member function ODCITableFetch(self in out domino_names_odci_mem, nrows in number, outSet out domino_names_table) return number as language java name 'domino_names_odci_mem.ODCITableFetch(java.math.BigDecimal, oracle.sql.ARRAY[]) return java.math.BigDecimal', member function ODCITableClose(self IN domino_names_odci_mem) return number as language java name 'domino_names_odci_mem.ODCITableClose() return java.math.BigDecimal' ); / show errors create function domino_names(param varchar2) return domino_names_table pipelined using domino_names_odci_mem; / EOF #      export NLS_LANG=American_America.UTF8 sqlplus -S /nolog<<EOF connect udomino/pdomino set serveroutput on call dbms_java.set_output(1000000); set linesize 160 set pagesize 1000 set feedback off col fio for a50 col tab_n for a10 col shn for a20 col dt for a20 select * from table(domino_names('some_string_param')) order by dt; exit; EOF 
 

export ORACLE_SID=MYDB # sqlplus '/as sysdba' <<EOF drop user udomino cascade; / create user udomino identified by "pdomino"; grant connect,resource to udomino; grant create any directory,drop any directory to udomino; exec dbms_java.grant_permission( 'UDOMINO', 'SYS:java.net.SocketPermission', 'ip_of_domino_server:DIIOP_port', 'connect,resolve' ); EOF # Oracle Database jar, Domino, # , .. Oracle , loadjava -user udomino/pdomino -order -resolve -genmissing -verbose -resolver "((* UDOMINO) (* PUBLIC) (* -))" NCSO.jar # sqlplus -S /nolog <<EOF connect udomino/pdomino drop type domino_names_odci_mem; drop type domino_names_table; drop type domino_name; drop function domino_names; drop java source domino_names_odci_mem_source; drop directory javadir / create type domino_name as object ( shn varchar(20), fio varchar(256), tab_n varchar(20), dt varchar(20) ); / create type domino_names_table as table of domino_name; / create or replace directory javadir as '/home/user/java/oracle_domino'; / create and compile java source named domino_names_odci_mem_source using bfile (javadir,'domino_names_odci_mem.java'); / show errors # ODCITable create type domino_names_odci_mem as object ( key integer, static function ODCITableStart(sctx in out domino_names_odci_mem,param varchar2) return number as language java name 'domino_names_odci_mem.ODCITableStart(oracle.sql.STRUCT[],java.lang.String) return java.math.BigDecimal', member function ODCITableFetch(self in out domino_names_odci_mem, nrows in number, outSet out domino_names_table) return number as language java name 'domino_names_odci_mem.ODCITableFetch(java.math.BigDecimal, oracle.sql.ARRAY[]) return java.math.BigDecimal', member function ODCITableClose(self IN domino_names_odci_mem) return number as language java name 'domino_names_odci_mem.ODCITableClose() return java.math.BigDecimal' ); / show errors create function domino_names(param varchar2) return domino_names_table pipelined using domino_names_odci_mem; / EOF # export NLS_LANG=American_America.UTF8 sqlplus -S /nolog<<EOF connect udomino/pdomino set serveroutput on call dbms_java.set_output(1000000); set linesize 160 set pagesize 1000 set feedback off col fio for a50 col tab_n for a10 col shn for a20 col dt for a20 select * from table(domino_names('some_string_param')) order by dt; exit; EOF


As a result, approximately the following will be displayed (at the end):

 SHN FIO TAB_N DT -------------------- ------------------------------ ---------- -------------------- user    12305 2011-08-25 11:55:14 .... 
 

SHN FIO TAB_N DT -------------------- ------------------------------ ---------- -------------------- user 12305 2011-08-25 11:55:14 ....


The fact that the result is obtained is the result of many steps to configure, in particular, the Domino server. Those. it is impossible to simply put the files “where necessary” and execute the shell script. But, I think, for those who wish, the cited references to documents and an example can help to achieve what they need.