Save Binary Streams to DB

Keywords:   Save Binary Streams to DB

---

Question:

I would like to load a file into an ADO 2.7 Stream Object and then save this stream object to a access or sql server table.

I transferred a working VB code to WB, the stream object loads the file and saves it to another file if I want, but it does not save it to the database nor does it show any ole errors. In Access I used a normal OLE Field in SQL Server I tried the image and longbinary file. No success :-(

RS = ObjectOpen("ADODB.Connection")
RS = ObjectOpen("ADODB.Recordset")
ST = ObjectOpen("ADODB.Stream")

ST.Type=1 ;binary
ST.Open

;DB.ConnectionString = "Provider=SQLOLEDB; Data Source=%sqlsrv%; Initial Catalog=%sqldb%; User Id=%sqluser%; Password=%sqlpass%"
DB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=H:\db3.mdb;"
DB.Open
RS.Open("t_faxin",DB,1,3) ;adOpenKeyset, adLockOptimistic

file = "c:\input.tif"

RS.AddNew

ST.LoadFromFile(file)
fld = RS.Fields("faximage")
fld.Value = ST.Read
;for debugging: saving the stream object works:
;ST.SaveToFile("C:\debug_stream.tif")

RS.Update

ST.Close
RS.Close
DB.Close

Answer:

WB just doesn't get along with Binary Streams, probably better to use Binary Buffers.

NOTE: You can ( or I have ) saved a Stream Binary Object to XML, no problem, however it is saved as BinHex ( or Binbase64 ), and doesn't easily convert to databases like Access/SQL.

Below is a script, which has the original code (which used the stream object), and the very simple workaround with the Binary Buffer ( which saves having the data assigned to two sources ).

As of WB 2002J, the OLE Extender was updated to handle Binary DataTypes ( or OLE Objects, or SQL Server Images, or Oracle BLOBS )... These datatypes are automatically stored as Winbatch Binary Buffers ( pre-allocated and EOD set ), so in effect Streams and Binary Buffers are equivalent. Thus WB can internally format a binary buffer to the size of a binary field without your script first having to issue BinaryAlloc().

Here is the a script using Access with 2 Caveats:

1. I had to use fileimage rather than image for the column name ( MAGE is reserved in MS SQL for SQL Server Data Types)

2. Once the data is in Access, you cannot read it from there ( will get an OLE Server error ) but when 'exported' it reads fine.

   I used Office 2000 and the MDB file was 202,000 bytes ( all for a 1,784 byte image ).

I will probably:

1. Append multiple images to XML ( as in the first script)
2. 'Zip' the XML file
3. Insert the ZIP file to an OLE field type.

Then I can unpack the data, unzip and re-create the images from XML.

NB: In the script below, to make it work with versions prior to WB 2002J, add:

IntControl(83,1,0,0,0)

to the program.

to earlier versions of WinBatch (prior to 2002J), to stuff single-byte OLE arrays into a BinaryBuffer. This has been changed after 2002J to put OLE arrays into a WinBatch array. With IntControl 83 as shown above, the older behavior can be preserved.

---

; same as binoutin.wbt
; only tested with Access Database and Table
; Stan Littlefield - August 18, 2002

cMDB    = StrCat( dirget(),"testjpg.mdb" )
cConn   = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%cMDB%;"
newfile = StrCat( dirget(),"temp.jpg" )


If IsDefined( param1 ) Then goto output

BoxOpen( "Creating Access Database With JPEG Image","Please Wait" )
If FileExist( cMDB ) Then FileDelete( cMDB )
cat       = ObjectOpen("ADOX.Catalog")
cat.Create(cConn)
ObjectClose(cat)
DB        = ObjectOpen("ADODB.Connection")
DB.Open(cConn)
DB.Execute( " CREATE TABLE PICTURES ( filename TEXT(100), filelen LONG, fileimage LONGBINARY );" )


file    = StrCat( dirget(),"stan.jpg" )
fs      = FileSize(file)


RS      = ObjectOpen("ADODB.Recordset")
RS.Open("PICTURES",cConn,2,3,2)
RS.addnew()
Fld       = RS.fields("filename")
Fld.Value = newfile
Fld       = RS.fields("filelen")
Fld.Value = fs
Fld       = RS.fields("fileimage")

buf       = BinaryAlloc( fs )
BinaryOleType(buf,103,0,0,0)
BinaryRead(buf,file)
Fld.AppendChunk(buf); buffer passed as parameter
BinaryFree( buf )

RS.Update()
ObjectClose(Fld)
RS.Close()
ObjectClose(RS)
DB.Close()
ObjectClose(DB)
Drop( DB,RS,Fld )
BoxShut()
Exit

:output
BoxOpen( "Recreating jpeg as %newfile%","Please Wait")
If ! FileExist( cMDB ) Then Exit
If FileExist( newfile ) Then FileDelete( newfile )


RS        = ObjectOpen("ADODB.RecordSet")
RS.Open("PICTURES",cConn,2,3,2)
RS.MoveFirst()
Fld       = RS.fields("filename")
newfile   = Fld.Value
Fld       = RS.fields("fileimage")
IntControl(83,1,0,0,0,)             ; tell ole to use Binary Buffer for Fld.Value
v         = Fld.Value               ; now loaded as binary buffer
BinaryOleType(v,103,0,0,0)
RS.close()
ObjectClose(RS)
BinaryWrite( v, newfile )

; either way, you still should free the buffer
BinaryFree( v )

BoxShut()
Exit