Solved

BLOB types

Posted on 2006-07-20
13
1,330 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 7

Expert Comment

by:FDzjuba
ID: 17150166
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

739 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