CODEWITHSUNDEEP
qtsqlitein-memory-database
xucheng
xucheng

Reputation: 275

How to copy between two sqlite-memory-database?

How to copy between two sqlite-memory-database in Qt. just like this:

QSqlDatabase db1 = QSqlDatabase::addDatabase ("QSQLITE", "db1");
QSqlDatabase db2 = QSqlDatabase::addDatabase ("QSQLITE", "db2");
db1.setDatabaseName (":memory:");
db2.setDatabaseName (":memory:");
db1.open();
db2.open();
//add Data to db1
//do sth to copy db1's data to db2 [How]

Upvotes: 2

Views: 4167

Answers (1)

sgibb
sgibb

Reputation: 25736

Some time ago I had a similar problem (but copy a MySQL database to SQLite). I used the following code (not tested, full code, ll. 40-167)

QString table("mytable");
QSqlDatabase srcDB=QSqlDatabase::addDatabase("QSQLITE", "db1");
QSqlDatabase destDB=QSqlDatabase::addDatabase("QSQLITE", "db2");

QSqlQuery srcQuery(srcDB);
QSqlQuery destQuery(destDB);

// get table schema
if (!srcQuery.exec(QString("SHOW CREATE TABLE %1").arg(table)))
    return false;

QString tableCreateStr;

while(srcQuery.next())
  tableCreateStr=srcQuery.value(1).toString();

// drop destTable if exists
if (!destQuery.exec(QString("DROP TABLE IF EXISTS %1").arg(table)))
     return false;

// create new one
if (!destQuery.exec(tableCreateStr))
    return false;

// copy all entries
if (!srcQuery.exec(QString("SELECT * FROM %1").arg(table)))
    return false;

while (srcQuery.next()) {
    QSqlRecord record=srcQuery.record();
    QStringList names;
    QStringList placeholders;
    QList<QVariant > values;

    for (int i = 0; i < record.count(); ++i) {
        names << record.fieldName(i);
        placeholders << ":" + record.fieldName(i);

        QVariant value=srcQuery.value(i);

        if (value.type() == QVariant::String)
            values << "\"" + value.toString() + "\"";
        else
            values << value;
    }

    // build new query
    QString queryStr;
    queryStr.append("INSERT INTO " + table);
    queryStr.append(" (" + names.join(", ") + ") ");
    queryStr.append(" VALUES (" + placeholders.join(", ") + ");");

    destQuery.prepare(queryStr);

    foreach(QVariant value, values)
        destQuery.addBindValue(value);

    if (!destQuery.exec())
        return false;
}

Upvotes: 4

Related Questions