blobs truncated to 256 bytes

From: Jarvis N. Brand <J.Brand_at_dslcomms.co.uk>
Date: Tue, 04 Sep 2001 12:30:07 GMT
Message-ID: <3b94c71c.13746075@news.clara.net>

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

I have an application whihc saves fax images to a generic database via

ODBC, Using MySQL, Sybase or even MS-SQL as the database this works but when I use Oracle all the fax images are truncated at 256 bytes. The same code is used to save the image in all versions with a change in the table definitions to accomodate any non standard fields. In this case this means defining the fax data as a Blob (cf LONG VARBINARY in MySQL and IMAGE in Sybase)

i.e.
  SQL> select messageid, dbms_lob.getlength(faxdata) from fax; returns
  MESSAGEID DBMS_LOB.GETLENGTH(FAXDATA)

• --------------------------- 200109041309FAX2379 256 200109041309FAX5566 256 200109041309FAX2380 256 200109041309FAX5567 256 etc.

The table defintion 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 which saves the fax 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 shouldn't need to set anything else and, as the code works under three other databases, I'm reasonably confident that is OK. So can anyone suggest what else I should be looking at?

PS all of this is under Oracle 8.1.7 with the standard 8.1.7 ODBC drivers accessed from a Delphi 3 application or SQLPLUS running under NT4 (just in case that makes a difference)

TIA Received on Tue Sep 04 2001 - 07:30:07 CDT