Retrieve a BLOB as .xls-file

• michael.rosquist

  Ten Centuries

  Points: 1227

  More actions

  November 10, 2006 at 12:18 am

  #97893

  I have a business problem.

  We create Excel files in xls-format in an AS 400 iSeries machine.

  We need to expose those files in a web application.

  The general idea is to

  1. Store the xls as a blob in DB2.

  2. Connect to the AS400 DB2 database by linked server and retrieve the blob.

  3. Store the blob in a SQL server table.

  This all works fine.

  However how do I retrieve the blob from sql server and send away it as an

  .xls-file?

  Since the blob is in binary format I need to convert it to an .xls-file.

  It there some way of doing that in a procedure?

  Or is that a bad idea, should I use a program instead?

  Further ideas?

   

   

• Lowell

  SSC Guru

  Points: 323518

  More actions

  November 10, 2006 at 2:24 pm

  #671183

  you don't fiddle with image/binary fields at the SQL server level usually; you do it an an application level, like with VB, VB.NET, Delphi, whatever.

  Here's the code as an example from vb; it takes the image field(in this case a .dll or .exe stored in the db) and creates a file on the harddrive.

  an image filed simply holds the binary format of the data; doesn't matter if it is XLS, PDF, whatever.

  Dim rs As ADODB.Recordset

  Dim binObj As ADODB.Stream

  Set rs = New ADODB.Recordset

              sql = "SELECT * FROM HDSMODULES WHERE MODULENAME='myProgram.exe' AND MODULEDATA IS NOT NULL"

              rs.Open sql, glbcn, adOpenDynamic, adLockOptimistic

              If Not (rs.BOF And rs.eof) Then

                  Set binObj = New ADODB.Stream

                  binObj.Type = adTypeBinary

                  binObj.Open

                  binObj.Write rs!MODULEDATA

                  binObj.SaveToFile App.Path & "\" & rs!MODULENAME, adSaveCreateOverWrite

                  binObj.Close

                  Set binObj = Nothing

  end if

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!