Recent

Author Topic: UPDATE on Postgresql gets stuck on "idle in transaction".  (Read 182 times)

defranz

  • Newbie
  • Posts: 3
UPDATE on Postgresql gets stuck on "idle in transaction".
« on: March 15, 2019, 12:58:37 pm »
I'm not sure if this is a problem with my Pascal code or something with, postgresql.
I'm new to free pascal, and postgresql.

Basically when I do an update from my program, the UPDATE query gets stuck in my database on "Idle in transaction".
Here is my code

Code: Pascal  [Select]
  1. unit Unit1;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, Menus,
  9.   ExtCtrls, StdCtrls, Grids, ComCtrls, DBGrids, Unit3, Unit2, pqconnection,
  10.   sqldb, db;
  11.  
  12. type
  13.  
  14.   { TForm1 }
  15.  
  16.   TForm1 = class(TForm)
  17.     DataSource1: TDataSource;
  18.     DBConn: TPQConnection;
  19.     DBGrid1: TDBGrid;
  20.     MainMenu1: TMainMenu;
  21.     MenuItem1: TMenuItem;
  22.     MenuItem3: TMenuItem;
  23.     MenuItem4: TMenuItem;
  24.     MenuItem5: TMenuItem;
  25.     MenuItem6: TMenuItem;
  26.     MenuItem7: TMenuItem;
  27.     MenuItem8: TMenuItem;
  28.     SQLQuery1: TSQLQuery;
  29.     SQLTransaction1: TSQLTransaction;
  30.  
  31.     procedure DBGrid1CellClick(Column: TColumn);
  32.     procedure DBGrid1DblClick(Sender: TObject);
  33.     procedure OnLoad(Sender: TObject);
  34.   private
  35.  
  36.   public
  37.  
  38.   end;
  39.  
  40. var
  41.   Form1: TForm1;
  42.   uredi: TForm2;
  43. implementation
  44.  
  45. {$R *.lfm}
  46.  
  47. { TForm1 }
  48.  
  49.  
  50. procedure TForm1.OnLoad(Sender: TObject);
  51. begin
  52.   SQLQuery1.Close;
  53.   SQLQuery1.SQL.Text:= 'select * from knjiga';
  54.   DBConn.Connected:= True;
  55.   SQLTransaction1.Active:= True;
  56.   SQLQuery1.Open;
  57.   DBGrid1.Options := DBGrid1.Options + [dgMultiSelect];
  58.  
  59. end;
  60.  
  61. procedure TForm1.DBGrid1DblClick(Sender: TObject);
  62. begin
  63.  
  64.       uredi:= TForm2.Create(Self);
  65.       Try
  66.        uredi.Edit1.Text := DBGrid1.DataSource.DataSet.FieldByName('sifra_knjiga').AsString; //taking information from db grid and puting it into text edits in another form
  67.        uredi.Edit2.Text := DBGrid1.DataSource.DataSet.FieldByName('isbn').AsString;
  68.        uredi.Edit3.Text := DBGrid1.DataSource.DataSet.FieldByName('naziv').AsString;
  69.        uredi.Edit4.Text := DBGrid1.DataSource.DataSet.FieldByName('autor').AsString;
  70.        uredi.Edit5.Text := DBGrid1.DataSource.DataSet.FieldByName('izdavac').AsString;
  71.        uredi.Edit6.Text := DBGrid1.DataSource.DataSet.FieldByName('godina_izdavanja').AsString; //taking information from db grid and puting it into text edits in another form
  72.        uredi.Edit7.Text := DBGrid1.DataSource.DataSet.FieldByName('kolicina').AsString;
  73.        uredi.Edit8.Text := DBGrid1.DataSource.DataSet.FieldByName('bar_kod').AsString;
  74.        uredi.Edit9.Text := DBGrid1.DataSource.DataSet.FieldByName('isbn_stari').AsString;
  75.        uredi.Edit10.Text := DBGrid1.DataSource.DataSet.FieldByName('mjesto_izdavanja').AsString; //taking information from db grid and puting it into text edits in another form
  76.        uredi.Memo1.Text := DBGrid1.DataSource.DataSet.FieldByName('sazetak').AsString;
  77.        SQLQuery1.Close;
  78.        SQLTransaction1.Active:= False;
  79.        DBConn.Connected:= False;
  80.        uredi.ShowModal;
  81.       Finally
  82.        uredi.Release;
  83.        uredi:= Nil;
  84.       End;
  85.  
  86. end;
  87.  
  88. procedure TForm1.DBGrid1CellClick(Column: TColumn);
  89. begin
  90.   DBGrid1.SelectedRows.CurrentRowSelected := True;
  91. end;
  92.  
  93.  
  94.  
  95.  
  96.  
  97. end.
  98.  

Code of the other form

Code: Pascal  [Select]
  1. unit Unit2;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, SysUtils, pqconnection, sqldb, db, FileUtil, Forms, Controls,
  9.   Graphics, Dialogs, Menus, DBGrids, StdCtrls;
  10.  
  11. type
  12.  
  13.   { TForm2 }
  14.  
  15.   TForm2 = class(TForm)
  16.     Button1: TButton;
  17.     Button2: TButton;
  18.     DataSource1: TDataSource;
  19.     DBConn: TPQConnection;
  20.     Edit1: TEdit;
  21.     Edit10: TEdit;
  22.     Edit2: TEdit;
  23.     Edit3: TEdit;
  24.     Edit4: TEdit;
  25.     Edit5: TEdit;
  26.     Edit6: TEdit;
  27.     Edit7: TEdit;
  28.     Edit8: TEdit;
  29.     Edit9: TEdit;
  30.     Label1: TLabel;
  31.     Label10: TLabel;
  32.     Label11: TLabel;
  33.     Label2: TLabel;
  34.     Label3: TLabel;
  35.     Label4: TLabel;
  36.     Label5: TLabel;
  37.     Label6: TLabel;
  38.     Label7: TLabel;
  39.     Label8: TLabel;
  40.     Label9: TLabel;
  41.     Memo1: TMemo;
  42.     SQLQuery1: TSQLQuery;
  43.     SQLTransaction1: TSQLTransaction;
  44.     procedure Button1Click(Sender: TObject);
  45.  
  46.  
  47.  
  48.   private
  49.  
  50.   public
  51.  
  52.   end;
  53.  
  54. var
  55.   Form2: TForm2;
  56.   sql: string;
  57.   pom: string;
  58. implementation
  59.  
  60. {$R *.lfm}
  61.  
  62. { TForm2 }
  63.  
  64.  
  65.  
  66.  
  67.  
  68. procedure TForm2.Button1Click(Sender: TObject);
  69. begin
  70.      SQLQuery1.Close;
  71.      sql := 'UPDATE knjiga SET bar_kod='+Edit8.Text+',isbn='+Edit2.Text+',isbn_stari='+Edit9.Text+',naziv='''+Edit3.Text+''',autor='''+Edit4.Text+''',izdavac='''+Edit5.Text+''',godina_izdavanja='+Edit6.Text+',mjesto_izdavanja='''+Edit10.Text+''',kolicina='+Edit7.Text+' WHERE sifra_knjiga='+Edit1.Text+';';
  72.      SQLQuery1.SQL.Text:= sql;
  73.      DBConn.Connected:= True;
  74.      SQLTransaction1.Active:= True;
  75.      SQLQuery1.ExecSQL;
  76.  
  77. end;
  78.  
  79.  
  80.  
  81.  
  82.  
  83.  
  84.  
  85. end.
  86.  

After I close the program, no changes are made to the database.


rvk

  • Hero Member
  • *****
  • Posts: 3621
Re: UPDATE on Postgresql gets stuck on "idle in transaction".
« Reply #1 on: March 15, 2019, 01:15:13 pm »
After I close the program, no changes are made to the database.
You need to commit the transaction.

 
Code: Pascal  [Select]
  1. procedure TForm2.Button1Click(Sender: TObject);
  2. begin
  3.      SQLQuery1.Close;
  4.      sql := 'UPDATE knjiga SET bar_kod='+Edit8.Text+',isbn='+Edit2.Text+',isbn_stari='+Edit9.Text+',naziv='''+Edit3.Text+''',autor='''+Edit4.Text+''',izdavac='''+Edit5.Text+''',godina_izdavanja='+Edit6.Text+',mjesto_izdavanja='''+Edit10.Text+''',kolicina='+Edit7.Text+' WHERE sifra_knjiga='+Edit1.Text+';';
  5.      SQLQuery1.SQL.Text:= sql;
  6.      DBConn.Connected:= True;
  7.      SQLTransaction1.Active:= True;
  8.      SQLQuery1.ExecSQL;
  9.      SQLTransaction1.Commit;
  10.      DBConn.Connected:= False;
  11. end;

Also I would change this too:
Code: Pascal  [Select]
  1. Finally
  2.   // uredi.Release;
  3.   // uredi:= Nil;
  4.   uredi.Free; // or FreeAndNil(uredi) if you really need uredi to be nil afterwards
  5. End;

And you can also use nil for creating the TForm2. It's not Self that's releasing the form but you, in code. Only use Self if Self is really freeing the TForm2.

Code: Pascal  [Select]
  1. uredi:= TForm2.Create(nil); // Only use Self if Self is going to free the form

Is there any special need to close the database before doing the ShowModal?
You could remove line 77,78,79 from unit1 and 73,74 from unit2.
« Last Edit: March 15, 2019, 01:19:44 pm by rvk »

defranz

  • Newbie
  • Posts: 3
Re: UPDATE on Postgresql gets stuck on "idle in transaction".
« Reply #2 on: March 15, 2019, 01:34:11 pm »
It works thank you so much.

I closed the connection after ShowModal, because I thought that open connection might be creating a lock in my database, preveting the update.

Thank you very much

rvk

  • Hero Member
  • *****
  • Posts: 3621
Re: UPDATE on Postgresql gets stuck on "idle in transaction".
« Reply #3 on: March 15, 2019, 01:48:59 pm »
I would suggest creating a new TDataModule and putting the TPQConnection and TSQLTransaction on that module/form.

You can create a TDataModule with File > New > Datamodule.
If you include the TDataModule in the uses of the units, those units/forms can share the same database-connection and transaction.

In that case you need might need to use SQLTransaction1.CommitRetaining otherwise the DBGrid and TSQLQuery for that grid will close because the transaction is committed, or you could set sqoKeepOpenOnCommit in the options of both TSQLQueries.

And for the new/changed records to be seen in TForm1 in TDBGrid you only need to Close and Open the TSQLQuery on that form (so you could do that directly after the TForm2.ShowModal).

(Those are just some thoughts... :))

defranz

  • Newbie
  • Posts: 3
Re: UPDATE on Postgresql gets stuck on "idle in transaction".
« Reply #4 on: March 15, 2019, 06:17:53 pm »
Will do, thanks!