VB Forum / Database Access / April 2008

Ok I admit semi-defeat. I have a basic script which snags files from a
temp location and stuffs them into a database. It appears to work
wonderfully every time except something is getting 'lost in
translation' between my ADO Stream and writing chunks to the field for
updating. Somewhere, somehow it appears that the data is truncated at
exactly 511,000 bytes. I've been working with 4-5meg files as a test.
This is in a system that uses a VBScript like interface that gives me
access to COM, etc. It's not an ASP page so it's not an IIS influence
on anything.

I know it's not the ADO stream as I can write it back to file and it's
solid. Something about the update or perhaps the ADO provider is
goofing this up and I just can't put my finger on it. Perhaps the
provider needs something else, or the DB itself needs a tweak. The
DOC_BLOB field is just your plain vanilla image field. I'm using
SQLEXPRESS at the moment if that makes any difference.

adTypeBinary = 1
adSaveCreateOverWrite = 2

sConnectionString = "Provider=SQLOLEDB;Persist Security
Info=False;Initial Catalog=MYDB;Data Source=MYSYSTEM\MYINSTANCE;User
Id=ID;Password=pwd;"

' I had these lower thinking a timeout was the issue
sConnectionTimeout = 900
sCommandTimeout = 900

Set oApplicationDS = CreateObject("ADODB.Connection")
oApplicationDS.ConnectionTimeout = sConnectionTimeout
oApplicationDS.CommandTimeout = sCommandTimeout
oApplicationDS.Open(sConnectionString)

' stuff happens here that has no bearing and has been trimmed for
clarity

oSearchDOCSSQL = CreateObject("ADODB.Command")
Set oSearchDOCSSql.ActiveConnection = oApplicationDS
oSearchDOCSSql.CommandType = 1

oSearchDOCSSql.CommandText = "SELECT EVENT_NUM,DOC_NUM,DOC_BLOB FROM
EVENTDOCS WHERE (EVENT_NUM=16337) AND (DOC_NUM='1');"

Set oSearchDOCSRS = CreateObject("ADODB.Recordset")
oSearchDOCSRS.CursorType = 3
oSearchDOCSRS.LockType =  3
oSearchDOCSRS.Open(oSearchDOCSSql)

IMGBinaryStream = CreateObject("ADODB.Stream")
IMGBinaryStream.Type = adTypeBinary
IMGBinaryStream.Open
IMGBinaryStream.LoadFromFile(sVal)

' tried various block sizes... no difference
adBlockSize = 1024
MidStart = 0
IMGBinaryStream.Position = MidStart

If not oSearchDOCSRS.EOF and not osearchDOCSRS.BOF then
 ColumnSize = IMGBinaryStream.Size
 NumBlocks = ColumnSize div adBlockSize
 LeftOver = ColumnSize Mod adBlockSize

 StrData = IMGBinaryStream.Read(LeftOver)

 oSearchDOCSRS.Fields("DOC_BLOB").AppendChunk(StrData)
 MidStart = MidStart + LeftOver

 For i = 1 To NumBlocks
   IMGBinaryStream.Position = MidStart
   StrData = IMGBinaryStream.Read(adBlockSize)
   oSearchDOCSRS.Fields("DOC_BLOB").AppendChunk(StrData)
   MidStart = MidStart + adBlockSize
 Next

 oSearchDOCSRS.Update
End If

' At this point data is in the field. I had wrapped all of this in a
try except to detect if maybe I had a timeout or other error.. nope

oSearchDOCSRS.Close
IMGBinaryStream.Close

Set oSearchDOCSRS = Nothing
Set oSearchDOCSSQL = Nothing
Set IMGBinaryStream = Nothing

When I extract the data to a file and run a file comparison utility to
the original they match exactly up until the 511,000 point where the
other file just ends.

Anybody seen truncation with no errors for no apparent reason before?
This happens on various image files so it's not something about one
file in particular either.

Thx,

Charles