How to switch to another Database during run-time?
lazarus / 16-Databases

----------------------------------------------------------------------
Vodnik · 2018-04-23 12:46

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 · 2018-04-23 13:34

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 · 2018-04-23 14:05

@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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1; {$mode objfpc}{$H+} interface uses  Classes, SysUtils, db,
sqldb, odbcconn, FileUtil, Forms, Controls, Graphics,  Dialogs,
StdCtrls; type   { TForm1 }   TForm1 = class(TForm)    Button1:
TButton;    Button2: TButton;    Label1: TLabel;    Label2: TLabel;  
DataSource1: TDataSource;    ODBCConnection1: TODBCConnection;   
SQLQuery1: TSQLQuery;    SQLTransaction1: TSQLTransaction;   
procedure Button1Click(Sender: TObject);    procedure
Button2Click(Sender: TObject);  private   public   end; var  Form1:
TForm1; implementation {$R *.lfm} { TForm1 } procedure
TForm1.Button1Click(Sender: TObject);begin 
ODBCConnection1.Close(False); 
ODBCConnection1.DatabaseName:='OSCC_RET_DB';  ODBCConnection1.Open; 
SQLQuery1.Close;  SQLQuery1.SQL.Text:='select count(all callid) from
callrecord';  SQLQuery1.Open; 
Label1.Caption:=SQLQuery1.Fields[0].AsString;end; procedure
TForm1.Button2Click(Sender: TObject);begin 
ODBCConnection1.Close(False); 
ODBCConnection1.DatabaseName:='OSCC_CCC_DB';  ODBCConnection1.Open; 
SQLQuery1.Close;  SQLQuery1.SQL.Text:='select count(all callid) from
callrecord';  SQLQuery1.Open; 
Label2.Caption:=SQLQuery1.Fields[0].AsString;

----------------------------------------------------------------------
Trenatos · 2018-04-23 14:13

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 · 2018-04-23 17:22

Quote from: Vodnik on April 23, 2018, 02:46:15 pmMy 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.

Quote from: Vodnik on April 23, 2018, 04:05:19 pmNo 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.