Board index » delphi » Cached Updates TQuery Problem with Modify/Delete (within transaction)

Cached Updates TQuery Problem with Modify/Delete (within transaction)

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

 

Re:Cached Updates TQuery Problem with Modify/Delete (within transaction)


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

Quote
Gowri Subramanian wrote:
> 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

Re:Cached Updates TQuery Problem with Modify/Delete (within transaction)


Quote
Gowri Subramanian wrote:

> 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.

TUpdateSQL creates its own transaction when you say
"DataBase1.ApplyUpdates([Query1])". Cached updates are updates applied
locally to your hard drive until you apply them. If a lot of updates
occur on the table you are caching then contention could be a problem.
Otherwise cached updates are cool!

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

Jonathan

Re:Cached Updates TQuery Problem with Modify/Delete (within transaction)


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

-D

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

Quote

>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

--
Dave Krajcar, Alternative Solutions
rai...@pacifier.com

Re:Cached Updates TQuery Problem with Modify/Delete (within transaction)


Quote
Gowri Subramanian wrote:

> 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

Hi Gowri,

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

Re:Cached Updates TQuery Problem with Modify/Delete (within transaction)


Quote
Gowri Subramanian wrote:

> 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

Thanks to everybody who tried to help me out on this....

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

Re:Cached Updates TQuery Problem with Modify/Delete (within transaction)


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

Quote
In article <338232F5.34DF3...@aspiresys.com>, go...@aspiresys.com wrote:
>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

'Gonna find my way to heaven...
..'cause I did my time in hell...
        Wasn't looking too good, but I was feeling really well!!"

-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

Re:Cached Updates TQuery Problem with Modify/Delete (within transaction)


Quote
Hermann Ragaller wrote:

> 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

Hi Hermann:

  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

Other Threads