Solved

BLOB types

Posted on 2006-07-20
13
1,331 Views
Last Modified: 2008-01-09
Is it possible to successfully store a PDF file as a BLOB type SQL SERVER using vb.net to access the database?

I am able to store a pdf file into image field in sql server, but when I retrieve the file and try to open it, it tells me that the file has been damaged and cannot be repaired.

Thanks in advance,

Ryan
0
Comment
Question by:dchau12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 17149928
You need to store it into a binary datatype not an image.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17150193
>>You need to store it into a binary datatype not an image. <<
Actually I beg to differ with Atlanta_Mike on this.  You should store it in an Image file, so there must be an error in your .NET code.

But the big question is why are you doing this?  You do realize the disadvantages of saving BLOBs in a database?
0
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 

Author Comment

by:dchau12
ID: 17151094
I have pdf files that our clients need to be able to pull down on demand.  That is why I am doing it.

Here is the code that I am using to store the pdf file to the database.  This code is written in VB.net for a windows application:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim StudyID As Integer = 3001
        Dim BinaryName As String = "test.pdf"

        Dim photofilepath As String = "c:\test.pdf"
        Dim MyPDFFile() As Byte = GetPhoto(photofilepath)
        Dim nwindConn As SqlConnection = New SqlConnection("Data Source=Nikotromus; Integrated Security=SSPI;Initial Catalog=PUP;")

        Dim cmd As SqlCommand = New SqlCommand("INSERT INTO tbl_BLOB (StudyID, BinaryName, MyBlob) " & _
                                                  "Values(@STUDYID, @BinaryName, @MyBlob)", nwindConn)
        cmd.Parameters.Add("@StudyID", SqlDbType.Int).Value = StudyID
        cmd.Parameters.Add("@BinaryName", SqlDbType.NVarChar, 100).Value = BinaryName
        cmd.Parameters.Add("@MyBlob", SqlDbType.Image, MyPDFFile.Length).Value = MyPDFFile

        nwindConn.Open()
        cmd.ExecuteNonQuery()
        nwindConn.Close()
    End Sub

    Public Shared Function GetPhoto(ByVal filePath As String) As Byte()
        Dim fs As FileStream = New FileStream(filePath, FileMode.Open, FileAccess.Read)
        Dim br As BinaryReader = New BinaryReader(fs)

        Dim MyPDFFile() As Byte = br.ReadBytes(fs.Length)

        br.Close()
        fs.Close()

        Return MyPDFFile
    End Function



Here is the code that I am using to retrieve it.  This code is written in vb.net for an asp.net web application:



Dim dsdata As New Data.DataSet
        Dim strqry As String
        Utils = New MyUtils
        strqry = " select myblob, BinaryName, studyID " & _
                 " from tbl_blob " & _
                 " WHERE StudyID  = " & MyFields.getMarketID & "" & _
                 " AND ID = " & CheckBoxID & ""
        dsdata = Utils.getDataSet(strqry)

        Dim rawData() As Byte
        'read out each of the blob types in the blob field.
        rawData = dsdata.Tables(0).Rows(0).Item("myblob")
        'This is the save as dialog box.
        Response.AddHeader("content-disposition", "attachment; filename= " & dsdata.Tables(0).Rows(0).Item("BinaryName") & "")
        'Write the data
        Response.BinaryWrite(rawData)


Please keep in mind that this code works just fine for word documents, excel documents and jpegs.  It is storing the pdf files, but they become corrupted upon retreival.  It is probably difficult to debug this.  does anyone have a code example of a successful storage and retrieval procedure of a .pdf file?

Thanks,

Ryan




0
 

Author Comment

by:dchau12
ID: 17151131
One more thing.  The original file is 36.4 kb.  After I store the file and then pull it out of the database, it grows to 46.9 kb.  

Ryan
0
 

Author Comment

by:dchau12
ID: 17151166
Ok here is another interesting fact.  It messes up .txt files as well, but it does not corrupt them.  I am able to open them up.  It is adding the html of the .aspx page that I am using to extract the file from the database to the end of the text file.  It is adding 10.5 kb to the text file.  That is about the same as it is adding to my pdf file.  I guess my extraction process is busted.
0
 

Author Comment

by:dchau12
ID: 17151254
Okay,  I have it debugged to these two lines:

'This is the save as dialog box.
 Response.AddHeader("content-disposition", "attachment; filename= " & dsdata.Tables(0).Rows(0).Item("BinaryName") & "")
        'Write the data
        Response.BinaryWrite(rawData)

this uses the respones class to allow the user to select the directory where they want to save the file.  It then saves the file in that location, but it corrupts it with the contents of the html of the current page.

This code works just fine, except that it does not give the user the file dialog box to allow them to choose the directory to save in.  hmm....

 Dim binWriter As New BinaryWriter(File.OpenWrite("c:\myfile1.pdf"))
 binWriter.Write(rawData)
 binWriter.Close()
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17151265
>>I have pdf files that our clients need to be able to pull down on demand.  That is why I am doing it.<<
It is usually recommended that you use the file system to store the files and just use the database for path to the file.

>>I guess my extraction process is busted. <<
It could be either one.  I would suggest you post in a more appropriate Topic Area such as:
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/ASP_DOT_NET/
0
 

Author Comment

by:dchau12
ID: 17151273
You are right.  I thought it might have been a database issue earlier, but now I have debugged it far enough to know that it is an asp.net problem.

Thanks for any consideration,

Ryan
0
 

Author Comment

by:dchau12
ID: 17153775
I found my problem.  I wasn't flushing the buffer and closing the socket connection properly after I retrieved the binary from the database.   Here is the complete code to retrieve a binary object from a databae and allow the user to save it on their file system:

Dim dsdata As New Data.DataSet
        Dim strqry As String
        Utils = New MyUtils
        strqry = " select myblob, BinaryName, studyID " & _
                 " from tbl_blob " & _
                 " WHERE StudyID  = " & MyFields.getMarketID & "" & _
                 " AND ID = " & CheckBoxID & ""
        dsdata = Utils.getDataSet(strqry)

        Dim rawData() As Byte
        'read out each the blob type field
        rawData = dsdata.Tables(0).Rows(0).Item("myblob")

        'This is the save as dialog box.
        Response.AddHeader("content-disposition", "attachment; filename= " & dsdata.Tables(0).Rows(0).Item("BinaryName") & "")
        'Write the data
        Response.BinaryWrite(rawData)
        'Flush the buffer
        Response.Flush()
        'Close the socket connection
        Response.Close()
        Response.End()

Ryan
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17156334
Sounds good.  Please close the question.  Here's how:

I answered my question myself. What do I do?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
0
 

Accepted Solution

by:
PashaMod earned 0 total points
ID: 17175028
Closed, 500 points refunded.
PashaMod
Community Support Moderator
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question