Data access in Object Extension

[jlpsant]

Hello!

I am using DOA's Object Extension with Delphi7 to manipulate objects returned by a select command in a TOracleQuery component.

After this component buffered some data, and while this data was being manipulated by the application, I noticed some network traffic.

Using a sniffer software, I saw that, for each object, every first reading made to any of its enbedded objects triggered an access to the database (querying for the structure of the object, I believe). This seems to happen when I use the TOracleObject.ObjAttr method.

I didn't expect this extra network traffic, because I thought that the entire object structure was sent once and only once to the application.

Am I doing something wrong (or anything right)?

This is a dummy example of the kind of thing I am trying to do:

CREATE TYPE Address_Typ AS OBJECT (
street VARCHAR2(30),
city VARCHAR2(30))
/
CREATE TYPE Address_Tab AS TABLE OF Address_Typ
/
CREATE TYPE Person_Typ AS OBJECT (
name VARCHAR2(30),
addresses Address_Tab)
/
-- just to provide some data...
CREATE TABLE persons (
name VARCHAR2(30),
street1 VARCHAR2(30),
city1 VARCHAR2(30),
street2 VARCHAR2(30),
city2 VARCHAR2(30))
/
BEGIN
FOR i IN 1..100 LOOP
INSERT INTO persons VALUES ('ABCDE','GHIJKL','MNOPQ','RSTU','VWXYZ');
END LOOP;
END;
/
COMMIT
/

In Delphi:

var
i: Integer;
Street: String;
begin
With TOracleQuery.Create(nil) do
try
Session := MySession;
SQL.Text := 'SELECT Person_Typ(name,Address_Tab(Address_Typ(street1,city1),Address_Typ(street2,city2))) AS person FROM persons';
Execute;
While not Eof do
begin
{the first execution of the following line, for each record, causes network traffic}
for i := 0 to ObjField('person').ObjAttr('addresses').ElementCount-1 do
Street := ObjField('person').ObjAttr('addresses').GetAttr('street');
Next;
end;
finally
Free;
end;
end;

Thanks in advance!

 

[Marco Kalter]

For a nested table you will indeed get an additional network access. The nested table records are not immediately fetched with the object instance.

 

[jlpsant]

Thanks for your answer!

Sorry to insist on it, but using a sniffer while debugging my application, I noticed something that appears to be a little different than this.

When the query was executed, all the data that compounded the object instance travelled through the network, including those for its embedded objects, nested tables etc. Some information about the object's structure seemed to be received, too.

Though, when using the ObjAttr method for each record, the additional traffic seemed to contain the nested table's structure, never its data. And every time the method was used, the structure was read again. (In my actual application, this also happens for some embedded objects that are not nested tables).

Now I tried this: instantiated a variable with TOracleObject.Create, and for each record returned by TOracleQuery used this variable's Assign method to copy the instance data. Using ObjAttr for this variable, and not for TOracleQuery.ObjField, the extra network access happens only for the first record read. So, it worked, but I didn't really understand how.

Obj := TOracleObject.Create(MySession,'Person_Typ','');
While not Eof do
begin
Obj.Assign(ObjField('person'));
{extra network access only for the first record}
for i := 0 to Obj.ObjAttr('addresses').ElementCount-1 do
Street := Obj.ObjAttr('addresses').GetAttr('street');
Next;
end;

In my actual application, I started then to create one TOracleObject variable for each kind of embedded object or nested table's objects that had their own embedded stuff, but this seemed not to be necessary for all of them (not all seemed to generate extra traffic). I got very confused, indeed.

Regards