Solved

BLOB types

Posted on 2006-07-20
13
1,329 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
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query for highest sequence 4 62
Rebooting Witness SQL Server 2 25
Need sql in string 2 29
SQL parsing XML works but want to do it another way 4 19
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

756 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