none
Need to extract .pdf attachments stored in MS SQL 2005 database

    Question

  • I have several thousand service tickets store in a sql database.  Many of these have attachments such as email, pdf, screen shots, etc.  The attachment is stored and identified in .dat format.

    Can anyone help me extract these attachments into a format (s) where I can see them properly.

    My table looks like:

    idRecord   SvcId     Fileid                         Filedata

    18            1           000000.20.dat            0xFFDIFFE000 . . .

    tia

    Thursday, September 30, 2010 7:08 PM

Answers

  • It will be easier to do this in  .NET or other programming languages like JAVA, C++ etc   

    Little snippet of code in VB.net 

    objConn = New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnStr").ToString())
    
       objConn.Open()
    
       objCmd = New SqlCommand()
       With objCmd
        .CommandText = "SELECT Filedata From testTable"
        .CommandType = CommandType.Text
        .Connection = objConn
       End With
    
       objReader = objCmd.ExecuteReader(CommandBehavior.SequentialAccess)
    
      If Not IsNothing(objReader) = True AndAlso objReader.HasRows = True Then
    
        objReader.Read()
        Dim arrbyte As Byte() = DirectCast(objReader("Filedata"), Byte())
    
        Dim objMemoryStream As System.IO.MemoryStream
        
    
     objMemoryStream = New System.IO.MemoryStream(arrbyte)
    
     'TO DO: write memory stream to disk 
    
    
      End IF
    
    
    
    
    

    >>anyone help me extract these attachments into a format (s) where I can see them properly.            

    You can tell the what type of image (JPEG, GIF or PDF) by reading header values from the byte array

    Lots of file types have what is called a "magic number", which is a string of bytes at (usually) the start of the file. In the case of a JPEG file, according to w3.org, the first 8 bytes are ff d8 ff e0.

    http://en.wikipedia.org/wiki/Magic_number_(programming)

    http://www.astro.keele.ac.uk/oldusers/rno/Computing/File_magic.html

     

    Thursday, September 30, 2010 7:34 PM

All replies

  • It will be easier to do this in  .NET or other programming languages like JAVA, C++ etc   

    Little snippet of code in VB.net 

    objConn = New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnStr").ToString())
    
       objConn.Open()
    
       objCmd = New SqlCommand()
       With objCmd
        .CommandText = "SELECT Filedata From testTable"
        .CommandType = CommandType.Text
        .Connection = objConn
       End With
    
       objReader = objCmd.ExecuteReader(CommandBehavior.SequentialAccess)
    
      If Not IsNothing(objReader) = True AndAlso objReader.HasRows = True Then
    
        objReader.Read()
        Dim arrbyte As Byte() = DirectCast(objReader("Filedata"), Byte())
    
        Dim objMemoryStream As System.IO.MemoryStream
        
    
     objMemoryStream = New System.IO.MemoryStream(arrbyte)
    
     'TO DO: write memory stream to disk 
    
    
      End IF
    
    
    
    
    

    >>anyone help me extract these attachments into a format (s) where I can see them properly.            

    You can tell the what type of image (JPEG, GIF or PDF) by reading header values from the byte array

    Lots of file types have what is called a "magic number", which is a string of bytes at (usually) the start of the file. In the case of a JPEG file, according to w3.org, the first 8 bytes are ff d8 ff e0.

    http://en.wikipedia.org/wiki/Magic_number_(programming)

    http://www.astro.keele.ac.uk/oldusers/rno/Computing/File_magic.html

     

    Thursday, September 30, 2010 7:34 PM
  • If you can use OLE automation enabled on the server, try the following.

    DECLARE

     

    @SQLIMG VARCHAR(MAX),

    @IMG_PATH

    VARBINARY(MAX),

    @TIMESTAMP

    VARCHAR(MAX),

    @ObjectToken

    INT

    DECLARE

     

    IMGPATH CURSOR FAST_FORWARD FOR

     

    Select '<your binary data here>' -- select col from table

     

    OPEN

     

    IMGPATH

    FETCH

     

    NEXT FROM IMGPATH INTO @IMG_PATH

    WHILE

     

    @@FETCH_STATUS = 0

     

    BEGIN

     

    SET @TIMESTAMP = '<Folder name>\' + '<File name>' + '.pdf'

     

    PRINT @TIMESTAMP

     

    PRINT @SQLIMG

     

    EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT

     

    EXEC sp_OASetProperty @ObjectToken, 'Type', 1

     

    EXEC sp_OAMethod @ObjectToken, 'Open'

     

    EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH

     

    EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2

     

    EXEC sp_OAMethod @ObjectToken, 'Close'

     

    EXEC sp_OADestroy @ObjectToken

     

    FETCH NEXT FROM IMGPATH INTO @IMG_PATH

     

    END

    CLOSE

     

    IMGPATH

    DEALLOCATE

     

    IMGPATH

     

    Monday, October 04, 2010 4:35 PM