View containing 'rowid' does not work

[lectoc]

Having an Oracle view containing 'rowid' does not work when using TOracleDataset. The field simply does not appear in the dataset. However using a TOracleQuery, it works fine. Now, I can not just switch over using TOracleQuery.

Is this an error in DOA, or is there some setting somewhere, that I missed?

An example of a view containing this problem:

CREATE VIEW doa_view
(
ID
)
AS
SELECT rowid FROM sometable.....

What we had to do in this topical situation was to implement the following "hack" in the view:

CREATE VIEW doa_view
(
ID
)
AS
SELECT concat('1', rowid) FROM
sometable.....

This forces the dataset component to believe it is a string field. This works in our current situation.

Regards
Torben.

------------------
Torben M. Christensen

 

[MichaelB]

Torben,

The TOracleDataset component uses the ROWID internally to update the table when you post updates to a record. It hides the value from you (making the assumption, I suppose, that the only reason you included a ROWID element in your query was because the DOA docs said to ). You can access the ROWID value for the current record using the ROWID property for the dataset.

i.e.

myDataset := TOracleDataset.Create(Self);
myDataset.Session := mySession;
myDataset.SQL.Append('SELECT t.*, t.ROWID from myTable t');
myDataset.Open;
while not myDataset.eof do
begin
// vvvvvvvvvvvvvvv
Memo1.Lines.Append(myDataset.ROWID);
// ^^^^^^^^^^^^^^^
myDataset.next;
end;
myDataset.close;
FreeAndNil(myDataset);

It's a pain, but it works. This even works if the only element in your dataset is the ROWID. It looks like you don't have any fields in your dataset, but you do get records and the navigational commands (next, first, etc.) seem to work ok.

Hope it helps...Michael

 

[Marco Kalter]

The easiest way to include the rowid as a field, is to convert it to a varchar2 field by using the rowidtochar function:

CREATE VIEW doa_view AS SELECT rowidtochar(rowid) id FROM sometable.....

------------------
Marco Kalter
Allround Automations

 

[lectoc]

Hi Michael.

Thanks for your reply. Unfortunately I can not use this.

The reason we have a problem is that we are using a framework that demands a unique ID field on any table (or views). On this particular table we do not have a unique field called ID. Therefore we are using a view containing a field called ID. This field must be unique. Therefore we used rowid.

In other words - the field we must read from the dataset is named "ID", and it must be contained in the Fields of the dataset. We can not access some other property of the dataset (this would mean that we had to change our framework).

Regards
Torben.

 

[lectoc]

Hi Marco.

Thanks for your reply. You posted your reply just while I was replying Michael.

Seems that we can use your suggestion without any problems.

However I still find it strange that such a field from a view is not included in a TOracleDataset, but anyways - it works!

Thanks!

Regards
Torben.