As an example of what you can do with stored procedures in Lotus Notes Domino, the Java code below executes a stored procedure that returns a Job Number value. The Job Number value is then used in an executed SQL statement.

import lotus.domino.*;
import java.sql.*;          // JDBC classes
import java.util.*;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.Date;



public class JavaAgent  extends AgentBase {
private static String _url   
= "jdbc:as400://fully.qualified.server:db2portid/dbname";
private static String dbuser   
= "user";
private static String dbpwd   
= "pwd";
private static String _drv   
= "com.ibm.as400.access.AS400JDBCDriver";
private java.sql.ResultSet rs;

public void NotesMain()  {
Vector vec = new Vector();
String [] recordKeyArrs = 
new String [10];
try {
Session session = getSession();
AgentContext agentContext = session.getAgentContext();

// (Your code goes here) 
Session s = NotesFactory.createSession();
Database db = agentContext.getCurrentDatabase();
// Get connection to the database
Class.forName(_drv);
Properties p = new Properties();
p.put("user", dbuser);
p.put("password", dbpwd);
System.out.println("Establish Connection url-" + _url );
Connection con = DriverManager.getConnection(_url, p);
PreparedStatement stmt = null;
String sql;
String userId = "";
   
Vector custListV= returnCustomerConfigList (s,db);
int j;
for ( j=0;j%lt;custListV.size();j++ ) {
 
String customerNo = (String) custListV.elementAt(j);
String suffix = "000";
//call to stored procedure  
String jobnumber = loadPMCurrencyRecords
(con, customerNo, suffix) ;
System.out.println("Job Number : " +  jobnumber );
if ( !jobnumber.equals( "" ) ){ //only execute 
if we have a job number
 
sql = "SELECT"
+ " KAUKTE, KCULTE, CRACNB,  CREFDT, H6LOTX"
+ " FROM"
+ " FCCRCPP"
+ " WHERE"
+ " JOBNUMBER = "+ jobnumber;
       
// Get a statement from the connection
stmt = con.prepareStatement( sql );
rs = stmt.executeQuery( );
       
while (rs.next()) {
//loop and create documents
String cC = rs.getString("KAUKTE");
String cL = rs.getString("KCULTE");
String rN = rs.getString("CRACNB");
String tD = rs.getString("CREFDT");
String pD = rs.getString("H6LOTX");
    
System.out.println(cC + cL );
   }

} //only execute if we have a job number
   } //
if (con != null) con.close();
   
} catch(Exception e) {
e.printStackTrace();
}
 }
 

public String loadPMCurrencyRecords
(Connection con, String customerNo, String suffix ) {
CallableStatement statement = null;
String jobNumber = "" ;

try {
statement = con.prepareCall
("CALL cayr9xjh_qp (?,?,?,?,?)");
            // Register the output parameters
            statement.registerOutParameter
(1, java.sql.Types.CHAR); // error code is 
7 char and is blank if okay
            statement.registerOutParameter
(5, java.sql.Types.CHAR); // returns job 
number require for sql 

            // Call the stored procedure
            statement.setString(2, userId);
            statement.setString(3, customerNo );
            statement.setString(4, suffix );

            statement.executeUpdate();

            String returnCode = statement.getString(1);
          /*  if (returnCode != null && 
!returnCode.matches("^\s+$")) {
                throw new DaoException
("Operation was unsuccessful: " + returnCode);
            }*/
   
            jobNumber = statement.getString(5); 
            //if (con  != null) con .close();
            
        } catch (Exception sqle) {
           System.err.println(sqle.getMessage());
            
        } 
   return jobNumber;
    }
}