MySQL server/client application. Newly inserted records are not displayed

[Omirbay]

Hello public!

Recently, I was posting "MySQL lazarus".

Now, I got some progress in achieving my aim.

Goal: I am creating a program that connects to the server and works with the database in the server; # of users are set.

Server: MySQL server is applied as a server machine in one of the computers.

Application : A simple program that connects to the mysql server installed in a computer.

Problem Once program is executed, users are updating and inserting the records in the database, and these records, newly-inserted ones, are not displayed in the dbgrid unless the program is closed and opened again.

I am using the following code to refresh. however, it is not working 

Code: [Select]

Query.refresh;
 dbgrid.refresh;

HELP!

[Omirbay]

I forgot to mention my
lazarus version is 1.6. My Pc is running on windows 8.1 x32 OS.

[mangakissa]

If you are using SQLdb the transaction must be updated (commit/commitretaining). Also applyupdates is necessary.

[kapibara]

Instead of

Code: Pascal  [Select][+][-]

1. Query.Refresh;

Try:

Code: Pascal  [Select][+][-]

1. Query.Close;
2. Query.Open;
3.  

I don't know why Refresh doesn't "take", but the data is written to db since you see it when you restart the application.

[mangakissa]

But refresh is the same as close/open.

[mirce.vladimirov]

When making changes to database tables always do this :

Code: Pascal  [Select][+][-]

1. query.execsql;
2. SQLTransaction1.commitretaining; // just "commit" will reset your transaction and you don't want that
3.  

And after that the change is available to all users.
See more details at: http://www.freepascal.org/docs-html/3.0.0/fcl/sqldb/tsqltransaction.commitretaining.html

[kapibara]

@mangakissa You are right, and it sounds silly, but it has happened to me that Refresh didn't refresh but Close/Open did.

But refresh is the same as close/open.

[Omirbay]

Hi guys,

I tried transaction.commit and it works quite well. I just need to test that program one more again and fix bugs if any.
Thank you so much. As I am new to this forum, can anybody help me understand how to choose a reply that solves the problem.

With respect,
Omirbay..))

[goodname]

But refresh is the same as close/open.

If refresh is really the same as close/open then there is likely a long term bug in refresh as I have had the same issue. That makes three of us reporting the same thing. Suppose it was not reported before now as just doing close/open was a obvious work around. Looking at TSQLQuery options there is sqoRefreshUsingSelect option which may be related to this.

I tried transaction.commit and it works quite well.

You should be aware the commit will close the transaction and the data set unless the TSQLQuery sqlKeepOpenOnCommit is selected. If selected then commit will close the transaction and leave the data set open. I recommend using this option with commit whenever possible as commitRetaining creates long running idle transactions on the database engine.

[LacaK]

Looking at TSQLQuery options there is sqoRefreshUsingSelect option which may be related to this.

This is related to record refresh not to dataset refresh.
It controls how record is refreshed when applied record change to database (on ApplyUpdates)

[goodname]

So sqoRefreshUsingSelect is used for retrieving data for fields that are automatically filled in or changed by the database engine on insert or update events.
This would include default field values filled in on insert such as timestamp fields.  Thanks LacaK.