copying between MSSql and a SQLite database

hi everyone,

My problem is that i want to write a qt code in which i can create a remote db which connects to a remote MSSql database and also a local db of type SQLite for my local p.c.
That is working fine and i'm able to create both the dbs in the same code. But now i want to copy the records from the mssql db to the sqlite db. But i'm not getting any idea as to how to do it?
Can anyone please help me regarding these. I'm totally fixed in this problem and not able to find any solution.
I would be highly grateful if anyone can help me out.

with regards,
satya prakash :confused:

Do a SELECT on the mssql and INSERT TO on the sqlite database. Or what exactly is your problem?

Quote:

---

Originally Posted by Lykurg

Do a SELECT on the mssql and INSERT TO on the sqlite database. Or what exactly is your problem?

---

first i had tried that way to execute that query the way you have mentioned. But it doesn't work that way. I searched in google regarding data copy between 2 different types of databases and found that there were so many tools available to do this. But, i want to do the same thing directly in my qt code. So, is there any predefined functions present in qt to do this?

Quote:

---

Originally Posted by sattu

first i had tried that way to execute that query the way you have mentioned. But it doesn't work that way.

---

Well, it works. Could you say what does not work? What have you tried/how looks your code like? What is the shema of your mssql database?

...and a predefined function does not exists.

Quote:

---

Originally Posted by Lykurg

Well, it works. Could you say what does not work? What have you tried/how looks your code like? What is the shema of your mssql database?

---

well this is the way i'm creating both the db:-
//////////creating db for remote mssql sever//////////////
QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
db.setDatabaseName("GIGIN_MS"); //here GIGIN_MS is the name of the DSN present in my odbc.ini file
db.setUserName(username);
db.setPassword(password);
db.open();

////////////////creating db for local sqlite/////////////
QSqlDatabase db2 = QSqlDatabase::addDatabase("QSQLITE");
db2.setDatabaseName("db_local");
db2.open();
QSqlQuery query.exec("create table user200(Id integer,Name varchar(100))");

///////////////////////////////////////////////////////////
after creating both the dbs, i'm executing the query: "SELECT * INTO db2..user200 FROM db..user20"

{{{{{{{{ Another information: On the remote mssql server, the db to which i'm connecting has a table named user20 which also has the same fields i.e ID(integer) and NAME(varchar(100)).}}}}}}}}

so, any modifications i need to make in it or is the code wrong?

Quote:

---

Originally Posted by sattu

after creating both the dbs, i'm executing the query: "SELECT * INTO db2..user200 FROM db..user20"

---

:confused: and that should work?

What about a simple - as told - select and then insert. (Pseudocode to leave it as an exercise up to you)

start a transaction on db2
prepare a INSERT INTO query for db2
Query the db and fetch all entries you like
during fetching bind the received values to the prepared query (db2) and execute it
commit transaction

Now the database should be copied.


EDIT: see QSqlQuery, QSqlQuery::prepare(), QSqlDatabase::transaction().

Quote:

---

Originally Posted by Lykurg

and that should work?

What about a simple - as told - select and then insert. (Pseudocode to leave it as an exercise up to you)

start a transaction.............EDIT: see QSqlQuery, QSqlQuery::prepare(), QSqlDatabase::transaction().

---

Thanks a ton Lykurg, but can't you send me the direct code please?(hee hee:p) Actually i have never worked on this preparing and binding functionalities.

O.k, i will work on those and reply as soon as possible. But it would really be great if you would give me the code rather than the algorithm(:cool: ;))

Waiting for your code,

once again, thanks a lot:)

Quote:

---

Originally Posted by sattu

Waiting for your code

---

Then you will wait forever :cool:

Only if you try it, you learn and don't need assistant in a further case like that.

hi lykurg!
I'm slowly moving forward regarding the concepts you gave.
But now i am getting another problem very frequently. I'm executing this simple code:
QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
db.setDatabaseName("GIGIN_MS");
db.setUserName(username);
db.setPassword(password);
db.open();

so, by this db gets opened. But when immediately after this i'm executing:

QSqlQuery query;
bool ok = query.exec("create table user20(Id integer,Name varchar(100))");

And the problem here is that, most of the time ok becomes false i.e the query is not executed, the qsqlquery error being that ''database not open''. I'm getting mad at this, as i have opened the database just before it. Even when i'm checking db.isOpen() after executing the query, it is returning true only. But the error to the query execution is still the same.:confused:

It's so strange that some times, it works and the query is executed successfully. But once the query execution fails, then no matter how much i try, it doesn't work. I really don't get what is the actual problem.

can you please help me regarding this? These errors seem to be so simple, but i'm not able to figure those out.:mad:

Thanking you in advance,
with regards,

Please use [code] tags around code.

Quote:

---

But when immediately after this i'm executing:

Code:

---

QSqlQuery query;
bool ok = query.exec("create table user20(Id integer,Name varchar(100))");

---

---

In your earlier code you are executing this after redefining the default connection to point at your Sqlite database. The SQL will fail if the table already exists, and QSqlQuery::lastError() will tell you everything there is to know about why.

More importantly, your original code connects a MSSQL database as the default connection and then replaces that default connection with the Sqlite database. Your MSSQL database will not be accessible after this and you will have received a runtime warning message to this effect. You need to look at the connection name parameter of QSqlDatabase::addDatabase().

This:

Code:

---

bool ok;
    QSqlDatabase a = QSqlDatabase::addDatabase("QSQLITE");
    a.setDatabaseName("test1.db");
    ok = a.open();
    qDebug() << "A open" << ok;
 
    QSqlDatabase b = QSqlDatabase::addDatabase("QSQLITE");
    b.setDatabaseName("test2.db");
    ok = b.open();
    qDebug() << "B open" << ok;
 
    qDebug() << "But look" << a.isOpen() << b.isOpen();

---

produces this:

Code:

---

A open true 
QSqlDatabasePrivate::removeDatabase: connection 'qt_sql_default_connection' is still in use, all queries will cease to work.
QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.
B open true 
But look false true

---

Hi ChrisW67!
Thanks for replying, and the errors you posted, i was exactly getting those messages in my Qt code and sometimes even if i was dealing only with one db connection, still it was showing the same problem. But, now it's solved. As you told, i was creating both the db's with the same default connection. So, now i'm creating both the db's in this way:

QSqlDatabase db = QSqlDatabase::addDatabase("QODBC","remote");
QSqlDatabase db2 = QSqlDatabase::addDatabase("QSQLITE","local");
So, now i am specifying different connection names for different dbs and when i am executing a query, i am defining it this way:
QSqlQuery query(db); //////////so now, this query is attached with db and show gets executed without any failure.

But do you have any other way of using any number of db's without getting their respective connections erased?

Please use [code] tags around code.

That is the way you maintain multiple simultaneous connections with Qt SQL. When you need to use a database elsewhere you get a handle to the database by specifying the connection name:

Code:

---

QSqlDatabase remote = QSqlDatabase("remote");

---

Lykurg & ChrisW67, i am extremely sorry for not having tagged my codes. I was in a hurry and so didn't see the warnings, now i got the idea how to tag the codes.

Thanks for the warning,:)

Ok, I moved the whole thread to the newbie section, since you obviously have little experience with QtSql at all. There is no other option to prevent erasing than using the second parameter.

Ok, let's see where you are right now:
You have successfully created two connections. But the list is untouched.

1. start a transaction on db2
2. prepare a INSERT INTO query for db2
3. Query the db and fetch all entries you like
4. during fetching bind the received values to the prepared query (db2) and execute it
5. commit transaction

Some hints for the next steps:

1) db2 is you SQLite connection, so call on it QSqlDatabase::transaction() to make it work faster.

2 + 3) use QSqlQuery to qery and fetch the data from db and for now simply print it via qWarning() to see if it works.


Further please try these steps and post the "full" code for this task. Then we bring you through your task hint after hint;) Free lecture on Qt Centre!

hi lykurg!
I am able to do what i had asked for, but in a little different way. the code is:

Code:

---

void MainWindow::insert_remote()
{
    QSqlQueryModel query_remote;        ///////////db is for mssql
    QSqlQuery query(db2);                     ///////////db2 is for sqlite
    int id;
    QString name;
    query_remote.setQuery("select * from user20",db);             ///////////user20 is the table of db
    rowcount= query_remote.rowCount();                                ///////////user200 is the table of db2
    int j=0;
    while(j!=rowcount)
    {
        id = query_remote.record(j).value("ID").toInt();
        name = query_remote.record(j).value("NAME").toString();
        query.exec("insert into user200(Id,Name) values("+QString::number(id)+",'"+name+"')");
        j++;
    }
    query_remote.clear();
}

---

The remote mssql server is on a different p.c and my local sqlite is on my p.c. Sorry, due to lack of time i am not able to work out on your method. So, when i am running this application on my p.c, it is working fine and copy of table records from mssql to sqlite is happening.
But, now the main thing is i have to run the same application on my arm-linux-embedded system. I am able to run even on the embedded board and do the neccessary copy.
But now i'm getting one more problem i.e the following error:

Code:

---

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

---

The integer and float values are coming fine, but when it comes to string data, it shows the above error.
So, can you tell me, what is the problem? Are the exact libraries associated with QString missing or something like that?:confused:

thanking in advance,

That is a unicode problem:

http://bugreports.qt.nokia.com/browse/QTBUG-8846
http://www.qtcentre.org/threads/2960...to-SQL-Express

Quote:

---

Originally Posted by tbscope

That is a unicode problem:

http://bugreports.qt.nokia.com/browse/QTBUG-8846
http://www.qtcentre.org/threads/2960...to-SQL-Express

---

Thanks for the reply,
Now, as far as i got the idea, i can do 2 things.
1) as mentioned in the QTBUG, i have got to use qt 4.6.3 or higher version, so i need to install another package. But, i am using qt 4.6.1, so is there some way of modifying it's libraries so as to avoid the new installation?:confused:

2) As given in the thread, now i am doing that, by changing odbc.pro file and compiling it once more. So, let me see if it works.

hi lykurg!

sorry, now we had 2 days off from office.

Upto now, i have done this:

Code:

---

QSqlQuery query_rem(db);
    bool ok = false;
    ok = db.transaction();
    QSqlQuery query(db2);
 
 
 
    query.prepare("INSERT INTO user200 (Id,Name) "
                  "VALUES (:id,:name)");
    query.bindValue(0, 1007);
    query.bindValue(1, "Bibhu_gigin");
    query.exec();
    ok = db.commit();

---

Now, i got the basic idea regarding the use of commit and prepare functions. But what i am not getting is that, as you told to prepare the insert into query for db2 and start fetching the data for db. Where exactly to put the QSqlQuery for db in this code? Also i couldn't get any such functions like query.fetch() or something like that.

Waiting for your reply,

with regards,
sattu:)

You need two queries. One selecting from the remote database, which you haven't devised yet, and the other inserting into the local one. You need to loop over the rows from the select query and execute an insert for each. The QSqlQuery docs have a cut and paste example of how to iterate over the results of a select query (and also binding)... you are right, it isn't called fetch().

Quote:

---

Originally Posted by ChrisW67

You need two queries. One selecting from the remote database, which you haven't devised yet, and the other inserting into the local one. You need to loop over the rows from the select query and execute an insert for each. The QSqlQuery docs have a cut and paste example of how to iterate over the results of a select query (and also binding)... you are right, it isn't called fetch().

---

Thanks chris,
but i didn't get any example in the docs where usage of 2 queries at a time are mentioned. But, do you mean to say that the code should look like this:

Code:

---

QSqlQuery query(db);      /////////db is for remote mssql
QSqlQuery query2(db2);       /////////db2 is for local sqlite
int id;
QString name;
    query.exec("select * from user20");        ///////user20 is the table of db(remote)
    bool ok = false;
    ok = db2.transaction();
    query2.prepare("INSERT INTO user200 (Id,Name) VALUES (:id,:name)");
 
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()

---

Is this the way, you want me to execute?:confused: