Page 1 of 1

Problem with Oracle CLOB field

Posted: 23.07.2009, 08:06
by radvid
Hello!

I have a problem with a clob inside a query
SELECT id, clob_field, other_columns FROM table;

In all returned rows a value of clob field is the same, the value of the last row of a table. The rest of fields is properly changing.

If the same query is used for each record separately a value of the clob field is adequate.

Any suggestions?

My environment:
- Oracle 11.1.0.1.0
- ZEOS 6.6.4
- Delphi 7

Posted: 02.09.2009, 14:10
by ymarenne
Hello,

I have the same problem... but if I don't call the RecordCount property of the Query or if I call MyQuery.Refresh just after RecourdCout, the value of the Lob is correct.

My environment:
- Oracle 10.2.0.4
- ZEOS 6.6.5
- Delphi 2006

Posted: 02.09.2009, 23:04
by mdaems
Seems like recordcount sets some BLOB pointer to the pointer in the last result row.

I'm not using Oracle, so who's going to look into it and find where it goes wrong. I might be able to help, but please, oracle people, give me a pointer to the code I should look at.

Mark

Posted: 04.11.2010, 20:19
by josimarz
Hello,

I have the same problem.

I identified that the fields whose value is null don't reads the value of the last record.
I imagine the problem lies in defining the pointer. In ZDbcOracleResultSet unit, in TZOracleResultSet.GetBlob method.

I'm trying to identify the source of the problem. Any breakthrough will communicate.

Josimar.

Posted: 08.11.2010, 01:14
by mdaems
Some good news already? :bash:

Mark

Posted: 08.11.2010, 17:21
by josimarz
Hello!

I'm debugging and will be found some news.

In the unit ZDbcOracleResultSet, TZOracleResultSet.GetBlob mthod create the pointer to the CLOB field content:

Code: Select all

GetSQLVarHolder(ColumnIndex);
  CurrentVar := @FOutVars.Variables[ColumnIndex];
  if CurrentVar.TypeCode in [SQLT_BLOB, SQLT_CLOB] then
  begin
    if CurrentVar.Indicator >= 0 then
      LobLocator := PPOCIDescriptor(CurrentVar.Data)^
    else LobLocator := nil;

    Connection := GetStatement.GetConnection as IZOracleConnection;
    Result := TZOracleBlob.Create(FPlainDriver, nil, 0, Connection, LobLocator,
      CurrentVar.ColType);
  end
{...}
The LobLocator pointer value is always the same.

The TZOracleBlob.ReadBlob method, read the CLOB field value. When the content is NULL or empty, the value is read correctly.

I could not identify when the pointer value is set.

Any breakthrough will communicate.

Josimar.

Posted: 08.11.2010, 22:28
by josimarz
Good news!

I kicked a code that solved the problem.

Theoretically, this change seems to have no effect. But in practice it worked.

The change was in the ZDbcOracleResultSet unit, the GetBlob method of the TZOracleResultSet class. Soon after creating the object TZOracleBlob, run with a call to ReadBlob with typecast to IZOracleBlob:

Code: Select all

GetSQLVarHolder(ColumnIndex);
  CurrentVar := @FOutVars.Variables[ColumnIndex];
  if CurrentVar.TypeCode in [SQLT_BLOB, SQLT_CLOB] then
  begin
    if CurrentVar.Indicator >= 0 then
      LobLocator := PPOCIDescriptor(CurrentVar.Data)^
    else LobLocator := nil;

    Connection := GetStatement.GetConnection as IZOracleConnection;
    Result := TZOracleBlob.Create(FPlainDriver, nil, 0, Connection, LobLocator,
      CurrentVar.ColType);
    (Result as IZOracleBlob).ReadBlob; // MY CHANGE
      
  end
  else
  {...}
With this change the reading and writing blobs is occurring normally.

Josimar.

Posted: 10.11.2010, 22:15
by mdaems
Change done on Testing Branch (SVN rev. 845). Will move to 6.6-patches at next merge.

If you feel like writing a test case for the test suite, that would be nice. If not, I'll trust you anyway.

Thanks for debugging!!

Mark

Posted: 03.03.2011, 16:13
by ymarenne
Thanks for the patch

It works well on my intallation to

Yves