Strange behavior of SQLQuery since I updated Lazarus [solved] lazarus / 16-Databases ---------------------------------------------------------------------- OldMan · 2014-09-28 07:01 Hi! I am programming  on a big project consisting of two Parts. They use a MySQL 5.1 Database. Since I made the update to Lazarus 1.2.4 r45510 FPC 2.6.4 I had to add a commit statement to each SQLQuery.Execute, because the autocommit wasn't implemented any more. Now it works again, but there is an error furthermore: When one part of the project (lets call it program A) is writing data to the database and the other part (program B) is reading via a SELECT Statement the new data are not read by program B until program B is closed and started again. What am I doing wrong? Greets Chris ---------------------------------------------------------------------- taazz · 2014-09-28 08:11 You must explicitly call the commit or commitretaining methods in order to save the data and of course you need to refresh or reopen the dataset from the reading application to see the changes after the commit call was made. ---------------------------------------------------------------------- OldMan · 2014-09-28 08:52 Thanks for reply! Is it not enough to open and close the SQLQuery each time when i'm reading data from the DB? Until updating it worked very well in this manner. I use the SQL-Server just as a dumb Storage and keep the "intelligence" within my program, because I'm not very skilled in SQL. How is the standard way to refresh the dataset? ---------------------------------------------------------------------- taazz · 2014-09-28 09:08 Quote from: OldMan on September 28, 2014, 10:52:25 am Is it not enough to open and close the SQLQuery each time when i'm reading data from the DB? Yes that is considered a refresh if it is done from Application B after Application A has called commit then you should see the new data by default. If you do not see them, then check the transaction settings on application B. It must be set to read committed depending on the server the name might change the idea behind it is the same. If everything else fails create a small application or pair of applications with the a sample database and attach it to a message to take a look. Don't forget to mention the sql server you are using. ---------------------------------------------------------------------- OldMan · 2014-09-28 09:22 I can't find such a setting for this transaction by far. Here is a pair of programs (lesen, schreiben) which shows the behavior (at least on my machine): SQLTest2.zip My server is mysql 5.1 running on a machine named klein. there is a database "testdb" with a table testtab consisting of the fields id (int, autoincrement) and text (varchar 20) the username and password have to be entered directly in the objectinspector. When program schreiben and program lesen are both running and you use "schreiben" for adding data to the db, you can see data with an SQL- Client (i.e. HeidiSQL) immediatly. But the program lesen will load it only after closing and restart. thank you for your help. ---------------------------------------------------------------------- Jkey · 2014-09-28 12:42 I confirm the issue with your code. You should insert the SQLTransaction commands (Start & Commit) to the lesen application too: Code: [Select] SQLTransaction1.StartTransaction;   // Start the transaction  ListBox1.Clear;  SQLQuery1.SQL.Text:='SELECT text FROM testtab';  SQLQuery1.Open;  while not SQLQuery1.EOF do  begin    ListBox1.Items.Add(SQLQuery1.Fields[0].AsString);    SQLQuery1.Next;  end;  SQLQuery1.Close;  SQLTransaction1.Commit;   // Finish the transaction  After inserting these two lines, the issue vanished, and table is displayed correctly in your lesen application, after the button click. Tested under Linux x64, TMySQL55Connection. ---------------------------------------------------------------------- OldMan · 2014-09-28 13:00 Quote from: Jkey on September 28, 2014, 02:42:17 pmI confirm the issue with your code. After inserting these two lines, the issue vanished, and table is displayed correctly in your lesen application, after the button click. It works with your additional Lines correctly here, too. Many thanks for finding this out. But if this is the only solution I have a lot of work before me, because such a construction is in my real application several hundred times  On the other hand: Is this behavior desired by the developers or is it an error? In no tutorial I found a clue for the necessarity of a starttransaction or commit command. Since it worked all the time until I updated it seems to be an unwanted effect. Thank you, anyway for your effort Chris ---------------------------------------------------------------------- Jkey · 2014-09-28 13:39 You are welcome! I don't have any information why it was changed like this, I suppose, it was necessary (more benefits than drawbacks). Regarding transactions, you can really take advantage of them if you place them to the correct location. For example when you have mass of independent inserts or updates, grouping them into a single transaction really speeds up writing into your DB. Maybe your application will also benefit from such "manual" transactions, if you learn how to use them efficiently. You wrote, that this impacts your code several hundred times. Well, I would avoid copying StartTransaction and Commit  more than 100 times to their currently necessary positions, I would try to smartly re- structure the code in order to write them only a few times. ---------------------------------------------------------------------- BigChimp · 2014-09-28 15:03 Quote from: OldMan on September 28, 2014, 03:00:48 pmOn the other hand: Is this behavior desired by the developers or is it an error? In no tutorial I found a clue for the necessarity of a starttransaction or commit command. http://wiki.lazarus.freepascal.org/Lazarus_1.2_fixes_branch which points to http://wiki.lazarus.freepascal.org/User_Changes_2.6.4#MySQL_support_fo r_transactions_added oh and regarding tutorials - e.g.: http://wiki.lazarus.freepascal.org/SQLdb_Tutorial2 If tutorials don't use transactions they should be updated. Thank you ---------------------------------------------------------------------- OldMan · 2014-09-28 16:26 @Jkey: Just now I tested to put the starttransaction into the BeforeOpen event and the commit into the AfterClose event of SQLQuery and it worked quite well. So it is solved for me, because I can handle this issue with little effort. I had thought, there is eventually a autocommit function for compatibility with old code, but this solution is equal to me. @BigChimp: I was already led to your second link by a german forums member, but I didn't realize that a commit is also necessary for reading procedures. Updating the wiki: I fear my bad english in combination with my little knowledge about SQL makes my a poor author for such an article. I would prefer to pay back my liabilities to the community with helpful answers to pascal beginners ...  Thanks to all Chris ---------------------------------------------------------------------- OldMan · 2014-09-28 18:15 Addendum: Its not so easy as I thought  In my big application I get a "transaction already active" error on various positions under unclear conditions. I can't put my finger on it. Going to sleep, tomorrow is another day ... ---------------------------------------------------------------------- Jkey · 2014-09-28 19:45 I faced with transaction already active error when I called StartTransaction without commit or rollback the previous transaction. For a very quick solution you can try to avoid starting it twice in your mentioned onBeforeOpen event: Code: [Select]procedure TForm1.SQLQuery1BeforeOpen(DataSet: TDataSet); begin   if not SQLTransaction1.Active then SQLTransaction1.StartTransaction; end;You had better start organizing your transactions. This feature is really a gift, even if it looks like a surprising nightmare. Anyway, I found a topic about this issue here: http://forum.lazarus.freepascal.org/index.php/topic,24443.0.html ---------------------------------------------------------------------- Mike.Cornflake · 2014-09-28 21:07 Been a while since I used transactions (D5?  D7?)- but the only advice I can give you if you're using MS-SQL is that if you change some data, commit that transaction as early as is sane in your code - otherwise any other apps trying to access changed tables will encounter issues. ---------------------------------------------------------------------- BigChimp · 2014-09-29 06:15 Quote from: OldMan on September 28, 2014, 06:26:55 pmUpdating the wiki: I fear my bad english in combination with my little knowledge about SQL makes my a poor author for such an article. I would prefer to pay back my liabilities to the community with helpful answers to pascal beginners ...  No problem (though there are also German tutorials - I suspect though that they are up to date with regard to transactions)... Just mentioned it - also useful for others reading this. In fact, there are some old mysql tutorials/code dumps that haven't been touched for years by anybody. I think I'll just remove them as the general SQLDB/Zeos tutorials work just fine...