Error: Object for field [EnteryourCLOBfieldhere] = nil

[JoaoMarcos]

Hi,

I got this error below after trying to edit a record.

I'm using Direct mode this time, an d one new setting was the RefreshOptions := [roBeforeEdit]

---------------------------
GSSISCOSERV
---------------------------
Object for field INFOCOMPLEMENTAR(17) = nil (D:\Projects\Delphi\Dac\Common\Source\MemData.pas, line 5431)
---------------------------
OK
---------------------------

[AlexP]

Hello,

Please provide the script for creating the table, your SQL query text, and the code fragment where the problem occurs.

[JoaoMarcos]

Hi Alex,

Here's the requested:

Code: Select all

-- Create table
create table TABLESIS
(
  filial               VARCHAR2(3) not null,
  registro             VARCHAR2(20) not null,
  modulo               CHAR(1) not null,
  moeda                VARCHAR2(3) not null,
  status               VARCHAR2(10),
  dataregistro         DATE,
  dataretificado       DATE,
  cnpj_fil             VARCHAR2(14),
  nome_fil             VARCHAR2(80),
  pais                 VARCHAR2(3),
  pessoaext            VARCHAR2(20),
  nomepessoaext        VARCHAR2(150),
  endpessoaext         VARCHAR2(150),
  nif                  VARCHAR2(40),
  exportacao           CHAR(1),
  importacao           CHAR(1),
  infocomplementar     CLOB,
  old_pais             VARCHAR2(3),
  old_nomepessoaext    VARCHAR2(150),
  old_endpessoaext     VARCHAR2(150),
  old_nif              VARCHAR2(40),
  old_infocomplementar CLOB,
  old_moeda            VARCHAR2(3),
  numlote              NUMBER(5),
  numloteretificacao   NUMBER(7),
  operadorincl         VARCHAR2(20),
  manutencaoincl       DATE,
  operador             VARCHAR2(20),
  manutencao           DATE,
  cliente              VARCHAR2(14),
  filialcliente        VARCHAR2(1),
  docto                VARCHAR2(44),
  liberadofat          VARCHAR2(1),
  liberadoregistro     VARCHAR2(1),
  liberadoretifica     VARCHAR2(1),
  dataliberadofat      DATE,
  dataliberadoregistro DATE,
  dataliberadoretifica DATE,
  diretorio            VARCHAR2(255),
  numlotecarga         NUMBER(7),
  operadorlibreg       VARCHAR2(20),
  operadorlibret       VARCHAR2(20),
  protocolo            VARCHAR2(100),
  numerorvsras         VARCHAR2(20),
  dataconferido        DATE,
  conferente           VARCHAR2(5),
  protocoloret         VARCHAR2(100),
  dataconferidoret     DATE,
  conferenteret        VARCHAR2(5),
  retificado           VARCHAR2(1),
  datasolicitacao      DATE,
  solicitante          VARCHAR2(20),
  refcliente           VARCHAR2(40),
  ordemservico         NUMBER(10),
  codempresa           VARCHAR2(20) not null
)
tablespace DBF01
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 5M
    next 5M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TABLESIS
  add constraint TABLESIS_PK primary key (CODEMPRESA, FILIAL, REGISTRO, MODULO)
  using index 
  tablespace DBF01
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 5M
    next 5M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );

The fragment of code where the problem occurs, I don't know how to get because it's when AutoEdit on dataset execute. The error then occurs when I try to edit data directly on any data-aware component (unchecking a DBCheckBox, for example).

For the table above, I'm using a TSmartQuery component with RefreshOptons set to [roBeforeEdit]. Setting RefreshOptions to [], works normally.

[JoaoMarcos]

and the SQL text:

Code: Select all

select a.*, b.descricao descricaostatus, a.rowid 
from  tablesis a, servstatus b
where
a.codempresa = :codempresa 
and a.modulo = 'V'
and b.status = a.status
and ((a.filial in (select filial from operadorperfil where codempresa = :codempresa and operador = :operador))
     or (nvl(:administrador,'N') = 'S'))
order by a.registro

In FieldsList only one of the CLOB fields was added (the one without the leading OLD in name).

[AlexP]

Hello,

You haven't provided the script for creating the servstatus table.

[JoaoMarcos]

Sorry here's the script

Code: Select all

-- Create table
create table SERVSTATUS
(
  status    VARCHAR2(2) not null,
  descricao VARCHAR2(50)
)
tablespace DBF01
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 5M
    next 5M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table SERVSTATUS
  add constraint SERVSTATUS_PK primary key (STATUS)
  using index 
  tablespace DBF01
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 5M
    next 5M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Grant/Revoke object privileges 
grant select, insert, update, delete, alter on SERVSTATUS to IMPMANAGER;
grant select, insert, update, delete, alter on SERVSTATUS to IMPSELECT;

[AlexP]

Hello,

You haven't provided the script for creating the operadorperfil table.

[JoaoMarcos]

I guess that's the last one:

Code: Select all

-- Create table
create table OPERADORPERFIL
(
  operador   VARCHAR2(20) not null,
  sequencia  NUMBER,
  filial     VARCHAR2(3) not null,
  codempresa VARCHAR2(20) not null
)
tablespace DBF01
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 5M
    next 5M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate indexes 
create index OPERADORPERFIL on OPERADORPERFIL (OPERADOR)
  tablespace DBF01
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 5M
    next 5M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table OPERADORPERFIL
  add constraint OPERADORPERFIL_PK primary key (CODEMPRESA, OPERADOR, FILIAL)
  using index 
  tablespace DBF01
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 5M
    next 5M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
alter table OPERADORPERFIL
  add constraint R_143 foreign key (CODEMPRESA)
  references EMPRESA (CODEMPRESA);

[AlexP]

Hello,

Thank you for the information, we have reproduced the problem and will try to fix it in the nearest future.

[JoaoMarcos]

Thank you for the help!!!

[cis-wurzen]

Is there an ETA for the fix?

Do you already have a fix and if yes, can you provide the patched source code by mail?

My test case is the following:

SQL for the test table

Code: Select all

CREATE TABLE ODACTESTROBEFOREEDIT (
  ID        INTEGER,
  TEXT      VARCHAR2(10),
  LONGTEXT  CLOB
);

ALTER TABLE ODACTESTROBEFOREEDIT ADD CONSTRAINT PK_ODACTESTROBEFOREEDIT PRIMARY KEY (ID);

INSERT INTO ODACTESTROBEFOREEDIT VALUES(1, '10', 'CLOB');

Delphi console application

Code: Select all

program ODACEditCLOBWithroBeforeEdit;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  DB,
  Ora,
  OraSmart,
  DBAccess;

{
CREATE TABLE ODACTESTROBEFOREEDIT (
  ID        INTEGER,
  TEXT      VARCHAR2(10),
  LONGTEXT  CLOB
);

ALTER TABLE ODACTESTROBEFOREEDIT ADD CONSTRAINT PK_ODACTESTROBEFOREEDIT PRIMARY KEY (ID);

INSERT INTO ODACTESTROBEFOREEDIT VALUES(1, '10', 'CLOB');
}

const
  cServer = 'YourServer';
  cUsername = 'YourUser';
  cPassword = 'YourPassword';

function TestEditCLOBWithroBeforeEdit: Boolean;
var
  se: TOraSession;
  Counter: Integer;

  procedure UpdateTEXTField(AID: Integer; AText: string);
  var
    qr: TOraQuery;
  begin
    qr := TOraQuery.Create(nil);
    try
      with qr do
      begin
        AutoCommit := True;
        SQL.Add('UPDATE ODACTESTROBEFOREEDIT SET TEXT = :TEXT WHERE ID = :ID');
        ParamByName('ID').AsInteger := AID;
        ParamByName('TEXT').AsString := AText;
        ExecSQL;
      end;
    finally
      qr.Free;
    end;
  end;

  procedure CheckFieldValue(ATest: Integer; AField: TField; AExpectedValue: string);
  var
    ActualValue: string;
  begin
    ActualValue := AField.AsString;
    if ActualValue = AExpectedValue then
      Inc(Counter)
    else
      WriteLn('FAIL #', ATest, ' Expected: ', AExpectedValue, ' Actual: ', ActualValue);
  end;

var
  qr: TSmartQuery;
  ID: Integer;
  F: TField;
begin
  Counter := 0;
  se := TOraSession.Create(nil);
  qr := TSmartQuery.Create(nil);
  try
    se.Server := cServer;
    se.Username := cUserName;
    se.Password := cPassword;
    with qr do
    begin
      Session := se;
      KeyFields := 'ID';
      RefreshOptions := RefreshOptions + [roBeforeEdit];
      SQL.Add('SELECT * FROM ODACTESTROBEFOREEDIT');

      Open;
      ID := FieldByName('ID').AsInteger;
      UpdateTEXTField(ID, '10');
      Close;

      Open;
      F := FieldByName('TEXT');

      CheckFieldValue(1, F, '10');

      UpdateTEXTField(ID, '11');
      Edit;
      CheckFieldValue(2, F, '11');

      F.AsString := '12';
      Post;
      CheckFieldValue(3, F, '12');

      Close;
    end;
  finally
    se.Free;
  end;
  Result := Counter = 3;
end;

begin
  try
    if TestEditCLOBWithroBeforeEdit then
      WriteLn('PASS')
    else
      WriteLn('FAIL');
  except
    on E: Exception do
    begin
      WriteLn('FAIL - Exception Error');
      WriteLn('  E.ClassName = ', E.ClassName);
      WriteLn('    E.Message = ', E.Message);
    end;
  end;
  ReadLn;
end.

Steps:
- execute the script
- create a new console application in Delphi and paste the code
- adjust the constants cServer, cUserName and cPassword
- make sure Assertions are enabled in the Debugging Options
- compile and run the example

expected: output is PASS
actual: output is
FAIL - Exception Error
E.ClassName = EAssertionFailed
E.Message = Object for field LONGTEXT(2) = nil (D:\Projects\Delphi\Dac\Common\Source\MemData.pas, line 5561)

[AlexP]

Hello,

We haven't fixed this problem yet; it will be fixed in the next build.

[cis-wurzen]

That is a blocking issue for us.

Do you have any ETA for the next build?

Do you have fixed that issue meanwhile and can provide the patched source files?

[AlexP]

Hello,

We have already fixed the problem, the fix will be included to the next version of ODAC, that will be released this week

[jfudickar]

Hi Alex,

will it be a christmas present

Regards
Jens