Solved

BLOB types

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

Expert Comment

by:Atlanta_Mike
Comment Utility
You need to store it into a binary datatype not an image.
0
 
LVL 7

Expert Comment

by:FDzjuba
Comment Utility
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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
 

Author Comment

by:dchau12
Comment Utility
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
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:dchau12
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Closed, 500 points refunded.
PashaMod
Community Support Moderator
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now