LOB-Bug in DOA 3.4.6.4 for D7

[Malte Schnack]

Hi,

we experience a strange behaviour in TOracleDataSet in DOA 3.4.6.4 for D7:

When posting the DataSet, a BLOB-Field supposed to be NULL is transferred to ORacle as empty string (''). This affects some query throughout our CMS programm very negatively.

Is this bug already known and been adressed to?

Thanks for your help in advance.

Kind regards,
Malte Schnack

 

[Marco Kalter]

I tried this, and it works just fine:

1. When inserting a record with a null BLOB, the column is omitted from the insert statement.

2. When updating a record with a null BLOB, either a NULL or EMPTY_BLOB() expression is used in the update statement, depending on the value of TOracleSession.Preferences.NullLOBIsEmpty.

Are you updating or inserting?

 

[igorilia]

I get error if I safe data into BLOB field and TOracleSession.ThreadSafe = True.
Oracle 8.1.7.4, DOA 3.4.6.4
Parallel option in Oracle does not set.

So I change it to False - All works. I suspect, that so Oracle works.

Here is code extract :

osess := TOracleSession.Create(nil);
try
osess.ThreadSafe := False; !!!!
// if osess.ThreadSafe := True, you later get error !!!

osess.LogonDatabase := ....
osess.LogonUsername := ....
osess.LogonPassword := ....
osess.Connected := True;
with TOracleQuery.Create(nil) do begin
try
Session := osess;
SQL.Add('BEGIN');
SQL.Add(' UPDATE IMAGE_0 SET TP = :TP WHERE CD_F = :CD_F AND NUM_L = :NUM_L;');
SQL.Add(' IF SQL%ROWCOUNT = 0 THEN');
SQL.Add(' INSERT INTO IMAGE_0(CD_F, NUM_L, TP) VALUESCD_F, :NUM_L, :TP);');
SQL.Add(' END IF;');
SQL.Add('END;');
DeclareVariable('CD_F', otInteger);
DeclareVariable('NUM_L', otInteger);
DeclareVariable('TP', otInteger);
SetVariable('CD_F', cd_f);
SetVariable('NUM_L', num_l);
SetVariable('TP', p_tp); // 1 - PCL_BMP, 0 - Undefined
Execute;
Clear;
DeleteVariable('TP');
LOB := TLOBLocator.Create(osess, otBLOB);
try
SQL.Add('UPDATE IMAGE_0 SET IMAGE = Empty_blob()');
SQL.Add('WHERE CD_F = :CD_F AND NUM_L = :NUM_L');
SQL.Add(' RETURNING IMAGE INTO :IMAGE');
DeclareVariable('IMAGE', otBlob);
MyStream.Seek(0,0);
SetLongVariable('IMAGE', MyStream.Memory, MyStream.Size);
SetComplexVariable('IMAGE', LOB);
Execute;
MyStream.Seek(0,0);
MyStream.SaveToStream(LOB);
Session.Commit; // you get here error, if ThreadSafe = True !!
finally
LOB.Free;
except
end;
finally
Free;
end;

 

[Marco Kalter]

What exactly is the error that you get?

 

[igorilia]

Originally posted by Marco Kalter:
What exactly is the error that you get?

Error was :
ORA-22990 : LOB locators cannot span transactions

 

[al0]

Originally posted by igorilia:

Originally posted by Marco Kalter:
What exactly is the error that you get?

Error was :
ORA-22990 : LOB locators cannot span transactions

Are you sure that AutoCommit property of your session is set to false? Otherwise each statement in your program is separate transaction and Oracle is absolutely correct.

 

[igorilia]

Yes, AutoCommit was True, but I thougth that ThreadSafe was responsible for this.