Recent

Author Topic: MySQL server/client application. Newly inserted records are not displayed  (Read 5267 times)

Omirbay

  • New Member
  • *
  • Posts: 12
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

  • New Member
  • *
  • Posts: 12
I forgot to mention my
lazarus version is 1.6. My Pc is running on windows 8.1 x32 OS.

mangakissa

  • Hero Member
  • *****
  • Posts: 1113
If you are using SQLdb the transaction must be updated (commit/commitretaining). Also applyupdates is necessary.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

kapibara

  • Hero Member
  • *****
  • Posts: 532
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.
Lazarus trunk / fpc 3.0.4 / Debian 10 - 64 bit

mangakissa

  • Hero Member
  • *****
  • Posts: 1113
But refresh is the same as close/open.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

mirce.vladimirov

  • Full Member
  • ***
  • Posts: 232
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

  • Hero Member
  • *****
  • Posts: 532
@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.
Lazarus trunk / fpc 3.0.4 / Debian 10 - 64 bit

Omirbay

  • New Member
  • *
  • Posts: 12
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

  • Sr. Member
  • ****
  • Posts: 297
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

  • Hero Member
  • *****
  • Posts: 618
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

  • Sr. Member
  • ****
  • Posts: 297
Re: MySQL server/client application. Newly inserted records are not displayed
« Reply #10 on: October 24, 2016, 03:03:20 pm »
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.  8)

 

TinyPortal © 2005-2018