copying between MSSql and a SQLite database

[Lykurg]

That look almost like it should be done. Have you tried the code, because it works not like you would expect it. First move the query2.exec() inside the loop and you want to call commit() on db2!

[sattu]

That look almost like it should be done. Have you tried the code, because it works not like you would expect it. First move the query2.exec() inside the loop and you want to call commit() on db2!

You mean this way:

while(query.next())
{
    id = query.record().value(0).toInt();
    name = query.record().value(1).toString();
    query2.bindValue(0, id);
    query2.bindValue(1, name);
    query2.exec();
}
    ok = db.commit()

while(query.next())
{
    id = query.record().value(0).toInt();
    name = query.record().value(1).toString();
    query2.bindValue(0, id);
    query2.bindValue(1, name);
    query2.exec();
}
    ok = db.commit()

To copy to clipboard, switch view to plain text mode 

Actually you are right Lykurg. This is not exactly what i want as i have already done it this way. To be precise, i want to avoid the following lines:

id = query.record().value(0).toInt();
    name = query.record().value(1).toString();

id = query.record().value(0).toInt();
    name = query.record().value(1).toString();

To copy to clipboard, switch view to plain text mode 

I donot want to manually extract from db and then put the values back in db2. I want the same thing to happen but only by the usage of queries.
Is it possible? Somehow i want to avoid this extraction kind of thing.

[Lykurg]

I donot want to manually extract from db and then put the values back in db2. I want the same thing to happen but only by the usage of queries.
Is it possible? Somehow i want to avoid this extraction kind of thing.

You can't avoid that because you are using two different database systems. There is no such feature like

SELECT ... INTO

SELECT ... INTO

To copy to clipboard, switch view to plain text mode 

[sattu]

You can't avoid that because you are using two different database systems. There is no such feature like

SELECT ... INTO

SELECT ... INTO

To copy to clipboard, switch view to plain text mode 

Ok, then it's completely fine. Thanks for helping me so much.
So is my code o.k or i need to make any better modifications over it?

[Lykurg]

I think it is fine and if it works, perfect. You could skip the local variables in the while loop which will speed up things a little bit. (If the compiler doesn't do so already which he normaly should...)

[sattu]

I think it is fine and if it works, perfect. You could skip the local variables in the while loop which will speed up things a little bit. (If the compiler doesn't do so already which he normaly should...)

Yaa yaa, i am able to do like that.
Actually, i need to tell one more thing and hope it doesnot irritate you.
As i told before, first we are doing for general PC to PC connection, if it is fine then we are putting it in our embedded board. This also i told previously the error that we are getting while we are trying from board. Board is not able to fetch the string or char type data,

qGetStringData: Error while fetching data ( "[FreeTDS][SQL Server]Program type out of range" )

qGetStringData: Error while fetching data ( "[FreeTDS][SQL Server]Program type out of range" )

To copy to clipboard, switch view to plain text mode 

the solutions that tbscope had provided, i worked on both, but thats not working. I mean, i tried for qt4.6.3, there we are able to fetch string data, but some other issues are coming.
So, now i want to know one thing. Currently i am using this query to fetch data:
"select * from user20"
Is there any other way or queries by which i can fetch basic character type variables from remote table?
Also, our string variable is of maximum 10chars. And we are using varchar type. What other data types we can use for the same thing? (like nchar or nvarchar or something like that)

[jfinn88]

sattu,

I know this is old but for any one else who might need this... This way use two database connections

Copies rows from one database table that are older than 30 days and stores them in another database table for archiving and then deletes the rows.

dbConnect();
        archiveDbConnect();
 
        QSqlQuery archiveDataQry("SELECT * FROM userlogevents7", m_selectDataBase);
        QSqlQuery copyDataQry(m_archiveDataBase);
 
        int id;
        QString userName;
        QString eventMessage;
        QDate dateTime;
 
        if(archiveDataQry.exec()){
            qDebug()<<"sql statement exicuted fine";
        }
        else{
            qDebug() << "Errors accured with sql statement";
            qDebug() << archiveDataQry.lastError();
        }
 
        m_selectDataBase.transaction();
 
        if(copyDataQry.prepare("INSERT INTO usereventarchive (id, userName, eventMessage, dateTime) VALUES (:id, :userName, :eventMessage, :dateTime)"))
        {
            qDebug()<<"prepare sql statement exicuted fine";
        }
       else{
            qDebug() << "Errors accured with prepare sql statement";
            qDebug() << copyDataQry.lastError();
        }
 
        while(archiveDataQry.next()){
                id = archiveDataQry.record().value(0).toInt();
                userName = archiveDataQry.record().value(1).toString();
                eventMessage = archiveDataQry.record().value(2).toString();
                dateTime = archiveDataQry.record().value(3).toDate();
                copyDataQry.bindValue(0,id);
                copyDataQry.bindValue(1, userName);
                copyDataQry.bindValue(2, eventMessage);
                copyDataQry.bindValue(3, dateTime);
                if(copyDataQry.exec()){
                    qDebug()<<"copy sql statement exicuted fine";
                }
               else{
                    qDebug() << "Errors accured with copy sql statement";
                    qDebug() << copyDataQry.lastError();
                }
        }
        m_selectDataBase.commit();
        m_selectDataBase.close();
    }

dbConnect();
        archiveDbConnect();
     
        QSqlQuery archiveDataQry("SELECT * FROM userlogevents7", m_selectDataBase);
        QSqlQuery copyDataQry(m_archiveDataBase);
     
        int id;
        QString userName;
        QString eventMessage;
        QDate dateTime;
     
        if(archiveDataQry.exec()){
            qDebug()<<"sql statement exicuted fine";
        }
        else{
            qDebug() << "Errors accured with sql statement";
            qDebug() << archiveDataQry.lastError();
        }
     
        m_selectDataBase.transaction();
     
        if(copyDataQry.prepare("INSERT INTO usereventarchive (id, userName, eventMessage, dateTime) VALUES (:id, :userName, :eventMessage, :dateTime)"))
        {
            qDebug()<<"prepare sql statement exicuted fine";
        }
       else{
            qDebug() << "Errors accured with prepare sql statement";
            qDebug() << copyDataQry.lastError();
        }
     
        while(archiveDataQry.next()){
                id = archiveDataQry.record().value(0).toInt();
                userName = archiveDataQry.record().value(1).toString();
                eventMessage = archiveDataQry.record().value(2).toString();
                dateTime = archiveDataQry.record().value(3).toDate();
                copyDataQry.bindValue(0,id);
                copyDataQry.bindValue(1, userName);
                copyDataQry.bindValue(2, eventMessage);
                copyDataQry.bindValue(3, dateTime);
                if(copyDataQry.exec()){
                    qDebug()<<"copy sql statement exicuted fine";
                }
               else{
                    qDebug() << "Errors accured with copy sql statement";
                    qDebug() << copyDataQry.lastError();
                }
        }
        m_selectDataBase.commit();
        m_selectDataBase.close();
    }

To copy to clipboard, switch view to plain text mode 

hope this helps any one that may need it...