INSERT ERROR

ckwong · Post by **ckwong** » Tue 21 Jul 2009 20:19

I used 6.70.0.45 oracle data access componet in Delphi 2009.
When I insert record to a oracle table, I found "refresh failed, found 0 record"
what is the porblem and how to capture the error code from oracle ?

Challenger · Post by **Challenger** » Thu 23 Jul 2009 07:22

If roAfterInsert is inculded in RefreshOptions, ODAC refreshes inserted record in order to get field values that were filled as default values or in triggers. If this refresh select statement returns 0 records ODAC raises the specified exception. You can set the StrictUpdate option to False to avoid it. Or enable Debug mode to find the problem.

osimax · Post by **osimax** » Thu 23 Jul 2009 07:43

I hane similar problem. I've found the solution for it , but I'm not sure if things should behave in such a way and this error and solution depends on values of many other properties of TOraQuery

Below is detailed description of the problem and solution found. I've sent support request on July 21, still do not have reply. Here is copy of support request:
------------------------------------------------------------------------------------
I am using TOraSession and TOraQuery. AutoCommit - false; - for both.
for TOra Query:
CachedUpdates=true;
CheckMode=cmException;
DMLRefresh=True;
KeyFields=DIC_ID;
KeySequence=DICGEMS_SEQ;
LockMode=lmLockDelayed;
RefreshOptions.roAfterInsert=true;
RefreshOptions.roAfterUpdate=true;
SequenceMode=smPost;
SQL="select dg.*, dg.rowid from jew_dicgems dg"

When I perform inserting new record into the grid and then call "Session1.ApplyUpdates([OraQuery1]); I get following error "Refresh failed.Found 0 records".
In SQLMonitor I can see that following SQls are called :
to insert
INSERT INTO JEW_DICGEMS
(DIC_ID, NAME, MEAS_TYPE, SHORTNAME, DENSITY, UNIT_FOR_MASS_GROUP, UNIT_FOR_PRICE, DIC_GOG_ID) VALUES
(DICGEMS_SEQ.NEXTVAL, :NAME, :MEAS_TYPE, :SHORTNAME, :DENSITY, :UNIT_FOR_MASS_GROUP, :UNIT_FOR_PRICE, :DIC_GOG_ID) RETURNING
DIC_ID
INTO
:DIC_ID
NAME = '3323232'
MEAS_TYPE = '22'
SHORTNAME = '22'
DENSITY = 0
UNIT_FOR_MASS_GROUP = 0
UNIT_FOR_PRICE = 0
DIC_GOG_ID = 1
DIC_ID = 515

and

to refresh:

SELECT DG.DIC_ID AS "_0", DG.NAME AS "_1", DG.MEAS_TYPE AS "_2", DG.SHORTNAME AS "_3", DG.DENSITY AS "_4", DG.UNIT_FOR_MASS_GROUP AS "_5", DG.UNIT_FOR_PRICE AS "_6", DG.DIC_GOG_ID AS "_7" FROM JEW_DICGEMS DG WHERE
DIC_ID = :Old_DIC_ID
Old_DIC_ID =

so it makes refresh using OLD_DIC_ID which is null because new value is generated only during post.

If I change SequenceMode to smInsert it works ok , but generating new value in Insert is not convenient for my purposes.
If I change DMLRefresh to True, it also works ok, because refresh is performed through returning values. But this method does not looks commonly used (default value is False) etc.

Why refresh do not use ID=:ID in Refresh statement or why Refresh do not use rowid for Refresh (as in DOA)? Or do I missing something to work correctly without DMLRefresh=True; ?

ckwong · Post by **ckwong** » Thu 23 Jul 2009 14:35

Thanks all people to help.
I follow Mr Challenger instruction to solve the problem.

osimax · Post by **osimax** » Thu 23 Jul 2009 14:58

Please post your solution here when you get one.

I guess my huge text frighten you a bit

Anyway, try DMLRefresh=true