Refresh inserted row with pk column changed by trigger.

MarkF · Post by **MarkF** » Wed 17 Aug 2011 18:22

Hi all,

I have an issue where refreshing an inserted row where the pk was set with a trigger fails. It looks like it's trying to use the inserted value of the pk to find the record for the refresh which is empty (as it's supplied by the trigger.) Using the PK value of an existing record causes it to refresh the display as that record, hiding the values that I entered.

Is there a way to get inserted records to be based on rowid automatically so this doesn't happen? An older middleware tool that I used did that and it seemed to work flawlessly.

Btw, it works if I use ROWID in the query, but I'd prefer not to require that.

Thanks for any help or suggestions.

-Mark

AlexP · Post by **AlexP** » Thu 18 Aug 2011 14:23

Hello,

To solve this problem, you should use the RETURNING construction in the Insert script, for example:

Code: Select all

INSERT INTO TABLE
  (ID, TEXT, TIME)
VALUES
  (:ID, :TEXT, :TIME)
RETURNING ID INTO :ID

also you should set the OraQuery.Options.ReturnParams option to true.

MarkF · Post by **MarkF** » Thu 18 Aug 2011 16:38

Hi Alex,

I'm relying on the dataset's built in editing support. Shouldn't this work by default? Shouldn't it be using ROWID internally to refresh inserts where appropriate? Other tools seem to handle this without issues.

-Mark

AlexP · Post by **AlexP** » Fri 19 Aug 2011 08:01

Hello,

By default we use ROWID for record identication only in the TOraTable component.
To get the behaviour I have described (using the RETURNIG clause) by default you should use TSmartQuery instead of the TOraQuery component.

MarkF · Post by **MarkF** » Fri 19 Aug 2011 10:42

Hi Alex,

I am using TSmartQuery. What do I have to do to get it to use ROWID automatically and solve the issue? Thanks!

-Mark

AlexP · Post by **AlexP** » Fri 19 Aug 2011 13:51

Hello,

ROWID is used only in TOraTable, in Query you need to set the DMLRefresh option to True, so that after insert and update actual values from the table are returned to the client.

MarkF · Post by **MarkF** » Fri 19 Aug 2011 14:12

Hi Alex,

The last time I looked into using DMLRefresh it only worked with the basic datatypes and not all of the possible datatypes. Unless that has changed it's really only usable in specific simple situations. A better alternative is for the dataset to use ROWID internally such that calling refresh works properly in all cases. Since the insert statement is generated automatically, it should be doing something like RETURNING ROWID and keeping it internally (instead of using the old pk values which is of course the problem here.)

-Mark

AlexP · Post by **AlexP** » Thu 25 Aug 2011 15:16

Hello,

TOraQuery and TSmartQuery components are used for user queries. Adding and/or changing fields, etc., can cause errors or selecting incorrect data, so we cannot add the ROWID field to the queries. However you can add this field yourself if you dont want to use TOraTable. If you add the ROWID field to the query and assign it to the KeyFields property, the ROWID field will be used as the record identifier.

MarkF · Post by **MarkF** » Thu 15 Sep 2011 18:17

Hi Alex,

Thanks, I appreciate your time. I do understand about using ROWID in queries. Unfortunately it's a problem in programs that allow the user to enter the query themselves (this is compounded in my case because my older software correctly handled this case by using ROWID behind the scenes.)

Unfortunately the logic that ODAC uses to determine the current record after a change is flawed. It relies on the key values not changing which is a problem since triggers can change these values. Any situation where a user can accidentally modify the wrong record is pretty serious.

I also suspect (but haven't verified) that the current logic, even when using ROWID in the query, doesn't work if the ROWID changes during an insert (such as for partitioned tables with row movement enabled.) I agree, by the way, that this would be a rare issue, but it illustrates the problem with the way your dataset works currently.

So my request is really for someone to just consider how this currently works and if there could be a way to make things safer. I suspect that the solution is to use rowids internally either using the OCI implicit rowid fetching (which needs to be done in conjunction with locking) or using the "RETURNING ROWID" clause.

Thanks for listening!

-Mark

AlexP · Post by **AlexP** » Mon 19 Sep 2011 09:02

Hello,

You are the first user who asks for this feature, which may lead to performance decreasing. Thats why we are not going to implement this feature in near future. However, if you have the ODAC edition with source code, we may tell you how to change the code to get the behaviour you want.

MarkF · Post by **MarkF** » Mon 19 Sep 2011 12:12

Thanks Alex. I do have the source code and I'll appreciate any suggestions that you have to fix the problem.

-Mark