How to copy tables between databases?

[dkaip]

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]

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]

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]

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.