Updating LOB field to null

[stunt]

Just upgraded komponents from ODAC 3.90 net to 5.70 net.
First step first problem:

Here is a DB monitor text:
Odac 3.90 Net:

UPDATE USERREP
SET
OPTIONS = EMPTY_BLOB(),
CHILD_TEXT = EMPTY_BLOB()
WHERE
USERREPID = :OLD_USERREPID
RETURNING
OPTIONS, CHILD_TEXT
INTO
:3, :8
:OLD_USERREPID(FLOAT,IN)=6
:3(BLOB,IN)=
:8(BLOB,IN)=

Odac 5.70 Net:

UPDATE USERREP
SET
OPTIONS = EMPTY_BLOB(),
CHILD_TEXT = NULL
WHERE
USERREPID = :OLD_1
RETURNING
OPTIONS, CHILD_TEXT
INTO
:3, :8
:OLD_1(FLOAT,IN)=6
:3(BLOB,IN)=
:8(BLOB,IN)=

Because of "CHILD_TEXT = NULL" (only one diference found) it fails with message:

ORA-22275 Invalid LOB locator specified
ORA-06512 at "SYS.DBMS_LOB", line 753
ORA-06512 at line 1

---------------------------
Is any option missed? Is this a bug? Help as soon as posible...

[Plash]

We cannot reproduce the error. Please specify versions of Oracle server and client you use and also Delphi version, whether you use Net option to connect to database.
If it is possible send to ODAC support address complete sample that demonstrates the problem and include script to create server objects.

[Petriukx]

Ai allready send demo project with this problems twice. First time I get answer that they cant reproduce it, second time still don't get answer.
This bug apears with the newest Odac Net version (downloaded yesterday and in any case even today).
Oracle version 9.2 windows
Delphi 5

Table SQL:
CREATE TABLE USERREP
( USERREPID NUMBER(10,0) NOT NULL ENABLE,
CODE VARCHAR2(150),
OPTIONS BLOB,
AUTO_AUKSTIS NUMBER(10,0),
CHARSET_FROM_FORM NUMBER(10,0),
EILNRPLOTIS NUMBER(10,0),
ADD_CHILD NUMBER(10,0),
CHILD_TEXT BLOB,
CONSTRAINT PK_USERREP PRIMARY KEY (USERREPID) ENABLE
)

Delphi source
{*.pas}

Code: Select all

type Form1 = class(form)
    OraSession1: TOraSession;
    SmartQuery1USERREPID: TFloatField;
    SmartQuery1CODE: TStringField;
    SmartQuery1OPTIONS: TBlobField;
    SmartQuery1AUTO_AUKSTIS: TFloatField;
    SmartQuery1CHARSET_FROM_FORM: TFloatField;
    SmartQuery1EILNRPLOTIS: TFloatField;
    SmartQuery1ADD_CHILD: TFloatField;
    SmartQuery1CHILD_TEXT: TBlobField;
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
end;



procedure TForm1.Button1Click(Sender: TObject);
begin
  SmartQuery1.SQL.Text:='SELECT * FROM USERREP';
  SmartQuery1.Open;
  if SmartQuery1.IsEmpty then
    begin
      SmartQuery1.Insert;
      SmartQuery1.FieldByName('UserRepID').AsInteger:=1;
      SmartQuery1.Post;
      Button1.click;
    end;
  if not (SmartQuery1.State in [dsInsert,dsEdit])
    then SmartQuery1.Edit;
  SmartQuery1.FieldByName('OPTIONS').AsString:='First time set value';
  SmartQuery1.FieldByName('CHILD_TEXT').AsString:='First time set value';
  SmartQuery1.Post;
  if not (SmartQuery1.State in [dsInsert,dsEdit])
    then SmartQuery1.Edit;
  SmartQuery1.FieldByName('OPTIONS').AsString:='2nd time set value';
  SmartQuery1.FieldByName('CHILD_TEXT').Value:=null;
  SmartQuery1.Post;
end;

//---------------------------------------//

So problem acure only after 2 blob fields was filled
and after post again updated one of the fields to null
on post it raise error.

Is any ideas how to solve this situation, or nobody get this message?..

[Petriukx]

I founded work around in this case, it doesn't tested yet but where it crashed now it works. Maby I lost functionality but problem temprorary solved.

Hope in the future get this bug fixed or adisional option added if still can't reproduce error.

So how I do it:

Overided two procedures:

Code: Select all

function TIBOraQuery.AssignedBeforeUpdateExecute:boolean; 
begin
//  inherited; // if not assigned then next procedure will not executs
  Result:=true;
end;

Code: Select all

procedure TIBOraQuery.DoBeforeUpdateExecute(Sender: TDataSet; StatementTypes: DBAccess.TStatementTypes; Params: TDAParams);
var i:integer;
begin
  if (DBAccess.stUpdate in StatementTypes) then
    if (Pos('RETURNING', UpperCase(FUpdateQuery.SQL.Text))>0) then //checking is any lob updateing
      begin
        for i:=0 to Sender.FieldCount-1 do
          begin
            if Sender.Fields[i].IsBlob then
              begin
//replacing SQL (this sometimes can replace incorect not in my case)  
//from "BLOB_FIELD = NULL" to "BLOB_FIELD = EMPTY_BLOB()" 
//because of version (3.90 and 5.70.1.33 ) diference compare  
//finded sql that works
                FUpdateQuery.SQL.Text:=StringReplace(FUpdateQuery.SQL.Text,UpperCase(Sender.Fields[i].FieldName+' = NULL'),UpperCase(Sender.Fields[i].FieldName+' = EMPTY_BLOB()'),[rfIgnoreCase,rfReplaceAll]);
              end;
          end;
       end;
//I gues AssignedBeforeUpdateExecute function did this... 
  if Assigned(BeforeUpdateExecute) then 
    inherited;
end;

So if components developers cant found this error please add addisional option or property that makes components work like in 3.90 version o somthing like that.

Good day for all

[Plash]

We are working on correcting this error. We will let you know when the bug is fixed.