After I created a Java external stored procedure in DB2 as in my previous question, the call
call user1.send_mail(
P_TO => '[email protected]'
,P_CC => NULL
,P_BCC => NULL
,P_FROM => '[email protected]'
,P_SUBJECT => 'db2 java subject'
,P_TEXT_MSG => 'db2 java test'
,P_HTML_MSG => null
,P_ATTACH_NAME => null
,P_ATTACH_MIME => null
,P_ATTACH_BLOB => null
,P_SMTP_HOST => 'domain.com'
,P_SMTP_PORT => 465
,P_USER_NAME => '[email protected]'
,P_PASSWORD => 'secret');
fails it due to the following error:
Java stored procedure or user-defined function "USER1.SEND_MAIL", specific name "SQL201208113215436" could not call Java method "send_mail", signature "(Ljava/lang/String".. SQLCODE=-4306, SQLSTATE=42724, DRIVER=3.68.61
I made sure that all parameters that have no default values receive non-NULL values. What else is necessary for this to work?
Update:
The db2diag.log file contains the following error message that I formatted for readability:
MESSAGE : java.lang.NoSuchMethodError:
db2/smtp.send_mail(
Ljava/lang/String;
Ljava/lang/String;
Ljava/lang/String;
Ljava/lang/String;
Ljava/lang/String;
Ljava/lang/String;
Ljava/lang/String;
Ljava/lang/String;
Ljava/lang/String;
Ljava/sql/Blob;
Ljava/lang/String;
ILjava/lang/String;
Ljava/lang/String;)V
It is clear that the integer P_SMTP_PORT parameter of the Java method is missing between the 2nd and 3d last lines. It looks like it has been totally skipped, even though my SQL call includes this parameter. What does this mean? Also, what is the significance of the extra I in front of Ljava on the 2nd last line?
The JAR file is being registered from a C# application, using the following code:
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "sqlj.db2_install_jar";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("blob", DB2Type.Blob).Value = File.ReadAllBytes(args[4]);
cmd.Parameters.Add("jarname", DB2Type.VarChar, 257).Value = "javadb2";
cmd.Parameters.Add("deploy", DB2Type.Integer).Value = 0;
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.CommandText = "SQLJ.REFRESH_CLASSES";
cmd.ExecuteNonQuery();
}
I checked and made sure that the JAR file correctly trickles down to C:\ProgramData\IBM\DB2\DB2COPY1\function\jar\USER1\JAVADB2.jar after the code above runs, and that it is still in the correct format.