Change Database(Schema) runtime without disconneting using TSQLconnection

[Bernt Wold]

Hello,
I have One Tsqlconnection and I would like to change the default
database(schema) it's pointing to.

I am using Deplhi 9, MSSQL2008 and dbexpress


Here is an example of what I am trying to do...

Connecting....

SQLConnection.Params.Clear;
SQLConnection.DriverName := 'MSSQL';
SQLConnection.GetDriverFunc := 'getSQLDriverMSSQL';
SQLConnection.LibraryName := 'dbxmss.dll';
SQLConnection.VendorLib := 'oledb';
SQLConnection.Params.Append('Database=test1');
SQLConnection.Params.Append('User_Name=user');
SQLConnection.Params.Append('Password=1234');
SQLConnection.Params.Append('HostName=sqlexpress');
SQLConnection.LoginPrompt:=false;
SQLConnection.Open;

Doing som work..
sqlquery1.SQLConnection:=SQLConnection;
sqlquery1.SQL.Clear;
sqlquery1.SQL.Add('UPDATE......');
sqlquery1.ExecSQL()
sqlquery1.Close;


Now I would like to change the default database for SQLconnection....
from test1 to SQLConnection.Params.Append('Database=test2');

Then continue with same work in another database using the same
connector..

Doing som work..
sqlquery1.SQLConnection:=SQLConnection;
sqlquery1.SQL.Clear;
sqlquery1.SQL.Add('UPDATE......');
sqlquery1.ExecSQL()
sqlquery1.Close;



Is this possible, if so where do I change the default
database(Schema), if not any other Idea how to do this.. Thanks in
advance..

Regards
B

[Bernt Wold]

Post by Bernt Wold
Hello,
I have One Tsqlconnection and I would like to change the default
database(schema)  it's pointing to.
I am using Deplhi 9, MSSQL2008  and dbexpress
Here is an example of what I am trying to do...
Connecting....
SQLConnection.Params.Clear;
    SQLConnection.DriverName := 'MSSQL';
    SQLConnection.GetDriverFunc := 'getSQLDriverMSSQL';
    SQLConnection.LibraryName := 'dbxmss.dll';
    SQLConnection.VendorLib := 'oledb';
    SQLConnection.Params.Append('Database=test1');
    SQLConnection.Params.Append('User_Name=user');
    SQLConnection.Params.Append('Password=1234');
    SQLConnection.Params.Append('HostName=sqlexpress');
    SQLConnection.LoginPrompt:=false;
    SQLConnection.Open;
Doing som work..
  sqlquery1.SQLConnection:=SQLConnection;
  sqlquery1.SQL.Clear;
  sqlquery1.SQL.Add('UPDATE......');
  sqlquery1.ExecSQL()
  sqlquery1.Close;
Now I would like to change the default database for SQLconnection....
from test1 to SQLConnection.Params.Append('Database=test2');
Then continue with same work in another database using the same
connector..
Doing som work..
  sqlquery1.SQLConnection:=SQLConnection;
  sqlquery1.SQL.Clear;
  sqlquery1.SQL.Add('UPDATE......');
  sqlquery1.ExecSQL()
  sqlquery1.Close;
Is this possible, if so where do I change the default
database(Schema), if not any other Idea how to do this..  Thanks in
advance..
Regards
B

I have about 18 different databases (Schema) that all have the same
tabels and I would like to do some update i each one off them pretty
often, but I don't want 18 connectors..

Maybe the best is to disconnect after each request, It' dos not seem
to slow down the UPDATE... see code below, is this good practice ?



procedure TForm1.Do_something(use_database:String);
begin

// Sett up each time I click....
SQLConnection.Params.Clear;
SQLConnection.DriverName := 'MSSQL';
SQLConnection.GetDriverFunc := 'getSQLDriverMSSQL';
SQLConnection.LibraryName := 'dbxmss.dll';
SQLConnection.VendorLib := 'oledb';
SQLConnection.Params.Append('Database='+use_database); //The
databse to use is sat up
SQLConnection.Params.Append('User_Name=user');
SQLConnection.Params.Append('Password=1234');
SQLConnection.Params.Append('HostName=sqlexpress');
SQLConnection.LoginPrompt:=false;
SQLConnection.Open;



sqlquery1.Close;
sqlquery1.SQLConnection:=SQLConnection;
sqlquery1.SQL.Clear;
sqlquery1.SQL.Add('UPDATE........');
sqlquery1.Open;
vis_verdier;
sqlquery1.Close;


// Close after....
SQLConnection.CloseDataSets;
SQLConnection.Close;




end;