db2 从jdbc调用sqlj.install_jar?

zujrkrfu  于 2022-11-07  发布在  DB2
关注(0)|答案(2)|浏览(144)

我有一个python代码片段,我试图将其移植到java。目标是Gradle,但我无法让它工作,所以我想我会从普通JDBC开始。python代码看起来像这样:

import argparse
import ibm_db
import sys

def main(hostname, database, username, password):

    connstr = f"DATABASE={database};HOSTNAME={hostname};PORT=50000;PROTOCOL=TCPIP;UID={username}; PWD={password}"
    conn = ibm_db.connect(connstr, "", "")
    s1 = ibm_db.prepare(conn, "call sqlj.install_jar('file:/tmp/commons.jar', 'StringUtil_TEST')")
    ibm_db.execute(s1, ())
    # print(ibm_db.fetch_tuple(s1)[0])
    ibm_db.commit(conn)
    sys.exit(0)

和预期的一样,jar文件在目标主机上的~/sqllib/function/jar/DB2 INST 1中结束。
我看过几个java的例子,并试图模仿它们,但我总是以错误告终(大多数可能是因为我对java一窍不通)。我目前的代码是:

public static void main(String argv[])
    {
        try {
            Connection con = null;
            Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
            con = DriverManager.getConnection(<host>, <user>, <passwd>);
            CallableStatement callStmt1 = null;
            callStmt1 = con.prepareCall("call sqlj.install_jar('" + "file:/tmp/commons.jar" + "', 'StringUtil_TEST2')");

            // tried this as well 
            // callStmt1 = con.prepareCall("call sqlj.install_jar('" + "file:/tmp/commons.jar" + "', 'StringUtil_TEST2', 0)");

            callStmt1.execute();
        }
        catch (Exception e)
        {
            //System.out.println(e.);
            e.printStackTrace();
        }
    }
}

编译为:


# > javac -cp db2jcc4.jar aa.java

并运行为:


# > java -cp .:db2jcc4.jar Aa

com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=SQLJ.INSTALL_JAR;PROCEDURE, DRIVER=4.29.24
    at com.ibm.db2.jcc.am.b7.a(b7.java:810)
    at com.ibm.db2.jcc.am.b7.a(b7.java:66)
    at com.ibm.db2.jcc.am.b7.a(b7.java:140)
    at com.ibm.db2.jcc.am.k9.c(k9.java:2844)
    at com.ibm.db2.jcc.am.k9.d(k9.java:2828)
    at com.ibm.db2.jcc.am.k9.a(k9.java:2254)
    at com.ibm.db2.jcc.am.k_.a(k_.java:8277)
    at com.ibm.db2.jcc.t4.ab.i(ab.java:204)
    at com.ibm.db2.jcc.t4.ab.e(ab.java:133)
    at com.ibm.db2.jcc.t4.p.d(p.java:63)
    at com.ibm.db2.jcc.t4.av.m(av.java:190)
    at com.ibm.db2.jcc.am.k9.am(k9.java:2249)
    at com.ibm.db2.jcc.am.k_.bs(k_.java:3778)
    at com.ibm.db2.jcc.am.k_.a(k_.java:4640)
    at com.ibm.db2.jcc.am.CallableStatement.b(CallableStatement.java:136)
    at com.ibm.db2.jcc.am.CallableStatement.a(CallableStatement.java:105)
    at com.ibm.db2.jcc.am.CallableStatement.execute(CallableStatement.java:95)
    at Aa.main(aa.java:28)

的含义

SQL0440N  No authorized routine named "<routine-name>" of type
  "<routine-type>" having compatible arguments was found.

我本以为这个错误会在准备过程中发生,但它发生在执行过程中,所以我不确定该如何处理它。
有没有人有从jdbc调用sqlj.install_jar的工作片段可以分享?

yrdbyhpb

yrdbyhpb1#

试试看:

Connection con = null;
Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
con = DriverManager.getConnection(<host>, <user>, <passwd>);
DB2Connection db2con = (DB2Connection) con;
FileInputStream is = new FileInputStream("/tmp/commons.jar");
db2con.installDB2JavaStoredProcedure(is, (int) is.getChannel().size(), "StringUtil_TEST2");
...

// if replaceDB2JavaStoredProcedure is used it is necessary
// to refresh classes to load the new jar
CallableStatement callStmt1 = null;
callStmt1 = db2con.prepareCall("call sqlj.refresh_classes()");
callStmt1.execute();
bmvo0sr5

bmvo0sr52#

当我调用sqlj.install_jar时,在jdbc的Db2-LUW v11.5.6.0上,我得到了与您的问题相同的-440症状。
可能是我的一些退步或错误。
比较一下DB2Connection.installDB2JavaStoredProcedure方法的行为(它应该运行sqlj.install_jar)可能也是值得的。
但是目前我在jdbc中使用的替代方法sqlj.DB2_INSTALL_JAR还可以。
下面的示例基于IBM的示例代码,我在Db2-LUW服务器上本地运行它,因此使用无密码样式的url。
请注意,如果需要使其可重新运行,则必须对其进行扩展,要检查sysibm.sysjarobjects中的jar_id,请根据需要使用sqlj.replace_jar和/或sqlj.remove_jarsqlj.refresh_classes()。代价是在构建主机名上有一个正确配置的Db2-client。在我的例子中,shell脚本比jdbc代码更短,更容易管理。

import java.sql.*;
import java.io.IOException;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;

        public class installj {
            public static void main(String[] args)  {
                String url = "jdbc:db2:sample";   // url syntax for passwordless connection local on Db2-server
                String user = "";
                String password = "";
                Connection con;
                CallableStatement callStmt;
                String returnedVarchar="";
                String jarid = "MYJARID";
                String jarfile = "/home/user1/test/jarname.jar";

                try {
                    Class.forName("com.ibm.db2.jcc.DB2Driver") ;

                    // the URL format is specifically for a Type-2 passwordless local connection
                    con = DriverManager.getConnection(url, user, password);
                    con.setAutoCommit(false);
                    File aFile = new File(jarfile);
                    FileInputStream inputStream = new FileInputStream(aFile);
                    CallableStatement stmt;
                    String sql = "Call SQLJ.DB2_INSTALL_JAR(?, ?, ?)";
                    stmt = con.prepareCall(sql);
                    stmt.setBinaryStream(1, inputStream, (int)aFile.length());
                    stmt.setString(2, jarid);
                    stmt.setInt(3, 0);
                    boolean isrs = stmt.execute();
                    stmt.close();
                    System.out.println("\n\nInstallation of JAR succeeded\n\n");
                    con.commit();
                    con.close();
                }

                catch (FileNotFoundException e) {
                    System.err.println("\nCaught exception FileNot Found\n");
                    e.printStackTrace();
                }

                catch (ClassNotFoundException e) {
                    System.err.println("Could not load JDBC driver");
                    System.out.println("Exception: " + e);
                    e.printStackTrace();
                }

                catch (SQLException ex) {
                    System.err.println("---> SQLException information\n");
                    while (ex != null) {
                        System.err.println("\nError msg: " + ex.getMessage());
                        System.err.println("\nSQLSTATE: " + ex.getSQLState());
                        System.err.println("\nError code: " + ex.getErrorCode());
                        ex.printStackTrace();
                        ex = ex.getNextException();
                    }
                }
            }
        }

相关问题