copying between MSSql and a SQLite database

[sattu]

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:

[Lykurg]

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

[sattu]

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?

[Lykurg]

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.

[sattu]

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?

[Lykurg]

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().

[sattu]

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:)

[Lykurg]

Waiting for your codeThen you will wait forever :cool:

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

[sattu]

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,

[ChrisW67]

Please use
tags around code.


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:


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:

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

[sattu]

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?

[ChrisW67]

Please use
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");

[sattu]

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,:)

[Lykurg]

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.

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


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!

[sattu]

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



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:


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,

[tbscope]

That is a unicode problem:

http://bugreports.qt.nokia.com/browse/QTBUG-8846
http://www.qtcentre.org/threads/29608-QSqlTableModel-select()-fails-with-QODBC-connection-to-SQL-Express

[sattu]

That is a unicode problem:

http://bugreports.qt.nokia.com/browse/QTBUG-8846
http://www.qtcentre.org/threads/29608-QSqlTableModel-select()-fails-with-QODBC-connection-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.

[sattu]

hi lykurg!

sorry, now we had 2 days off from office.

Upto now, i have done this:



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:)

[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().

[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().

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:


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:

[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()


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();


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

[sattu]

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

Ok, then it's completely fine. Thanks for helping me so much.:cool:
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" )


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?:confused:
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();
}


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