0

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.

0

1 Answer 1

1

As per Table 17. SQL Data Types Mapped to Java Declarations on p. 241 of IBM DB2 10.5 for Linux, UNIX, and Windows Developing User-defined Routines (SQL and External), BLOB SQL data type maps to java.sql.Blob Java data type, not to byte[] that I had initially.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.