[Lazarus] SQLTransaction: Operation cannot be performed on an active transaction
Eric Kom
erickom at metropolitancollege.co.za
Thu Jun 6 19:24:10 CEST 2013
On 06/06/2013 16:01, Eric Kom wrote:
> On 06/06/2013 15:17, Antonio Fortuny wrote:
>> Hi all,
>>>
>>> I tried reviewed the code and commented the try...except statement
>>> for more debug:
>>>
>>> procedure TfrmMainWindow.bitBtn1Click(Sender: TObject);
>>> begin
>>> //try
>>> DataModuleConn.openConnection;
>>> DataModuleConn.MySQL55Conn.Transaction :=
>>> DataModuleConn.SQLTransaction;
>>> //DataModuleConn.MySQL55Conn.Transaction.Active:=False;
>>> DataModuleConn.SQLTransaction.DataBase:=DataModuleConn.MySQL55Conn;
>>> DataModuleConn.SQLQuery.DataBase:=DataModuleConn.MySQL55Conn;
>>> DataModuleConn.SQLQuery.Transaction:=DataModuleConn.SQLTransaction;
>>> DataModuleConn.SQLQuery.Active:=False;
>>> DataModuleConn.SQLQuery.SQL.Text:='';
>>> DataModuleConn.Datasource.DataSet:=DataModuleConn.SQLQuery;
>>> DataModuleConn.Datasource.DataSet.Active:=False;
>>> //DBGrid1.DataSource.DataSet.Active:=False;
>>> DBGrid1.DataSource:=DataModuleConn.Datasource;
>>> DBNavigator.DataSource:=DataModuleConn.Datasource;
>>>
>>> if DataModuleConn.MySQL55Conn.Connected=True then begin
>>> frmMainWindow.memReports.Append(msgOpeningDB);
>>> frmMainWindow.memReports.Append(msgDBReady);
>>> end;
>>>
>>> if (cbbList1.Items[cbbList1.ItemIndex] = 'ALL') then begin
>>> DataModuleConn.SQLQuery.SQL.Text:='SELECT gender, year,
>>> country FROM reiser';
>>> end
>>>
>>> else if (cbbList1.Items[cbbList1.ItemIndex] = lisCountry1)
>>> then begin
>>> DataModuleConn.SQLQuery.SQL.Text:='SELECT gender, year,
>>> country FROM reiser WHERE country = ''Dutch Republic''';
>>> end
>>> else //(cbbList1.Items[cbbList1.ItemIndex] = lisCountry3)
>>> begin
>>> DataModuleConn.SQLQuery.SQL.Text:='SELECT gender, year,
>>> country FROM reiser WHERE country = ''Holland''';
>>> end;
>>>
>>> //DataModuleConn.SQLTransaction.StartTransaction;
>>> DataModuleConn.SQLQuery.Close;
>>> DataModuleConn.Datasource.DataSet.Active:=True;
>>> DataModuleConn.SQLQuery.Open;
>>>
>>> //except
>>> // frmMainWindow.memReports.Append(ErrConnectionFailed);
>>> //end;
>>> end;
>>>
>>> The first trigged for the procedure bitBtn1Click1 works fine, the
>>> second trigged give up, with the above error:
>>>
>>> Operation cannot be performed on an active dataset.
>> Could you point out which line generates the error ?
> Thank you for your response
> No specific line has been reported.
> According to the error message, for another query to be executed, the
> Dataset must be turn to false so that previous query must be cancelled.
>> As a matter of fact, there are too much DB related operations.
>> For instance the first 9 lines after the one // Try should be moved
>> somewhere in an independent method and called only once in some
>> intialization routine.
>> Secondly, the two next lines do exactle the same thing, only one of
>> them should be removed
>> DataModuleConn.Datasource.DataSet.Active:=True; <==== keep this
>> one as more datasource independent
>> DataModuleConn.SQLQuery.Open; <---------- this one can
>> be removed
>>
>> This rearrangement should work provided the 9 preceeding lines have
>> been moved somewhere else and executed at least once
>>
>> if DataModuleConn.SQLQuery.Active then
>> DataModuleConn.SQLQuery.Close;
>> if DataModuleConn.MySQL55Conn.Transaction.InTransaction then
> on the DataModuleConn.MySQL55Conn.Transaction.InTransaction, the
> InTransaction property/var its not reorganized by lazarus.
>> DataModuleConn.MySQL55Conn.Transaction.Commit;
>> if DataModuleConn.MySQL55Conn.Connected=True then begin
>> frmMainWindow.memReports.Append(msgOpeningDB);
>> frmMainWindow.memReports.Append(msgDBReady);
>> end;
>>
>> if (cbbList1.Items[cbbList1.ItemIndex] = 'ALL') then begin
>> DataModuleConn.SQLQuery.SQL.Text:='SELECT gender, year,
>> country FROM reiser';
>> end
>>
>> else if (cbbList1.Items[cbbList1.ItemIndex] = lisCountry1) then
>> begin
>> DataModuleConn.SQLQuery.SQL.Text:='SELECT gender, year,
>> country FROM reiser WHERE country = ''Dutch Republic''';
>> end
>> else //(cbbList1.Items[cbbList1.ItemIndex] = lisCountry3)
>> begin
>> DataModuleConn.SQLQuery.SQL.Text:='SELECT gender, year,
>> country FROM reiser WHERE country = ''Holland''';
>> end;
>>
>> DataModuleConn.SQLTransaction.StartTransaction;
>> DataModuleConn.SQLQuery.Open;
>>
>> Antonio.
>>> Press OK to ignore and risk data corruption.
>>> Press Cancel to kill the program.
>>>
>>> I tried to close and open the Dataset on my procedure bitBtn1Click1.
>>>
>>> May you please assist me?
The following code works:
//try
DataModuleConn.openConnection;
if DataModuleConn.SQLQuery.Active then begin
DataModuleConn.SQLQuery.Close;
end;
if DataModuleConn.SQLTransaction.Active then begin
//DataModuleConn.MySQL55Conn.Transaction.Commit;
DataModuleConn.SQLTransaction.Active:=True;
DataModuleConn.SQLTransaction.Rollback;
DataModuleConn.SQLTransaction.Active:=False;
end;
if DataModuleConn.MySQL55Conn.Connected=True then begin
frmMainWindow.memReports.Append(msgOpeningDB);
frmMainWindow.memReports.Append(msgDBReady);
DataModuleConn.SQLTransaction.DataBase:=DataModuleConn.MySQL55Conn;
DataModuleConn.SQLQuery.DataBase:=DataModuleConn.MySQL55Conn;
DataModuleConn.SQLQuery.Transaction:=DataModuleConn.SQLTransaction;
DataModuleConn.Datasource.DataSet:=DataModuleConn.SQLQuery;
end;
if (cbbList1.Items[cbbList1.ItemIndex] = 'ALL') then begin
DataModuleConn.SQLQuery.SQL.Text:='SELECT gender, year,
country FROM reiser';
end
else if (cbbList1.Items[cbbList1.ItemIndex] = lisCountry1) then
begin
DataModuleConn.SQLQuery.SQL.Text:='SELECT gender, year,
country FROM reiser WHERE country = ''Dutch Republic''';
end
else //(cbbList1.Items[cbbList1.ItemIndex] = lisCountry3)
begin
DataModuleConn.SQLQuery.SQL.Text:='SELECT gender, year, country
FROM reiser WHERE country = ''Holland''';
end;
DataModuleConn.SQLTransaction.StartTransaction;
DataModuleConn.SQLQuery.Open;
DBGridStudentRegistration.DataSource:=DataModuleConn.Datasource;
DBNavigatorStudentRegistration.DataSource:=DataModuleConn.Datasource;
>>>
>>>>
>>>>
>>>> --
>>>> _______________________________________________
>>>> Lazarus mailing list
>>>> Lazarus at lists.lazarus.freepascal.org
>>>> http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
>>>>
>>>
>>>
>>
>> --
>> Sita Software
>> *Antonio Fortuny*
>> Senior Software engineer
>>
>> 220, avenue de la Liberté
>> L-4602 Niederkorn
>> Tel.: +352 58 00 93 - 93
>> www.sitasoftware.lu <http://www.sitasoftware.lu>
>> Your IT Partner
>>
>>
>>
>> --
>> _______________________________________________
>> Lazarus mailing list
>> Lazarus at lists.lazarus.freepascal.org
>> http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
>
>
> --
> Kind Regards
>
> Eric Kom
>
> System Administrator & Programmer - Metropolitan College
> _________________________________________
> / You are scrupulously honest, frank, and \
> | straightforward. Therefore you have few |
> \ friends. /
> -----------------------------------------
> \
> \
> .--.
> |o_o |
> |:_/ |
> // \ \
> (| Kom | )
> /'\_ _/`\
> \___)=(___/
>
> 2 Hennie Van Till, White River, 1240
> Tel: 013 750 2255 | Fax: 013 750 0105 | Cell: 078 879 1334
> erickom at kom.za.net |erickom at metropolitancollege.co.za
> www.kom.za.net |www.kom.za.org |www.erickom.co.za
>
> Key fingerprint: 513E E91A C243 3020 8735 09BB 2DBC 5AD7 A9DA 1EF5
>
>
> --
> _______________________________________________
> Lazarus mailing list
> Lazarus at lists.lazarus.freepascal.org
> http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
--
Kind Regards
Eric Kom
System Administrator & Programmer - Metropolitan College
_________________________________________
/ You are scrupulously honest, frank, and \
| straightforward. Therefore you have few |
\ friends. /
-----------------------------------------
\
\
.--.
|o_o |
|:_/ |
// \ \
(| Kom | )
/'\_ _/`\
\___)=(___/
2 Hennie Van Till, White River, 1240
Tel: 013 750 2255 | Fax: 013 750 0105 | Cell: 078 879 1334
erickom at kom.za.net | erickom at metropolitancollege.co.za
www.kom.za.net | www.kom.za.org | www.erickom.co.za
Key fingerprint: 513E E91A C243 3020 8735 09BB 2DBC 5AD7 A9DA 1EF5
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus-ide.org/pipermail/lazarus/attachments/20130606/093786de/attachment-0003.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/jpeg
Size: 2306 bytes
Desc: not available
URL: <http://lists.lazarus-ide.org/pipermail/lazarus/attachments/20130606/093786de/attachment-0006.jpe>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/jpeg
Size: 921 bytes
Desc: not available
URL: <http://lists.lazarus-ide.org/pipermail/lazarus/attachments/20130606/093786de/attachment-0007.jpe>
More information about the Lazarus
mailing list