Enigma

What is better ??

[Personal Thoughts] + [SQL Server] + [VB] - What is Better ??

[rant]

Everybody, (including me) is more comfortable in doing things in a way we know how to. For example, few minutes back I got into a heated argument with a developer regarding technology to be used to read mails received and save the attatchments in a folder in SQL. The developer was advocating on using VB for making the application for the same and me advocating T-SQL. 

I showed him how to do this using xp_readmail.

CREATE procedure usp_ProcessMailAttatchments @copypath varchar(256) = 'c:\Upload\'
as
begin
 set nocount on
 declare @status int 
 declare @msg_id varchar(94) 
 declare @originator varchar(255) 
 declare @cc_list varchar(255) 
 declare @msgsubject varchar(255) 
 declare @query varchar(8000) 
 declare @messages int 
 declare @mapifailure int 
 declare @resultmsg varchar(80) 
 declare @current_msg varchar(94) 
 declare @attachedfiles varchar(800)
 declare @folder varchar(200)
 declare @filename varchar(256)
 declare @filepath varchar(256)
 
 select @messages=0 
 select @mapifailure=0 
 create table #temp
 (
  results varchar(300)
 )
 exec @status = master.dbo.xp_findnextmsg 
    @msg_id=@msg_id output, 
    @unread_only='true' 
 
 if @status <> 0 
   select @mapifailure=1 
 
 while (@mapifailure=0) 
   begin 
 
      if @msg_id is null break 
      if @msg_id = '' break 
 
      exec @status = master.dbo.xp_readmail 
     @msg_id=@msg_id, 
     @originator=@originator output, 
     @cc_list=@cc_list output, 
     @subject=@msgsubject output,
     @suppress_attach = 'false',
     @attachments = @attachedfiles output,   
     @message=@query output, 
     @peek='true' 
 
    if (@attachedfiles is not null)
  begin
   Select
    reverse(substring(reverse(Path),1,charindex('\',reverse(Path),1)-1)) "Filename",
    reverse(substring(reverse(Path),charindex('\',reverse(Path),1),len(path))) "Path"
   into
    #Filepath
   from
   (
    SELECT
     NullIf(SubString(';'
     + @attachedfiles
     + ';' , ID , CharIndex(';' , ';'
     + @attachedfiles
     + ';' , ID) - ID) , '') AS Path
    FROM
     Tally
    WHERE
     ID <= Len(';' + @attachedfiles + ';')
     AND SubString(';' + @attachedfiles + ';' , ID - 1, 1) = ';'
     AND CharIndex(';' , ';' + @attachedfiles + ';' , ID) - ID > 0
   ) a
   declare c1 cursor for select Filename,Path from #Filepath
   open c1
   fetch next from c1 into @filename, @filepath
   while (@@fetch_status = 0)
   begin
    select @query = 'copy '+ @filepath + @filename + '  ' + @copypath + @filename
    insert into #temp exec master..xp_cmdshell @query
    fetch next from c1 into @filename, @filepath
   end
   close c1
   deallocate c1
   drop table #Filepath
  end
      if @status <> 0 
   begin 
     select @mapifailure=1 
    break 
    END

   select @current_msg=@msg_id 
   exec @status = master.dbo.xp_findnextmsg 
     @msg_id=@msg_id output, 
     @unread_only='true' 
 
       if @status <> 0 
   begin 
     select @mapifailure=1 
  end
 END 
 set nocount off
 end  

I was thinking I had him satisfied, until he reverted back with the VB code for the same and i must say I was pretty impressed too.

Private Function OpenMailAndSavedTheAttachFile()


On Error GoTo err
'create the pop3 object
Dim nNumberOfMessages As Integer
Dim nNumberOfAttachment As Integer
nNumberOfMessages = 0
nNumberOfAttachment = 0

Set objPop3 = New POP3svg.Mailer
'set the mail drop folder
objPop3.MailDirectory = strMailDirectory & "\Temp"
'set the remote host info
objPop3.RemoteHost = strRemoteHost
objPop3.UserName = strUser
objPop3.Password = strPassword

'open the pop3 session
objPop3.OpenPop3
'Check for Errors in the Open Mail component
If objPop3.Error <> "" Then
OpenMailAndSavedTheAttachFile = False
Exit Function
End If



nNumberOfMessages = objPop3.MessageCount


Dim nCurrentMessage As Integer
Dim nCurrentAttachment As Integer

For nCurrentMessage = 1 To nNumberOfMessages
objPop3.Retrieve nCurrentMessage

nNumberOfAttachment = objPop3.AttachmentCount

'Check the Mail subject - if it is the correct mail

For nCurrentAttachment = 1 To nNumberOfAttachment

'Save the file

objPop3.SaveAttachment (nCurrentAttachment)

Next nCurrentAttachment

objPop3.Delete (nCurrentMessage)
Next nCurrentMessage

'close the session
objPop3.ClosePop3
Set objPop3 = Nothing
OpenMailAndSavedTheAttachFile = True

Exit Function

End Function