Error : Invalid BLOB ID

[zifou1958]

I have a trigger on an articles_ft table that contains several blobs:

CREATE OR ALTER TRIGGER ARTICLE_FT_BEFORE_INSERT_UPDATE
ACTIVE BEFORE INSERT OR UPDATE ON ARTICLE_FT POSITION 0
EXTERNAL NAME 'udrbouz!article_ft' ENGINE UDR;

The udr function is written in Pascal. Its role is to fill a small blob (< 64Kb) in the article_ft table.
Everything works on the udr side.
And my table is updated correctly.

CREATE TABLE ARTICLE_FT (
    CODEART  INTEGER NOT NULL,
    FT0      SMALLBLOB /* SMALLBLOB = BLOB SUB_TYPE BINARY SEGMENT SIZE 80 */,
    FT1      SMALLBLOB /* SMALLBLOB = BLOB SUB_TYPE BINARY SEGMENT SIZE 80 */,
    FT2      SMALLBLOB /* SMALLBLOB = BLOB SUB_TYPE BINARY SEGMENT SIZE 80 */,
    FT3      SMALLBLOB /* SMALLBLOB = BLOB SUB_TYPE BINARY SEGMENT SIZE 80 */,
    FT4      SMALLBLOB /* SMALLBLOB = BLOB SUB_TYPE BINARY SEGMENT SIZE 80 */,
    FT5      SMALLBLOB /* SMALLBLOB = BLOB SUB_TYPE BINARY SEGMENT SIZE 80 */,
    FT6      SMALLBLOB /* SMALLBLOB = BLOB SUB_TYPE BINARY SEGMENT SIZE 80 */,
    FT7      SMALLBLOB /* SMALLBLOB = BLOB SUB_TYPE BINARY SEGMENT SIZE 80 */
);
ALTER TABLE ARTICLE_FT ADD CONSTRAINT ARTICLE_FT PRIMARY KEY (CODEART);

insert into ARTICLE_FT(codeart) select code from article where octet_length(fichetechnique) > 0;

After committing the last query,
I execute:

select A.codeart, octet_length(A.FT0) from article_ft a where octet_length(A.FT0) > 0

I get the error: Invalid blob id

with the following query:

select BLOBIDBIN(A.FT0) from ARTICLE_FT A rows 2

I get the following results:

you'll notice that the blob id begins with zero, which means the stored blob id is temporary.

[aafemt]

If you get this error on temporary BLOB, most likely you try to open it in a different transaction.

[zifou1958]

I am using Firebird version 5.0.3.1693

[zifou1958]

My trigger permanently saves the bob in the article_ft table.
And therefore, it's normal to read the table in a new transaction.

[aafemt]

Permanent BLOBs have permanent BLOB IDs. If you see temporary IDs then something is wrong with your application or trigger. And even for permanent BLOBs it is a requirement to open them in the same transactions that received the ID because stability of BLOBs between transactions is not guaranteed in any Firebird version.

[zifou1958]

Here are the functions I use to save the blob:

function TIBBlob.WriteBlobFromStream(AStream: TStream): ISC_QUAD;
const
  bpb: array [0 .. 6] of byte = (isc_bpb_version1, isc_bpb_type, 1, isc_bpb_type_segmented, isc_bpb_storage, 1, isc_bpb_storage_main);
var
  Blob: Firebird.IBlob;
begin
  Blob := FDataBase.CreateBlob(FIStatus, FTransaction, @Result, sizeof(bpb), @bpb);
  try
    Blob.WriteBlobFromStream(FIStatus, AStream);
    Blob.Close(FIStatus);
    Blob := nil;
  finally
    if Blob <> nil then
      Blob.release;
  end;
end;

procedure TFbBlobHelper.WriteBlobFromStream(AStatus: IStatus; AStream: TStream);
var
  xStreamSize: integer;
  xReadLength: integer;
  buf: PByte;
begin
  If AStream is TBytesStream then
    WriteBlobFromBytesStream(AStatus, TBytesStream(AStream))
  else
  begin
    xStreamSize := MAX_SEGMENT_SIZE;
    buf := GetMemory(MAX_SEGMENT_SIZE);
    try
      while True do
      begin
        xReadLength := AStream.read(buf^, xStreamSize);
        if xReadLength > 0 then
          Self.putSegment(AStatus, xReadLength, buf)
        else
          break;
      end;
    finally
      FreeMem(buf);
    end;
  end;
end;

[zifou1958]

On the application side, when I write my blob to a table, I initially get an id with gds_quad_high = 0, which means that in this transaction it is considered temporary. However, after a commit, its id changes to a value <> 0.

[zifou1958]

This isn't the case on the UDR side, where the ID with gds_quad_high=0 remains even after commit.
I don't know how to make it permanent.

[hvlad]

On the application side, when I write my blob to a table, I initially get an id with gds_quad_high = 0, which means that in this transaction it is considered temporary. However, after a commit, its id changes to a value <> 0.

Engine materialize (make permanent) blob when it is assigned to the some table, i.e. when blob id of new blob (temporary of course) is assigned to the some field in INSERT or UPDATE statement. When later you SELECT that blob field value, engine returns permanent blob id.

This isn't the case on the UDR side, where the ID with gds_quad_high=0 remains even after commit. I don't know how to make it permanent.

So, how do you assign blob id in your trigger ?

[hvlad]

So, how do you assign blob id in your trigger ?

I mean - how do you assign id of new blob into the NEW message in external trigger ?

[zifou1958]

Indeed I solved my problem with a trigger as follows:

create or alter trigger ARTICLE_FT_BEFORE_INSERT_UPDATE for ARTICLE_FT
active before insert or update position 0
as
begin
  new.FT0 = FILL_ART_FT_BLOB(new.CODEART);
end

I use a udr function as follows:

create or alter function FILL_ART_FT_BLOB (
    ACODEART integer)
returns SMALLBLOB
external NAME 'udrbouz!splitblob' engine UDR  

my problem is solved.
Thank you very much for your help

[zifou1958]

I mean - how do you assign id of new blob into the NEW message in external trigger ?

Here is a summary implementation of my trigger in Pascal.

TBouzTrigger = class(IExternalTriggerImpl)
  private
    procedure BindInputOutput(Astatus: IStatus; Ametadata: IRoutineMetadata);
  protected
    FProcNeedAttchment: TprocNeedAttachment;
    FAttachment: IAttachment;
    FTransaction: ITransaction;
    FOld, FNew: TIBXSQLDA;
    FTrgTable: string;
    FTrgType: TTriggerType;
    FUserName: string;
  public
    constructor Create;
    procedure dispose(); override;
    procedure getCharSet(status: IStatus; context: IExternalContext; name: PAnsiChar; nameSize: Cardinal); override;
    procedure execute(status: IStatus; context: IExternalContext; action: Cardinal; oldMsg: Pointer; newMsg: Pointer); override;
    procedure excTrigger(Astatus: IStatus; action: TTrigerAction; Acontext: IExternalContext); virtual;
  end;

procedure TBouzTrigger.execute(status: IStatus; context: IExternalContext; action: Cardinal; oldMsg, newMsg: Pointer);
begin
  try
    FAttachment := context.getAttachment(status);
    FTransaction := context.getTransaction(status);
    FUserName := String(context.getUserName());
    if FOld <> nil then
      FOld.LinkMessageBuffer(oldMsg);
    if FNew <> nil then
      FNew.LinkMessageBuffer(newMsg);
    excTrigger(status, TTrigerAction(action), context);
  except
    on e: Exception do
      FbException.catchException(status, e);
  end;
end;

procedure TBouzTriggerArticlleFt.splitBuffer;
Const
  MAX_BYTE_LEN = 65534;
var
  I, L, ipos: Integer;
  CompressedBuf: TMemoryStream;
  size: Integer;
begin
  CompressedBuf := TMemoryStream.create();
  try
    ZCompressStream(FBuffer, CompressedBuf);
    FreeAndNil(FBuffer);
    L := CompressedBuf.size;
    if L > 0 then
    begin
      CompressedBuf.Position := 0;
      ipos := 0;
      for I := 1 To FNew.Count - 1 do
      begin
        size := min(L, MAX_BYTE_LEN);
        if FNew[I].SQLType = SQL_BLOB then
        begin
          FNew[I].WriteBlobFromStream(CompressedBuf, size);
          Dec(L, size);
          ipos := I;
          if L <= 0 then
            break;
        end;
      end;
      for I := ipos + 1 to FNew.Count - 1 do
        FNew[I].IsNull := True;
    end;
  finally
    CompressedBuf.free;
    FreeAndNil(FBuffer);
  end;
end;

procedure TIBXSQLVAR.WriteBlobFromStream(AStream: TStream; Alen: integer);
var
  bs: TIBBlob;
  FAttachment: IAttachment;
  FTransaction: ITransaction;
begin
  if (Alen <= 0) or (AStream.size - AStream.Position <= 0) then
  begin
    IsNull := True;
    exit;
  end;
  if Assigned(FParent.FProcNeedAttchment) then
  begin
    FParent.FProcNeedAttchment(FAttachment, FTransaction);
    bs := TIBBlob.Create(FAttachment, FTransaction);
    try
      AsQuad := bs.WriteBlobFromStream(AStream, Alen);
    finally
      bs.free;
    end;
  end
  else
    IBError('Missing Attachment', '', '');
end;

function TIBBlob.WriteBlobFromStream(AStream: TStream; Alen: integer): ISC_QUAD;
const
  bpb: array [0 .. 6] of byte = (isc_bpb_version1, isc_bpb_type, 1, isc_bpb_type_segmented, isc_bpb_storage, 1, isc_bpb_storage_main);
var
  Blob: Firebird.IBlob;
begin
  Blob := FDataBase.CreateBlob(FIStatus, FTransaction, @Result, sizeof(bpb), @bpb);
  try
    Blob.WriteBlobFromStream(FIStatus, AStream, Alen);
    Blob.Close(FIStatus);
    Blob := nil;
  finally
    if Blob <> nil then
      Blob.release;
  end;
end;

[mrotteveel]

I'm going to move this to discussions.