使用SQL Server sp_send_dbmail从java发送电子邮件
我正在尝试使用SQL Server sp_send_dbmail从java发送电子邮件
在SQLServer本身中,以下命令可以很好地发送HTML电子邮件。[即,我已按照要求正确设置发送电子邮件的所有内容 Microsoft's instructions ]
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'example profile',
@recipients = 'me@example.com',
@body = @tableHTML,
@body_format = 'HTML' ,
@subject = 'Email from SQL Server';
但是,当尝试使用以下代码从java发送时
public static synchronized int sendEmail(String profileName,String recipients,String body, String body_format,String subject) {
final String sendEmailStr = "{execute msdb.dbo.sp_send_dbmail (?,?,?,?,?,?)}";
dbConn = DBConnection.getInstance();
Connection conn = dbConn.getConnection();
CallableStatement stmt = null;
int result = RESULT_FAILED;
try {
stmt = conn.prepareCall(sendEmailStr);
stmt.setString("profile_name", profileName);
stmt.setString("recipients", recipients);
stmt.setString("body", body);
stmt.setString("body_format", body_format);
stmt.setString("subject", subject);
stmt.registerOutParameter(6, java.sql.Types.INTEGER);
stmt.execute();
result = stmt.getInt(6);
} catch(SQLException e) {
System.out.println(e);
Log.getInstance().write("Exception on sendEmail " + e.toString());
} finally {
try {
stmt.close();
dbConn.returnConnection(conn);
} catch(SQLException e) {
System.out.println(e);
Log.getInstance().write("Exception on sendEmail " + e.toString());
}
}
return result;
}
我得到以下异常com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: Parameter profile_name was not defined for stored procedure
我做错了什么
# 1 楼答案
请将查询字符串更改为使用
call
,如下所示:要使用当前数据库中的存储过程,请在当前数据库中执行此操作,通过本地过程包装MSDB调用: