使用 Chilkat 和 SQL Server 将单个电子邮件附件保存到 BLOB

Question

Chilkat.IMAP 9.5.0.75 - ActiveX 和 SQL Server

在SQL Server中，我将所有新电子邮件从邮箱拉入SQL表中，并保存UID，主题，正文和日期。这很好用。

如果电子邮件有附件，我正在尝试将它们作为 BLOB 保存在单独的表中。我成功地将 SaveAllAttachments 用于文件系统，然后从文件系统导入它们。但是，邮箱中的某些附件具有相同的文件名，我宁愿处理内存中的文件，而不是先将它们保存到文件系统。

我尝试将 Chilkat.Email GetAttachmentData直接使用到varbinary(max(变量中。它大于 4000 字节，所以我尝试使用表变量。还尝试以相同的方式使用Chilkat.IMAP FetchAttachmentBytes，结果相同：

直接到变量二元(max(变量：

声明@bin_file变量(最大值( EXEC sp_OAMethod @email，'GetAttachmentData'，@bin_file出来，@j

结果：

ChilkatLog： 获取附件数据： 日期： Aug 25 2018 奇尔卡特版本： 9.5.0.75 解锁前缀：*********** 建筑：小端序;64 位 语言： ActiveX/x64 详细日志记录： 0 索引： 0 字节数： 426484 成功。 --获取附件数据 --ChilkatLog

@bin_file 是空的，即使它说成功。我认为这是由于 4000 字节的限制，但我不确定。

对于具有变量(max(列的表变量：

声明@attach表(附件变量(最大值(( 插入到@attach执行sp_OAMethod @email"获取附件数据"中，@j

结果：

ChilkatLog： GetHeaderField： 奇尔卡特版本： 9.5.0.75 --获取标头字段 --ChilkatLog

出于某种原因，日志显示的是上一个命令而不是 GetAttachmentData，就好像 SQL 跳过了该语句一样。我使用相同的表变量方法来成功获取电子邮件正文属性。无论我使用 Chilkat.Email.GetAttachmentData 还是 Chilkat.IMAP.FetchAttachmentBytes，结果都是一样的。

以下是整个脚本：

创建表 #email_uid (uid VARCHAR(1024((

DECLARE @hr int
DECLARE @iTmp0 int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @imap int
EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Imap', @imap OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
--  Anything unlocks the component and begins a fully-functional 30-day trial.
DECLARE @success int
EXEC sp_OAMethod @imap, 'UnlockComponent', @success OUT, 'hidden'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @imap
RETURN
END
--  Connect to an IMAP server.
--  Use TLS
EXEC sp_OASetProperty @imap, 'Ssl', 1
EXEC sp_OASetProperty @imap, 'Port', 993
EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'hidden'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @imap
RETURN
END
--  Login
EXEC sp_OAMethod @imap, 'Login', @success OUT, 'hidden account name', 'hidden password'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @imap
RETURN
END
--  Select an IMAP mailbox
EXEC sp_OAMethod @imap, 'SelectMailbox', @success OUT, 'Inbox'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @imap
RETURN
END
DECLARE @messageSet int
--  fetch UIDs not sequence numbers.
DECLARE @fetchUids int
SELECT @fetchUids = 1
--  Return all messages.
DECLARE @allMsgs nvarchar(4000)
SELECT @allMsgs = 'ALL'
EXEC sp_OAMethod @imap, 'Search', @messageSet OUT, @allMsgs, @fetchUids
IF @messageSet Is NULL 
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @imap
RETURN
END
--  Fetch the email headers into a bundle object:
DECLARE @bundle int
EXEC sp_OAMethod @imap, 'FetchHeaders', @bundle OUT, @messageSet
IF @bundle Is NULL 
BEGIN
EXEC @hr = sp_OADestroy @messageSet
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @imap
RETURN
end
-- get all UID's from the inbox; loop through them and download the if we haven't downloaded that UID before
DECLARE @i int
EXEC sp_OAGetProperty @bundle, 'MessageCount', @iTmp0 OUT
SELECT @i = 0
WHILE @i <= @iTmp0 - 1
BEGIN
DECLARE @email int
EXEC sp_OAMethod @bundle, 'GetEmail', @email OUT, @i
EXEC sp_OAMethod @email, 'GetHeaderField', @sTmp0 OUT, 'ckx-imap-uid'
insert into #email_uid
select @sTmp0
EXEC @hr = sp_OADestroy @email
SELECT @i = @i + 1
end
-- delete UIDs we have already downloaded
delete a
from #email_uid a
inner join email b on b.uid = a.uid
declare @bUid int
select @bUid = 1
declare @s varchar(1024)
select @s = min(uid) from #email_uid
while exists (select 1 from #email_uid)
begin
--  Download the email by UID number.
EXEC sp_OAMethod @imap, 'FetchSingle', @email OUT, @s, @bUid
-- get number of attachments in the email
DECLARE @numAttach int
EXEC sp_OAMethod @imap, 'GetMailNumAttach', @numAttach OUT, @email
declare @subject varchar(1024)
, @date varchar(1024)
, @from varchar(1024)
, @body varchar(max)
-- Fetch a longer property string into a temp table:
DECLARE @tmp TABLE (emailBody ntext)
INSERT INTO @tmp EXEC sp_OAGetProperty @email, 'Body'
select @body = emailBody from @tmp
EXEC sp_OAGetProperty @email, 'Subject', @subject out
EXEC sp_OAGetProperty @email, 'From', @from out
EXEC sp_OAMethod @email, 'GetHeaderField', @date OUT, 'Date'
insert email ([UID],[Subject],[Date],[from],[Body])
select @s, @subject, @date, @from, @body
set @subject = null
set @from    = null
set @date    = null
set @body    = null
DECLARE @j int
-- Loop through the attachments and insert them as BLOBS into attachment table
SELECT @j = 0
WHILE @j <= @numAttach - 1
BEGIN
DECLARE @filename nvarchar(4000)
EXEC sp_OAMethod @imap, 'GetMailAttachFilename', @filename OUT, @email, @j
PRINT @filename
DECLARE @attach TABLE (attachment varbinary(max))
INSERT INTO @attach EXEC sp_OAMethod @email, 'GetAttachmentData', @j
IF not exists (select 1 from @attach) 
BEGIN
EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @email
EXEC @hr = sp_OADestroy @imap
RETURN
end
--declare @bin_file varbinary(max)
--EXEC sp_OAMethod @email, 'GetAttachmentData', @bin_file out, @j
--if @bin_file is null
--BEGIN
--EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
--PRINT @sTmp0
--EXEC @hr = sp_OADestroy @email
--EXEC @hr = sp_OADestroy @imap
--RETURN
--END
insert into [dbo].[email_attachment] (UID,Filename,Attachment)
select @s, @filename, attachment
from @attach
delete @attach
select @j = @j + 1
END
EXEC @hr = sp_OADestroy @email
delete from #email_uid where uid = @s
select @s = min(uid) from #email_uid
end
--  Disconnect from the IMAP server.
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
EXEC @hr = sp_OADestroy @messageSet
EXEC @hr = sp_OADestroy @bundle
EXEC @hr = sp_OADestroy @imap

谢谢 达林

Answer 1

方法的名称应为 GetAttachmentData(而不是 GetAttachmentBytes(。 见 https://www.chilkatsoft.com/refdoc/xChilkatEmailRef.html#method72

我不确定为什么当您使用名称"GetAttachmentBytes"时SQL没有抛出/返回错误。 它似乎只是跳过了那句话。