Recent

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

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: TSQLQuery.ExecSQL not making changes
« Reply #30 on: May 11, 2021, 11:15:41 pm »
Please post the code you have now.
(Your previous code mixed different methods and was incorrect)

Code currently looks 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. (*  SQLTransaction1.StartTransaction();
  12.   Query.Edit;
  13.   if (FrmMode = 'NEW') then
  14.    Query.SQL.Text:='insert into Table1 values (0, :Gender, :GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal)'
  15.   else
  16.     Query.SQL.Text:='update Table1 set ChatLocation=:Gender, :GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal where IDNo=:IDNo'; *)
  17.   setField('Gender', cbxContactLocation.Text);
  18.   setField('GivenName', edtGiven.Text);
  19.   setField('FamilyName', edtFamily.Text);
  20.   setField('Age', StrToInt(trim(medtAge.text)));
  21.   setField('Email', edtEmail.Text);
  22.   SetField('Phone', edtPhone.text);
  23.   SetField('Postal', memPostal.Lines.Text);
  24. //  Query.ExecSQL();
  25.   SQLTransaction1.Commit();
  26.   Close();
  27. end;
  28.  

dseligo

  • Sr. Member
  • ****
  • Posts: 277
Re: TSQLQuery.ExecSQL not making changes
« Reply #31 on: May 11, 2021, 11:41:48 pm »
Try to uncomment 'insert', 'update' and 'ExecSQL' lines.  :)

Code: Pascal  [Select][+][-]
  1. procedure TfrmData.btnSaveClick(Sender: TObject);
  2.       procedure SetField(FieldName: String; Data : String);
  3.       begin
  4.         Query.ParamByName(FieldName).AsString:=Data;
  5.       end;
  6.       procedure SetField(FieldName: String; Data: Integer);
  7.       begin
  8.         Query.ParamByName(FieldName).AsInteger:=Data;
  9.       end;
  10.     begin
  11.       SQLTransaction1.StartTransaction();
  12.     //  Query.Edit; <-- no edit
  13.   Query.Close;
  14.       if (FrmMode = 'NEW') then
  15.        Query.SQL.Text:='insert into Table1 values (0, :Gender, :GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal)'
  16.       else
  17.         Query.SQL.Text:='update Table1 set ChatLocation=:Gender, :GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal where IDNo=:IDNo';
  18.       setField('Gender', cbxContactLocation.Text);
  19.       setField('GivenName', edtGiven.Text);
  20.       setField('FamilyName', edtFamily.Text);
  21.       setField('Age', StrToInt(trim(medtAge.text)));
  22.       setField('Email', edtEmail.Text);
  23.       SetField('Phone', edtPhone.text);
  24.       SetField('Postal', memPostal.Lines.Text);
  25.       if (FrmMode <> 'NEW') then
  26.         SetField('IDNo', ExistingID);
  27.       Query.ExecSQL();
  28.       SQLTransaction1.Commit();
  29.       Close();
  30.     end;
« Last Edit: May 12, 2021, 02:46:08 am by dseligo »

rvk

  • Hero Member
  • *****
  • Posts: 4479
Re: TSQLQuery.ExecSQL not making changes
« Reply #32 on: May 12, 2021, 12:34:03 am »
Yup. Your mixing your methods again.

Where is your Query.Post ???
And where is the assignment of :idno for the update ?

@dseligo, do not use Query.Fieldbyname with a direct insert/update in Query.SQL.Text. It just doesn't work.

@HopefulGuy only use the correct statements with each other.

(following on an open query with a result-set)
Query.Edit or Query.Insert (with a select in sql.text so you have a result dataset)
Query.Fieldbyname
Query.Post
Query.Applyupdates
Query.Transaction.commit (note that the open dataset is closed unless you add something to the options)

or (and this really means OR)

Query.Sql.text with insert or update (with :variablefield as params)
Query.Params.parambyname (with :idno when update)
Query.ExecSql

That's it. Don't mix any of these commands.

Sometimes it is easier to work with an open result-dataset (if you have already opened one for a sbgrid for example). And sometimes it's easier to encase updatimg a record in a separate procedure where you update it in a separate form.

Do note that if you update a record with a separate update, you'll need to reopen any other result-dataset to show the update there too.

« Last Edit: May 12, 2021, 12:37:31 am by rvk »

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: TSQLQuery.ExecSQL not making changes
« Reply #33 on: May 12, 2021, 01:40:57 am »
Try to uncomment 'insert', 'update' and 'ExecSQL' lines.  :)

Just tried. No effect. All three lines are uncommented. Here's the code now (including extra lines I threw in just to ensure I was getting the right values from the form):

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. var testint : integer;
  11.     teststr : string;
  12. begin
  13.   SQLTransaction1.StartTransaction();
  14.   Query.Edit;
  15.   if (FrmMode = 'NEW') then
  16.    Query.SQL.Text:='insert into Table1 values (0, :Gender, :GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal)'
  17.   else
  18.     Query.SQL.Text:='update Table1 set ChatLocation=:Gender, :GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal where IDNo=:IDNo';
  19.   setField('Gender', cbxContactLocation.Text);
  20.   setField('GivenName', edtGiven.Text);
  21.   setField('FamilyName', edtFamily.Text);
  22.   teststr := trim(medtAge.text);
  23.   testint := StrToInt(trim(medtAge.text));
  24.   setField('Age', StrToInt(trim(medtAge.text)));
  25.   setField('Email', edtEmail.Text);
  26.   SetField('Phone', edtPhone.text);
  27.   SetField('Postal', memPostal.Lines.Text);
  28.   Query.ExecSQL();
  29.   SQLTransaction1.Commit();
  30.   Close();
  31. end;
  32.  

dseligo

  • Sr. Member
  • ****
  • Posts: 277
Re: TSQLQuery.ExecSQL not making changes
« Reply #34 on: May 12, 2021, 02:43:01 am »
@dseligo, do not use Query.Fieldbyname with a direct insert/update in Query.SQL.Text. It just doesn't work.

I just copied his example, didn't even noticed that. Instead of FieldByName it should be ParamByName.

dseligo

  • Sr. Member
  • ****
  • Posts: 277
Re: TSQLQuery.ExecSQL not making changes
« Reply #35 on: May 12, 2021, 02:48:14 am »
Try to uncomment 'insert', 'update' and 'ExecSQL' lines.  :)

Just tried. No effect. All three lines are uncommented. Here's the code now (including extra lines I threw in just to ensure I was getting the right values from the form):

I started post as a joke because you had most of DB related stuff commented. I corrected code I posted, try it now.
And you need to provide 'id' if row already exists (in the ExistingID variable).
« Last Edit: May 12, 2021, 02:49:49 am by dseligo »

egsuh

  • Hero Member
  • *****
  • Posts: 751
Re: TSQLQuery.ExecSQL not making changes
« Reply #36 on: May 12, 2021, 03:44:26 am »
Based on your explanations, I think what you need is following codes. Set transaction to auto-commitretaining.

Code: Pascal  [Select][+][-]
  1. procedure TfrmData.btnSaveClick(Sender: TObject);
  2.   procedure SetField(FieldName: String; Data : String);
  3.   begin
  4.     Query.FieldByName(FieldName).AsString:=Data;                // Not query.parambyname
  5.   end;
  6.   procedure SetField(FieldName: String; Data: Integer);
  7.   begin
  8.     Query.FieldByName(FieldName).AsInteger:=Data;
  9.   end;
  10. begin
  11.     //  SQLTransaction1.StartTransaction();
  12.    //  Query.Edit;
  13.   if (FrmMode = 'NEW') then Query.insert   // you need this
  14.      //  Query.SQL.Text:='insert into Table1 values (0, :Gender, :GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal)'
  15.   else Query.Edit;     // and this
  16.    //  Query.SQL.Text:='update Table1 set ChatLocation=:Gender, :GivenName, :FamilyName, :Age, :ImgCount, :Email, :Phone, :Postal where IDNo=:IDNo'; *)
  17.  
  18.   setField('Gender', cbxContactLocation.Text);
  19.   setField('GivenName', edtGiven.Text);
  20.   setField('FamilyName', edtFamily.Text);
  21.   setField('Age', StrToInt(trim(medtAge.text)));
  22.   setField('Email', edtEmail.Text);
  23.   SetField('Phone', edtPhone.text);
  24.   SetField('Postal', memPostal.Lines.Text);
  25.  
  26.    Query.Post;
  27.    Query.ApplyUpdates;
  28.  
  29.    //  Query.ExecSQL();
  30.   // SQLTransaction1.Commit();   // set this to auto-commitretaining
  31.    //  Query.Close();    
  32. end;

Or, you should use  insert/update/delete + parambyname not fieldbyname +  ExecSQL , as rvk pointed out.

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: TSQLQuery.ExecSQL not making changes
« Reply #37 on: May 12, 2021, 03:52:14 am »
Yup. Your mixing your methods again.

Where is your Query.Post ???
And where is the assignment of :idno for the update ?

@dseligo, do not use Query.Fieldbyname with a direct insert/update in Query.SQL.Text. It just doesn't work.

@HopefulGuy only use the correct statements with each other.

(following on an open query with a result-set)
Query.Edit or Query.Insert (with a select in sql.text so you have a result dataset)
Query.Fieldbyname
Query.Post
Query.Applyupdates
Query.Transaction.commit (note that the open dataset is closed unless you add something to the options)

I see. I am getting mixed messages that's why it's not working. I updated it and things look good on that end. Now for a related question. I updated the (incomplete) code to include querying from another table, one with a One-To-Many relationship with Table1 (i.e 1 record in Table1 can have multiple records in Table2 referencing it). That works great for the select, but when I try to update something screwy goes on. Here's what I tried, but I get an error saying the operation cannot be performed on an inactive dataset:

Code: Pascal  [Select][+][-]
  1. procedure TfrmLadyData.btnSaveClick(Sender: TObject);
  2.     procedure SetField(FieldName: String; Data : String);
  3.     begin
  4.       if (trim(data)+' ' = ' ') then
  5.          data := #0;
  6.       Query.FieldByName(FieldName).AsString:=Data;
  7.     end;
  8.     procedure SetField(FieldName: String; Data: Integer);
  9.     begin
  10.       Query.FieldByName(FieldName).AsInteger:=Data;
  11.     end;
  12.   begin
  13.     if (FrmMode = 'NEW') then
  14.     begin
  15.       Query.Insert();
  16.       SetField('IDNo', 0); // Auto-Increment field, so 0 = next available number
  17.       btnSave.Caption:='Add Record';
  18.     end
  19.     else
  20.     begin
  21.       Query.Close();
  22.       Query.SQL.Text := 'select * from Table1 where IDNo = ' + IntToStr(IDNO);
  23.       Query.open();
  24.       Query.Edit();
  25.       btnSave.Caption:='Update Record';
  26.     end;
  27.   setField('Gender', cbxContactLocation.Text);
  28.   setField('GivenName', edtGiven.Text);
  29.   setField('FamilyName', edtFamily.Text);
  30.   setField('Age', StrToInt(trim(medtAge.text)));
  31.   setField('Email', edtEmail.Text);
  32.   SetField('Phone', edtPhone.text);
  33.   SetField('Postal', memPostal.Lines.Text);
  34.   Query.ExecSQL();
  35.   SQLTransaction1.Commit();
  36.   Close();
  37. end;
  38.  

egsuh

  • Hero Member
  • *****
  • Posts: 751
Re: TSQLQuery.ExecSQL not making changes
« Reply #38 on: May 12, 2021, 05:07:21 am »
I know that you have background of BDE. What you are thinking is operations on table. Now you are using TSQLQuery using select statement. TTable or TSQLQuery are descendants of TDataSet. So, when we say dataset, it means a structure that lie behind a table structure.

When you open TSQLQuery using 'select * from table...', then you have a local copy of the records, and what you see in the DBGrid or DBEdit fields, or values from Query.FieldByName('fieldname').AsString are all from this copy, not physical database in the server.

TDataSet.insert, TDataSet.edit, TDataSet.Post, etc. are operations on this local copy, not server file. To apply the changes on this local copy to server file, you have to call TDataSet.ApplyUpdates.

SQL statement  'insert into table..' is different from TDataSet.Insert. This statement operates directly on server database, and has nothing to do with current open dataset.


So in your program, with your open Query, i.e. Dataset, :

1) Query.insert should be done on active dataset.

2) If you want Query.Edit and ApplyUpdates, you have to find the record using Locate, not SQL statement ('select * ...).


Now, to move on to SQL programming,

Use separate TSQLQuery --- I mean drop another TSQLQuery component, and use it for SQL statements of 'insert into table1 values ...' or 'update table1 set ....' etc.

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: TSQLQuery.ExecSQL not making changes
« Reply #39 on: May 12, 2021, 07:36:22 am »
Now, to move on to SQL programming,

Use separate TSQLQuery --- I mean drop another TSQLQuery component, and use it for SQL statements of 'insert into table1 values ...' or 'update table1 set ....' etc.

Okay, so Basically You're indicating that I should have one TSQLQuery for each table I intend to access?

Seems overkill, but okay. So let me see if I have the logic in connecting the components together correct. There are four components:
A: TSQLQuery
B: TSQLTransaction
C: TDataSource
D: TSQLConnector (or decendant)

The links go like this, right?
A.Database := D
B.Database := D
C.Database := D
D.Transaction := B

Is that right?

egsuh

  • Hero Member
  • *****
  • Posts: 751
Re: TSQLQuery.ExecSQL not making changes
« Reply #40 on: May 12, 2021, 07:54:00 am »
You should have followings and hook them in following way.

A.  TSQLConnection
B.  TSQLTransaction   Database=A.
C.  TSQLQuery           Database=A.      for Table1.   SQL.text:  select * from table1 where...
D.  TSQLQuery           Database=A.      for Tabe2.    SQL.text:  select * from table2 where...
E.  TSQLQuery           Database=A.      for update records. SQL.Text :  update or insert into table1 or table2 values...


If you are using Data-aware controls, like TDBGrid, TDBNavigator, TDBEditor, etc., then you need

F.   TDataSource      DataSet is  C or D. 
G.  TDBGrid, TDBNavigator,  TDBEdit          Datasource=F.

rvk

  • Hero Member
  • *****
  • Posts: 4479
Re: TSQLQuery.ExecSQL not making changes
« Reply #41 on: May 12, 2021, 02:15:07 pm »
That works great for the select, but when I try to update something screwy goes on. Here's what I tried, but I get an error saying the operation cannot be performed on an inactive dataset:
About this code:
Code: Pascal  [Select][+][-]
  1. Query.ExecSQL();
  2. SQLTransaction1.Commit();
  3. Close();

You still have ExecSQL with a "select" in SQL.Text.
I already told (twice) that that doesn't mix.

About the last line... the Close(). What does this close? Was it meant to close the entire Form/Application (because it does).
If it was meant to close the dataset it should have been Query.Close();
And then only if you have an Open somewhere.

The Query itself... where is it defined. Do you use it elsewhere (in a DBGrid for example).
This is important. Because if you have a DBGrid with edit fields besides it to edit a record... ALL your code can be much much much much easier if you just use TDBEdits (which someone already suggested).

So we can't judge the current code if we don't have the complete picture.

rvk

  • Hero Member
  • *****
  • Posts: 4479
Re: TSQLQuery.ExecSQL not making changes
« Reply #42 on: May 12, 2021, 08:16:28 pm »
You can almost program an entire address-editing program with zero lines of code if you use TDBEdits and a TDBNavigator  8)

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: TSQLQuery.ExecSQL not making changes
« Reply #43 on: May 13, 2021, 02:41:33 am »
You can almost program an entire address-editing program with zero lines of code if you use TDBEdits and a TDBNavigator  8)

That's a nice idea, but I seem to he having issues. I had to start over because the thumb drive I had the files on died. So I created a new database, inserted a single bogus record for testing, and started rebuilding. Now, on the information form, I have all Data-Aware components, with fields defined and pointing to the dataset, but they are not getting populated.

Also, for some reason, the first call to a no-condition query works fine. But any subsequent calls return NO records. Here's the table:
Code: MySQL  [Select][+][-]
  1.  CREATE TABLE `Contact` (
  2.   `IDNo` int(11) NOT NULL AUTO_INCREMENT,
  3.   `GivenName` varchar(20) NOT NULL,
  4.   `FamilyName` varchar(20) NOT NULL,
  5.   `email` varchar(90) NOT NULL,
  6.   `age` tinyint(3) unsigned NOT NULL,
  7.   `phone` varchar(15) NOT NULL,
  8.   `postal` text NOT NULL,
  9.   `notes` text NOT NULL,
  10.   PRIMARY KEY (`IDNo`),
  11.   UNIQUE KEY `Names` (`GivenName`,`FamilyName`)
  12.  

Here's the one record:
Code: MySQL  [Select][+][-]
  1. +------+-----------+------------+---------------+------+----------------+--------+-------+--------+
  2. | IDNo | GivenName | FamilyName | email         | age  | phone          | postal | notes | gender |
  3. +------+-----------+------------+---------------+------+----------------+--------+-------+--------+
  4. |    1 | Jane      | Doe        | spam@spam.com |   19 | +0000000000000 |        |       | Female |
  5. +------+-----------+------------+---------------+------+----------------+--------+-------+--------+
  6.  

The query has a hard coded SQL (i.e. defined in the lfm file, not in the pas) of a simple query: select * from Contact order by IDNo;
Here's the code that is SUPPOSED to correctly repopulate a ComboBox:
Code: Pascal  [Select][+][-]
  1. procedure TfrmList.FormUpdate;
  2. var sCBXEntry : string;
  3.     intListCount : integer;
  4.     strSQLExecuted : string;
  5. begin
  6.      cbxContactList.items.Clear;
  7.      cbxContactList.ItemIndex:=-1;
  8.   try
  9.     if not (connection.Connected) then
  10.        connection.Connected:=true; // hope thatworks
  11.        try
  12.          ReadQuery.close(); // in order to force the Query to re-request info from the database engine;
  13.        except
  14.        on E:Exception do
  15.          MessageDlg('BUG?', 'Possible BUG encountered. Exception Message: ' + E.Message, mtWarning, [mbOk], 0);
  16.        end;
  17.        strSQlExecuted:=Query.SQL.Text;
  18.        ReadQuery.Open();
  19.      while not (query.EOF) do
  20.      begin
  21.        sCBXEntry := Query.FieldByName('IDNo').AsString + ' - ' + Query.FieldByName('GivenName').AsString + ' ' + Query.FieldByName('FamilyName').AsString;
  22.        cbxContactList.Items.Add(sCBXEntry);
  23.        ReadQuery.Next();
  24.      end;
  25.      intListCount:=cbxContactList.Items.Count;
  26.     if (cbxContactList.Items.Count = 0) then
  27.      begin
  28.        cbxContactList.Enabled := false;
  29.        cbxContactList.Text:='[NO ENTRIES]';
  30.      end
  31.      else
  32.       cbxContactList.ItemIndex:=0;
  33.   except
  34.     on E:Exception do
  35.     begin
  36.        MessageDlg('Unkown error', 'An unexpected error occured within the program. The message was: ' + E.Message + '. Execution terminated.', mtError, [mbClose], 0);
  37.        halt(1);
  38.     end;
  39.   end;
  40. end;
  41.  

From my understanding, this should work. But it's not working AFTER the first. The first time, no problems. But if I go into another form and then back to this one and re-call FormUpdate() then the EOF is set to true BEFORE reading the first record.

What am I doing wrong?

egsuh

  • Hero Member
  • *****
  • Posts: 751
Re: TSQLQuery.ExecSQL not making changes
« Reply #44 on: May 13, 2021, 03:11:02 am »
1.  With your program, try inserting

      ReadQuery.First;

    just after Requery.Open; . Not sure for now whether this will solve the problem.

2.  You can define calculated field for sCBXEntry.

3.  Then, you don't have to populate combobox manually as you did. There is a TDBComboBox component.

Before you try combobox, just drop a DBGrid and a DataSource on the form, and set DBGrid's datasource property to the DataSource, and DataSource's DataSet to ReadQuery. And see whether the query result is displayed correctly whenever you open ReadQuery.

 

TinyPortal © 2005-2018