• Registered: 2015-12-25
  • Posts: 211

Topic: Using Two Databases, is it possible?

Hi, i want to use two databases , so when i backup the database, i backup the "Data" Database, i am going to use the other database for images, it will soon become too heavy for backups.

  • Registered: 2015-02-24
  • Posts: 446

Re: Using Two Databases, is it possible?

Hello VascoMorais,


Yes, you can use two SQLite databases at the same time, but this can only be done programmaticaly : you will have to issue all the commands to this DB by code.


First, you need a database located somewhere and you will have to inform you application of it's availability or not. This is done by using the ATTACH and DETACH commands. You also need a name for this DB. I advise you to choose a simple one.


Connecting your database is done like this :

SQLExecute('ATTACH database ''.\DB_OLD\old_sqlite.db'' AS OLD_DB');

And disconnecting the Db (if needed) is done like this :

SQLExecute('DETACH OLD_DB');

To attach, you need a path to the database, to detach, you just refer to the database via the alias you gave it (it's name).


From now on, if you want to execute some SQL but on this secondary database, you need to precede all your command with the alias of the database.

If you wright for example :

SQLExecute('INSERT INTO my_table(field1, field2) VALUES(X,Y)');

this will be executed on the main database.
If you want to target the attached database, the command becomes :

SQLExecute('INSERT INTO OLD_DB.my_table(field1, field2) VALUES(X,Y)');

Some links on basic attach command :
http://www.sqlitetutorial.net/sqlite-attach-database/
and
https://www.tutorialspoint.com/sqlite/s … tabase.htm


Hope this helps


Math


PS : my code examples have been written from memory and might not compile, this was just to give you the general idea

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor
  • Registered: 2015-12-25
  • Posts: 211

Re: Using Two Databases, is it possible?

would it be possible to use two mysql databases ? or one mysqldatabase and one sql_lite database?

  • Registered: 2014-02-14
  • Posts: 10,294

Re: Using Two Databases, is it possible?

If you project uses SQLite, you can connect to MySQL by script, example:

procedure Form1_Button1_OnClick (Sender: string; var Cancel: boolean);
var
    MySQL: TMyConnection;
begin
    MySQL := TMyConnection.Create(Form1);
    try
            MySQL.Options.UseUnicode := True;
            MySQL.Server := '10.10.10.10'; // ip address of MySQL server
            MySQL.Port := 3306;
            MySQL.Username := 'username';
            MySQL.Password := 'password';
            MySQL.Database := 'databasename';
            MySQL.LoginPromt := False;
        
            try
                MySQL.Connect;
            except
                ShowMessage('Can''t connect to database.');
            end;

            if MySQL.Connected then
            begin
                // execute SQL                
                MySQL.ExecSQL('INSERT INTO test (field1, field2) VALUES ("value1", "value2")');

                // populate TableGrid from MySQL
                Form1.TableGrid2.dbMySQLConnection := MySQL;
                Form1.TableGrid2.dbSQL:='SELECT id, lastname, firstname FROM employees'; // the id field, want to be able to edit or delete the entry from the table component
                Form1.TableGrid2.dbGeneralTable := 'employees';
                Form1.TableGrid2.dbListFieldsNames :='delete_col,Lastname,Firstname';
                Form1.TableGrid2.dbSQLExecute;
                MySQL.Disconnect;                                                                                         
            end;

    finally
        MySQL.Free;
    end;
end;
Dmitry.