* * *

Author Topic: How to switch to another Database during run-time?  (Read 340 times)

Vodnik

  • New member
  • *
  • Posts: 38
How to switch to another Database during run-time?
« on: April 23, 2018, 02:46:15 pm »
Hello,
My program is using TODBCConnection, TSQLTransaction, TSQLQuery, TDataSource and TDBGrid components on a form. It worked fine until I decided to allow the user to select a DSN to deal with (there are several available). Just changing TODBCConnection.DatabaseName doesn't work: subsequent SQL queries continue to work with the first DatabaseName assigned. I'm going to prepare a trivial example, but maybe the problem is already known: what are the necessary steps to switch to another Database during run-time?

Trenatos

  • Sr. Member
  • ****
  • Posts: 297
  • Software developer - Open source contributor
Re: How to switch to another Database during run-time?
« Reply #1 on: April 23, 2018, 03:34:01 pm »
If I remember right, if you want to re-use the connection handler you need to close the previous connection, set up the new connection details, and connect it again.

Or you could use several connections and switch between them when the user picks one, you don't have to connect them all on start (No point in idling connections taking up resources).

Vodnik

  • New member
  • *
  • Posts: 38
Re: How to switch to another Database during run-time?
« Reply #2 on: April 23, 2018, 04:05:19 pm »
@Trenatos, that's so easy... I have just applied your advice to my example below, and now it works.
No need to close and reopen SQLTransaction1 and DataSource1?
Code: Pascal  [Select]
  1. unit Unit1;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, SysUtils, db, sqldb, odbcconn, FileUtil, Forms, Controls, Graphics,
  9.   Dialogs, StdCtrls;
  10.  
  11. type
  12.  
  13.   { TForm1 }
  14.  
  15.   TForm1 = class(TForm)
  16.     Button1: TButton;
  17.     Button2: TButton;
  18.     Label1: TLabel;
  19.     Label2: TLabel;
  20.     DataSource1: TDataSource;
  21.     ODBCConnection1: TODBCConnection;
  22.     SQLQuery1: TSQLQuery;
  23.     SQLTransaction1: TSQLTransaction;
  24.     procedure Button1Click(Sender: TObject);
  25.     procedure Button2Click(Sender: TObject);
  26.   private
  27.  
  28.   public
  29.  
  30.   end;
  31.  
  32. var
  33.   Form1: TForm1;
  34.  
  35. implementation
  36.  
  37. {$R *.lfm}
  38.  
  39. { TForm1 }
  40.  
  41. procedure TForm1.Button1Click(Sender: TObject);
  42. begin
  43.   ODBCConnection1.Close(False);
  44.   ODBCConnection1.DatabaseName:='OSCC_RET_DB';
  45.   ODBCConnection1.Open;
  46.   SQLQuery1.Close;
  47.   SQLQuery1.SQL.Text:='select count(all callid) from callrecord';
  48.   SQLQuery1.Open;
  49.   Label1.Caption:=SQLQuery1.Fields[0].AsString;
  50. end;
  51.  
  52. procedure TForm1.Button2Click(Sender: TObject);
  53. begin
  54.   ODBCConnection1.Close(False);
  55.   ODBCConnection1.DatabaseName:='OSCC_CCC_DB';
  56.   ODBCConnection1.Open;
  57.   SQLQuery1.Close;
  58.   SQLQuery1.SQL.Text:='select count(all callid) from callrecord';
  59.   SQLQuery1.Open;
  60.   Label2.Caption:=SQLQuery1.Fields[0].AsString;
  61.  

Trenatos

  • Sr. Member
  • ****
  • Posts: 297
  • Software developer - Open source contributor
Re: How to switch to another Database during run-time?
« Reply #3 on: April 23, 2018, 04:13:00 pm »
I can't remember to be honest, but I don't think you do because you're still using the old connection handler, but that's just from memory, give it go and see if that works.

valdir.marcos

  • Sr. Member
  • ****
  • Posts: 363
Re: How to switch to another Database during run-time?
« Reply #4 on: April 23, 2018, 07:22:43 pm »
My program is using TODBCConnection, TSQLTransaction, TSQLQuery, TDataSource and TDBGrid components on a form ... what are the necessary steps to switch to another Database during run-time?

Both Trenatos' solutions work correctly.

No need to close and reopen SQLTransaction1 and DataSource1?
No, they are all closed altogether automatically, but pay special attention on TSQLTransaction.Action and TSQLTransaction.Params for commit settings.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus