UPDATE on Postgresql gets stuck on "idle in transaction".

Forum > Databases

(1/1)

defranz:
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---unit Unit1; {$mode objfpc}{$H+} interface uses Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, Menus, ExtCtrls, StdCtrls, Grids, ComCtrls, DBGrids, Unit3, Unit2, pqconnection, sqldb, db; type { TForm1 } TForm1 = class(TForm) DataSource1: TDataSource; DBConn: TPQConnection; DBGrid1: TDBGrid; MainMenu1: TMainMenu; MenuItem1: TMenuItem; MenuItem3: TMenuItem; MenuItem4: TMenuItem; MenuItem5: TMenuItem; MenuItem6: TMenuItem; MenuItem7: TMenuItem; MenuItem8: TMenuItem; SQLQuery1: TSQLQuery; SQLTransaction1: TSQLTransaction; procedure DBGrid1CellClick(Column: TColumn); procedure DBGrid1DblClick(Sender: TObject); procedure OnLoad(Sender: TObject); private public end; var Form1: TForm1; uredi: TForm2;implementation {$R *.lfm} { TForm1 } procedure TForm1.OnLoad(Sender: TObject);begin SQLQuery1.Close; SQLQuery1.SQL.Text:= 'select * from knjiga'; DBConn.Connected:= True; SQLTransaction1.Active:= True; SQLQuery1.Open; DBGrid1.Options := DBGrid1.Options + [dgMultiSelect]; end; procedure TForm1.DBGrid1DblClick(Sender: TObject);begin uredi:= TForm2.Create(Self); Try uredi.Edit1.Text := DBGrid1.DataSource.DataSet.FieldByName('sifra_knjiga').AsString; //taking information from db grid and puting it into text edits in another form uredi.Edit2.Text := DBGrid1.DataSource.DataSet.FieldByName('isbn').AsString; uredi.Edit3.Text := DBGrid1.DataSource.DataSet.FieldByName('naziv').AsString; uredi.Edit4.Text := DBGrid1.DataSource.DataSet.FieldByName('autor').AsString; uredi.Edit5.Text := DBGrid1.DataSource.DataSet.FieldByName('izdavac').AsString; uredi.Edit6.Text := DBGrid1.DataSource.DataSet.FieldByName('godina_izdavanja').AsString; //taking information from db grid and puting it into text edits in another form uredi.Edit7.Text := DBGrid1.DataSource.DataSet.FieldByName('kolicina').AsString; uredi.Edit8.Text := DBGrid1.DataSource.DataSet.FieldByName('bar_kod').AsString; uredi.Edit9.Text := DBGrid1.DataSource.DataSet.FieldByName('isbn_stari').AsString; uredi.Edit10.Text := DBGrid1.DataSource.DataSet.FieldByName('mjesto_izdavanja').AsString; //taking information from db grid and puting it into text edits in another form uredi.Memo1.Text := DBGrid1.DataSource.DataSet.FieldByName('sazetak').AsString; SQLQuery1.Close; SQLTransaction1.Active:= False; DBConn.Connected:= False; uredi.ShowModal; Finally uredi.Release; uredi:= Nil; End; end; procedure TForm1.DBGrid1CellClick(Column: TColumn);begin DBGrid1.SelectedRows.CurrentRowSelected := True;end; end.
Code of the other form

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---unit Unit2; {$mode objfpc}{$H+} interface uses Classes, SysUtils, pqconnection, sqldb, db, FileUtil, Forms, Controls, Graphics, Dialogs, Menus, DBGrids, StdCtrls; type { TForm2 } TForm2 = class(TForm) Button1: TButton; Button2: TButton; DataSource1: TDataSource; DBConn: TPQConnection; Edit1: TEdit; Edit10: TEdit; Edit2: TEdit; Edit3: TEdit; Edit4: TEdit; Edit5: TEdit; Edit6: TEdit; Edit7: TEdit; Edit8: TEdit; Edit9: TEdit; Label1: TLabel; Label10: TLabel; Label11: TLabel; Label2: TLabel; Label3: TLabel; Label4: TLabel; Label5: TLabel; Label6: TLabel; Label7: TLabel; Label8: TLabel; Label9: TLabel; Memo1: TMemo; SQLQuery1: TSQLQuery; SQLTransaction1: TSQLTransaction; procedure Button1Click(Sender: TObject); private public end; var Form2: TForm2; sql: string; pom: string;implementation {$R *.lfm} { TForm2 } procedure TForm2.Button1Click(Sender: TObject);begin SQLQuery1.Close; 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+';'; SQLQuery1.SQL.Text:= sql; DBConn.Connected:= True; SQLTransaction1.Active:= True; SQLQuery1.ExecSQL; end; end.
After I close the program, no changes are made to the database.

rvk:

--- Quote from: defranz on March 15, 2019, 12:58:37 pm ---After I close the program, no changes are made to the database.

--- End quote ---
You need to commit the transaction.

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---procedure TForm2.Button1Click(Sender: TObject);begin SQLQuery1.Close; 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+';'; SQLQuery1.SQL.Text:= sql; DBConn.Connected:= True; SQLTransaction1.Active:= True; SQLQuery1.ExecSQL; SQLTransaction1.Commit; DBConn.Connected:= False;end;
Also I would change this too:

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---Finally // uredi.Release; // uredi:= Nil; uredi.Free; // or FreeAndNil(uredi) if you really need uredi to be nil afterwardsEnd;
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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.

defranz:
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:
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:
Will do, thanks!

Navigation

[0] Message Index