none
SQl Server 2005 : Storing pdf,txt doc files in Tables

Answers

  •  

    I also forgot you can use OPENROWSET(BULK...

     

    Code Snippet

     

    create table #ORStable (doclen bigint, doc varbinary(max))

    insert into #ORStable

    select len(bulkcolumn), *

    from

    openrowset(bulk 'C:\test.doc', SINGLE_BLOB)

    as r

     

    select *

    from #ORStable

     

     

    To use BULK INSERT you need a format file.

    Unfortunately, the format file requires the exact size of the word doc

     

    Format file definition: (worddoc.fmt)

    Code Snippet
     9.0
     1
     1 SQLBINARY 0 20480 "" 1 doc ""

     

     

    create table #BItable (doc varbinary(max))

    BULK INSERT #BItable

    FROM 'C:\test.doc'

    WITH ( FORMATFILE= 'C:\worddoc.fmt', CODEPAGE= 'RAW' )

     

    select len(doc), *

    from #BITable

     

    To export the data back to a .doc file requires using BCP and a format file (again with the exact size)

     

     Working with the .txt files and varchar(max) is a little easier, but you need varbinary to save the exact images of the PDF and DOC files.

     

     

    Friday, May 25, 2007 12:33 PM

All replies

  •  

    Load them into a varbinary(max) column.

    The txt could also go into a varchar(max) column.

    varbinary(max) and varchar(max) are limited to 2GB of data.

     

    1.  Create table with varbinary(max) column(s)

    2.  Use BCP or BULK INSERT or OPENROWSET(BULK... or create an SSIS Package to transfer the data from flat file to SQL

     

     

    Thursday, May 24, 2007 12:39 PM
  • Hi Dale,

    I am very new to this concept. I cannot go for SSIS. So I need to use bulk insert. Can you please illustrate with an example.

     

    Thanks,

    Uma Ramiya

    Friday, May 25, 2007 7:02 AM
  •  

    I also forgot you can use OPENROWSET(BULK...

     

    Code Snippet

     

    create table #ORStable (doclen bigint, doc varbinary(max))

    insert into #ORStable

    select len(bulkcolumn), *

    from

    openrowset(bulk 'C:\test.doc', SINGLE_BLOB)

    as r

     

    select *

    from #ORStable

     

     

    To use BULK INSERT you need a format file.

    Unfortunately, the format file requires the exact size of the word doc

     

    Format file definition: (worddoc.fmt)

    Code Snippet
     9.0
     1
     1 SQLBINARY 0 20480 "" 1 doc ""

     

     

    create table #BItable (doc varbinary(max))

    BULK INSERT #BItable

    FROM 'C:\test.doc'

    WITH ( FORMATFILE= 'C:\worddoc.fmt', CODEPAGE= 'RAW' )

     

    select len(doc), *

    from #BITable

     

    To export the data back to a .doc file requires using BCP and a format file (again with the exact size)

     

     Working with the .txt files and varchar(max) is a little easier, but you need varbinary to save the exact images of the PDF and DOC files.

     

     

    Friday, May 25, 2007 12:33 PM
  • Thankyou it worked
    Monday, May 28, 2007 9:07 AM
  • Could you please post an example of pulling the data out of the server and making it back into a file. I've tried to and must be missing something. Thanks!
    Monday, May 28, 2007 2:48 PM
  • Hi,

     

    Here is an easy and straight forward approach for Uploading the files files from hard disk and vice versa unless you are not going to process the contents in the file. This code works for pdf,txt,doc,jpeg,gif,xls. Remember to add file extension when saving the file.

     

    http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b317016

     

     

    public void SqlBlob2File(string DestFilePath)
    {
       try
       {
               int PictureCol = 0; // the column # of the BLOB field
               SqlConnection cn = new SqlConnection("server=localhost;integrated security=yes;database=NorthWind");
               SqlCommand cmd = new SqlCommand("SELECT Picture FROM Categories WHERE CategoryName='Test'", cn);
               cn.Open();
               SqlDataReader dr  = cmd.ExecuteReader();
               dr.Read();
               Byte[] b = new Byte[(dr.GetBytes(PictureCol, 0, null, 0, int.MaxValue))];
              dr.GetBytes(PictureCol, 0, b, 0, b.Length);
              dr.Close();
              cn.Close();
              System.IO.FileStream fs =      new System.IO.FileStream(DestFilePath, System.IO.FileMode.Create, System.IO.FileAccess.Write);

             fs.Write(b, 0, b.Length);
               fs.Close();
               MessageBox.Show("Image written to file successfully");
       }
       catch(SqlException ex)
       {
                  MessageBox.Show (ex.Message);
       }  
    }

    Friday, June 01, 2007 5:09 AM
  •  

    Do you have an example where the bcp is done in a stored procedure?  When I try this, it creates a file that appears to be the same size as the original file that was loaded into the Attachments table, but when I pull it up in an editor, it says the file is not valid.

     

    declare @SQLcommand nvarchar(4000)

    set @SQLcommand = 'bcp "SELECT Photo FROM Attachments WHERE [ImageName] = ''Picture.bmp'' " queryout "C:\PicutureOut.bmp" -T -N -SServerName'

    exec xp_cmdshell @SQLcommand

    Thursday, August 09, 2007 5:27 PM
  • Hi,

     

    I Guess you didn't save the binary file with the file extension. ImgData & ImgDocIcon - files that are saved

     

    CREATE PROCEDURE [dbo].[insertSiteDocs]

    @SiteId int,

    @imgName nvarchar(50),

    @imgType nvarchar(4),

    @imgSize int,

    @imgData varbinary(MAX),

    @imgDocIcon varbinary(MAX),

    @comment varchar(250),

    @RetVal int OUTPUT

     

    AS

    INSERT INTO dbo.SiteDocs VALUES(

    @SiteId,

    @imgName,

    @imgType,

    @imgSize,

    @imgData,

    @imgDocIcon,

    @comment

    )

    SELECT @RetVal = @@ERROR

    Return @RetVal

     

    select pk_siteId, SiteId, imgName, imgType, (select CAST(ROUND(imgSize/1000, 0) As varchar(50)) + 'KB') As imgSize, imgData, width, height, comment

    from dbo.SiteImages

    WHERE SiteId = @SiteId

     

    The code for retrieving the file at front end is

                    Bitmap bmp;
                    Image img;
                    Database db = DatabaseFactory.CreateDatabase("Con");
                    DbCommand cmd = db.GetStoredProcCommand("spS_SiteImages_SiteImagesId");
                    db.AddInParameter(cmd, "@SiteImagesId", DbType.Int16, id);
                    db.AddInParameter(cmd, "@UserId", DbType.String, "");
                    Byte[] file = (Byte[])db.ExecuteScalar(cmd);
                    System.IO.MemoryStream ImgStream = new MemoryStream(file);
                    bmp = new Bitmap(ImgStream);

     

     

    Monday, August 13, 2007 5:10 AM
  •  

    CREATE TABLE [TestTable]
    (
             [ID] [int] IDENTITY(1,1) NOT NULL,
             [FileName] [nvarchar](15) NOT NULL,
             [Extension] [nvarchar](5) NOT NULL,
             [Content] [image] NULL
    )

    Fig - (1) Scrpit for Table

             In my demo project I have used one file Upload control (to upload the file), one Textbox (where user can enter ID for uploaded file to retrieve it) and 2 buttons (one for uploading file and other for retrieving).

             When user select the file and click on Upload button the code stores the selected file in database. Below is the code for that,

        using (SqlConnection cnn = new SqlConnection(“Connection String”))
        {
                        cnn.Open();
                        SqlCommand cmd = new SqlCommand(“InsertFile”, cnn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add(new SqlParameter(“@FileName”, “Name of 
                                                                   Uploaded File”
    ));
                        cmd.Parameters.Add(new SqlParameter(“@Extension”, “Extension of
                                                                   Uploaded File”
    ));
                        cmd.Parameters.Add(new SqlParameter(“@Content”, “byte array
                                                                   (byte[]) of uploaded file”
    ));
                        cnn.Close()
         }

       Fig - (2) Code for inserting selected file in database.    

              Now when user enter FileID for uploaded file in textbox and click on retrieve button we will get the Content and extension field from database for that file id. You can use FillDataSet method to retrieve the byte array. Below code shows how to send retrieved file to user depending on the extension.

                    string strExtenstion = “extension of retrieved file”;
                    byte[] bytFile =  “Byte array retrieved from database”;

                    Response.Clear();
                    Response.Buffer = true;

                    if (strExtenstion == “.doc” || strExtenstion == “.docx”)
                    {
                            Response.ContentType = “application/vnd.ms-word”;
                            Response.AddHeader(“content-disposition”,
                                                              “attachment;filename=Tr.doc”);
                    }
                    else if (strExtenstion == “.xls” || strExtenstion == “.xlsx”)
                    {
                             Response.ContentType = “application/vnd.ms-excel”;
                             Response.AddHeader(“content-disposition”,
                                                             “attachment;filename=Tr.xls”
    );
                    }
                    else if (strExtenstion == “.pdf”)
                    {
                             Response.ContentType = “application/pdf”;
                             Response.AddHeader(“content-disposition”,
                                                            “attachment;filename=Tr.pdf”
    );
                    }

                    Response.Charset = “”;
                    Response.Cache.SetCacheability(HttpCacheability.NoCache);

                    // If you write,
                   
    // Response.Write(bytFile1);
                   
    // then you will get only 13 byte in bytFile.
                    Response.BinaryWrite(bytFile);

                    Response.End();

    Fig - (3) Code to retrieve the file from database.

    Happy Programming !!!

    Monday, June 16, 2008 10:48 AM