Lazarus

Programming => Databases => Topic started by: Vodnik on November 24, 2020, 04:40:22 pm

Title: Working with database locally
Post by: Vodnik on November 24, 2020, 04:40:22 pm
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.
Title: Re: Working with database locally
Post by: Thaddy on November 24, 2020, 04:46:50 pm
Assign the result of the select to a TBufDataset.
The time out is a network time out, not related to the pascal code.
Title: Re: Working with database locally
Post by: Vodnik on November 24, 2020, 05:53:29 pm
Thanks for the idea, Thaddy.
I'm investigating https://forum.lazarus.freepascal.org/index.php/topic,31698.45.html (https://forum.lazarus.freepascal.org/index.php/topic,31698.45.html) to understand how to do this.
Title: Re: Working with database locally
Post by: egsuh on November 26, 2020, 07:11:32 am
Try CommitRetaining or RollbackRetaining, instead of Commit or Rollback of transacition.    XXX Retaining maintains the dataset open even after commit or rollback of transaction.
Title: Re: Working with database locally
Post by: devEric69 on November 26, 2020, 09:30:48 am
So 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).
Title: Re: Working with database locally
Post by: Vodnik on November 27, 2020, 10:05:38 am
Assign 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...

Try 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 (https://forum.lazarus.freepascal.org/index.php?topic=14756.0) have example for FireBird only.

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).

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?

Title: Re: Working with database locally
Post by: cris75 on November 27, 2020, 10:24:35 am
I think this post ( https://forum.lazarus.freepascal.org/index.php?topic=31698.0  (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
Title: Re: Working with database locally
Post by: devEric69 on November 27, 2020, 02:21:07 pm
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?

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 (https://delphisources.ru/pages/faq/master-delphi-7/content/LiB0151.html)).
Title: Re: Working with database locally
Post by: egsuh on November 28, 2020, 05:27:25 am
Quote
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.

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][+][-]
  1.      Transaction1.Active := True;
  2.      SQLQuery1.SQL.Text := 'select * from mytable where...';
  3.      SQLQuery1.open;
  4.  
  5.     { In the following sentence, if you call Transaction1.Commit, then SQLQuery1 will be closed.
  6.        But if you use CommitRetaining, then SQLQuery1 will remain in Active state.  }
  7.     Transaction1.CommitRetaining;  
  8.  

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.
Title: Re: Working with database locally
Post by: Vodnik on November 29, 2020, 10:33:05 pm
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  :(
Title: Re: Working with database locally
Post by: Vodnik on November 30, 2020, 11:32:02 am
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][+][-]
  1.      Transaction1.Active := True;
  2.      SQLQuery1.SQL.Text := 'select * from mytable where...';
  3.      SQLQuery1.open;
  4.  
  5.     { In the following sentence, if you call Transaction1.Commit, then SQLQuery1 will be closed.
  6.        But if you use CommitRetaining, then SQLQuery1 will remain in Active state.  }
  7.     Transaction1.CommitRetaining;  
  8.  
I use the following code:
Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. begin
  3.   SQLQuery1.Close;
  4.   SQLQuery1.SQL.Text := 'select * from users';
  5.   SQLQuery1.Open;
  6.   BufDataset1.CopyFromDataset(SQLQuery1,True);
  7.   ODBCConnection1.Close();
  8. end;
  9.  
SQLDB Tutorial1 https://wiki.freepascal.org/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(); ?
Title: Re: Working with database locally
Post by: egsuh on December 01, 2020, 04:58:10 am
If you copy SQLQuery1 to BufDataSet, then closing your Database connection is irrelevant with Bufdataset's action. BufDataSet will stay as open.

Quote
What  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][+][-]
  1.     procedure TForm1.Button1Click(Sender: TObject);
  2.     begin
  3.       SQLQuery1.Close;
  4.       SQLQuery1.SQL.Text := 'select * from users';
  5.       SQLQuery1.Open;
  6.    
  7.       SQLTransaction1.RollbackRetaining;
  8.       showmessage('Can you see the content in the dBGrid?');
  9.       ODBCConnection1.Close();
  10.       showmessage('Still can you see the content in the dBGrid?');
  11.     end;
  12.  
     


Title: Re: Working with database locally
Post by: Vodnik on December 07, 2020, 04:54:41 pm
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.
Title: Re: Working with database locally
Post by: mangakissa on December 09, 2020, 01:27:55 pm
Quote
So 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.
TinyPortal © 2005-2018