Solved

inserting a pdf file and reading it ?

Posted on 2004-10-12
14
368 Views
Last Modified: 2011-10-03
How to insert a BLOB datatype ( I'm using a .pdf file) in SQL Server and to read it ? In my schema "blob" is considered as "image".
0
Comment
Question by:biswaranjan_rath
  • 6
  • 3
  • 2
  • +2
14 Comments
 
LVL 2

Expert Comment

by:praveen_ms
ID: 12285360
What are using to insert (VB VC++)??

If you are using VB you can user ADODB.Strean object basically open stream your pdf file using this object and assign it to Recordset for inserting.

If you need code sample please let me know.

Cheers
Praveen
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12285368
You are much better to not store this in SQL but rather maintain a URL in sql which points to the file stored in a filing system.
0
 
LVL 3

Author Comment

by:biswaranjan_rath
ID: 12285375
I'm using java
0
 
LVL 3

Author Comment

by:biswaranjan_rath
ID: 12285385
I can't save the URL, design doesn't approve this.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12285409
when you say " design doesn't approve this."  are you refering to a department or person responsible for designing the system.   If you you might want to approach them with a v'long list of why the fileing system should be used,  .eg.   Major Performance degredation of SQL Server,   Increased DB size causing longer backup and resore times, etc etc.  All the arguments against this are well published on EE.
0
 
LVL 2

Expert Comment

by:praveen_ms
ID: 12285421
If you are using Java, best is to bcp in and bcp out....

Cheers
Praveen
0
 
LVL 3

Author Comment

by:biswaranjan_rath
ID: 12285429
this approach is taken coz, it can be shown directly in reports from database. anyway at this moment i can't suggest about design changes.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 18

Expert Comment

by:ShogunWade
ID: 12285437
Fair enough but be warned.   Its a recipe for disaster.
0
 
LVL 3

Author Comment

by:biswaranjan_rath
ID: 12285499
Praveen, could you elaborate little on bcp in and bcp out.
0
 
LVL 3

Expert Comment

by:pra_kumar03
ID: 12287294
There is a similar thing that we are doing... though we do it with VB , but i did refer to one of my colleague...In java there is some classes which u can use to convert the PDF file to a Binary object and then update the content of the image field using jdbc.
0
 
LVL 9

Expert Comment

by:apirnia
ID: 12289777
Couple of things it is recomended not to store the blob in the database.
What you can do is to store the PDF in a directory in your server and save the link or path in the database table.

Any ways here is teh code to upload blob to a DB.
You need 3 files.  
1) is your HTML file that usesr use to browse to the file to upload
2) this is an ASP include file with bunch of functions
3) is the file that calls the functions and takes care of uploading and reading the file.
and offcourse the database table to hold the info.

I'll give you my code which is based on the following table structure. You can run this in Query analyzer.

CREATE TABLE [dbo].[LetterHeads] (
      [ID] [int] IDENTITY (1, 1) NOT NULL ,
      [FileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [FileSize] [int] NOT NULL ,
      [FileData] [image] NOT NULL ,
      [ContentType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [PCID] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

********************************************************************
Here is the HTML file which is for browsing

<!-- insert.htm -->
<html>
<head>
      <title>Inserts Images into Database</title>
      <style>
            body, input { font-family:verdana,arial; font-size:10pt; }
      </style>
</head>
<body>
      <p align="center">
            <b>Inserting Binary Data into Database</b><br>
            <a href="show.asp">To see inserted data click here</a>
      </p>
      
      <table border="0" align="center">
      <tr>
      <form method="POST" enctype="multipart/form-data" action="Insert.asp">
      <td>First Name :</td><td>
            <input type="text" name="fname" size="40"></td>
      </tr>
      <td>Last Name :</td><td>
            <input type="text" name="lname" size="40"></td>
      </tr>
      <td>PCID :</td><td>
            <input type="text" name="PCID" size="40">
      </td></tr>
      <td>File :</td><td>
            <input type="file" name="file" size="40"></td></tr>
      <td> </td><td>
            <input type="submit" value="Submit"></td></tr>
      </form>
      </tr>
      </table>

</body>
</html>


*************************************************
Here is Show.asp. From previous page there is a link to here to see what you uploaded


<%
   ' -- show.asp --
   ' Generates a list of uploaded files
   
   Response.Buffer = True
   
   ' Connection String
      Dim connStr
            connStr = "Provider=SQLOLEDB;Data Source=localhost;" & _      
            "Initial Catalog=MyTesting;User Id=sa;" & _      
            "Password=sa"

%>
<html>
<head>
   <title>Inserts Images into Database</title>
   <style>
      body, input, td { font-family:verdana,arial; font-size:10pt; }
   </style>
</head>
<body>
   <p align="center">
      <b>Showing Binary Data from the Database</b><br>
      <a href="insert.htm">To insert data click here</a>
   </p>
   
   <table width="700" border="1" align="center">
<%
   ' Recordset Object
   Dim rs
      Set rs = Server.CreateObject("ADODB.Recordset")
     
      ' opening connection
      rs.Open "select ID,FileName,FileSize,ContentType," & _
            "FirstName,LastName,PCID from LetterHeads " & _
            "order by ID desc", connStr, 3, 4

      If Not rs.EOF Then
         Response.Write "<tr><td colspan=""7"" align=""center""><i>"
         Response.Write "No. of records : " & rs.RecordCount
         Response.Write ", Table : Files</i><br>"
         Response.Write "</td></tr>"
   
         While Not rs.EOF
            Response.Write "<tr><td>"
            Response.Write rs("ID") & "</td><td>"
            Response.Write "<a href=""file.asp?ID=" & rs("ID") & """>"
            Response.Write rs("FileName") & "</a></td><td>"
            Response.Write rs("FileSize") & "</td><td>"
            Response.Write rs("ContentType") & "</td><td>"
            Response.Write rs("FirstName") & "</td><td>"
            Response.Write rs("LastName") & "</td><td>"
            Response.Write rs("PCID") & "</td><td>"
            Response.Write "</td></tr>"
            rs.MoveNext
         Wend
      Else
         Response.Write "No Record Found"
      End If
     
      rs.Close
      Set rs = Nothing
%>
   </table>
</body>
</html>



******************************************************
Here is Insert.asp. From previous page we redirect here from the form

<% ' Insert.asp %>
<!--#include file="Loader.asp"-->
<%
  Response.Buffer = True

  ' load object
  Dim load
    Set load = new Loader
   
    ' calling initialize method
    load.initialize
   
  ' File binary data
  Dim fileData
    fileData = load.getFileData("file")
  ' File name
  Dim fileName
    fileName = LCase(load.getFileName("file"))
  ' File path
  Dim filePath
    filePath = load.getFilePath("file")
  ' File path complete
  Dim filePathComplete
    filePathComplete = load.getFilePathComplete("file")
  ' File size
  Dim fileSize
    fileSize = load.getFileSize("file")
  ' File size translated
  Dim fileSizeTranslated
    fileSizeTranslated = load.getFileSizeTranslated("file")
  ' Content Type
  Dim contentType
    contentType = load.getContentType("file")
  ' No. of Form elements
  Dim countElements
    countElements = load.Count
  ' Value of text input field "fname"
  Dim fnameInput
    fnameInput = load.getValue("fname")
  ' Value of text input field "lname"
  Dim lnameInput
    lnameInput = load.getValue("lname")
  ' Value of text input field "PCID"
  Dim PCID
    PCID = load.getValue("PCID")  
   
  ' destroying load object
  Set load = Nothing
%>

<html>
<head>
  <title>Inserts Images into Database</title>
  <style>
    body, input, td { font-family:verdana,arial; font-size:10pt; }
  </style>
</head>
<body>
  <p align="center">
    <b>Inserting Binary Data into Database</b><br>
    <a href="show.asp">To see inserted data click here</a>
  </p>
 
  <table width="700" border="1" align="center">
  <tr>
    <td>File Name</td><td><%= fileName %></td>
  </tr><tr>
    <td>File Path</td><td><%= filePath %></td>
  </tr><tr>
    <td>File Path Complete</td><td><%= filePathComplete %></td>
  </tr><tr>
    <td>File Size</td><td><%= fileSize %></td>
  </tr><tr>
    <td>File Size Translated</td><td><%= fileSizeTranslated %></td>
  </tr><tr>
    <td>Content Type</td><td><%= contentType %></td>
  </tr><tr>
    <td>No. of Form Elements</td><td><%= countElements %></td>
  </tr><tr>
    <td>First Name</td><td><%= fnameInput %></td>
  </tr><tr>
    <td>Last Name</td><td><%= lnameInput %></td>
  </tr>
  <tr>
    <td>PCID</td><td><%= PCID %></td>
  </tr>
  </table><br><br>
 
  <p style="padding-left:220;">
  <%= fileName %> data received ...<br>
  <%
    ' Checking to make sure if file was uploaded
    If fileSize > 0 Then
   
      ' Connection string
      Dim connStr
            connStr = "Provider=SQLOLEDB;Data Source=localhost;" & _      
            "Initial Catalog=MyTesting;User Id=sa;" & _      
            "Password=sa"
   
      ' Recordset object
      Dim rs
        Set rs = Server.CreateObject("ADODB.Recordset")
       
        rs.Open "LetterHeads", connStr, 2, 2
       
        ' Adding data
        rs.AddNew
          rs("FileName") = fileName
          rs("FileSize") = fileSize
          rs("FileData").AppendChunk fileData
          rs("ContentType") = contentType
          rs("FirstName") = fnameInput
          rs("LastName") = lnameInput
          rs("PCID") = PCID
        rs.Update
       
        rs.Close
        Set rs = Nothing
       
      Response.Write "<font color=""green"">File was successfully uploaded..."
      Response.Write "</font>"
    Else
      Response.Write "<font color=""brown"">No file was selected for uploading"
      Response.Write "...</font>"
    End If
     
     
    If Err.number <> 0 Then
      Response.Write "<br><font color=""red"">Something went wrong..."
      Response.Write "</font>"
    End If
  %>
  </p>
 
  <br>
  <table border="0" align="center">
  <tr>
  <form method="POST" enctype="multipart/form-data" action="Insert.asp">
  <td>First Name :</td><td>
    <input type="text" name="fname" size="40" ></td>
  </tr>
  <td>Last Name :</td><td>
    <input type="text" name="lname" size="40" ></td>
  </tr>
  <td>PCID :</td><td>
    <input type="text" name="PCID" size="40" ></td>
  </tr>
  <td>File :</td><td>
    <input type="file" name="file" size="40"></td>
  </tr>
  <td> </td><td>
    <input type="submit" value="Submit"></td>
  </tr>
  </form>
  </tr>
  </table>

</body>
</html>


******************************************************
Here is include file on top of insert.asp its called loader.asp


<%

  Class Loader
    Private dict
   
    Private Sub Class_Initialize
      Set dict = Server.CreateObject("Scripting.Dictionary")
    End Sub

    Private Sub Class_Terminate
      If IsObject(intDict) Then
        intDict.RemoveAll
        Set intDict = Nothing
      End If
      If IsObject(dict) Then
        dict.RemoveAll
        Set dict = Nothing
      End If
    End Sub

    Public Property Get Count
      Count = dict.Count
    End Property

    Public Sub Initialize
      If Request.TotalBytes > 0 Then
        Dim binData
          binData = Request.BinaryRead(Request.TotalBytes)
          getData binData
      End If
    End Sub

    Public Function getFileData(name)
      If dict.Exists(name) Then
        getFileData = dict(name).Item("Value")
        Else
        getFileData = ""
      End If
    End Function

    Public Function getValue(name)
      Dim gv
      If dict.Exists(name) Then
        gv = CStr(dict(name).Item("Value"))
       
        gv = Left(gv,Len(gv)-2)
        getValue = gv
      Else
        getValue = ""
      End If
    End Function

    Public Function saveToFile(name, path)
      If dict.Exists(name) Then
        Dim temp
          temp = dict(name).Item("Value")
        Dim fso
          Set fso = Server.CreateObject("Scripting.FileSystemObject")
        Dim file
          Set file = fso.CreateTextFile(path)
            For tPoint = 1 to LenB(temp)
                file.Write Chr(AscB(MidB(temp,tPoint,1)))
            Next
            file.Close
          saveToFile = True
      Else
          saveToFile = False
      End If
    End Function

    Public Function getFileName(name)
      If dict.Exists(name) Then
        Dim temp, tempPos
          temp = dict(name).Item("FileName")
          tempPos = 1 + InStrRev(temp, "\")
          getFileName = Mid(temp, tempPos)
      Else
        getFileName = ""
      End If
    End Function

    Public Function getFilePath(name)
      If dict.Exists(name) Then
        Dim temp, tempPos
          temp = dict(name).Item("FileName")
          tempPos = InStrRev(temp, "\")
          getFilePath = Mid(temp, 1, tempPos)
      Else
        getFilePath = ""
      End If
    End Function

    Public Function getFilePathComplete(name)
      If dict.Exists(name) Then
        getFilePathComplete = dict(name).Item("FileName")
      Else
        getFilePathComplete = ""
      End If
    End Function

    Public Function getFileSize(name)
      If dict.Exists(name) Then
        getFileSize = LenB(dict(name).Item("Value"))
      Else
        getFileSize = 0
      End If
    End Function

    Public Function getFileSizeTranslated(name)
      If dict.Exists(name) Then
        temp = LenB(dict(name).Item("Value"))
          If temp <= 1024 Then
            getFileSizeTranslated = temp & " bytes"  
          Else
            temp = FormatNumber((temp / 1024), 2)
            getFileSizeTranslated = temp & " kilobytes"
          End If
      Else
        getFileSizeTranslated = ""
      End If
    End Function

    Public Function getContentType(name)
      If dict.Exists(name) Then
        getContentType = dict(name).Item("ContentType")
      Else
        getContentType = ""
      End If
    End Function

  Private Sub getData(rawData)
    Dim separator
      separator = MidB(rawData, 1, InstrB(1, rawData, ChrB(13)) - 1)

    Dim lenSeparator
      lenSeparator = LenB(separator)

    Dim currentPos
      currentPos = 1
    Dim inStrByte
      inStrByte = 1
    Dim value, mValue
    Dim tempValue
      tempValue = ""

    While inStrByte > 0
      inStrByte = InStrB(currentPos, rawData, separator)
      mValue = inStrByte - currentPos

      If mValue > 1 Then
        value = MidB(rawData, currentPos, mValue)

        Dim begPos, endPos, midValue, nValue
        Dim intDict
          Set intDict = Server.CreateObject("Scripting.Dictionary")

          begPos = 1 + InStrB(1, value, ChrB(34))
          endPos = InStrB(begPos + 1, value, ChrB(34))
          nValue = endPos

        Dim nameN
          nameN = MidB(value, begPos, endPos - begPos)

        Dim nameValue, isValid
          isValid = True
         
          If InStrB(1, value, stringToByte("Content-Type")) > 1 Then

            begPos = 1 + InStrB(endPos + 1, value, ChrB(34))
            endPos = InStrB(begPos + 1, value, ChrB(34))

            If endPos = 0 Then
              endPos = begPos + 1
              isValid = False
            End If

            midValue = MidB(value, begPos, endPos - begPos)
              intDict.Add "FileName", trim(byteToString(midValue))

          begPos = 14 + InStrB(endPos + 1, value, stringToByte("Content-Type:"))
          endPos = InStrB(begPos, value, ChrB(13))

            midValue = MidB(value, begPos, endPos - begPos)
              intDict.Add "ContentType", trim(byteToString(midValue))

            begPos = endPos + 4
            endPos = LenB(value)

            nameValue = MidB(value, begPos, ((endPos - begPos) - 1))
          Else
            nameValue = trim(byteToString(MidB(value, nValue + 5)))
          End If

          If isValid = True Then

            intDict.Add "Value", nameValue
            intDict.Add "Name", nameN

            dict.Add byteToString(nameN), intDict
          End If
      End If

      currentPos = lenSeparator + inStrByte
    Wend
  End Sub
 
  End Class

  Private Function stringToByte(toConv)
    Dim tempChar
     For i = 1 to Len(toConv)
       tempChar = Mid(toConv, i, 1)
      stringToByte = stringToByte & chrB(AscB(tempChar))
     Next
  End Function

  Private Function byteToString(toConv)
    For i = 1 to LenB(toConv)
      byteToString = byteToString & Chr(AscB(MidB(toConv,i,1)))
    Next
  End Function
%>
0
 
LVL 3

Author Comment

by:biswaranjan_rath
ID: 12295741
I don't think it'll help me, since i tried this and gone through the logic. In java, i'm using outputstream class to save image data. but in database the format changed and it stored as text. means while i store a .pdf file into it, it just show me a sequence of hexadecimal numbers. after save the data as file, it doesn't show me the file contents. so is there anything, so that i can get my file back. i think the way it  stores data, is different. so can you suggest something.
0
 
LVL 9

Accepted Solution

by:
apirnia earned 125 total points
ID: 12300001
Are you trying to this in Java??  I provided an ASP code above which is the easiest way to do this.

You might have to twick the above code a little. When I was copy pasting from our servers I might of missed something but 99.9% is there and has been tested.

0
 
LVL 3

Author Comment

by:biswaranjan_rath
ID: 12306086
i tried in java with inputstream/outputstream classes and it's working fine...
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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.
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.
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…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

757 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

23 Experts available now in Live!

Get 1:1 Help Now