VB6/SQLServer/ADO stream : Corruptions when inserting/updating binary/image fields
Posted on 2003-10-30
Hi all - there are . . . FIVE HUNDRED ( 500 ) points up for grabs on this one . . .
I have created a bespoke document management system that allows a user to read in documents from the file system into a local MSDE database, and check files in and out between the local MSDE "cache" and the central SQL Server 2000 "repository". I am unable to store text pointers to external file system objects in this case, so I am using binary/image objects.
There are three tables used, over two databases...
CACHE.cDocument_New : When new documents are imported locally on a workstation, they are inserted into this table. The table contains numerous fields of meta-data relating to the document, and a field called, "XMLContent" (it's not always XML) which contains binary data.
REPOSITORY.rDocument : When the document is "checked in", it is inserted into this table and becomes "under version control" status. This table is synchronised to:
CACHE.cDocument - which "downloads" a copy of the rDocument data, such that a user can continue to access files when not connected to the REPOSITORY.
I can insert a new record into cDocument_New using rs.AddNew and write it to file using ADODB.stream -> this file opens correctly
When I check the file in (DELETE from rDocument, then rs.AddNew) and then attempt to open it by writing it to file and opening -> the file is corrupted.
.txt files do not corrupt - even when as big as 128kb.
.jpg files corrupt, but can still be opened - however the bottom 3/4 of the image is usually inverted in colour (corruptions).
Anyone - please help! Suggestions welcome ;)