Solved

Delphi Error Trap For Failed Stored Procedure

Posted on 2014-04-04
4
535 Views
Last Modified: 2014-04-16
I have a Delphi 2006 application which calls several ADO Stored Procedures.
My problem is that the SP returns a Transaction Deadlock every now and then and If have implemented the standard Delphi error trapping but it does not seem to work ?

Is there another way of catching these errors so my application does not crash ?

        Memo1.Text:=Memo1.Text+'Start Recalc'+FormatDateTime('HH:MM:ss',Time)+#13+#10;
        Brk:=1;
        while Brk=1 do
           try
             Try
             ADOCAN.ExecProc;
           except on E:Exception do
              Begin
                Memo1.Text:=Memo1.Text+'ADOCan Error';
                Sleep(10000);
              End;
             End;
           finally
             Brk:=2;

        end;
        Memo1.Text:=Memo1.Text+'End Recalc'+FormatDateTime('HH:MM:ss',Time)+#13+#10;

Thanks

Tim
0
Comment
Question by:Hornwood
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 39977679
why this?

Sleep(10000);
0
 
LVL 27

Expert Comment

by:Sinisa Vuk
ID: 39977861
I suggest to use:
  DBConn.BeginTrans;
  try
    ADOCAN.ExecProc;
    DBConn.CommitTrans; //on success, commit the changes;
  except
    DBConn.RollbackTrans; //on failure, undo the changes;
  end;

Open in new window

DBConn is AdoConnection, and your ADOCAN is linked to it.

... your procedure can be called using TADOCommand component too:
q: TADOCommand;
....
    q.CommandText:='exec procName param1, prarm2';
    q.Execute;

Open in new window

0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 39977922
Your while loop does not make any sense. The wrapped code will just run once, so what is the point of the while loop?

I would have written that block of code as follows (note the e.message I added):
Try
  Memo1.lines.add('Start Recalc '+FormatDateTime('HH:MM:ss',Time));
  ADOCAN.ExecProc;
  Memo1.lines.add('End Recalc '+FormatDateTime('HH:MM:ss',Time));
except on E:Exception 
  do Memo1.lines.add('ADOCan Error: ' + e.message);      
End;	

Open in new window


can you elaborate on "...implemented the standard Delphi error trapping but it does not seem to work"? What is not working as expected? What error are you getting?
0
 
LVL 37

Accepted Solution

by:
Geert Gruwez earned 500 total points
ID: 39979957
you will never be able to solve the deadlock from the delphi app
you need to look at the stored procedure

somewhere you are locking row A, someone else is locking row B
then someone else wants to lock row A and then you want to lock row B
that's your deadlock

find what 2 rows you are locking, and then see if anyone can do it in the reverse order
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Syntax issue with my Where Clause SQL 2012 20 42
Estimating my database size 7 51
When are cursors useful? 8 62
STUFF function - proper use to update column? 6 18
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question