Blobs truncated to 256 bytes!

From: Jarvis N. Brand <J.Brand_at_dslcomms.co.uk>
Date: Tue, 04 Sep 2001 13:06:33 +0100
Message-ID: <3B94C3C9.C76D518E@dslcomms.co.uk>

and I thought a 4K limit was bad :-) !

I have an application which records fax images in a database via an ODBC connection. Using MySQL, Sybase and even MS-SQL this works but on using Oracle all blobs are truncated to 256 bytes, i.e.

  select messageid, dbms_lob.getlength(faxdata) from fax;

returns

  MESSAGEID DBMS_LOB.GETLENGTH(FAXDATA)

• --------------------------- 200109031808FAX2367 256 200109031808FAX2368 256 200109031808FAX2369 256 etc.

The same code is used to store this data on all databases, with some configuration changes to allow for non standard fields, the only change between the oracle implementation and the others is that FAXDATA is defined as BLOB (cf LONG VARBINARY in MySQL, and IMAGE in Sybase)

The DDL for the table is;
  CREATE TABLE FAX (MESSAGEID CHAR(21) NOT NULL,

                    IMAGESUFFIX INTEGER NOT NULL,
                    FAXPART INTEGER NOT NULL,
                    FAXDATA BLOB,
                    BACKEDUP1 INTEGER DEFAULT 0 NOT NULL, 
                    BACKEDUP2 INTEGER DEFAULT 0 NOT NULL,
                    PRIMARY KEY (MESSAGEID, IMAGESUFFIX, FAXPART));

The code saving the image is;
  { save fax image data for message }
  procedure TArchiveDataBase.SaveFaxData(Msg : THostMessage; ImageSuffix : Integer);
  var

    FieldList     : string;
    FaxPartStream : TMemoryStream;
    FaxPart       : Integer;

  begin

    FieldList := MessageIDFld + ',' +
                 ImgSuffixFld + ',' +
                 FaxPartFld + ',' +
                 FaxDataFld;

    FaxPart := 1;
    Msg.FaxData.Seek(0, soFromBeginning);     {.. build a TMemoryStream which is a copy of the fax image ..}     FaxPartStream := TMemoryStream.Create;     try

      FaxPartStream.CopyFrom(Msg.FaxData, Msg.FaxData.Size);
      { why does this truncate to 256 bytes? }
      with Query do
      begin
	{ Field2Param converts 'field1,field2' ... into ':field1,:field2' etc.
}
        SQL := 'INSERT INTO ' + ImgFaxTable +
               ' (' + FieldList + ')' +
               ' VALUES (' + Field2Param(FieldList) + ')';
        Prepare;
        BindStringByName(MessageIDFld, Msg.MessageID);
        BindIntegerByName(ImgSuffixFld, ImageSuffix);
        BindIntegerByName(FaxPartFld, FaxPart);
        BindMemoryByName(FaxDataFld, FaxPartStream, true);
        Execute;
      end;
    finally
      FaxPartStream.Free;

    end;
  end;   

As far as I can tell I shouldn't need anything more than this so can anyone suggest why the fax images are truncated?

PS all of this is under Oracle 8.1.7.

TIA Received on Tue Sep 04 2001 - 07:06:33 CDT