BeginTrans not working. Am I doing things wrong?

I use Delphi 2009 Pro to connect to a MySQL database online. To do so, I use ADO components (dbGo palette).

In my case, an order can have many people attached to it and many items attached to the order. I have a table for orders, peoples and order items.

I first post the peoples then the order and finally order items. When an error occurs I want to undo my operations (if I get an error creating the order, wipe it's newly created associated peoples and so on...).

To do so I use transactions with BeginTrans, CommitTrans and RollbackTrans... So far, no success. I might do something wrong.

Here is how I do it:

----------

adocMyConn.BeginTrans;
try
    //***** shipTo *************************************
    try
        AWideStr := OrderMessageBatch.HubOrder[i].ShipTo.Attributes['personPlaceID']
    except
        on E:Exception do
            Raise Exception.Create('Some error');
    end;
    APersonPlace := FindPersonPlace(OrderMessageBatch.HubOrder[i], AWideStr);
    if APersonPlace = nil then
        Raise Exception.Create('Some error');
    ShipToInternalId := InsertPersonPlace(APersonPlace);
    if ShipToInternalId = 0 then
        Raise Exception.Create('Some error');

    //***** order ***********************************
    InsertHubOrder(OrderMessageBatch.HubOrder[i], FromId, ShipToInternalId, BillToInternalId);

    //***** order item ******************************
    for j := 0 to OrderMessageBatch.HubOrder[i].LineItem.Count - 1 do
        InsertOrderItem(OrderMessageBatch.HubOrder[i].LineItem[j], OrderMessageBatch.HubOrder[i].OrderId);

    adocMyConn.CommitTrans; //All is OK!
except
    on E:Exception do
    begin
        adocMyConn.RollbackTrans; //Should cancel peoples, order and order items
        Msg := 'Error inserting to the database ! Message : ' + E.Message;
    end;
end;

----------

InsertPersonPlace,
InsertHubOrder,
InsertOrderItem
uses their own TADOQuery which are all attached to the same TADOConnection (adocMyConn).

All of these procedures/functions do something like:

begin
    adoqMyQuery.Parameters.ParamByName('someParam').Value := SomeValue;
    adoqMyQuery.Prepared := True;
    adoqMyQuery.ExecSQL;
end;

Of course the ExecSQL can throw an exception - that should be catched by my "try" - but it's not...

Also, are my raise problematic in that case?

What am I doing wrong?

Thanks!
0
Alexandre
1/22/2009 3:50:03 PM
embarcadero.delphi.ado 597 articles. 1 followers. Follow

3 Replies
697 Views

Similar Articles

[PageSpeed] 3

Where are you calling ADOConnection.BeginTrans? I didn't see the code.

You say "no success". But, what is the error message? What kind of exception is being raised?

Best regards
0
Alexandre
1/22/2009 3:43:22 PM
Oops, just edited my previous post to show the BeginTrans (at the very top before the 1st try).

My error is that I have a key violation on one ExecSQL (for one order). The peoples of this duplicate order aren't cleaned up... RollbackTrans is fired, but it does no clean the poeples added.

> {quote:title=Alexandre Machado wrote:}{quote}
> Where are you calling ADOConnection.BeginTrans? I didn't see the code.
> 
> You say "no success". But, what is the error message? What kind of exception is being raised?
> 
> Best regards
0
Alexandre
1/22/2009 4:01:57 PM
OK, I've found my solution... Nothing to do with my code. It's the data provider (in my case MySQL).

I was using MyISAM which isn't transactionnal... I changed my tables (alter) to use InnoDB and everything is OK now.
0
Alexandre
1/22/2009 7:32:28 PM
Reply:

Similar Artilces:

ado begintrans does not work
I am having issues with ADO begintrans method on the connection object. It gives the following error: Access Violation. Note: Transaction statements on session object in OLE DB work fine with no problem. Is anyone aware of any issues with ADO transactions? Thanks. Iraj. Can you provide a sample for this. This code snippet works as we expect it. Dim objADO As ADODB.Connection Dim objCMD As ADODB.Command objADO = New ADODB.Connection() objADO.ConnectionString = "'Provider=asaprov.80;user id=dba;dsn=ASA 8.0 Sample;" objADO.Open() 'objADO.Execute("C...

Wrong Thing, Wrong Time, Wrong Prof
Hey folks, I couldn't resist passing this along from Fred Langa's List, from the "Just For Grins" department, where, in a .wmv media presentation, a professor pretty much spells out in how much - and how deep - doo-doo a thief is for stealing his laptop and how it is he knows who did it. http://media1.break.com/dnet/media/content/stolenlaptop.wmv -- Captain Ozone "Beat me, daddy, eight to the bar!" 8-) Captain Ozone writes: > Hey folks, I couldn't resist passing this along from Fred Langa's List, from > the "Just For Gri...

ASA8 and Delphi ADO? Is it work?
Anybody test this configuration? As i can remember ASA7 don't work with Delphi via ADO. ---------------------------------- Oleg Kovalevsky Moscow, Russia I haven't tested ASA8 with Delphi, but I'm using ASA7 with Delphi 5 and ADOExpress. There are some glitches, but ASA support has been very helpful with addressing the problems. On Fri, 29 Mar 2002 17:01:05 +0300, "Oleg Kovalevsky" <okovalevsky@hotmail.com> wrote: >Anybody test this configuration? As i can remember ASA7 don't work with >Delphi via ADO. > > >----------...

Convert Delphi 5 ADO App to Delphi 2007 ADO.NET
I have an appliction tha that is writen in Delphi 5 with ADOConnetion, ADOTable, and ADOQuery components. I need to upgrade to Delphi 2007 and use ADO.NET. I have the program building in with Delphi 2007. Is there anything I need to do to convert the ADO to ADO.NET? > I need to upgrade to Delphi 2007 and use ADO.NET. Why ? -- With best regards, Dmitry Arefiev AnyDAC (www.anydac.com) - Oracle, MS SQL Server, IBM DB2, MySQL, Firebird, Interbase, PostgreSQL, Sybase ASA, SQLite, MS Access, DBX, ODBC, InMem - universal high-speed native data access engine Reasons for ...

ADO Connection not working if Delphi and Oracle not on same computer
I had Delphi working fine with Oracle using ADO when Oracle 11g is on the same pc as Delphi. I tried to use Delphi from another pc and I do not know what to enter for "Servername" on the Build connection string screen. Originally I had tough I needed to enter the computer name, aka Hostname. I also tried entering a name of the oracle instance or Oracle service, and also tried using this info in for format of hostname.service and \\hostname-service. Can someone help -is it just the computer name, is it the ip address, does it need the Oracle instance name? Thanks. steve Hel...

Componet installation does not work in Delphi 2010
Hi, I'm trying to install a very simple component ("MyButton") package into Delphi 2010, but when I put the component on my form and try to compile it, I get a path releated error: *[DCC Fatal Error] Unit1.pas(7): F1026 File not found: 'MyButton.dcu'* If I make the exact same operation in Delphi 2007 it compiles without any errors and works like a charm. Both installations are new and no extra paths has been added to either of them. What am I doing wrong and why does this not work in D2010 when it works in D2007? Here are the steps* done in order to create a t...

WRONG WRONG WRONG
Name: ROZ mandelcorn Email: thunderstaratearthlinkdotnet Product: Firefox Summary: WRONG WRONG WRONG Comments: as a long-time user, first of netscape and, mozilla firefox, i must say i DO NOT LIKE THE PLACEMENT of TABS in version 4. nor do i like the whole rearrangement of the upper/navigation portion of firefox. I NEED/WANT MY TABS DIRECTLY ABOVE MY WINDOWS AS IT IS NOW....NOT ON TOP OF THE BROWSER. I WANT MY FILE-EDIT-ETC-MENU on TOP. ETC. AT LEAST GIVE US THE CHOICE AS TO WHICH FORMAT WE WANT....CAN YOU SAY CUSTOMIZATION! I WILL NOT USE 4 IF THIS IS THE WAY IT IS. [just ...

Delphi 6 to Delphi 2010 upgrade and ADO
We have an application written in Delphi 6 Professional that uses ADO to access databases servers that may be on other PCs in our network. We want to move to Delphi 2010 but can't decide if we need Professional or Enterprise. 2010 Pro has dbGO - will this give us the same functionality as the ADO components we use in Delphi 6 Pro? Thanks, Keith > 2010 Pro has dbGO - will this give us the same functionality as the > ADO components we use in Delphi 6 Pro? Yes. > {quote:title=Joachim Uersfeld wrote:}{quote} > > 2010 Pro has dbGO - will this give us the same func...

Why does this work fine in Delphi 2009, but not in Delphi 2010
I've been racking my head trying to figure this out and can't understand what is wrong, TFileStream.ReadComponentRes fails when I try to read a component containing a record, it works fine in Delphi 2009 and Delphi 2007, but fails with Delphi 2010 Here is the unit source and DFM *+//UnitSource+* unit Unit1; interface uses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, StdCtrls; type TThresholdMode = (tm0,tm1,tm2,tm3,tm4,tm5); TThreshold = Record Mode:TThresholdMode; Filter,Start,Stop:Integer; end; ...

ADO Connection with VB6 works, XE2 does not work
I am facing a strange problem in a concrete Windows 7 64 bits Home Premium SP2 machine, with other machines in the same local network all is working ok. My tests are the following: 1.) I create an *.UDL file. I click in "Test connection" and it works, I am using the generated string to test the connection in the programs: Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Password=mypassword;Data Source=SERVIDOR\HOMEDES; 2.) Using an old VB6 program to connect to the SQL Server it works. 3.) Using a test program using XE2 it does not work, it fails in the lin...

Modify a working Delphi XE4 installation does not work
Hello, i need to upgrade the intraweb component in Delphi XE4 to the official atozed license. The info from atozed is first to uninstall the bundled version. But when i start the installation wizard from XE4 and select modify, a message "serial number unvalid" appears and i can only stop with taskmanager. What can i do. Thanks Theo Ok, i found the solution myself. Here is how it goes: http://support.embarcadero.com/article/43089 ...

Unit working on delphi 7
Hello, i have unit working on delphi7 - 2010 BUT on XE5 give me a lot of errors can you help me please to check whats wrong in the unit on XE5 {$WARNINGS OFF} unit CommonMemoryUnit; interface uses Windows, Messages, SysUtils, Classes, Dialogs; type TCommMemNT = class(TObject) private FMemPtr:pointer; FhProcess:THandle; FdwProcessId:DWORD; FSize:integer; FPosition : Cardinal; protected procedure Open(AllocSize:integer); procedure Close; public constructor Create(hTarget:HWND;AllocSize:integer); destructor Destroy;override; procedure Ze...

Code works in Delphi 7 but not in Delphi 2010 [Edit]
hello, i have a procedure that open's a file by passing the file name as the parameter to the executable. something like this {code} C : \ P r o g r a m F i l e s \ Da c k e r \ D r a c k e r . e x e " G : \ D E l p h i 7 \ D e l p h i 7 A p p _ l o g . t " {code} The source code is {code} procedure OpenFileWithExe var hReg: HKEY; Ret: Longint; RegDataType, RegDataSize: DWORD; CmdLine: array [0..560] of Char; Len: Integer; SInfo: TStartupInfo; PInfo: TProcessInformation; begin Ret := windows.RegOpenKeyEx(HKEY_CURRENT_USER, ...

Is dll developed in Delphi 6 works on Delphi 2?
I have a one dll, whose work is to creates a form with some normal vcl controls, print selected tables and email some reports. It was developed in Delphi 6. Can any other application which was developed in Delphi 2 use that dll.? If not, please let me know in which areas i need change. The dll work is only to print and email. With regards, Srikanth Varma Srikanth varma wrote: > I have a one dll, whose work is to creates a form with some normal > vcl controls, print selected tables and email some reports. It was > developed in Delphi 6. Can any other application which was d...

Web resources about - BeginTrans not working. Am I doing things wrong? - embarcadero.delphi.ado

Microsoft Data Access Components - Wikipedia, the free encyclopedia
This article has an unclear citation style . The references used may be made clearer with a different or consistent style of citation , footnoting ...

Resources last updated: 12/31/2015 8:19:21 PM