Stored Procedure via Notes Java Agent

Question

Hello,

I am trying to get a stored procedure to be able to be called from a java agent in Lotus Notes. I have been able to get these type(s) of agents to work fine as far as moving data to and from db2 tables, and I have also been able to run the stored procedure from Iseries Navigator using the same username/password that I am using for this agent. However, when I try to run the agent, it gives me an error. Here is my Java code:

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.net.URL;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Types;

import java.util.Properties;

import java.util.Vector;

import com.ibm.as400.access.AS400JDBCDriver;

import lotus.domino.Agent;

import lotus.domino.AgentBase;

import lotus.domino.AgentContext;

import lotus.domino.Database;

import lotus.domino.Document;

import lotus.domino.NotesException;

import lotus.domino.Session;

public class NotesUpdateAS400Control extends AgentBase {

private Session session = null; private Agent agt = null; private Database db = null; private Document doc = null; private Vector allObjects = new Vector(); // private final String INIT_FILE_NAME = "AgentInitProps.properties"; //set the values we will use to log in private String dbUser = null; private String dbPswd = null; private String dbHost = null; //

public NotesUpdateAS400Control() { super(); }

private void init() { try { // Gain access to the current document session = getSession(); AgentContext agentContext = session.getAgentContext(); allObjects.addElement(agentContext); agt = agentContext.getCurrentAgent(); db = agentContext.getCurrentDatabase(); allObjects.addElement(db); String tempNoteID = agt.getParameterDocID(); doc = db.getDocumentByID(tempNoteID); allObjects.addElement(doc);
} catch (NotesException notesEx) { System.out.println("buildPreparedStatement NotesException: " + notesEx); notesEx.printStackTrace(System.out); } }

// protected Connection buildConnection() throws NotesException { Connection con = null;

// Get AS/400 Connection try { URL url = ClassLoader.getSystemResource("."); File initFile = new File(url.getFile()+INIT_FILE_NAME); FileInputStream fis = new FileInputStream(initFile); Properties props = new Properties(); props.load(fis); dbUser = props.getProperty("dbUser"); dbPswd = props.getProperty("dbPswd"); dbHost = props.getProperty("dbHost"); fis.close();

// Register AS/400 Driver DriverManager.registerDriver(new AS400JDBCDriver()); con = DriverManager.getConnection("jdbc:as400://" + dbHost, dbUser, dbPswd); doc.save(); } catch (SQLException sqlEx) { System.out.println("buildConnection SQLException: " + sqlEx); sqlEx.printStackTrace(System.out); doc.save(); } catch (IOException ioe) { ioe.printStackTrace(); } catch (NotesException notesEx) { System.out.println("buildConnection NotesException: " + notesEx); doc.save(); notesEx.printStackTrace(System.out); } return con; } //

public void NotesMain() { init(); try { System.out.println("Test of Stored Procedure Agent");

CallableStatement pspmt = null;

try { con = this.buildConnection(); if (con != null) { pspmt = con.prepareCall("CALL DB2ADMIN.MYZIPTOZIP(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); System.out.println("Test"); pspmt.clearParameters(); pspmt.registerOutParameter(7, Types.CHAR); pspmt.registerOutParameter(8, Types.CHAR); pspmt.registerOutParameter(9, Types.DECIMAL); pspmt.registerOutParameter(10, Types.CHAR); pspmt.setString(1, "NASHVILLE"); pspmt.setString(2, "TN"); pspmt.setString(3, "37202"); pspmt.setString(4, "COOKEVILLE"); pspmt.setString(5, "TN"); pspmt.setString(6, "38501");

pspmt.execute();
System.out.println("OutParam7 " + pspmt.getString(7));
String value1 = pspmt.getString(7);
String value2 = pspmt.getString(8);
String value3 = pspmt.getBigDecimal(9).setScale(0).toString();
String value4 = pspmt.getString(10);

doc.replaceItemValue("test1", value1.trim());
System.out.println("Test " + value1.trim());
doc.replaceItemValue("test2", value2.trim());
doc.replaceItemValue("test3", value3.trim());
doc.replaceItemValue("test4", value4.trim());


            Vector vals = new Vector();

            vals.addElement(pspmt.getString(7));
            vals.addElement(pspmt.getString(8));
            vals.addElement(pspmt.getBigDecimal(9).setScale(0).toString());
            vals.addElement(pspmt.getString(10));

            System.out.println("Zip to zip value object : " + vals.toString());


}

} catch (SQLException sqlEx) { System.out.println("executeUpdate SQLException: " + sqlEx); sqlEx.printStackTrace(System.out); } catch (NotesException notesEx) { System.out.println("executeUpdate NotesException: " + notesEx); notesEx.printStackTrace(System.out); } finally { try { if(pspmt != null) { pspmt.close(); pspmt = null; } // end of if if(con != null) { con.close(); con = null; } // end of if } catch (SQLException sqlEx) { System.out.println("close con/pspmt SQLException: " + sqlEx); sqlEx.printStackTrace(System.out); } } // doc.save(); doc.recycle(); // recycle the one we're done with agt.recycle(); db.recycle(); } // end of try catch (Exception e) { System.out.println (e); e.printStackTrace(System.out); } // end of catch finally { try { session.recycle(allObjects); session.recycle(); //session = null; System.runFinalization(); } // end of try catch (Exception e) { System.out.println (e); e.printStackTrace(System.out); } // end of catch } // end of catch System.gc(); long memAfter = Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory(); System.out.println("NotesUpdateAS400 agent completed. Current JVM Heap Size : " + memAfter);

} // end of notes main }

However, when I run this code, I get the following error in the console:

[0774:0025-0BD0] 08/31/2010 09:11:03 AM 0 Transactions/Minute, 0 Notes Users[0774:009C-032C] 08/31/2010 09:11:41 A M Agent printing: Test of Stored Procedure Agent [0774:009C-032C] 08/31/2010 09:11:42 AM Agent printing: executeUpdate SQLException: java.sql.SQLException: [SQL044 0] Routine MYZIPTOZIP in DB2ADMIN not found with specified parameters. [0774:009C-032C] 08/31/2010 09:11:42 AM Agent printing: java.sql.SQLException: [SQL0440] Routine MYZIPTOZIP in DB2 ADMIN not found with specified parameters. [0774:009C-032C] 08/31/2010 09:11:42 AM Agent printing: at com.ibm.as400.access.JDError.throwSQLException(J DError.java:646) [0774:009C-032C] 08/31/2010 09:11:42 AM Agent printing: at com.ibm.as400.access.JDError.throwSQLException(J DError.java:617) [0774:009C-032C] 08/31/2010 09:11:42 AM Agent printing: at com.ibm.as400.access.AS400JDBCStatement.commonPr epare(AS400JDBCStatement.java:1578) [0774:009C-032C] 08/31/2010 09:11:42 AM Agent printing: at com.ibm.as400.access.AS400JDBCPreparedStatement. (AS400JDBCPreparedStatement.java:227) [0774:009C-032C] 08/31/2010 09:11:42 AM Agent printing: at com.ibm.as400.access.AS400JDBCCallableStatement. (AS400JDBCCallableStatement.java:106) [0774:009C-032C] 08/31/2010 09:11:42 AM Agent printing: at com.ibm.as400.access.AS400JDBCConnection.prepare Call(AS400JDBCConnection.java:1808) [0774:009C-032C] 08/31/2010 09:11:42 AM Agent printing: at com.ibm.as400.access.AS400JDBCConnection.prepare Call(AS400JDBCConnection.java:1709) [0774:009C-032C] 08/31/2010 09:11:42 AM Agent printing: at com.averitt.notesProcessing.NotesUpdateAS400Cont rol.NotesMain(NotesUpdateAS400Control.java:110) [0774:009C-032C] 08/31/2010 09:11:42 AM Agent printing: at lotus.domino.AgentBase.runNotes(Unknown Source) [0774:009C-032C] 08/31/2010 09:11:42 AM Agent printing: at lotus.domino.NotesThread.run(Unknown Source) [0774:009C-032C] 08/31/2010 09:11:42 AM Agent printing: NCC Forms NotesUpdateAS400 agent completed. Current JVM He ap Size : 5927920

Again, the stored procedure works fine in Iseries navigator and when called by our J2EE application that normally uses it, with the same username I am using here. The Java agents work fine when I am doing selects or inserts for other programs.. Does someone have any ideas out there?

Thanks,

Answer 1

Your code's hard to read, but the stored procedure appears to require 10 parameters, and the error you're getting is self-explanatory: whatever it is you end up passing to the iSeries, it's not a valid call to the procedure. In other words, the issue is not with Domino's Java implementation per se, it's with the call you're making (like you, I can confirm that AS400 stored procedurs work just fine from Java).

The main culprits in instances like this:

1. Are the data type of the parameters you're registering correct?
2. When registering out parameters, are you setting an appropriate scale?
3. When setting values for the inputs, are the data types correct?