[Lazarus] SQLTransaction: Operation cannot be performed on an active transaction

[Antonio Fortuny]

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 ?
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
         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?
>
>>
>>
>> -- 
>> _______________________________________________
>> 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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus-ide.org/pipermail/lazarus/attachments/20130606/68738299/attachment-0003.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: bjebcbje.jpg
Type: image/jpeg
Size: 2306 bytes
Desc: not available
URL: <http://lists.lazarus-ide.org/pipermail/lazarus/attachments/20130606/68738299/attachment-0006.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: idiiejja.jpg
Type: image/jpeg
Size: 921 bytes
Desc: not available
URL: <http://lists.lazarus-ide.org/pipermail/lazarus/attachments/20130606/68738299/attachment-0007.jpg>