Recent

Author Topic: TSQLQuery.ExecSQL not making changes  (Read 8212 times)

HopefulGuy

  • New Member
  • *
  • Posts: 28
TSQLQuery.ExecSQL not making changes
« on: May 10, 2021, 06:51:58 am »
Okay, I'm sure I'm just missing something here. But I've read the docs and am at a loss.
I'm trying to make a form that displays a MariaDB record, and if I change the form and press the SAVE button it writes everything back to the record. So I tried something like this:
Code: Pascal  [Select][+][-]
  1. Procedure TfrmData.btnSaveClick(Sender: TObject);
  2.   procedure SetField(FieldName: String; Data : String);
  3.   begin
  4.     Query.FieldByName(FieldName).AsString:=Data;
  5.   end;
  6.   procedure SetField(FieldName: String; Data: Integer);
  7.   begin
  8.     Query.FieldByName(FieldName).AsInteger:=Data;
  9.   end;
  10. begin
  11.   Query.Edit;
  12.   if (FrmMode = 'NEW') then
  13.     Query.SQL.Text:='inert into Table1 values (0, :Gender, :GivenName, :FamilyName, :Age,  :Email,  :Phone, :Postal)'
  14.   else
  15.   begin
  16.     Query.SQL.Text:='update Table1 set Gender=:Gender GivenName=:GivenName, FamilyName:=FamilyName, Age=:Age, , Email=:Email, Phone=:Phone, Postal=:Postal where IDNo=:IDNo';
  17.     SetField('IDNo', IDNo);
  18.   end;
  19.   setField('Gender', cbxGender.Text);
  20.   setField('GivenName', edtGiven.Text);
  21.   setField('FamilyName', edtFamily.Text);
  22.   setField('Age', StrToInt(trim(medtAge.text)));
  23.   setField('Email', edtEmail.Text);
  24.   SetField('Phone', edtPhone.text);
  25.   SetField('Postal', memPostal.Lines.Text);
  26.   Query.ExecSQL();
  27.   Query.Close();
  28. end;
  29.  
But when I go back and look at the actual database after I run this procedure, NOTHING HAS CHANGED.   Can anyone see what I did wrong?
« Last Edit: May 13, 2021, 07:11:56 am by HopefulGuy »

Gustavo 'Gus' Carreno

  • Hero Member
  • *****
  • Posts: 593
  • Professional amateur ;-P
Re: TSQLQuery.ExecSQL not making changes
« Reply #1 on: May 10, 2021, 07:21:36 am »
Hey HopefulGuy,

Could I, very politely, ask for you to use the code tags when putting code examples here?

Apart from the syntax highlighting, if and when you publish a bigger example, it will create a scrollable box that will be easier for anyone looking at your code AND your message at the same time.

Thank you very much in advance.

Cheers,
Gus
Lazarus 2.3.0(trunk) FPC 3.3.1(trunk) Ubuntu 21.04 64b Dark Theme
Lazarus 2.0.12(stable) FPC 3.2.2(stable) Ubuntu 21.04 64b Dark Theme
http://github.com/gcarreno

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: TSQLQuery.ExecSQL not making changes
« Reply #2 on: May 10, 2021, 07:26:45 am »
Hopefully that fixed things for you.   :)

Gustavo 'Gus' Carreno

  • Hero Member
  • *****
  • Posts: 593
  • Professional amateur ;-P
Re: TSQLQuery.ExecSQL not making changes
« Reply #3 on: May 10, 2021, 07:33:11 am »
Hey HopefulGuy,

Hopefully that fixed things for you.   :)

Yes it did and thank you very much !!!!

Cheers,
Gus
Lazarus 2.3.0(trunk) FPC 3.3.1(trunk) Ubuntu 21.04 64b Dark Theme
Lazarus 2.0.12(stable) FPC 3.2.2(stable) Ubuntu 21.04 64b Dark Theme
http://github.com/gcarreno

Gustavo 'Gus' Carreno

  • Hero Member
  • *****
  • Posts: 593
  • Professional amateur ;-P
Re: TSQLQuery.ExecSQL not making changes
« Reply #4 on: May 10, 2021, 07:38:55 am »
Hey HopefulGuy,

After looking at your code I have a question:

Why do you start your code with the variable Query and put it on Edit mode, but then use AsianLadiesQuery to execute the SQL?

Is this a copy/paste error?

Cheers,
Gus
Lazarus 2.3.0(trunk) FPC 3.3.1(trunk) Ubuntu 21.04 64b Dark Theme
Lazarus 2.0.12(stable) FPC 3.2.2(stable) Ubuntu 21.04 64b Dark Theme
http://github.com/gcarreno

rvk

  • Hero Member
  • *****
  • Posts: 4479
Re: TSQLQuery.ExecSQL not making changes
« Reply #5 on: May 10, 2021, 07:41:25 am »
Just looking at your code I see multiple errors.

Your insert statement reads INERT.
And in your update statement you are missing a comma after :gender.
These errors should have given you an exception when executed them.

If these are typos.... Then please don't just edit your code because it can create these typos.
Post actual code.

Then another observation... You fill in Query.
But you actually perform the ExecSQL on a AsianLadiesQuery.
AsianLadiesQuery has nothing to do with Query so you actually never perform the update or insert.

And... You can't set Query.SQL.Text when you are in browse or edit mode (and you are with Query.Edit in edit mode).


HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: TSQLQuery.ExecSQL not making changes
« Reply #6 on: May 10, 2021, 07:44:09 am »
I'm coming from a BDE perspective, so perhaps I'm doing it wrong.  But before I put the Edit in there, when I tried to assign the fields it gave me an error about the database not being in edit mode. So I inserted the Edit statement. As for the Query, That part was a copy/paste error. AsianGalsQuery was replaced with Query when I expanded the database. I've made edits to the code shown here to fix that.

rvk

  • Hero Member
  • *****
  • Posts: 4479
Re: TSQLQuery.ExecSQL not making changes
« Reply #7 on: May 10, 2021, 07:51:54 am »
You still have invalid SQL statement (INERT and that missing comma after :gender).

Second... You can't use statements like insert and update an a dataset. If you use Query.SQL.Edit and Query.Findbyfield, you can't use insert/update and ExelSQL. You just use Query.Edit and Query.Post in conbination with Query.Findbyfield.

If you use INSERT/UPDATE in SQL.Text you'll need to use Query.Parambyname to fill in the parameter fields.

Now you are mixing two methods if updating (Query.Edit and Query.ExelSql) and you can't do that.

https://wiki.freepascal.org/Working_With_TSQLQuery
« Last Edit: May 10, 2021, 07:54:44 am by rvk »

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: TSQLQuery.ExecSQL not making changes
« Reply #8 on: May 10, 2021, 07:54:18 am »
Just looking at your code I see multiple errors.

Your insert statement reads INERT.

And in your update statement you are missing a comma after :gender.
These errors should have given you an exception when executed them.

Oops. Thanks for pointing those out. They are both fixed now.

Quote
If these are typos.... Then please don't just edit your code because it can create these typos.
Post actual code.

Then another observation... You fill in Query.
But you actually perform the ExecSQL on a AsianLadiesQuery.
AsianLadiesQuery has nothing to do with Query so you actually never perform the update or insert.

Mis-Copy, see above. Didn't realize I had two versions of the file open and accidentally copied part from new file some from old file. In the new file, AsianLadiesQuery has been completely replaced by Query.
Quote
And... You can't set Query.SQL.Text when you are in browse or edit mode (and you are with Query.Edit in edit mode).
Interesting. It doesn't throw any errors. I have just changed the code ever so slightly. Observe this section now and see if you think it will work better:
Code: Pascal  [Select][+][-]
  1. begin
  2.   if (FrmMode = 'NEW') then
  3.   begin
  4.     Query.SQL.Text:='insert into Table1 values (0, :Gender, :GivenName, :FamilyName, :Age,  :Email,  :Phone, :Postal)';
  5.     AsianLadiesQuery.Edit;
  6.   end
  7.   else
  8.   begin
  9.     Query.SQL.Text:=update Table1 set Gender=:Gender GivenName=:GivenName, FamilyName:=FamilyName, Age=:Age, Email=:Email, Phone=:Phone, Postal=:Postal where IDNo=:IDNo'
  10.    AsianLadiesQuery.Edit;
  11.    SetField('IDNo', LadyIDNo);
  12.  end;
  13.  setField('GivenName', edtGiven.Text);
  14.  
« Last Edit: May 10, 2021, 07:59:01 am by HopefulGuy »

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: TSQLQuery.ExecSQL not making changes
« Reply #9 on: May 10, 2021, 07:58:19 am »
Second... You can't use statements like insert and update an a dataset. If you use Query.SQL.Edit and Query.Findbyfield, you can't use insert/update and ExelSQL. You just use Query.Edit and Query.Post in conbination with Query.Findbyfield.

If you use INSERT/UPDATE in SQL.Text you'll need to use Query.Parambyname to fill in the parameter fields.

Now you are mixing two methods if updating (Query.Edit and Query.ExelSql) and you can't do that.

https://wiki.freepascal.org/Working_With_TSQLQuery
I will look again. I did read that and kind of got lost. Maybe because of my BDE perspective.

Anyway, I cleaned up the code. This is now what shows, copied directly from the window. There may be errors yet because I haven't tried recompiling yet. So stupid syntax errors are a possibility.
Code: Pascal  [Select][+][-]
  1. procedure TfrmData.btnSaveClick(Sender: TObject);
  2.   procedure SetField(FieldName: String; Data : String);
  3.   begin
  4.     Query.Params.ParamByName(FieldName).AsString:=Data;
  5.   end;
  6.   procedure SetField(FieldName: String; Data: Integer);
  7.   begin
  8.     Query.Params.ParamByName(FieldName).AsInteger:=Data;
  9.   end;
  10. begin
  11.   if (FrmMode = 'NEW') then
  12.   begin
  13.     Query.SQL.Text:='insert into Table1 values (0, :GivenName, :FamilyName, :Age, :Country, :Email,  :Phone, :Postal)';
  14. //    Query.Edit;
  15.   end
  16.   else
  17.   begin
  18.     Query.SQL.Text:='update Table1 set GivenName=:GivenName, FamilyName:=FamilyName, Age=:Age, Country=:Country, Email=:Email, Phone=:Phone, Postal=:Postal, where IDNo=:IDNo';
  19. //    Query.Edit;
  20.     SetField('IDNo', IDNo);
  21.   end;
  22.   setField('GivenName', edtGiven.Text);
  23.   setField('FamilyName', edtFamily.Text);
  24.   setField('Age', StrToInt(trim(medtAge.text)));
  25.   SetField('Country', cbxCountry.Text);
  26.   setField('Email', edtEmail.Text);
  27.   SetField('Phone', edtPhone.text);
  28.   SetField('Postal', memPostal.Lines.Text);
  29.   Query.ExecSQL();
  30.   Query.Close();
  31.   Close();
  32. end;
  33.  
« Last Edit: May 10, 2021, 08:09:25 am by HopefulGuy »

rvk

  • Hero Member
  • *****
  • Posts: 4479
Re: TSQLQuery.ExecSQL not making changes
« Reply #10 on: May 10, 2021, 08:00:46 am »
You're still have that AsianLadiesQuery in there and you fill in Query.

Don't use INSERT and UPDATE with Query.Edit.
They don't mix.
Use INSERT/UPDATE with Query.Parambyname and Query.ExelSQL
OR
Use Query.Edit with Query.Findbyname and Query.Post.
But don't mix them.

Please copy and paste actual code because you're still missing the comma after :gender and mussing quotes around the statement.

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: TSQLQuery.ExecSQL not making changes
« Reply #11 on: May 10, 2021, 08:20:20 am »
Re-read the code. I fixed it but when I tried to post my message it said you had posted a message. So, the code shown in #9 is correct.

rvk

  • Hero Member
  • *****
  • Posts: 4479
Re: TSQLQuery.ExecSQL not making changes
« Reply #12 on: May 10, 2021, 09:00:14 am »
Yes, that looks better.
Final note... You shouldn't use Query.Close() on a ExecSQL.

With SELECT you get a result-dataset and you do need Open/Close.
With UPDATE you don't get a result-dataset and you only use ExecSQL and no Open/Close.



Zvoni

  • Hero Member
  • *****
  • Posts: 627
Re: TSQLQuery.ExecSQL not making changes
« Reply #13 on: May 10, 2021, 11:12:37 am »
Not knowing, what connection-parameters are set, i'm missing the Transaction.Commit after an UPDATE/INSERT
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircraft

egsuh

  • Hero Member
  • *****
  • Posts: 785
Re: TSQLQuery.ExecSQL not making changes
« Reply #14 on: May 10, 2021, 11:15:24 am »
You have to check whether the errors come from DB server or Pascal. Following statement still has error. You have written

FamilyName := FamilyName,  which should be FamilyName = :FamilyName

Code: Pascal  [Select][+][-]
  1. Query.SQL.Text:='update Table1 set GivenName=:GivenName, FamilyName:=FamilyName, Age=:Age, Country=:Country, Email=:Email, Phone=:Phone, Postal=:Postal, where IDNo=:IDNo';

And there should not be a comma (,) before where.

Please run your code and look at the error report whether it is SQL systax error or Free Pascal Execution error. SQL Syntax error will raise Pascal Execution error.

 

TinyPortal © 2005-2018