calling sqlj.install_jar from jdbc?
I have a python snippet that I try to port to java. The aim is Gradle but I could not get that to work, so I figured I would start with plain JDBC. The python code looks like this:
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)
and works. The jar file ends up in ~/sqllib/function/jar/DB2INST1 on the target host as expected.
I've looked at several examples in java and try to mimic them, but I always end up with an error (most of them likely because I'm illiterate when it comes to java). The current code I have is:
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();
}
}
}
compiled as:
#> javac -cp db2jcc4.jar aa.java
and run as:
#> 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)
Meaning of
SQL0440N No authorized routine named "<routine-name>" of type
"<routine-type>" having compatible arguments was found.
I would have expected that error to happen during prepare, but it happens during execute so I'm not sure what to make out of it.
Does anyone have a working snippet to share for calling sqlj.install_jar from jdbc?
Try this:
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();
I get the same -440 symptom as your question, on Db2-LUW v11.5.6.0 from jdbc when I call sqlj.install_jar .
Possibly some regression or mistake by me.
It may also be worthwhile to compare the behaviour of method DB2Connection.installDB2JavaStoredProcedure (which supposedly runs sqlj.install_jar )
But the alternate method sqlj.DB2_INSTALL_JAR works ok from jdbc for me currently.
Sample below based on IBM's example code, and I run it locally on the Db2-LUW server so use passwordless style url.
Note that if you need to make it re-runnable, you have to extend it, to check sysibm.sysjarobjects for the jar_id , use sqlj.replace_jar and/or sqlj.remove_jar and sqlj.refresh_classes() as appropriate. For that reason I prefer to use shell-script instead of jdbc for orchestrating my build scripts, at the cost of having a properly configured Db2-client at the build hostname. The shell scripts are shorter and easier to manage than jdbc code in my case.
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();
}
}
}
}