Recent

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

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: TSQLQuery.ExecSQL not making changes
« Reply #15 on: May 10, 2021, 07:15:27 pm »
Not knowing, what connection-parameters are set, i'm missing the Transaction.Commit after an UPDATE/INSERT

Do I need to use a transaction? I didn't have a transaction.start or anything. I don't see the point of a transaction when this is never going to be multi-user/multi-access.

Zvoni

  • Hero Member
  • *****
  • Posts: 596
Re: TSQLQuery.ExecSQL not making changes
« Reply #16 on: May 10, 2021, 07:53:16 pm »
IIRC from another thread, a Transaction is mandatory
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

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: TSQLQuery.ExecSQL not making changes
« Reply #17 on: May 10, 2021, 08:13:39 pm »
It's still not working. I added a commit line (SQLTransaction1.Commit(); ) , I ran the program. It reported no errors at all. But when I look at the database, the fields I changed in the program are unchanged on disk. So what am I doing wrong now?
« Last Edit: May 10, 2021, 08:56:17 pm by HopefulGuy »

af0815

  • Hero Member
  • *****
  • Posts: 678
Re: TSQLQuery.ExecSQL not making changes
« Reply #18 on: May 10, 2021, 09:15:22 pm »
I use often  aQuery.Options := [sqoAutoApplyUpdates,sqoAutoCommit] , if not i have to apply the updates and do the transaction in the correct position by myself.
regards
Andreas

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: TSQLQuery.ExecSQL not making changes
« Reply #19 on: May 10, 2021, 11:08:05 pm »
I use often  aQuery.Options := [sqoAutoApplyUpdates,sqoAutoCommit] , if not i have to apply the updates and do the transaction in the correct position by myself.

Interesting. And how does that work? I'm still learning the database engine, so forgive me for potentially stupid questions. If I set the query options to autocommit and autoapplyupdates do I just say query.FieldByName('Postal').AsString := 'This is my address' or something?

IMHO the documentation for the SQL engine needs to be rewriten and expanded. What's there is good, but it's hard for me to find anything.

I tried this, and it's still not working. I have the query set for AutoApplyUpdates and AutoCommit. But when I call Query.FieldByName('age').AsInteger := strtoint(trimmedtAge.Text)) nothing gets updated. No errors are thrown, everything SEEMS to be working well. But all updates are discarded.
« Last Edit: May 11, 2021, 01:07:49 am by HopefulGuy »

egsuh

  • Hero Member
  • *****
  • Posts: 740
Re: TSQLQuery.ExecSQL not making changes
« Reply #20 on: May 11, 2021, 05:34:19 am »
Transaction works as temporary storage of changes to the database. When you change database with insert, update or delete, the changes are stored in the temporary memory, and saved to the physical media when transaction is committed or discarded if transaction is rollbacked.

So if you are using TSQLConnection then you should have a transaction linked to the database.

The general process is in the following order. You can set the transaction as autocommit but I'll write them down so that you can understand the concept and process.

Code: Pascal  [Select][+][-]
  1. Query1.Close;
  2. Query1.SQL.Text := 'insert into table1 values (:v1, :v2, ...);';
  3. if not Transaction.Active then Transaction.StartTransaction;   // Transaction := Active does the same thing.
  4. Query1.Prepare;
  5. Query1.ParamByName('v1').AsString:= string1;
  6. Query1.ParamByName('v2').AsString:= string2;
  7. // ...................
  8. Query1.ExecSQL;  
  9.  
  10. Transaction.Commit; // permanently store changes since the start of transaction. or transaction.rollback to discard the changes
  11. Query1.Close; // this is not necessary, but anyway conceptually.
  12.  

You may not explicitly start and commit transaction by setting them automatically do them, but anyway this is the flow of SQL operation.

And Query.FieldByName('Postal').AsString := 'my address' will change the local dataset of Query (which is a copy of whole table, in your PC), and do not change the record in the server. To do that, you have to call ApplyUpdate, etc. But your examples are not related with this. You are using SQL statements.  You should write Query.ParamByName('Postal').AsString, instead.


af0815

  • Hero Member
  • *****
  • Posts: 678
Re: TSQLQuery.ExecSQL not making changes
« Reply #21 on: May 11, 2021, 09:31:41 am »
Is the Data tranfered from the local Buffer to the DB-Server ? I did not found any ApplyUpdates ? See https://wiki.freepascal.org/Working_With_TSQLQuery chapter Cached Updates. This is the reason for sqoAutoApplyUpdates in the queryoptions.

sqoAutoCommit is for automatic tranactionhandling. If you have no special code for not using this, it is the easiest way. You need transaction handling manual, if you must make safe if eg. in one table the data is deleted and inserted in another table. With the transaction you can say: "All or nothing". So you can avoid inconsistence of data.
regards
Andreas

Zvoni

  • Hero Member
  • *****
  • Posts: 596
Re: TSQLQuery.ExecSQL not making changes
« Reply #22 on: May 11, 2021, 09:37:25 am »
It's still not working. I added a commit line (SQLTransaction1.Commit(); ) , I ran the program. It reported no errors at all. But when I look at the database, the fields I changed in the program are unchanged on disk. So what am I doing wrong now?
And your Transaction is correctly linked to the Connection and Query?
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: 740
Re: TSQLQuery.ExecSQL not making changes
« Reply #23 on: May 11, 2021, 11:32:43 am »
Quote
Is the Data tranfered from the local Buffer to the DB-Server ? I did not found any ApplyUpdates ? See https://wiki.freepascal.org/Working_With_TSQLQuery chapter Cached Updates. This is the reason for sqoAutoApplyUpdates in the queryoptions.

There are methods of TCustomBufDataSet.ApplyUpdates and TCustomSQLQuery.ApplyUpdates, which are ancestors of TSQLQuery.

This is used when you open TSQLDataset using select SQL statements, do some modifications to it (using TDataSet's edit, insert, post, etc.), and ApplyUpdates will save the changes. Primary key or key fields must be pre-defined. I have used this, but not frequently, and do not know exactly how sqoAutoApplyUpdates, etc. operate. I prefer to use SQL statement updateorinsert, using matching fields.

af0815

  • Hero Member
  • *****
  • Posts: 678
Re: TSQLQuery.ExecSQL not making changes
« Reply #24 on: May 11, 2021, 01:14:10 pm »
Back to the roots. Have your table a PK (Primary Key) ? Is this key listed in the select statement ?

If you have a good, simple Select-Statement without joins, Query is able to autocreate the nessesary insert, update, delete messages by itself.

You mus only set the dataset in the correct state. For a simple test, put a connetion, transaction, query, dataset, dbgrid and a dbnavigator on a form. Connect these components together and set a select statement in the query. In the designer, if you make the connection and the query active you will see the actual data in the db. Make it inactive. Now make a button and set in the click the connection and query active. Make a second button and place there the applyupdates an make the connection inactive. Now run your program. You should able to browse, edit, delete insert data with the navigator and the grid.

If you close the programm and reopen, your changes should be saved. If this work you have the basics.

regards
Andreas

Zvoni

  • Hero Member
  • *****
  • Posts: 596
Re: TSQLQuery.ExecSQL not making changes
« Reply #25 on: May 11, 2021, 03:57:58 pm »
Back to the roots. Have your table a PK (Primary Key) ? Is this key listed in the select statement ?

If you have a good, simple Select-Statement without joins, Query is able to autocreate the nessesary insert, update, delete messages by itself.

You mus only set the dataset in the correct state. For a simple test, put a connetion, transaction, query, dataset, dbgrid and a dbnavigator on a form. Connect these components together and set a select statement in the query. In the designer, if you make the connection and the query active you will see the actual data in the db. Make it inactive. Now make a button and set in the click the connection and query active. Make a second button and place there the applyupdates an make the connection inactive. Now run your program. You should able to browse, edit, delete insert data with the navigator and the grid.

If you close the programm and reopen, your changes should be saved. If this work you have the basics.

DON'T! Your Lazarus will go KABOOM!
It's a Bug, and fixed in Trunk

See here: https://forum.lazarus.freepascal.org/index.php/topic,53653.msg397188.html#msg397188
« Last Edit: May 11, 2021, 04:01:29 pm by Zvoni »
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

af0815

  • Hero Member
  • *****
  • Posts: 678
Re: TSQLQuery.ExecSQL not making changes
« Reply #26 on: May 11, 2021, 05:01:53 pm »
Ok, my Lazarus with mssqldb driver did not know about the Kaboom error. So save early, save often :-)
regards
Andreas

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: TSQLQuery.ExecSQL not making changes
« Reply #27 on: May 11, 2021, 06:11:48 pm »
It's still not working. I added a commit line (SQLTransaction1.Commit(); ) , I ran the program. It reported no errors at all. But when I look at the database, the fields I changed in the program are unchanged on disk. So what am I doing wrong now?
And your Transaction is correctly linked to the Connection and Query?

Yes, I have a Query object that is linked to the Tansaction item. The Connection and DataSource are present on the parent form. The data is Read in just fine. It's assigned the proper fields on the form and shows up fine. But if I change something and hit the save button, it goes through all the assignments, and nothing happens. I even have a Transaction.Commit at the end, and nothing. I believe I don't need the Query.ExecSQL

af0815

  • Hero Member
  • *****
  • Posts: 678
Re: TSQLQuery.ExecSQL not making changes
« Reply #28 on: May 11, 2021, 07:09:53 pm »
ExecSQl is only for insert, update and delete statements meaningfull, For a normal query with select as base and autocreated insert, delet and update stemants you should only use open (or active).
regards
Andreas

rvk

  • Hero Member
  • *****
  • Posts: 4479
Re: TSQLQuery.ExecSQL not making changes
« Reply #29 on: May 11, 2021, 07:16:10 pm »
I even have a Transaction.Commit at the end, and nothing. I believe I don't need the Query.ExecSQL
Please post the code you have now.
(Your previous code mixed different methods and was incorrect)

 

TinyPortal © 2005-2018