Figures

import lotus.notes.*;
public class TestJDBC extends AgentBase
{
Connection conn;
PreparedStatement pstmt;
public void disConnect()
{
try {
conn.close();
}
catch (SQLException exc) {
System.out.println(« Didn’t Close Right »);
}
}
public void NotesMain()
{
System.out.println(« Inside TestJDBC »);
try
{
DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
}
catch(SQLException exc)
{
System.out.println(« DB2/400 JDBC driver not found! »);
System.exit(1);
}
System.out.println(« Class found OK »);
TestJDBC test = new TestJDBC(); // create instance of us
String sysName = « MYAS400 »;
try
{
System.out.println(« NotesSession Signed On… »);
Session s = getSession();
AgentContext ac = s.getAgentContext();
Database db = ac.getCurrentDatabase();
Document doc = db.createDocument();
Item item = doc.appendItemValue(« TestData »);
try
{
System.out.println(« Connecting to system  » + sysName + « … »);
try
{
conn = DriverManager.getConnection(« jdbc:as400:// »+sysName);
}
catch (SQLException exc)
{
System.out.println(« connect failed with: ‘ » + exc.getMessage() + « ‘ »);
}
System.out.println(« connected ok »);
System.out.println(« querying all records… »);
String theStmt;
theStmt = « SELECT FIELD1, FIELD2 FROM MYLIB.MYFILE WHERE FIELD1 = ‘XXXX' »;
pstmt = conn.prepareStatement(theStmt);
ResultSet rs = pstmt.executeQuery();
System.out.println(« query results: »);
doc.appendItemValue(« Form », »FormName »);
while (rs.next())
item.appendToTextList((rs.getString(1) +  »  » + rs.getString(2) ));
doc.save();
rs.close();
pstmt.close();
} // end try
catch (SQLException exc) {
System.out.println(« query all failed with: ‘ » +
exc.getMessage() + « ‘ »);
//return false;
}
System.out.println(« query done »);
test.disConnect();
}
catch(NotesException e)
{
System.out.println(« NotesSession all failed with  » + e);
}
} // end main method
public boolean testConnect(String sys)
{
return true;
}
} // end class TestJDBC

Figure 4 : Code permettant d’écrire chaque champ du jeu de résultats dans une liste du formulaire de la figure 3

import java.lang.*;
import java.sql.*;
import java.util.Vector;
import com.ibm.as400.*;
import lotus.notes.*;
public class TestJDBC2 extends AgentBase
{
Connection conn;
PreparedStatement pstmt;
public void disConnect()
{
try {
conn.close();
}
catch (SQLException exc) {
System.out.println("Didn't Close Right");
}
}
public void NotesMain()
{
System.out.println("Inside TestJDBC");
try
{
DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
}
catch(SQLException exc)
{
System.out.println("DB2/400 JDBC driver not found!");
System.exit(1);
}
System.out.println("Class found OK");
TestJDBC2 test = new TestJDBC2(); // create instance of us
String sysName = "MYAS400";
try
{
System.out.println("NotesSession Signed On...");
Session s = getSession();
AgentContext ac = s.getAgentContext();
Database db = ac.getCurrentDatabase();
Document doc = db.createDocument();
Item item1 = doc.appendItemValue("TestData1");
Item item2 = doc.appendItemValue("TestData2");
Item item3 = doc.appendItemValue("TestData3");
Item item4 = doc.appendItemValue("TestData4");
try
{
System.out.println("Connecting to system " + sysName + "...");
try
{
conn = DriverManager.getConnection("jdbc:as400://"+sysName);
}
catch (SQLException exc)
{
System.out.println("connect failed with: '" + exc.getMessage() + "'");
}
System.out.println("connected ok");
System.out.println("querying all records...");
String theStmt;
theStmt = "SELECT FIELD1, FIELD2, FIELD3, FIELD4 FROM
MYLIB.MYFILE WHERE FIELD1 = 'XXXX'";
pstmt = conn.prepareStatement(theStmt);
ResultSet rs = pstmt.executeQuery();
System.out.println("query results:");
doc.appendItemValue("Form","FormName");
while (rs.next()) {
item1.appendToTextList((rs.getString(1).trim()));
item2.appendToTextList((rs.getString(2).trim()));
item3.appendToTextList((rs.getString(3).trim()));
item4.appendToTextList((rs.getString(4).trim()));
}
doc.save();
rs.close();
pstmt.close();
} // end try
catch (SQLException exc) {
System.out.println("query all failed with: '" +
exc.getMessage() +
//return false;
}
System.out.println("query done");
test.disConnect();
}
catch(NotesException e)
{
System.out.println("NotesSession all failed with " + e);
}
} // end main method
public boolean testConnect(String sys)
{
return true;
}
} // end class TestJDBC

Figure 7 : Mise à  jour des documents existants
par une méthode « single-threaded »

/************************************************************
The following work is a Java program that updates a set of
Lotus Notes documents from an AS400 DB2 SQL generated Result
Set using JDBC connectivity. It was created as independent
Research and used later in a production environment.
************************************************************/
import java.lang.*;
import java.sql.*;
import java.util.Vector;
import com.ibm.as400.*;
import lotus.notes.*;
public class UpdateCPF extends AgentBase
{
Connection conn;
PreparedStatement pstmt;
public void disConnect()
{
try {
conn.close();
}
catch (SQLException exc) {
System.out.println("Didn't Close Right");
}
}
public void NotesMain()
{
System.out.println("Inside UpdateCPF");
try
{
DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
}
catch(SQLException exc)
{
System.out.println("DB2/400 JDBC driver not found!");
System.exit(1);
}
System.out.println("Class found OK");
UpdateCPF test = new UpdateCPF(); // create instance of us
String sysName = "MYAS400";
try
{
System.out.println("NotesSession Signed On...");
Session s = getSession();
AgentContext ac = s.getAgentContext();
Database db = ac.getCurrentDatabase();
Document newdoc;
Item item;
try
{
System.out.println("Connecting to system " + sysName + "...");
try
{
conn = DriverManager.getConnection("jdbc:as400://"+sysName);
}
catch (SQLException exc)
{
System.out.println("connect failed with: '" + exc.getMessage() + "'");
}
System.out.println("connected ok");
System.out.println("querying all records...");
String theStmt;
theStmt = "SELECT SHOP#, MOWNER, CRREF, TERM, STAT FROM MYLIB.MYFILE";
pstmt = conn.prepareStatement(theStmt);
ResultSet rs = pstmt.executeQuery();
System.out.println("query results:");
while (rs.next()) {
newdoc = db.createDocument();
newdoc.appendItemValue("Form","CPF");
newdoc.appendItemValue("Status",rs.getString(5).trim());
newdoc.appendItemValue("ShopNo",rs.getString(1).trim());
newdoc.appendItemValue("OwnerNo",rs.getString(2).trim());
newdoc.appendItemValue("CredRef",rs.getString(3).trim());
newdoc.appendItemValue("Terms",rs.getString(4).trim());
newdoc.computeWithForm(false,false); newdoc.save();
}
rs.close();
pstmt.close();
} // end try
catch (SQLException exc) {
System.out.println("query all failed with: '" +
exc.getMessage() + "'");
}
System.out.println("query done");
test.disConnect();
}
catch(NotesException e)
{
System.out.println("NotesSession all failed with");
}
} // end main method
public boolean testConnect(String sys)
{
return true;
}
} // end class TestJDBC