Reading fields from Access via ADO into a BLOB

Hi’ all … When importing data into Navision (3.70) from an Access .mdb database via ADO, how do You suggest handling fields with more than 2000 bytes of data? The maximum allowed size for a Variable of the the type Binary is 2000, and since several of the fields in this database exceeds that amount, Binary really isn’t an option. I’ve played around with In-/OutStreams trying to pipe data into a BLOB field, but all I get from Navision is “You are using an illegal datatype for this function”, probably because Navision can’t address the data indirectly. At this point I really don’t care if I have to run some obscure commandline utility and subsequently import the data byte per byte from a textfile. All I need now is a workaround - or (by jolly) an explanation on how to make Navision cope by itself - if at all possible. Any suggestions ?

Okay, I got a little closer - but I’m still having trouble. The following code seems to work, as long as FieldLen stays within 1024 bytes. C/SIDE will read from the ADO and write to the stream as It’s supposed to, but on the second GetChunk it suddenly “realises” that this is an illegal opreration and responds with :


This Message is for C/AL Programmers: This datatype is not supported by C/SIDE. The following datatypes are allowed: VT_VOID, VT_I2, VT_I4, VT_R4, VT_R8, VT_CY, VT_DATE, VT_BSTR and VT_BOOL

<Variables> Pos, Bufferpos, FieldLen, ChunkRead, I = Integer Chunk = Text[1024] BinBuffer = Binary[2000] MyOutStream = Outstream </Variables> <Code> FieldLen := ADOrs.Fields.Item('txt9').ActualSize; MyRec.BLOB.CREATEOUTSTREAM(MyOutStream); Pos := 0; BufferPos := 0; ChunkRead := 1024; WHILE (Pos < FieldLen) AND (FieldLen <> 0) DO BEGIN IF Pos + ChunkRead > FieldLen THEN ChunkRead := FieldLen - Pos; Chunk := ADOrs.Fields.Item('txt9').GetChunk(ChunkRead); FOR I := 1 TO ChunkRead DO BEGIN BufferPos += 1; BinBuffer[BufferPos] := Chunk[I]; IF (BufferPos = 2000) OR (Pos+I = FieldLen) THEN BEGIN MyOutStream.WRITE(BinBuffer); CLEAR(BinBuffer); BufferPos := 0; END; END; Pos += ChunkRead; END; </Code> Any suggestions ?

I’ve hit this problem many times, both with respect to “illegal” data definitions in databases being read through ADO, as well as integrating with DLL functions that return unsupported data types. I’ve taken two different approaches: 1. Create a codeunit to test the field value to see if it can be read - this facilitates using CODEUNIT.RUN so the whole process doesn’t error out. This is only good if the acceptable approach is to skip any unreadable fields (i.e. it’s fine for simply skipping Null values). 2. Create a wrapper DLL in VB to read the fields and break them into chunks to return into navision. If anyone has a better approach, I’d be very glad to hear about it!