Recent

Author Topic: [SOLVED] Database update not working  (Read 763 times)

pjtuloup

  • New Member
  • *
  • Posts: 45
[SOLVED] Database update not working
« on: October 03, 2024, 07:02:18 pm »
Hi all,

I'm afraid I'll have to call on you again...

I have a problem updating two Mysql tables in succession. The first update on table 'eoperations' succeeds but the second on table 'ehistocreaop' fails (without error message: the update simply does not take place). Yet when I perform the live SQL query with a front end, the update is performed, so the query is correct.
Here is my code:

Code: Pascal  [Select][+][-]
  1.    //++++++++++++++++++++++++++++++Enregistrement dans la Table operations
  2.    Requete:=QuotedStr('INSERT INTO eoperations VALUES(');
  3.    Requete+=IntToStr(NoNewOp);
  4.    Requete+=QuotedStr(',');
  5.    //Détermination Nature Opération
  6.    for i:=0 To SelNature.Items.Count-1 do
  7.       if(SelNature.Text=FormSaisMvtEmp.SelNature.Items[i]) then
  8.          IdOpTyp:=i;
  9.    Requete+=IntToStr(IdOpTyp);
  10.    Requete+=QuotedStr(',');
  11.    //Détermination Id Type Support
  12.    for i:=0 To SelNatSup.Items.Count-1 do
  13.       if(SelNatSup.Text=FormSaisMvtEmp.SelNatSup.Items[i]) then
  14.          IdSupTyp:=i;
  15.    Requete+=IntToStr(IdSupTyp);
  16.    Requete+=QuotedStr(',');
  17.    Requete+=SaisNatRef.Text;
  18.    Requete+=QuotedStr(',');
  19.    Requete+=RightStr(DateToStr(SelDateCPT.Date),4);
  20.    Requete+='-';
  21.    Requete+=copy(DateToStr(SelDateCPT.Date),4,2);
  22.    Requete+='-';
  23.    Requete+=LeftStr(DateToStr(SelDateCPT.Date),2);
  24.    Requete+=QuotedStr(',');
  25.    Requete+=RightStr(DateToStr(SelDateValid.Date),4);
  26.    Requete+='-';
  27.    Requete+=copy(DateToStr(SelDateValid.Date),4,2);
  28.    Requete+='-';
  29.    Requete+=LeftStr(DateToStr(SelDateValid.Date),2);
  30.    Requete+=QuotedStr(',');
  31.    Requete+=RightStr(DateToStr(SelDateSais.Date),4);
  32.    Requete+='-';
  33.    Requete+=copy(DateToStr(SelDateSais.Date),4,2);
  34.    Requete+='-';
  35.    Requete+=LeftStr(DateToStr(SelDateSais.Date),2);
  36.    Requete+=QuotedStr(',');
  37.    Requete+=RightStr(DateToStr(SelDateEffet.Date),4);
  38.    Requete+='-';
  39.    Requete+=copy(DateToStr(SelDateEffet.Date),4,2);
  40.    Requete+='-';
  41.    Requete+=LeftStr(DateToStr(SelDateEffet.Date),2);
  42.    Requete+=QuotedStr(',');
  43.    Requete+=Observations.Text;
  44.    Requete+=QuotedStr(');');
  45.  
  46.    //Suppression des apostrophes de début et de fin dans la requête
  47.    Requete:=copy(Requete,1,Requete.Length-1);
  48.    Requete:=RightStr(Requete,Requete.Length-1);
  49.  
  50.    //Application.MessageBox(PAnsiChar(UserEnCours), 'Alerte Utilisateur');
  51.    //Application.MessageBox(PAnsiChar(Requete), 'Alerte Utilisateur');
  52.  
  53.    //Changement de User et modification de la table
  54.    DataModule1.SQLConnection1.UserName := 'LugesAdm';
  55.    DataModule1.SQLConnection1.Password := 'LugesAdm';
  56.    with DataModule1.SQLQuery1 do
  57.    begin
  58.        Close;
  59.        SQL.Text:=Requete;
  60.        ExecSQL;
  61.    end;
  62.    //++++++++++++++++++++++++++++++Enregistrement dans la Table Historique
  63.    NoNewHistOp:=StrToInt(DerNoHistOp);
  64.    NoNewHistOp:=NoNewHistOp+1;
  65.    Requete:=QuotedStr('INSERT INTO ehistocreaop VALUES(');
  66.    Requete+=IntToStr(NoNewHistOp);
  67.    Requete+=QuotedStr(',');
  68.    Requete+=IntToStr(NoNewOp);
  69.    Requete+=QuotedStr(',');
  70.    Requete+=RightStr(DateToStr(Date),4);
  71.    Requete+='-';
  72.    Requete+=copy(DateToStr(Date),4,2);
  73.    Requete+='-';
  74.    Requete+=LeftStr(DateToStr(Date),2);
  75.    Requete+=QuotedStr(',');
  76.    Requete+=TimeToStr(Time);
  77.    Requete+=QuotedStr(',');
  78.    Requete+='DGFIP';
  79.    Requete+=QuotedStr(',');
  80.    Requete+=UserEnCours;
  81.    Requete+=QuotedStr(',');
  82.    Requete+='AdmTemp';
  83.    Requete+=QuotedStr(',');
  84.    Requete+='eoperations';
  85.    Requete+=QuotedStr(');');
  86.  
  87.    //Suppression des apostrophes de début et de fin dans la requête
  88.    Requete:=copy(Requete,1,Requete.Length-1);
  89.    Requete:=RightStr(Requete,Requete.Length-1);
  90.    Application.MessageBox(PAnsiChar(Requete), 'Alerte Utilisateur');
  91.  
  92.    //Changement de User et modification de la table
  93.    DataModule1.SQLConnection1.UserName := 'LugesAdm';
  94.    DataModule1.SQLConnection1.Password := 'LugesAdm';
  95.    with DataModule1.SQLQuery1 do
  96.    begin
  97.        Close;
  98.        SQL.Text:=Requete;
  99.        ExecSQL;
  100.    end;
  101.  
  102.  

Can you tell me where I'm going wrong? many thanks in advance !
« Last Edit: October 06, 2024, 04:09:54 pm by pjtuloup »

vfclists

  • Hero Member
  • *****
  • Posts: 1146
    • HowTos Considered Harmful?
Re: Database update not working
« Reply #1 on: October 03, 2024, 07:19:08 pm »
Showing the SQL generated might help, and be sure that there is nothing happening server side that might cause the update to fail silently such as incorrect triggers and procedures.
Lazarus 3.0/FPC 3.2.2

pjtuloup

  • New Member
  • *
  • Posts: 45
Re: Database update not working
« Reply #2 on: October 03, 2024, 08:26:53 pm »
thanks ! No Mysql procedure or trigger used. And I am sure that the generated query is correct, because I had it displayed in a text field and copied and pasted it for execution in HeidiSQL and that execution was successful.

paweld

  • Hero Member
  • *****
  • Posts: 1249
Re: Database update not working
« Reply #3 on: October 03, 2024, 08:47:37 pm »
the way you write queries is very unreadable and will lead to errors very quickly. you should use parameters, then it is the component that takes care of the proper formatting of the data. in addition, after the name of the table you should list the names of the columns in exactly the same order as the parameters in the “VALUES” section, that is:
insert into table (col1, col2, col3) values (val1, val2, val3).

I took the liberty of rewriting your code, it is much more readable and avoids errors:
Code: Pascal  [Select][+][-]
  1.   //Détermination Nature Opération
  2.   for i := 0 to SelNature.Items.Count - 1 do
  3.     if (SelNature.Text = FormSaisMvtEmp.SelNature.Items[i]) then
  4.     begin
  5.       IdOpTyp := i;
  6.       break;
  7.     end;
  8.     //Détermination Id Type Support
  9.   for i := 0 to SelNatSup.Items.Count - 1 do
  10.     if (SelNatSup.Text = FormSaisMvtEmp.SelNatSup.Items[i]) then
  11.     begin
  12.       IdSupTyp := i;
  13.       break;
  14.     end;
  15.   // query operations
  16.   if SQLQuery1.Active then
  17.     SQLQuery1.Close;
  18.   SQLQuery1.SQL.Clear;
  19.   SQLQuery1.SQL.Add(' INSERT INTO eoperations '); //add list of columns
  20.   SQLQuery1.SQL.Add(' VALUES (:nno, :iot, :ist, :snr, :sdc, :sdv, :sds, :sde,); ');
  21.   SQLQuery1.ParamByName('nno').AsInteger := NoNewOp;
  22.   SQLQuery1.ParamByName('iot').AsInteger := IdOpTyp;
  23.   SQLQuery1.ParamByName('ist').AsInteger := IdSupTyp;
  24.   SQLQuery1.ParamByName('snr').AsString := SaisNatRef.Text;
  25.   SQLQuery1.ParamByName('sdc').AsDate := SelDateCPT.Date;
  26.   //or is must be TEXT then
  27.   //SQLQuery1.ParamByName('sdc').AsString := FormatDateTime('yyyy-mm-dd', SelDateCPT.Date);
  28.   SQLQuery1.ParamByName('sdv').AsDate := SelDateValid.Date;
  29.   SQLQuery1.ParamByName('sds').AsDate := SelDateSais.Date;
  30.   SQLQuery1.ParamByName('sde').AsDate := SelDateEffet.Date;
  31.   SQLQuery1.ParamByName('o').AsString := Observations.Text;
  32.   //Changement de User et modification de la table
  33.   SQLConnection1.UserName := 'LugesAdm';
  34.   SQLConnection1.Password := 'LugesAdm';
  35.   SQLQuery1.ExecSQL;
  36.  
  37.   //++++++++++++++++++++++++++++++Enregistrement dans la Table Historique
  38.   NoNewHistOp := StrToInt(DerNoHistOp);
  39.   NoNewHistOp := NoNewHistOp + 1;
  40.   SQLQuery1.SQL.Clear;
  41.   SQLQuery1.SQL.Add(' INSERT INTO ehistocreaop '); //add list of columns
  42.   SQLQuery1.SQL.Add(' VALUES (:nnho, :nno, :dt, :tm, :s1, :s2, :s3, :s4); ');
  43.   SQLQuery1.ParamByName('nnho').AsInteger := NoNewHistOp;
  44.   SQLQuery1.ParamByName('nno').AsInteger := NoNewOp;
  45.   SQLQuery1.ParamByName('dt').AsDate := Date;
  46.   SQLQuery1.ParamByName('tm').AsTime := Time;
  47.   SQLQuery1.ParamByName('s1').AsString := 'DGFIP';
  48.   SQLQuery1.ParamByName('s2').AsString := UserEnCours;
  49.   SQLQuery1.ParamByName('s3').AsString := 'AdmTemp';
  50.   SQLQuery1.ParamByName('s4').AsString := 'eoperations';
  51.  
  52.   //Changement de User et modification de la table
  53.   SQLConnection1.UserName := 'LugesAdm';
  54.   SQLConnection1.Password := 'LugesAdm';
  55.   SQLQuery1.ExecSQL;
« Last Edit: October 03, 2024, 09:26:32 pm by paweld »
Best regards / Pozdrawiam
paweld

pjtuloup

  • New Member
  • *
  • Posts: 45
Re: Database update not working
« Reply #4 on: October 03, 2024, 09:20:13 pm »
Thanks, Paweld !

I'm just a beginner ;)

pjtuloup

  • New Member
  • *
  • Posts: 45
Re: Database update not working
« Reply #5 on: October 05, 2024, 07:52:42 pm »
Well,

I rewrote my code but I'm still at the same point: the Insert to query has no effect !

In the following code I limited the update to a single element: no error but "insert to" does nothing:

Code: Pascal  [Select][+][-]
  1. //++++++++++++++++++++++++++++++Enregistrement dans la Table Historique
  2.    NoNewHistOp:=StrToInt(DerNoHistOp);
  3.    NoNewHistOp:=NoNewHistOp+1;
  4.    DataModule1.SQLQuery1.SQL.Clear;
  5.  
  6.    DataModule1.SQLQuery1.SQL.Add('INSERT INTO ehistocreaop (IdEvt) VALUES (:Chp1i);');
  7.  
  8.    DataModule1.SQLQuery1.ParamByName('Chp1i').AsInteger := NoNewHistOp;
  9.  
  10.    //Change User for modification de la table
  11.    DataModule1.SQLConnection1.Connected := false;
  12.    DataModule1.SQLConnection1.UserName := 'LugesAdm';
  13.    DataModule1.SQLConnection1.Password := 'LugesAdm';
  14.    DataModule1.SQLConnection1.Connected := true;
  15.    DataModule1.SQLQuery1.Active:=false;
  16.    DataModule1.SQLQuery1.ExecSQL;
  17.    //DataModule1.SQLQuery1.Active:=true;
  18.  
  19.    DataModule1.SQLConnection1.Connected := false;
  20.  
  21.  

Note that I commented this line:

   //DataModule1.SQLQuery1.Active:=true;

Because otherwise I would get a “non select statement” error even though, as you can seein the code, the SQLQuery was closed before the ExecSQL!!!

In addition, compared to my primitive code, I closed the connection before changing User/pwd and reopened it afterwards because with the mysql "showprocesslist" command I noticed that otherwise the request was not carried out with the desired User. But that doesn't change the problem of the query having no effect.

I understand absolutely nothing and I spent the afternoon on this.

dseligo

  • Hero Member
  • *****
  • Posts: 1386
Re: Database update not working
« Reply #6 on: October 05, 2024, 10:42:03 pm »
I rewrote my code but I'm still at the same point: the Insert to query has no effect !

Did you set up and use transaction?

Quote
Note that I commented this line:

   //DataModule1.SQLQuery1.Active:=true;

Because otherwise I would get a “non select statement” error even though, as you can seein the code, the SQLQuery was closed before the ExecSQL!!!

Of course. You set query's Active property to True (or you can also call SQLQuery1.Open) when you want results to be returned, not when you execute queries with ExecSQL (they don't return results, they include insert, update and delete, among others).

Quote
I understand absolutely nothing and I spent the afternoon on this.

I suggest that you make some small, but complete project which shows what are you trying to do. It is hard to guess how you set it up with code fragments you posted.
Include SQL that create tables and some sample data (if needed).

pjtuloup

  • New Member
  • *
  • Posts: 45
Re: Database update not working
« Reply #7 on: October 06, 2024, 07:51:11 am »

Did you set up and use transaction?

No, I don't use a transaction.

Quote
Of course. You set query's Active property to True (or you can also call SQLQuery1.Open) when you want results to be returned, not when you execute queries with ExecSQL (they don't return results, they include insert, update and delete, among others).

Yes, I know that the dataset must be close or inactive before an ExecSQL, but why can't I not open or active it after the ExecSQL ???

Quote
I suggest that you make some small, but complete project which shows what are you trying to do. It is hard to guess how you set it up with code fragments you posted.
Include SQL that create tables and some sample data (if needed).

I'm just trying to add a row by "Insert Into" in one table first (which works) then in another (which fails). I'll try to give you more information.

pjtuloup

  • New Member
  • *
  • Posts: 45
Re: Database update not working
« Reply #8 on: October 06, 2024, 11:21:10 am »
Well... I am reposting here the code of the procedure in question (rewritten thanks to Paweld). This procedure inserts a (different) row into two tables, an eoperations table and an ehiscreaop table which records the creation history in the first table.

The data to be recorded in the first table comes from entering a form and the data to be recorded in the second are mainly either constants or hourly data.

The main point is that writing to the first table always succeeds and writing to the second always fails (without errors) even though the code is almost the same... Why?

Code: Pascal  [Select][+][-]
  1. procedure TFormSaisMvtEmp.BtnCreerClick(Sender: TObject);
  2. var
  3.   SaisOk: Boolean;
  4.   NoNewOp:Integer;
  5.   NoNewHistOp:Integer;
  6.   i:Integer;
  7.   IdOpTyp:Integer;
  8.   IdSupTyp:Integer;
  9. begin
  10. SaisOk:=true;
  11. if(SelNature.Text='Choisissez...') OR (SelNature.Text='') then SaisOk:=false;
  12. if(SelNatSup.Text='Choisissez...') OR (SelNatSup.Text='') then SaisOk:=false;
  13. if(SaisNatRef.Text='')  then SaisOk:=false;
  14. if(SaisNoOp.Text='')   then SaisOk:=false;
  15.  
  16. if(SaisOk=true) then
  17.    begin
  18.    if(StrToInt(SaisNoOp.Text)<=StrToInt(DerNoOp)) then
  19.         begin
  20.         Application.MessageBox('No déjà utilisé: rectification automatique!', 'Alerte Utilisateur');
  21.         NoNewOp:=StrToInt(DerNoOp);
  22.         NoNewOp:=NoNewOp+1;
  23.         SaisNoOp.Text:=IntToStr(NoNewOp);
  24.         end;
  25.    //++++++++++++++++++++++++++++++Enregistrement dans la Table operations
  26.    if DataModule1.SQLQuery1.Active then
  27.       DataModule1.SQLQuery1.Close;
  28.    DataModule1.SQLQuery1.SQL.Clear;
  29.    DataModule1.SQLQuery1.SQL.Add(' INSERT INTO eoperations (IdOp, IdOpTyp, IdSupTyp, RefSup, DateCTP, DateValid, DateSaisie, DateEffet, Observations) ');
  30.    DataModule1.SQLQuery1.SQL.Add(' VALUES (:Chp1i, :Chp2i, :Chp3i, :Chp4s, :Chp5d, :Chp6d, :Chp7d, :Chp8d, :Chp9b); ');
  31.    //Détermination Nature Opération
  32.    for i:=0 To SelNature.Items.Count-1 do
  33.       if(SelNature.Text=FormSaisMvtEmp.SelNature.Items[i]) then
  34.          IdOpTyp:=i;
  35.    //Détermination Id Type Support
  36.    for i:=0 To SelNatSup.Items.Count-1 do
  37.       if(SelNatSup.Text=FormSaisMvtEmp.SelNatSup.Items[i]) then
  38.          IdSupTyp:=i;
  39.    //Paramètres de la Requête
  40.    DataModule1.SQLQuery1.ParamByName('Chp1i').AsInteger := NoNewOp;
  41.    DataModule1.SQLQuery1.ParamByName('Chp2i').AsInteger := IdOpTyp;
  42.    DataModule1.SQLQuery1.ParamByName('Chp3i').AsInteger := IdSupTyp;
  43.    DataModule1.SQLQuery1.ParamByName('Chp4s').AsString := SaisNatRef.Text;
  44.    DataModule1.SQLQuery1.ParamByName('Chp5d').AsDate := SelDateCPT.Date;
  45.    DataModule1.SQLQuery1.ParamByName('Chp6d').AsDate := SelDateValid.Date;
  46.    DataModule1.SQLQuery1.ParamByName('Chp7d').AsDate := SelDateSais.Date;
  47.    DataModule1.SQLQuery1.ParamByName('Chp8d').AsDate := SelDateEffet.Date;
  48.    DataModule1.SQLQuery1.ParamByName('Chp9b').AsString := Observations.Text;
  49.    //Changement de User et modification de la table
  50.    DataModule1.SQLConnection1.Connected := false;
  51.    DataModule1.SQLConnection1.UserName := 'LugesAdm';
  52.    DataModule1.SQLConnection1.Password := 'LugesAdm';
  53.    DataModule1.SQLConnection1.Connected := true;
  54.    //Exécution de la Requête
  55.    DataModule1.SQLQuery1.ExecSQL;
  56.  
  57.    //++++++++++++++++++++++++++++++Enregistrement dans la Table Historique
  58.    NoNewHistOp:=StrToInt(DerNoHistOp);
  59.    NoNewHistOp:=NoNewHistOp+1;
  60.    if DataModule1.SQLQuery1.Active then
  61.       DataModule1.SQLQuery1.Close;
  62.    DataModule1.SQLQuery1.SQL.Clear;
  63.  
  64.    //DataModule1.SQLQuery1.SQL.Add('INSERT INTO ehistocreaop (IdEvt,IdItem,Date,Heure,Domaine,User,Profil,Table) VALUES (:Chp1i, :Chp2i, :Chp3d, :Chp4t, :Chp5s, :Chp6s, :Chp7s, :Chp8s);');
  65.    DataModule1.SQLQuery1.SQL.Add('INSERT INTO ehistocreaop (IdEvt) VALUES (:Chp1i);');
  66.  
  67.    DataModule1.SQLQuery1.ParamByName('Chp1i').AsInteger := NoNewHistOp;
  68.    //DataModule1.SQLQuery1.ParamByName('Chp2i').AsInteger := NoNewOp;
  69.    //DataModule1.SQLQuery1.ParamByName('Chp3d').AsDate := Date;
  70.    //DataModule1.SQLQuery1.ParamByName('Chp4t').AsString := FormatDateTime('hh:nn:ss', Time);
  71.    //DataModule1.SQLQuery1.ParamByName('Chp5s').AsString := 'DGFIP';
  72.    //DataModule1.SQLQuery1.ParamByName('Chp6s').AsString := UserEnCours;
  73.    //DataModule1.SQLQuery1.ParamByName('Chp7s').AsString := 'AdmTemp';
  74.    //DataModule1.SQLQuery1.ParamByName('Chp8s').AsString := 'eoperations';
  75.  
  76.    //Changement de User et modification de la table
  77.    DataModule1.SQLConnection1.Connected := false;
  78.    DataModule1.SQLConnection1.UserName := 'LugesAdm';
  79.    DataModule1.SQLConnection1.Password := 'LugesAdm';
  80.    DataModule1.SQLConnection1.Connected := true;
  81.  
  82.    try
  83.    DataModule1.SQLQuery1.Active:=false;
  84.    DataModule1.SQLQuery1.ExecSQL;
  85.    //DataModule1.SQLQuery1.Active:=true;
  86.  
  87.    DataModule1.SQLConnection1.Connected := false;
  88.    except
  89.      on e : exception do
  90.      ShowMessage('Erreur: '+e.Message);
  91.    end;
  92.  
  93.    Application.MessageBox('La fiche opération a été prise en compte et enregistrée dans l''historique.', 'Alerte Utilisateur');
  94.  
  95.    end
  96. else
  97.    begin
  98.    Application.MessageBox('Veuillez servir les champs obligatoires!', 'Alerte Utilisateur');
  99.    end
  100. end;  

dseligo

  • Hero Member
  • *****
  • Posts: 1386
Re: Database update not working
« Reply #9 on: October 06, 2024, 12:15:51 pm »

Did you set up and use transaction?

No, I don't use a transaction.

That is then probably a culprit.
Go through this article: https://wiki.freepascal.org/SQLdb_Tutorial1
If I'll have time, maybe I'll make example with transaction based on your code.

Quote
Quote
Of course. You set query's Active property to True (or you can also call SQLQuery1.Open) when you want results to be returned, not when you execute queries with ExecSQL (they don't return results, they include insert, update and delete, among others).

Yes, I know that the dataset must be close or inactive before an ExecSQL, but why can't I not open or active it after the ExecSQL ???

You didn't change SQL.Text - you still have 'insert' in SQL.Text and you are trying to return rows with Open.

You must do something like this:
Code: Pascal  [Select][+][-]
  1. SQL.Text := 'insert into ... ...';
  2. ExecSQL;
  3.  
  4. SQL.Text := 'select row1, row2 from ... ...';
  5. Open;

You could also use more SQLQuery components (i.e. in one could be code for insert, and in another code for select).

And if you could give some information.
I guess you use SQLdb components for database access?
What version of FPC/Lazarus do you have, and what OS?
What database you have problems with?

pjtuloup

  • New Member
  • *
  • Posts: 45
Re: Database update not working
« Reply #10 on: October 06, 2024, 01:23:47 pm »
You didn't change SQL.Text - you still have 'insert' in SQL.Text and you are trying to return rows with Open.

Oh, I see now.

You could also use more SQLQuery components (i.e. in one could be code for insert, and in another code for select).

I thought about it

And if you could give some information.
I guess you use SQLdb components for database access?
What version of FPC/Lazarus do you have, and what OS?
What database you have problems with?

Yes, I use SQL_db components
Lazarus  3.4
FPC 3.2.2
Os Client Windows 11 10.0.22631
Os Server Linux Debian 12.4
Mysql 8.0.36

dseligo

  • Hero Member
  • *****
  • Posts: 1386
Re: Database update not working
« Reply #11 on: October 06, 2024, 03:04:49 pm »
OK, I looked at your code.

It is strange when you say you don't use transaction because TSQLConnection class of SQLdb components should emit error 'Transaction not set.'.
But because you didn't make compileable test project which shows your problem, it is guessing from side of people who are trying to help you.

Anyway, try to do following: put TSQLTransaction on your DataModule1 from SQLdb components in Lazarus - now you should have SQLTransaction1 there alongside with existing SQLConnection1 and SQLQuery1.

Now change your code as below.

I also moved closing connection and changing user to the top, no need to do that twice.
IMHO, part under comment 'transaction setup' should be earlier in the code, but it will work in your code. If you have two separate accounts for read only and for inserting into database, you could have two connections. That way you don't need to clutter code where you insert and select data.

Code: Pascal  [Select][+][-]
  1. procedure TFormSaisMvtEmp.BtnCreerClick(Sender: TObject);
  2. var
  3.   SaisOk: Boolean;
  4.   NoNewOp:Integer;
  5.   NoNewHistOp:Integer;
  6.   i:Integer;
  7.   IdOpTyp:Integer;
  8.   IdSupTyp:Integer;
  9. begin
  10. SaisOk:=true;
  11. if(SelNature.Text='Choisissez...') OR (SelNature.Text='') then SaisOk:=false;
  12. if(SelNatSup.Text='Choisissez...') OR (SelNatSup.Text='') then SaisOk:=false;
  13. if(SaisNatRef.Text='')  then SaisOk:=false;
  14. if(SaisNoOp.Text='')   then SaisOk:=false;
  15.  
  16. if(SaisOk=true) then
  17.    begin
  18.  
  19.    //Changement de User et modification de la table
  20.    DataModule1.SQLConnection1.Connected := false;
  21.  
  22.    // transaction setup
  23.    DataModule1.SQLTransaction1.Action := caCommit;
  24.    DataModule1.SQLConnection1.Transaction := DataModule1.SQLTransaction1;
  25.    DataModule1.SQLQuery1.Transaction := DataModule1.SQLTransaction1;
  26.  
  27.    DataModule1.SQLConnection1.UserName := 'LugesAdm';
  28.    DataModule1.SQLConnection1.Password := 'LugesAdm';
  29.    DataModule1.SQLConnection1.Connected := true;
  30.  
  31.    if(StrToInt(SaisNoOp.Text)<=StrToInt(DerNoOp)) then
  32.         begin
  33.         Application.MessageBox('No déja utilisé: rectification automatique!', 'Alerte Utilisateur');
  34.         NoNewOp:=StrToInt(DerNoOp);
  35.         NoNewOp:=NoNewOp+1;
  36.         SaisNoOp.Text:=IntToStr(NoNewOp);
  37.         end;
  38.    //++++++++++++++++++++++++++++++Enregistrement dans la Table operations
  39.    if DataModule1.SQLQuery1.Active then
  40.       DataModule1.SQLQuery1.Close;
  41.    DataModule1.SQLQuery1.SQL.Clear;
  42.    DataModule1.SQLQuery1.SQL.Add(' INSERT INTO eoperations (IdOp, IdOpTyp, IdSupTyp, RefSup, DateCTP, DateValid, DateSaisie, DateEffet, Observations) ');
  43.    DataModule1.SQLQuery1.SQL.Add(' VALUES (:Chp1i, :Chp2i, :Chp3i, :Chp4s, :Chp5d, :Chp6d, :Chp7d, :Chp8d, :Chp9b); ');
  44.    //Détermination Nature Opération
  45.    for i:=0 To SelNature.Items.Count-1 do
  46.       if(SelNature.Text=FormSaisMvtEmp.SelNature.Items[i]) then
  47.          IdOpTyp:=i;
  48.    //Détermination Id Type Support
  49.    for i:=0 To SelNatSup.Items.Count-1 do
  50.       if(SelNatSup.Text=FormSaisMvtEmp.SelNatSup.Items[i]) then
  51.          IdSupTyp:=i;
  52.    //Parametres de la Requete
  53.    DataModule1.SQLQuery1.ParamByName('Chp1i').AsInteger := NoNewOp;
  54.    DataModule1.SQLQuery1.ParamByName('Chp2i').AsInteger := IdOpTyp;
  55.    DataModule1.SQLQuery1.ParamByName('Chp3i').AsInteger := IdSupTyp;
  56.    DataModule1.SQLQuery1.ParamByName('Chp4s').AsString := SaisNatRef.Text;
  57.    DataModule1.SQLQuery1.ParamByName('Chp5d').AsDate := SelDateCPT.Date;
  58.    DataModule1.SQLQuery1.ParamByName('Chp6d').AsDate := SelDateValid.Date;
  59.    DataModule1.SQLQuery1.ParamByName('Chp7d').AsDate := SelDateSais.Date;
  60.    DataModule1.SQLQuery1.ParamByName('Chp8d').AsDate := SelDateEffet.Date;
  61.    DataModule1.SQLQuery1.ParamByName('Chp9b').AsString := Observations.Text;
  62.    //Exécution de la Requete
  63.    DataModule1.SQLQuery1.ExecSQL;
  64.  
  65.    //++++++++++++++++++++++++++++++Enregistrement dans la Table Historique
  66.    NoNewHistOp:=StrToInt(DerNoHistOp);
  67.    NoNewHistOp:=NoNewHistOp+1;
  68.    if DataModule1.SQLQuery1.Active then
  69.       DataModule1.SQLQuery1.Close;
  70.    DataModule1.SQLQuery1.SQL.Clear;
  71.  
  72.    //DataModule1.SQLQuery1.SQL.Add('INSERT INTO ehistocreaop (IdEvt,IdItem,Date,Heure,Domaine,User,Profil,Table) VALUES (:Chp1i, :Chp2i, :Chp3d, :Chp4t, :Chp5s, :Chp6s, :Chp7s, :Chp8s);');
  73.    DataModule1.SQLQuery1.SQL.Add('INSERT INTO ehistocreaop (IdEvt) VALUES (:Chp1i);');
  74.  
  75.    DataModule1.SQLQuery1.ParamByName('Chp1i').AsInteger := NoNewHistOp;
  76.    //DataModule1.SQLQuery1.ParamByName('Chp2i').AsInteger := NoNewOp;
  77.    //DataModule1.SQLQuery1.ParamByName('Chp3d').AsDate := Date;
  78.    //DataModule1.SQLQuery1.ParamByName('Chp4t').AsString := FormatDateTime('hh:nn:ss', Time);
  79.    //DataModule1.SQLQuery1.ParamByName('Chp5s').AsString := 'DGFIP';
  80.    //DataModule1.SQLQuery1.ParamByName('Chp6s').AsString := UserEnCours;
  81.    //DataModule1.SQLQuery1.ParamByName('Chp7s').AsString := 'AdmTemp';
  82.    //DataModule1.SQLQuery1.ParamByName('Chp8s').AsString := 'eoperations';
  83.  
  84.    try
  85.    DataModule1.SQLQuery1.Active:=false;
  86.    DataModule1.SQLQuery1.ExecSQL;
  87.    //DataModule1.SQLQuery1.Active:=true;
  88.  
  89.    DataModule1.SQLConnection1.Connected := false;
  90.    except
  91.      on e : exception do
  92.      ShowMessage('Erreur: '+e.Message);
  93.    end;
  94.  
  95.    Application.MessageBox('La fiche opération a été prise en compte et enregistrée dans l''historique.', 'Alerte Utilisateur');
  96.  
  97.    end
  98. else
  99.    begin
  100.    Application.MessageBox('Veuillez servir les champs obligatoires!', 'Alerte Utilisateur');
  101.    end
  102. end;

pjtuloup

  • New Member
  • *
  • Posts: 45
Re: Database update not working
« Reply #12 on: October 06, 2024, 03:22:03 pm »
Thanks, dseligo !

I'm going to look into it all.

pjtuloup

  • New Member
  • *
  • Posts: 45
Re: Database update not working
« Reply #13 on: October 06, 2024, 04:09:26 pm »
It worked !

It is strange when you say you don't use transaction because TSQLConnection class of SQLdb components should emit error 'Transaction not set.'.

In fact, I had a SQLTransaction component on my Datamodule but it was not used because I didn't knew how to use it.

I had to set "Action=CaCommit in the objects Inspector (F11) because in the code the line "DataModule1.SQLTransaction1.Action := CaCommit;" caused an error "identifier not found: CaCommit".

But It worked.

I still don't know why it didn't work without Transaction, which I didn't use because I didn't know how to use it; but I'm going to dig into the subject.
And thank you again because without you I was at a dead end and ready to give up definitely.

dseligo

  • Hero Member
  • *****
  • Posts: 1386
Re: Database update not working
« Reply #14 on: October 06, 2024, 11:27:28 pm »
I had to set "Action=CaCommit in the objects Inspector (F11) because in the code the line "DataModule1.SQLTransaction1.Action := CaCommit;" caused an error "identifier not found: CaCommit".

Probably you didn't have 'SQLDB' in your uses. But it is same if you set it in Object inspector.

Quote
I still don't know why it didn't work without Transaction, which I didn't use because I didn't know how to use it; but I'm going to dig into the subject.

You must use TSQLTransaction with SQLdb components. See https://www.freepascal.org/docs-html/fcl/sqldb/tsqltransaction.html and https://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.transaction.html.

They are useful i.e. when you are inserting with multiple statements (like you do it in your program).
You can start transaction in the beginning, and then, if error occurs in the middle or in the end, you can rollback and it will be as if you didn't insert anything.
If there is no error, you will commit and all inserts will be 'confirmed' in the database.

Quote
And thank you again because without you I was at a dead end and ready to give up definitely.

Np, I am glad it worked.

 

TinyPortal © 2005-2018