MySQL server/client application. Newly inserted records are not
displayed
lazarus / 16-Databases

----------------------------------------------------------------------
Omirbay · 2016-10-13 04:24

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 · 2016-10-13 04:26

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

----------------------------------------------------------------------
mangakissa · 2016-10-13 06:55

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

----------------------------------------------------------------------
fpcoder · 2016-10-14 06:40

Instead of

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Query.Refresh;
Try:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Query.Close;Query.Open; 
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 · 2016-10-18 09:47

But refresh is the same as close/open.

----------------------------------------------------------------------
mirce.vladimirov · 2016-10-18 11:09

When making changes to database tables always do this :
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
query.execsql;SQLTransaction1.commitretaining; // just "commit" will
reset your transaction and you don't want that 
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

----------------------------------------------------------------------
fpcoder · 2016-10-18 21:38

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

Quote from: mangakissa on October 18, 2016, 11:47:47 amBut refresh is
the same as close/open.

----------------------------------------------------------------------
Omirbay · 2016-10-21 07:51

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 · 2016-10-21 14:00

Quote from: mangakissa on October 18, 2016, 11:47:47 amBut 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.

Quote from: Omirbay on October 21, 2016, 09:51:22 amI 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 · 2016-10-22 16:23

Quote from: goodname on October 21, 2016, 04:00:01 pmLooking 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 · 2016-10-24 13:03

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.