Working with database locally lazarus / 16-Databases ---------------------------------------------------------------------- Vodnik · 2020-11-24 15:40 My application connects to Informix DB via ODBC driver. It provides user a report in DBGrid, allowing him to deal with it. So it only reads the DB, no updates are used. I would like to close connection to DB while user is playing with report. But data in DBGrid is shown only while dataset is open. Is there the way how to do this? I don't want to copy the whole DB into memory, it is very big, just the result of SELECT. I also have noticed, that if user keep the application opened for an hour or longer, an error connecting to DB is reported. The same happens in WinSQL when session is left opened. ---------------------------------------------------------------------- Thaddy · 2020-11-24 15:46 Assign the result of the select to a TBufDataset. The time out is a network time out, not related to the pascal code. ---------------------------------------------------------------------- Vodnik · 2020-11-24 16:53 Thanks for the idea, Thaddy. I'm investigating https://forum.lazarus.freepascal.org/index.php/topic,31698.45.html to understand how to do this. ---------------------------------------------------------------------- egsuh · 2020-11-26 06:11 Try CommitRetaining or RollbackRetaining, instead of Commit or Rollback of transacition.    XXX Retaining maintains the dataset open even after commit or rollback of transaction. ---------------------------------------------------------------------- devEric69 · 2020-11-26 08:30 Quote from: Vodnik on November 24, 2020, 04:40:22 pmSo it only reads the DB, no updates are used. For information - as asides - when doing reports, it is advisable to set the level of isolation - for queries - in a "snapshot" transaction through one connection, if possible (if it's a really a very present and important server-side database activity, the creation of dedicated "view" objects can also be justified, in order too to isolate it further from other activities). ---------------------------------------------------------------------- Vodnik · 2020-11-27 09:05 Quote from: Thaddy on November 24, 2020, 04:46:50 pmAssign the result of the select to a TBufDataset. I give up    Have no idea how to do that. Please advice. The closest finding is in ZMSQL database implementation, which supports non-standard SQL-command ASSIGN TO to store SQL result in a named variable. I have found idea to use BufDataset1.CopyFromDataset(), but this seems to me being a non-efficient way. After SQLQuery1.Open() dataset is in memory and copying it to another memory... Quote from: egsuh on November 26, 2020, 07:11:32 amTry CommitRetaining or RollbackRetaining, instead of Commit or Rollback of transacition.  XXX Retaining maintains the dataset open even after commit or rollback of transaction. You are talking about setting DB Isolation Level, yes? I start to investigate this and discovered that isolation level can be set in DB (SQL command SET ISOLATION), in ODBC driver or in my application. The last method looks most attractive, but how to do this? The 9-years old discussion about isolation levels https://forum.lazarus.freepascal.org/index.php?topic=14756.0 have example for FireBird only. Quote from: devEric69 on November 26, 2020, 09:30:48 amFor information - as asides - when doing reports, it is advisable to set the level of isolation - for queries - in a "snapshot" transaction through one connection, if possible (if it's a really a very present and important server-side database activity, the creation of dedicated "view" objects can also be justified, in order too to isolate it further from other activities). Seems that setting isolation level is an important thing... But how to do that in Lazarus? If this can be done by setting SQLTransaction.params, then I have no idea which parameters will accept IBM Informix ODBC driver. Setting isolation level works for the whole DB or for my session only? ---------------------------------------------------------------------- cris75 · 2020-11-27 09:24 I think this post ( https://forum.lazarus.freepascal.org/index.php?topic=31698.0 ) with suggestions by Thaddy about TBufDataset and TMemDataset could be useful as it was for me ---------------------------------------------------------------------- devEric69 · 2020-11-27 13:21 Quote from: Vodnik on November 27, 2020, 10:05:38 amSeems that setting isolation level is an important thing... But how to do that in Lazarus? If this can be done by setting SQLTransaction.params, then I have no idea which parameters will accept IBM Informix ODBC driver. Setting isolation level works for the whole DB or for my session only? In fact, don't bother too much about the level of isolation of a transaction (to begin, keep the "do it work, first!" principle). I explain myself: AFAIK, if the general idea is to retrieve data to make one or several associated reports, the idea of getting a coherent historical snapshot of data - the time to create all the associated reports, fetched by various queries inside a transaction - can be a real challenge. It can use the transaction named "repeatable read", if present. It's a bit of a gadget, a feature at the margin, but it is present on some databases. So, the database and its native driver must support it. I don't know Informix. Nevertheless, the ODBC driver must also have implemented the relay of such a functionality, if present (a link: https://delphisources.ru/pages/faq/master- delphi-7/content/LiB0151.html). ---------------------------------------------------------------------- egsuh · 2020-11-28 04:27 QuoteYou are talking about setting DB Isolation Level, yes? I start to investigate this and discovered that isolation level can be set in DB (SQL command SET ISOLATION), in ODBC driver or in my application. I've never programmed with ODBC, but whatever the connection is, I believe you have to use transaction to run query on a database. You must be using 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      Transaction1.Active := True;     SQLQuery1.SQL.Text := 'select * from mytable where...';      SQLQuery1.open;      { In the following sentence, if you call Transaction1.Commit, then SQLQuery1 will be closed.       But if you use CommitRetaining, then SQLQuery1 will remain in Active state.  }    Transaction1.CommitRetaining; You don't have to set up anything on isolation level, etc.  If you are not using, please post your database connection component, and related codes as above. ---------------------------------------------------------------------- Vodnik · 2020-11-29 21:33 Up to now I didn't set anything for SQLTransaction1. I just set SQLQuery1.SQL.Text and then call SQLQuery1.open, as in the basic published examples. I didn't find good description for TSQLTransaction to understand how it should be used ---------------------------------------------------------------------- Vodnik · 2020-11-30 10:32 Quote from: egsuh on November 28, 2020, 05:27:25 amI've never programmed with ODBC, but whatever the connection is, I believe you have to use transaction to run query on a database. You must be using 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      Transaction1.Active := True;     SQLQuery1.SQL.Text := 'select * from mytable where...';      SQLQuery1.open;      { In the following sentence, if you call Transaction1.Commit, then SQLQuery1 will be closed.       But if you use CommitRetaining, then SQLQuery1 will remain in Active state.  }    Transaction1.CommitRetaining;   I use the following code: 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 procedure TForm1.Button1Click(Sender: TObject);begin  SQLQuery1.Close;  SQLQuery1.SQL.Text := 'select * from users';  SQLQuery1.Open;  BufDataset1.CopyFromDataset(SQLQuery1,True);  ODBCConnection1.Close();end; SQLDB Tutorial1 https://wiki.freepascal.org/SQLdb_Tutorial1 tells that SQLTransaction1.Active := True; is performed automatically by SQLQuery1.Open; After that I copy data to BufDataset1 and close the DB connection. User can browse the data in DBGrid1 linked to DataSource1 linked to BufDataset1. What  SQLTransaction1.CommitRetaining;  will do, if applied before ODBCConnection1.Close(); ? ---------------------------------------------------------------------- egsuh · 2020-12-01 03:58 If you copy SQLQuery1 to BufDataSet, then closing your Database connection is irrelevant with Bufdataset's action. BufDataSet will stay as open. QuoteWhat  SQLTransaction1.CommitRetaining;  will do, if applied before ODBCConnection1.Close(); ? Transaction is more related with server. If you change any content of a table, e.g.  using SQL statements like insert, delete, or update, the operatios are not reflected in the physical DB table immediately. They are stored in a temporary virtual space. If you rollback transaction, then the changes you made since the start of transaction are just lost. You have to commit your changes in order for the changes to be stored in the DB. But in the case of just browsing (SQL select statement), it does not matter whether you commit or rollback, because there are no change to be updated to the database. One thing related with transaction is that it closes all the datasets -- in your case, SQLQuery1.  If you connect DBGrid to SQLQuery1, not BufDataSet1, then you'll see it is closed by Transaction.Commit or Rollback, even though you do not run ODBCConnection.Close. But if you run Transaction.CommitRetaining or RollbackRetaining, SQLQuery1 will stay as open. You'll see the content of it at DBGrid. I do not know whether ODBCConnection.Close will automatically close all the relevant datasets --- I have never done that. You may check yourself this by running following code, and what happens when showmessage boxes are showing themselves.  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     procedure TForm1.Button1Click(Sender: TObject);    begin      SQLQuery1.Close;      SQLQuery1.SQL.Text := 'select * from users';    SQLQuery1.Open;         SQLTransaction1.RollbackRetaining;       showmessage('Can you see the content in the dBGrid?');       ODBCConnection1.Close();      showmessage('Still can you see the content in the dBGrid?');     end; ---------------------------------------------------------------------- Vodnik · 2020-12-07 15:54 Thank you for explanation, egsuh. Yes, DBgrid1 shows data after SQLTransaction1.RollbackRetaining. And yes, DBgrid1 becomes empty after ODBCConnection1.Close. So transactions are mostly important when modifying the DB, not in my case.. My task is to read the data to the DBGrid1 and then to close the connection to the DB, leaving user to browse the data in DBGrid1. So copying SQLQuery1 to BufDataset1 is the only solution that I can do for this task. The upset is that copying takes some time, slowing the data appearance in DBGrid1. ---------------------------------------------------------------------- mangakissa · 2020-12-09 12:27 QuoteSo copying SQLQuery1 to BufDataset1 is the only solution that I can do for this task.Nope. Put your query in an object and read this into a stringgrid / listview. With beginupdate ... endupdate it can load very fast. Is a little more work, but it works.