Preface:
How to link notes and relational databases (RDBMS) has always been a headache for many notes application developers. Render manager notes starts from r5.0 (or even goes back to r4.6x later than release) and provides DECS to allow the Notes application system to develop individual settings, you can access the data on RDBMS in the Notes application, but you can only add new data on the Notes client to the expected RDBMS, there is no way to allow the newly added information on the RDBMS end to be reversed on the notes end, so that the actual discount value of DECS is greatly increased. On the other hand, render manager Lei is indeed a very difficult solution, but high sales volume is not enough for most small and medium-sized enterprises. I guess ODBC or ADO should be used by many people at present, but it can be used by hundreds or even dozens of dynamic terminal PCs, installing robot programs and setting up scripts is truly a hope for everyone. Therefore, I would like to share this notebook with you, hoping to help you a little bit.
Oracle JDBC driver:
JDBC is a set of APIs that allow Java programs to access RDBMS. Here, we do not want to discuss too many discussions about JDBC APIs, if you are interested, you can refer to the introduction of JDBC. As far as the Notes client completes Oracle database, the following two methods can be used:
- Oracle OCI driver
Oracle OCI driver is a native migration program provided by Oracle. When you need to use store procedure in large quantities, Oracle recommends that you use oracle oci driver, the best performance is achieved with optimized performance. However, when using the Oracle OCI driver, the client PC must install the Oracle client program, my notebook has never been able to successfully install the Oracle client program ).
- Oracle thin driver
Oracle thin driver is also a pure Java notebook provided by Oracle, which can be used together with the terminal or Java applet. The advantage of using the Oracle thin driver is that you can use Java applications to access the Oracle database without any prior security or setup. However, in contrast, the performance of the primary node may be significantly worse than that of the Oracle OCI driver.
In this example, we will use the Oracle thin driver to connect to the Oracle database, the reason is very simple-you don't need to set up the bunch of remote PCs that you can never decide to use.
Zookeeper jobs:
First, you must first obtain the Oracle thin JDBC driver.Http://otn.oracle.com/software/tech/java/sqlj_jdbc/content.htmlOffline website, of course, you must first become an Oracle Web site member, but it will not take much time, and later, you can download all the Oracle databases (more generous than IBM ??). Please note that we need JDK 1.2.
After completing the following steps, place the classes12.zip folder on the merged disk path (for example, c: \ Lotus \ notes \ data \ domino \ Java ), then we will go to c: \ Lotus \ notes. INI plugin added to J avauserclasses = c: \ Lotus \ notes \ data \ domino \ Java \ classes12.zip settings, let the Java connector in Domino designer find the Java class program we need.
Next, you can start to register the mobile Java agent. In fact, the Java agent is different from the instructions used and the Protocol rules, the operations of the Notes class are almost the same as those of the lotus script. Therefore, if you are familiar with Lotus script, it is easy to import the hosts domain of the Java agent. In this example, what we need to do is:
- When a user creates a new file, an employee logs in, then, the relevant information of this employee is returned to the Oracle database through the Java agent and displayed on the plane.
- Because the user may release the application for this document, the file cannot be saved until the user presses the action to save or send it.
Soon, you will find that notes does not provide a front-end Java class, which is intended for those who use the lotus script UI class, at the beginning, you may feel a little pain. In our secondary example, because no filedappendtext method in notesuidocument class can be used, therefore, we are forced to first upload the data returned by the Oracle database to the notes. INI, and then use another program to upload the information to the file opened on the UI. Of course, if the file you are working on has been saved or can be saved, there will be no issues if there is no UI class to use.
Let's roll:
1. First, we will set the following link information on the Oracle database. In actual scenarios, you can ask your Oracle system administrator. After figuring out what happens to Oracle database, we can start the mobile operation.
- Host Name: oracledb
- Sid: Personnel
- Username: User
- Password: Password
- Table Name: person
- Filed: empnum, name, department, title
2. Create a set of tables. Partition 1 is the set of table design we need.
[Region 1]
| Namespace |
Type |
Formula |
| Empnum percentile bit |
Text; optional |
No |
| Name second bit |
Text, computing time |
"" |
| Department ranking bit |
Text, computing time |
"" |
| Title Digit |
Text, computing time |
"" |
| Retrieve data by hour |
Formula |
@ Command ([toolsrunmacro]; "(retrievedata )");
@ Command ([toolsrunmacro]; "(showdata )");
@ Command ([toolsrunmacro]; "(cleartempdata )") |
3. Create a retrievedata agent on the resource. The basic setting of the agent is second. (Using: I am using the nd6 designer)
[Option 2]
4. select the program to use the Java program to design the proxy program, and click [Program program project] and then click "program, we want to bring the oracle JDBC class "package" into this agent.
[March 3]
5. for example, in Release 4, first switch to the category where we saved classes12.zip, then find the case of classes12.zip, and press Add/replace case, import classes12.zip "package. At this time, the reverse case type shown in the list box on the left can be selected through explicit case type (for example, standard example (2, therefore, if you do not select "all" or "memory storage" (memory: R5 as "Memory"), you may not find this case.
【4]
6. After the classes12.zip package is imported, press confirm to return to the zookeeper agent.
[Option 5]
7. retrievedata agent content
(Release 7.1: In order to facilitate the explanation, each line of the program is marked with "line". When writing a line of code, please remove it)
(Example 7.2: for the graphic Area Layout, this program contains full-form white space characters. Do not copy-paste directly. Please use the additional example at the end of the article)
001 import java. SQL .*;
002 import Lotus. Domino .*;
003
004 public class retrievedata extends agentbase {
005
006 public void notesmain (){
007
008 driver DRV = NULL;
009 printwriter out = NULL;
010
011 try
012 {
013
014 session = getsession ();
015 agentcontext = session. getagentcontext ();
016
017 document DOC = agentcontext. getdocumentcontext ();
018 string tmpempnum = Doc. getitemvaluestring ("empnum ");
019
020 // load the oracle JDBC driver
021 drivermanager. registerdriver (New Oracle. JDBC. Driver. oracledriver ());
022
023 // connect to the database... ("JDBC: oracle: thin: @ hostnameoripaddress: Port: Sid", "username", "password ")
024 connection conn = drivermanager. getconnection ("JDBC: oracle: thin: @ ORACLE: 1521: Personnel", "username", "password ");
025
026 // create a statement
027 statement stmt = conn. createstatement ();
028
029 // select the columns we need from the person table
030 resultset rset = stmt.exe cutequery ("Select name, department, title from person where empnum = '" + tmpempnum + "'");
031
032 // write the results to notes. ini which located on users 'computer
033 if (rset. Next ())
034 {
035 session. setenvironmentvar ("xx_system_name", rset. getstring ("name"), false );
036 session. setenvironmentvar ("xx_system_department", rset. getstring ("Department"), false );
037 session. setenvironmentvar ("xx_system_title", rset. getstring ("title"), false );
038}
039
040 // close the resultset
041 rset. Close ();
042
043 // close the statement
044 stmt. Close ();
045
046 // close the connection
047 conn. Close ();
048}
049 catch (exception E)
050 {
051 system. Out. println ("dosql err:" + E. getmessage ());
052 E. printstacktrace ();
053 }}}
8. retrievedata proxy content explanation
001,002 is the Java compiler. Which class package should we use?
014 create a session object (Session). Like LotusScript, a session is usually the first object to be declared.
015 when an agentcontext object (agentcontext) is created, getagentcontext () will return to the environment in which the agent processes the current session.
017 create a docuement object (DOC). The getdocumentcontext () method of agentcontext class will reply to the file content in the memory when the agent program is dynamic.
018 create a tmpempnum string so that it is equal to the value of empnum item in the DOC file. getitemvaulestring () refers to the return of the item value in the string format
021 login input Oracle thin JDBC notebook
024 create a connection object (conn), that is, connecting to the Oracle Database Server. Remember that the number after the @ symbol should work with your real environment.
027 create a statement object (stmt). The statement object allows you to require begin rows of SQL commands for Oracle database.
030 create a resultset object (rset) and require the Oracle database server to restore all the data that meets the SQL query condition command and store it in rset.
033 use the next () method in the resultset class to verify whether there is any data returned
035-037 obtain the values of each digit (string format) from the rset and separate them into notes. in INI, It is the historical data of the Notes client.
041 related resultset objects
044 related statement objects
047 connecting a connection object, that is, connecting a client to an Oracle Database Server
049 accidental crash handling
051 if an accident occurs, the warning message is displayed on the Java console.
052 when an accident occurs, the program starts from the method of the last line of the program and traces back to the program of the first method of the last line of the program. The program is displayed on the Java console. 9. showdata agent
The basic settings of the showdata agent are the same as those of the retrievedata agent, but you can use the formula to compile it. The program procedure is as follows:
@ If (@ environment ("xx_system_name") = ""; @ Do (@ prompt ([OK]; "system prompt "; "Unable to find employee information for the employee you entered! "); @ Return (""));"");
@ Setfield ("name"; @ environment ("xx_system_name "));
@ Setfield ("department"; @ environment ("xx_system_department "));
@ Setfield ("title"; @ environment ("xx_system_title "));
10. cleartempdata agent
In fact, we can also upload this program to the back of the showdata agent. However, in this case, we are still releasing them. If there is any need, we can simply call this agent to compile the program.
@ Environment ("xx_system_name ";"");
@ Environment ("xx_system_department ";"");
@ Environment ("xx_system_title ";""); |
