通过 Notes Java 代理的存储过程

Question

我正在尝试获取一个能够从 Lotus Notes 中的 java 代理调用的存储过程。我已经能够让这些类型的代理在将数据移入和移出 db2 表时正常工作，并且我还能够使用与我相同的用户名/密码从 Iseries Navigator 运行存储过程用于此代理。但是，当我尝试运行代理时，它给了我一个错误。这是我的 Java 代码：

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
}

但是，当我运行此代码时，我在控制台中收到以下错误：

[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.
<init>(AS400JDBCPreparedStatement.java:227)
[0774:009C-032C] 08/31/2010 09:11:42 AM  Agent printing:        at com.ibm.as400.access.AS400JDBCCallableStatement.
<init>(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

同样，存储过程在 Iseries 导航器中运行良好，并且当由通常使用它的 J2EE 应用程序调用时，使用与我相同的用户名我在这里使用。当我为其他程序进行选择或插入时，Java 代理工作正常。有人有什么想法吗？

谢谢，

Answer 1

您的代码很难阅读，但存储过程似乎需要 10 个参数，并且您得到的错误是不言自明的：无论您最终传递给 iSeries 的是什么，它都不是对该过程的有效调用。换句话说，问题不在于 Domino 的 Java 实现本身，而在于您正在进行的调用（像您一样，我可以确认 AS400 存储过程在 Java 中工作得很好）。

此类情况的罪魁祸首是：

1. 您注册的参数的数据类型是否正确？
2. 注册参数时，您是否设置了合适的比例？
3. 设置输入值时，数据类型是否正确？