Recent

Author Topic: Working with database locally  (Read 2390 times)

Vodnik

  • Full Member
  • ***
  • Posts: 210
Working with database locally
« 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.

Thaddy

  • Hero Member
  • *****
  • Posts: 14201
  • Probably until I exterminate Putin.
Re: Working with database locally
« Reply #1 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.
Specialize a type, not a var.

Vodnik

  • Full Member
  • ***
  • Posts: 210
Re: Working with database locally
« Reply #2 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 to understand how to do this.

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: Working with database locally
« Reply #3 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.
« Last Edit: November 26, 2020, 07:14:13 am by egsuh »

devEric69

  • Hero Member
  • *****
  • Posts: 648
Re: Working with database locally
« Reply #4 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).
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

Vodnik

  • Full Member
  • ***
  • Posts: 210
Re: Working with database locally
« Reply #5 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 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?


cris75

  • Jr. Member
  • **
  • Posts: 59
Re: Working with database locally
« Reply #6 on: November 27, 2020, 10:24:35 am »
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
Lazarus: 3.0 / FPC: 3.2.2
[x86_64-win64-win32/win64]
OS IDE: Win10 64bit

devEric69

  • Hero Member
  • *****
  • Posts: 648
Re: Working with database locally
« Reply #7 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).
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: Working with database locally
« Reply #8 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.

Vodnik

  • Full Member
  • ***
  • Posts: 210
Re: Working with database locally
« Reply #9 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  :(

Vodnik

  • Full Member
  • ***
  • Posts: 210
Re: Working with database locally
« Reply #10 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 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(); ?
« Last Edit: November 30, 2020, 11:33:55 am by Vodnik »

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: Working with database locally
« Reply #11 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.  
     



Vodnik

  • Full Member
  • ***
  • Posts: 210
Re: Working with database locally
« Reply #12 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.

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: Working with database locally
« Reply #13 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.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

 

TinyPortal © 2005-2018