getting files stored as images back out of sql server 05

• James Clifton

  Valued Member

  Points: 56

  More actions

  July 24, 2006 at 7:50 am

  #118796

  I'm creating a blob database that will store files (.ppt, .doc, .xls etc) in a table using the image data type.  I was able to load the file using:

  PROCEDURE [dbo].[usp_load_blob_files]

  @blob_loc varchar(5000)

  AS

  DECLARE

  @ExecString varchar(5000)

  SELECT

  @ExecString = '

  Insert blob_files (blob_id, blob_col) Select (select max(p_id) from metadata) as blob_id, BulkColumn from Openrowset( Bulk '''

  + @blob_loc + ''', Single_Blob) as blob_col'

  EXEC

  (@ExecString)

   

  I works great.  Now that i've got them in the database how do i get them back out in their original form?  Any assistance would be greatly appreciated.

• James Clifton

  Valued Member

  Points: 56

  More actions

  July 24, 2006 at 12:30 pm

  #651166

  I just found my own answer.  I would still like any addtional input or critique of this procedure.

  you have to first add a com reference "Microsoft ActiveX data object 2.8 library"

  create a datasource and a record set add the name of the stored procedure and the SP's parameter value.

  SP's sql = select * from blob_files where blob_id = @blob_id

  myConn.Open()

  Recordset.SelectCommand = metadataCMD

  Recordset.Fill(ds)

  Dim BlobStream As New ADODB.Stream

  BlobStream.Type = ADODB.StreamTypeEnum.adTypeBinary

  BlobStream.Open()

  BlobStream.Write(ds.Tables(0).Rows(0)(ds.Tables(0).Columns.Item(

  "blob_col").Ordinal))

  BlobStream.SaveToFile(

  "C:\Inetpub\wwwroot\test.ppt")

   

  also just to be clear i worte this as a sub procedure in vb.net inside of my webpage.