Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

OLE COM ADO CDO ADSI LDAP

	ADO DAO
	Collections
	Constants
	Crystal Reports
	DSOFILE
	Errors
	ImageMagick
	IMAPI
	LogParser
	Lotus Notes
	MDAC
	Netscape
	OLE and Outlook
	OLE TUTORIAL
	OLE with Access
	OLE with Acrobat Viewer
	OLE with Excel
	OLE with Google Earth
	OLE with MSIE
	OLE with PowerPoint
	OLE with Word
	OLE with XML
	OpenOffice
	PDF Conversion
	RegExp
	Samples from Users
	SAPI
	Shell
	SQLite
	Task Scheduler
	Textract
	UDFs
	WIA
	WIL Type Viewer
	WindowsInstaller
	WinHttpRequest
	Winsock
	WSH
	Zip

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

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:

I had to use fileimage rather than image for the column name ( MAGE is reserved in MS SQL for SQL Server Data Types)
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

Article ID:   W15587

File Created: 2003:05:13:11:29:08

Last Updated: 2003:05:13:11:29:08