Hi Everybody:

  I am having a major issue with the error "Update Failed" when I try
modifying records using a TQuery within a transaction.  I am using
TUpdateSQL to do the updates.  Things work OK if I am not using a
transaction, which is very, very limiting.  Inserts work fine either
way.

  In order to work around this, I am using TTables which is enormously
slowing performance on Oracle. It is also more lines to code.

  Is this a bug in the BDE? Is there anyway to fix this or does anybody
know a workaround. Or am I just missing something. Anybody, please help
or post your experience!!

Gowri

Gowri
Ive just been through the same hair-pulling experience using Delphi 2.0
and
an Oracle database.

As far as I can remember the problem was in the OnUpdateRecord event of
the TQuery where I was setting up the update SQL command (within the
TUpdateSQL component) and then calling ExecSQL (as in page 174 of the
Database Application Developers Guide). The solution was to call the
Apply method rather than the ExecSQL. The Apply method resets the
parameters of the SQL statement while the ExecSQL just re-executes the
old statement.

Regards
Mike Fordyce
AMEC Process & Energy Ltd

I just don't know why you want to have a transaction running first. ;-)

Jonathan

Is it ok if you don't use cached updates??

-D

In article <338232F5.34DF3...@aspiresys.com>, go...@aspiresys.com says...

you can do something like this:

  database1.starttransaction;
  query1.open;
  query1.first;
  query1.fieldbyname('DOLLARE').asCurrency := strtofloat(edit1.text);
  query1.applyupdates;
  query1.close;
  database1.commit;

In order for this to work you have to do the following:

1. on your TQuery set property CachedUpdate to true
2. property UpdateSQL MUST point to an UpdateSQL component. You
   have to have an UpdateSQL component through which the updates
   are done.
3. you should not have an OnUpdateRecord handler in your TQuery.

I also used this with more than one query within the same transaction.

The only problem I have is that Delphi seems not to be able to
handle Oracle table columns defined as NUMBER(X) or NUMBER(X,Y).
If there is such a column all values will be rounded to integers!
It only works fine if the column is defined as NUMBER (without
size).

Any Help on this would be much appreciated.

Hermann

It actually turned out that the problem was with using CHAR(20) in
Oracle. I am using a key field as CHAR(20). Say, I have PAT_NUM = 'A-R',
when UpdateSQL tries to set it's parameters, it sets OLD_PAT_NUM as
'A-R' without providing all the buffer spaces after 'A-R' in order to
bring it to 20 Characters.

Since Oracle stores OLD_PAT_NUM as 'A-R                 ' with the
buffer spaces, obviously UpdateSQL couldn't find 'A-R' and the
update/delete failed. Insert succeeded since it was not trying to
reference anything from the database. I am now changing everything to
VARCHAR(2), as there does not seem to be a way to mess with OLD_PAT_NUM
(a read-only value).

Why I pointed to Transactions as the problem is just one of those
debugging things, where you think something else is wrong.....

Again, thanks for the help.

Gowri Subramanian
Aspire Systems, Inc.
http://www.aspiresys.com
CONSULTANTS FOR:  VB, PowerBuilder, Delphi, Oracle, Informix, Java,
Intranet and Client/Server

For god's sake, ALWAYS use TQueries vs. TTables in a C/S environment as with
Oracle.  If you could send me some code examples and a little more info on
what you are trying to do maybe I can help.  Please tell me more on what you
mean by performing 'transactions'...I have used Cached updates...etc. and have
not run into this....but, have had no problems.  Post here, but, ir e-mailing
me send it to my work address where I will get to it sooner.

kgr...@acxiom.com

Regards,

cayenne

-Mick and Keith: Some Girls  "I will walk before they make me run"

Come visit my website at: http://www.cei.net/~cayenne/index.html

  I am faced with the same problem as above ....... Just like a CHAR
from Oracle is treated like a VARCHAR(2), so is the NUMBER(X,Y).

 Does anybody know if there are any flags to set in SQLLinks (or BDE) to
prevent this from happening. Thanks.

Gowri