How to copy tables between databases?

In this forum you can discuss database related issues which can be wxWidgets related, but also generic in nature.
Post Reply
dkaip
Super wx Problem Solver
Super wx Problem Solver
Posts: 251
Joined: Wed Jan 20, 2010 1:15 pm

How to copy tables between databases?

Post by dkaip » Thu Jun 04, 2020 3:55 am

Hello.
I must copy the rows from a one database to other.
Usually i use ...

Code: Select all

    wxSQLite3Database* db = initDB();
    wxSQLite3ResultSet set = db->ExecuteQuery(wxS("SELECT client FROM clients"));
    int count = 0;
    wxString s;
    while (set.NextRow())
    {
        s = set.GetAsString(0);
        s.Trim();
        if(s.IsEmpty())continue;
        myvector.push_back(s);
        count++;
    }
    set.Finalize();
and the initDB is

Code: Select all

wxSQLite3Database* initDB(void)
{
    wxString DBName = globalDBname;
    wxSQLite3Database* db = new wxSQLite3Database();
        if (!wxFileExists(DBName))
    {
        db->Open(DBName);
        db->UserAdd(wxS("user"), wxS("pswd"), true);
        db->UserLogin(wxS("user"), wxS("pswd"));
        db->ExecuteUpdate(wxS("CREATE TABLE clients (id int unique, client blob unique, miniseis blob, daneia blob , kartes blob , logariasmoi blob )"));
        db->Open(DBName);
    return db;
Now i have the external temp.db with same type of clients table. I must add all objects in main database. As i see i must use the ATTACH key.
https://www.sqlite.org/lang_attach.html
I have no picture how i can do that. There is any example to try with wxSqlite3?
Thank you.
Jim

utelle
Moderator
Moderator
Posts: 949
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: How to copy tables between databases?

Post by utelle » Thu Jun 04, 2020 8:48 am

dkaip wrote:
Thu Jun 04, 2020 3:55 am
I must copy the rows from a one database to other.
[...]
Now i have the external temp.db with same type of clients table. I must add all objects in main database. As i see i must use the ATTACH key.
https://www.sqlite.org/lang_attach.html
I have no picture how i can do that. There is any example to try with wxSqlite3?
Well, the sample application coming with wxSQLite3 shows how to attach a database file. The rest is pure SQL logic. Your code would look similar to the following:

Code: Select all

wxSQLite3Database* db = ...;

db->AttachDatabase(wxS("temp.db"), wxS("dbtemp"));
db->ExecuteUpdate(wxS("INSERT INTO main.clients SELECT * FROM dbtemp.clients;"));
db->DetachDatabase(wxS("dbtemp"));
That is, if the table "clients" already exists in both databases and you want to copy all records from the temporary database to the main database, the above statements should be sufficient. However, if the target table is not empty and there is a chance of conflicts, you will have to add a conflict handling clause in the INSERT SQL statement - see upsert clause.

Regards,
Ulrich

dkaip
Super wx Problem Solver
Super wx Problem Solver
Posts: 251
Joined: Wed Jan 20, 2010 1:15 pm

Re: How to copy tables between databases?

Post by dkaip » Sun Jun 07, 2020 2:12 pm

Sorry, don’t know how to code this ...
I have try ...

Code: Select all

wxFileDialog openFileDialog(this, _("Open db file"), "", "","files (*.db)|*.db", wxFD_OPEN|wxFD_FILE_MUST_EXIST);
    if (openFileDialog.ShowModal() == wxID_CANCEL)return;
    wxString file=openFileDialog.GetPath();

    wxSQLite3Database* db = initDB();
    wxString temp=globalDBname;
    wxString dbtemp = file;
    db->AttachDatabase(temp, dbtemp);
    db->ExecuteUpdate(wxS("INSERT INTO main.clients SELECT * FROM dbtemp.clients;"));
    db->DetachDatabase(dbtemp);
... but is wrong ...

utelle
Moderator
Moderator
Posts: 949
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: How to copy tables between databases?

Post by utelle » Sun Jun 07, 2020 5:17 pm

Try this:

Code: Select all

    wxString file = openFileDialog.GetPath();
    wxSQLite3Database* db = initDB();
    db->AttachDatabase(file, wxS("dbtemp"));
    db->ExecuteUpdate(wxS("INSERT INTO main.clients SELECT * FROM dbtemp.clients;"));
    db->DetachDatabase(wxS("dbtemp"));
The first parameter of method AttachDatabase is the file name of the database file to be attached.
The second parameter is the logical schema name you want to use in SQL for the attached database.

Post Reply