Solved

BLOB types

Posted on 2006-07-20
13
1,327 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql server concatenate fields 10 35
T-SQL: "HAVING CASE" Clause 1 25
SQL - Update field defined as Text 6 17
SQL Server Import/Error Wizard error 12 19
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

772 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