Save a single email attachment to BLOB using Chilkat and SQL Server

Question

Chilkat.IMAP 9.5.0.75 - ActiveX and SQL Server

In SQL Server, I'm pulling all new emails from a mailbox into a SQL table and saving the UID, Subject, Body and Date. That works great.

If the email has attachments, I'm trying to save them in a separate table as BLOBs. I was successful using SaveAllAttachments to the file system and then importing them from the file system. However, some of the attachments in the mailbox have the same filename and I'd rather work with the files in memory and not save them to the file system first.

I tried using Chilkat.Email GetAttachmentData directly into a varbinary(max) variable. It's bigger than 4000 bytes so I tried using a table variable. Also tried using Chilkat.IMAP FetchAttachmentBytes the same way with the same results:

Directly to varbinary(max) variable:


declare @bin_file varbinary(max)
EXEC sp_OAMethod @email, 'GetAttachmentData', @bin_file out, @j

Results:


ChilkatLog:
  GetAttachmentData:
    DllDate: Aug 25 2018
    ChilkatVersion: 9.5.0.75
    UnlockPrefix: ***************
    Architecture: Little Endian; 64-bit
    Language: ActiveX / x64
    VerboseLogging: 0
    index: 0
    numBytes: 426484
    Success.
  --GetAttachmentData
--ChilkatLog

@bin_file is null even though it says success. I think this is due to the 4000 byte limit but I'm not sure.

To a table variable with a varbinary(max) column:


DECLARE @attach TABLE (attachment varbinary(max))
INSERT INTO @attach EXEC sp_OAMethod @email, 'GetAttachmentData', @j

Results:


ChilkatLog:
  GetHeaderField:
    ChilkatVersion: 9.5.0.75
  --GetHeaderField
--ChilkatLog

For some reason, the log is showing the previous command not GetAttachmentData as if SQL is skipping that statement. I'm using this same table variable approach to get the email body property successfully. The results are the same whether I use Chilkat.Email.GetAttachmentData or Chilkat.IMAP.FetchAttachmentBytes.

Here is the entire script:

   

CREATE TABLE #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

Thanks, Darin

score 0 · Answer 1 · answered Sep 10 '18 at 12:03

0

The name of the method should be GetAttachmentData (not GetAttachmentBytes). See https://www.chilkatsoft.com/refdoc/xChilkatEmailRef.html#method72

I'm not sure why SQL did not throw/return an error when you used the name "GetAttachmentBytes". It seems to have just skipped over that statement.

answered Sep 10 '18 at 12:03

Chilkat Software

1,405
1
9
8

Thanks @Matt. That was a typo in my pasted code as I was trying both Chilkat.IMAP and Chilkat.Email (FetchAttachmentBytes and GetAttachmentData respectively). I did correct it to GetAttachmentData and got the same results. For some reason, when I use the table variable approach (INSERT INTO (at)attach EXEC sp_OAMethod (at)email, 'GetAttachmentData', (at)j), it acts like it skips that statement. I'm using the exact same approach to get the email Body property successfully. So, I corrected the method name and the LastErrorText property remained the same. – Darin Searcy Sep 10 '18 at 14:00
Do you think I need to pull the file into a Chilkat.BinData container in order to work with it? – Darin Searcy Sep 11 '18 at 15:34

Save a single email attachment to BLOB using Chilkat and SQL Server

1 Answers1