It's always a challenging job to build a system which communicates with Oracle.From past few years I was working only on web applications.Two days back I've asked to suggest best method to move Lotus Notes documents to Oracle without using local or server level DSN.
I was very much familiar with Lotus Notes ODBC method , which is very simple and easy to implement. However it's not very good for large volume of data,though I was not suppose to consider because it needs DSN.Next,I've considered LC LSX but assumed it needs DSN too ( point me if I am wrong ),You don't need any DSN in LC LSX method(look at the comments section for more information).Finally I thought about JDBC which is Java based engine to connect Oracle database.Funny thing was that, I studied Java in my graduation and never ever used in working environment except some simple domino java agents. I've checked with my mates and over the net, but didn't got much to start. So, I had decided to do by myself and with help of my wife ( Rashmi, who is Java developer :) ).
My first task was to start looking correct versions of software which I need along with suitable drivers to connect them.Let's have a look what I've done.
First, My wife suggested me to setup Java environment in my workstation.I've done by knowing Domino itself built on Java.
 Java JDK |
Next, I had started looking to Oracle versions which should be lite and easy to use for newbie.I've chosen Oracle 10g express edition which was up to my expectation and could easily install in my machine.Here is the one,
 Oracle 10g Express |
Third, I needed correct jdbc driver which should be compatible with Oracle 10g . Here is the one which I've used,
 JDBC drivers |
Domino-Java coding starts from here,
Create new Java agent and click on "Edit Project" to link jar file in your agent. Find your .jar file and add in "Current Agent files" list at the right side.
 |
Next, you need to start importing required classes for connectivity . Add below mentioned classes at the beginning of the agent along with "lotus.domino.*;" package.
import java.sql.*;
import oracle.jdbc.*;
Next step is to register our jdbc driver . To do so , Add this line in your code.
// Register your driver
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
Once driver is registered we need to establish a connection with the Oracle. Here is the code which establish connection with Oracle.This is the most important line which needs little bit knowledge of Oracle otherwise you might struggle like me :)
// Build connection with Oracle
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@SPRPRG020V.ap.vdgc.com:1521:XE",
"SYSTEM", "password");
Some important points about the above line,
* I am using thin driver which is also called level 4 driver. There are one more called OCI driver also known as level 2 driver. Oracle thin driver is entirely written in Java so no other software need to install to use this driver where as Oracle OCI driver requires Oracle client to be installed in order to use this driver.So,When you need to connect Oracle remotely without installing Client , go for Oracle thin driver.
* Passing "Host name:Port:SID"SID is nothing but a "Oracle System ID".I've spent 1 hour in this line to pass correct host name,port number and SID. To make it quicker and easier , Open "tnsnames.ora" file where Oracle runs . It can be found in C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN ( for my example ). E.g,
 |
* Last argument is log-in user name and password of Oracle database.
Now your connection is established. To test this create table in Oracle and fire some SQL statements. Something like,
// Query the Customer table
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery ("SELECT cust_name FROM Customer");
// Read something and print to console , getString to get & setString to set
System.out.println("Printing existing records ...");
while (rset.next())
System.out.println (rset.getString(1));
System.out.println("Inserting new records ...");
// Insert example
stmt.executeUpdate("INSERT INTO Customer VALUES('001','Rishi')");
You can view the output in Java console ,
 |
Even in Oracle web console ,
 |
Here is the complete code,
import lotus.domino.*; import java.sql.*; import oracle.jdbc.*; public class JavaAgent extends AgentBase { public void NotesMain() { try { Session session = getSession(); AgentContext agentContext = session.getAgentContext(); lotus.domino.Session s = getSession(); lotus.domino.AgentContext agentContex = s.getAgentContext(); // Register your driver DriverManager.registerDriver (new oracle.jdbc.OracleDriver()); // Build connection with Oracle using thin driver Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@SPRPRG020V.ap.vdgc.com:1521:XE", "SYSTEM", "password"); // Query the Customer table Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery ("SELECT cust_name FROM Customer"); // Read something and print to console , getString to get & setString to set System.out.println("Printing existing records ..."); while (rset.next()) System.out.println (rset.getString(1)); System.out.println("Inserting new records ..."); // Insert example stmt.executeUpdate("INSERT INTO Customer VALUES('001','Rishi')"); // close the result set, statement, and the connection rset.close(); stmt.close(); conn.close(); } catch(Exception e) { e.printStackTrace(); } } } |
One more thing which I forgot to mention is that I've used Eclipse to write my code because Domino doesn't provide type ahead for methods/property.
Though it's just a POC to kick off my project , But definitely got some confidence to start my first Java project in Domino. Hope I can write all my learning here to help other Domino developers to start quickly with Java. I would love to see your suggestions/comments.
Rob and pete has suggested me to use LC LSX method in comments section of this post . So, Written the sample code for connectivity using LC LSX and it works well . Here is the code who wish to use as a reference ,
'Adding LC LSX library Option Public Uselsx "*lsxlc" Sub Initialize On Error Goto handler Dim connect As New LCConnection ("Oracle") ' You can pass Db2 or odbc2 for SQL Server Dim fldLst As New LCFieldList Dim keyLst As New LCFieldList Dim fld As LCField Dim count As Integer 'connect.database="XE" 'Don't use this method because it doesn't work connect.Server = "XE" ' I've Oracle express edition 10g . To check server name open "tnsnames.ora" file and check SID connect.Userid = "SYSTEM" ' Id connect.Password = "password" ' Password connect.Metadata = "customer" ' Table name connect.Connect Print "Successfully connected to Oracle...." connect.FieldNames = "cust_no,cust_name" ' Two columns in customer table Set fld = keyLst.Append ("cust_no", LCTYPE_INT) fld.Flags = LCFIELDF_KEY_NE Or LCFIELDF_KEY fld.Value = 200 If (connect.Select (keyLst, 1, fldLst) = 0) Then Print "No data found." End End If Set fld = fldLst.Lookup ("cust_name") Print "The 'contact names' stored in the table are:" While (connect.Fetch (fldLst) > 0) count = count + 1 Msgbox " record #" & Cstr(count) & " = '" & fld.text(0) & "'" Wend If (count = 0) Then Print "The table contains no records." Exit Sub handler: Msgbox "Connection failed with error " & Err & ": " & Error Exit Sub End Sub |
To be continued....
Posts
June 1, 2009 at 3:03 AM
Dear
As useual you are spreading your learing which is great and makes you different from others.
Now coming to the post.. its a nice step by step details.. certainly which will be a great help for any one who doesnt know about this envirnoment...
Few basic questions for me because i know nothing about this environmnet..
The agent which you are creating to fetch or push the data, is this a agent created in the domino db ? (Dont laugh if its a stupid question :) ).
Second question what is the difference and beifit of using this method over the traditional ODBC connectivty using lsxLC classes...
This time kudos to rashi also.. to make u understand about java..
keep them coming..
cheers mate... mithun
June 1, 2009 at 5:46 AM
I don't for simple data push and pull why you have written such a dangerous code:) sorry but I think this much is not needed. and the other query I have is Can I transfer data from notes to oracle in runtime.
June 1, 2009 at 6:24 AM
@Animesh,
I've requirement to move LN contents to Oracle without using DSN, Can you suggest me which method in LN allows to do the same ? Most of the methods are ODBC based where you need to create DSN on client or server machine where code is running.
Yes, this method transfer data in run time. Actually in the code I've hard coded the query. Next, I've to read LN documents using Java and passed those data to Oracle. It's just a sample to connect to Oracle using JDBC in LN. I'll add another post once I move LN documents.
June 1, 2009 at 6:37 AM
@Mithun,
Here is the answer,
* Yes, agent is domino Java agent (java selected from agent type selection)
* ODBC:-if I wanted to use ODBC, I needed to configure the ODBC connection at the O/S level, I could then access it by name in the program
JDBC:-However, I did not have to configure anything at the O/S level.
So, I would say Java offers several benefits: portability,errors, reusability of objects and integration with intranet/Internet technologies.
At the end I believe you've to create an opportunity for you in your own environment to learn maximum. I could convince my PM for ODBC but I didn't because I wish to do something new.
June 1, 2009 at 8:28 AM
Hi Rishi,
I have few queries here.
1.Does the driver which you have installed on your machine is needed on every machine or .. I am asking these questions becoz I am new as well in this area.
2.And the other question is does the oracle needs to be installed on every machine or not.
June 1, 2009 at 8:47 AM
The LC LSX supports access to Oracle using a native Oracle connector. It does not require you to use ODBC and so you don't need a DSN.
You can find information on configuring connectivity in the Lotus Connectors and Connectivity Guide found here: https://www.ibm.com/developerworks/lotus/documentation/lei/
A good resource for infomation on the LC LSX can be found in technote 1233597
http://www-01.ibm.com/support/docview.wss?uid=swg21233597
June 1, 2009 at 1:09 PM
I think you should consider the LC LSX. You need to have the Oracle client loaded where the LotusScript code runs. However, this can be on the server. It doesn't need to be on each client.
You can also invoke Oracle stored procedures this way and pass parameters so that the main code executes on the Oracle server.
June 1, 2009 at 6:44 PM
@Pete/Rob,
Thanks guys, Just tried with test program and LC LSX works fine as you've mentioned. Only found one funny thing, While connecting to the database it's mentioned to use
connection.Database="XE" method
But if you try this, it throws an error saying "Connection failed with error 182: Instance member DATABASE does not exist". But I've found the correct syntax ,
connect.Server = "XE"
But nowhere it's mentioned in the documentation.
Thanks again,
Rishi
June 1, 2009 at 7:13 PM
@Animesh,
Here is the answer,
* Driver is nothing but a java jar file. Which doesn't need to install instead just link with your agent.I've mentioned same in the blog.So, wherever your code deploys it will work from there.
* Oracle doesn't need to install in every machine. Just need to install where code will run.So, if code runs on Server A, Server A needs Oracle to be installed.
Rishi
June 17, 2009 at 1:43 PM
Hi, Rishi!
Thanks for your post! It's useful for me! I'm trying to do my first steps in linking domino and oracle with jdbc. And your post is quite lite and simple!
WBR!
June 23, 2009 at 6:28 PM
@Roman,
Good to hear if my post helping you out.Feel free to share your thoughts and challenges in your JDBC project.
July 3, 2009 at 7:37 PM
Dear All,
I have few queries, would appreciate your help:
Our client have Domino combination [7.0.1+5.0.8] and Oracle version is 9i.
Now they wish to upgrade Domino to version 8.5,Oracle to version 11G and LEI version to 8.5.
Web App's -> Domino Data -> LEI -> Oracle -> Reporting
Web apps stores data directly to Domino database and thru LEI data is pushed to Oracle and used for further reporting purpose.
QUESTION: Does Domino 8.5 supports Oracle 11G?
FYI: LEI 8.5 supports Oracle 11G.
Please reply. Thanks.
Kuldeep Singh Solanki
July 10, 2009 at 1:55 AM
@ Kuldeep,
LEI 8.5 supports following RDBMS,
- AIX 6.1
- RHEL5.1
- DB2 9.5
- MYSQL5.0 - ODBC only.
- Oracle 11g
- Data Direct ODBC 5.3 SP1 drivers
December 21, 2011 at 11:22 AM
Rishi,
I'm trying to get my Lotusscript code to connect to oracle but get the following error:
Error:[Attach Server]ORA-12154: TNS: could not resolve the connect identifier specified, Connector 'oracle', Method-Connect-
the following is my code:
Uselsx "*lsxlc"
Dim Ora_Server As String, Ora_User As String, Ora_Password As String
Ora_Server = "XE"
Ora_User = "xxxxx"
Ora_Password = "xxxxxx"
Dim OraCon As New LCConnection( "oracle" )
OraCon.Server = Ora_Server
OraCon.UserID = Ora_User
OraCon.Password = Ora_Password
OraCon.Connect
I have oracle client 10g express edition installed on my machine. The oracle 10g XE server is also running on my machine. I have tnsnames.ora file at $ORACLE_HOME/NETWORK/ADMIN and it has entry for the "XE". i have checked using "lsnrctl services" command and I could see the XE service name. I have set the TNS_ADMIN environment variable and the tnsnames.ora is also in my system path. I can connect to oracle using sqlplus.
I do not have connectivity to Domino server. I just have a local copy of the nsf file from which I'm trying to extract the data and put in oracle. I really do not know what else to look for. I will appreciate if you can point me in the right direction.
Thanks.
December 22, 2011 at 8:45 AM
Hi 04cdc8ca-2c09-11e1-b3b1-000bcdcb8a73,
There is something wrong in connection string. BTW, XE shouldn't be XE , XE is service name.. Server should be something else. Can you post me ora file to verify?
Rishi
December 22, 2011 at 9:11 AM
Rishi,
here is my XE entry from the tnsnames.ora file:
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.200)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
Thanks for replying,
Arshad.
December 22, 2011 at 9:13 AM
Host will be your server name and XE is service name.
December 22, 2011 at 9:49 AM
I modified the Server as follows:
OraCon.Server = "10.1.1.200"
but it still does not work. do you have any suggestions?
Thanks.
December 22, 2011 at 9:50 AM
Can you access your server with this ip ?
December 22, 2011 at 10:10 AM
yes I can access the server. I did a ping and able to access it. I also did a "tnsping XE" to check the listener and its running fine. I did "lsnrctl status" and the listener is ok and the service "XE" is registered with it.