Page 1 of 2 12 LastLast
Results 1 to 20 of 27

Thread: copying between MSSql and a SQLite database

  1. #1
    Join Date
    Sep 2010
    Location
    Bangalore
    Posts
    169
    Thanks
    59
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default 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

  2. #2
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    4,380
    Thanks
    19
    Thanked 1,005 Times in 913 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows Symbian S60
    Wiki edits
    5

    Default Re: copying between MSSql and a SQLite database

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

  3. #3
    Join Date
    Sep 2010
    Location
    Bangalore
    Posts
    169
    Thanks
    59
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: copying between MSSql and a SQLite database

    Quote Originally Posted by Lykurg View Post
    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?

  4. #4
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    4,380
    Thanks
    19
    Thanked 1,005 Times in 913 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows Symbian S60
    Wiki edits
    5

    Default Re: copying between MSSql and a SQLite database

    Quote Originally Posted by sattu View Post
    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.

  5. #5
    Join Date
    Sep 2010
    Location
    Bangalore
    Posts
    169
    Thanks
    59
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Question Re: copying between MSSql and a SQLite database

    Quote Originally Posted by Lykurg View Post
    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?

  6. #6
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    4,380
    Thanks
    19
    Thanked 1,005 Times in 913 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows Symbian S60
    Wiki edits
    5

    Default Re: copying between MSSql and a SQLite database

    Quote Originally Posted by sattu View Post
    after creating both the dbs, i'm executing the query: "SELECT * INTO db2..user200 FROM db..user20"
    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().

  7. The following user says thank you to Lykurg for this useful post:

    sattu (3rd February 2011)

  8. #7
    Join Date
    Sep 2010
    Location
    Bangalore
    Posts
    169
    Thanks
    59
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: copying between MSSql and a SQLite database

    Quote Originally Posted by Lykurg View Post
    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) 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( )

    Waiting for your code,

    once again, thanks a lot

  9. #8
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    4,380
    Thanks
    19
    Thanked 1,005 Times in 913 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows Symbian S60
    Wiki edits
    5

    Default Re: copying between MSSql and a SQLite database

    Quote Originally Posted by sattu View Post
    Waiting for your code
    Then you will wait forever

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

  10. The following user says thank you to Lykurg for this useful post:

    sattu (3rd February 2011)

  11. #9
    Join Date
    Sep 2010
    Location
    Bangalore
    Posts
    169
    Thanks
    59
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: copying between MSSql and a SQLite database

    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.

    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.

    Thanking you in advance,
    with regards,

  12. #10
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: copying between MSSql and a SQLite database

    Please use [code] tags around code.

    But when immediately after this i'm executing:
    Qt Code:
    1. QSqlQuery query;
    2. bool ok = query.exec("create table user20(Id integer,Name varchar(100))");
    To copy to clipboard, switch view to plain text mode 
    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:
    Qt Code:
    1. bool ok;
    2. QSqlDatabase a = QSqlDatabase::addDatabase("QSQLITE");
    3. a.setDatabaseName("test1.db");
    4. ok = a.open();
    5. qDebug() << "A open" << ok;
    6.  
    7. QSqlDatabase b = QSqlDatabase::addDatabase("QSQLITE");
    8. b.setDatabaseName("test2.db");
    9. ok = b.open();
    10. qDebug() << "B open" << ok;
    11.  
    12. qDebug() << "But look" << a.isOpen() << b.isOpen();
    To copy to clipboard, switch view to plain text mode 
    produces this:
    Qt Code:
    1. A open true
    2. QSqlDatabasePrivate::removeDatabase: connection 'qt_sql_default_connection' is still in use, all queries will cease to work.
    3. QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.
    4. B open true
    5. But look false true
    To copy to clipboard, switch view to plain text mode 

  13. The following user says thank you to ChrisW67 for this useful post:

    sattu (4th February 2011)

  14. #11
    Join Date
    Sep 2010
    Location
    Bangalore
    Posts
    169
    Thanks
    59
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: copying between MSSql and a SQLite database

    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?

  15. #12
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: copying between MSSql and a SQLite database

    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:
    Qt Code:
    1. QSqlDatabase remote = QSqlDatabase("remote");
    To copy to clipboard, switch view to plain text mode 

  16. #13
    Join Date
    Sep 2010
    Location
    Bangalore
    Posts
    169
    Thanks
    59
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: copying between MSSql and a SQLite database

    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,
    Last edited by sattu; 4th February 2011 at 07:28. Reason: taaging codes

  17. #14
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    4,380
    Thanks
    19
    Thanked 1,005 Times in 913 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows Symbian S60
    Wiki edits
    5

    Default Re: copying between MSSql and a SQLite database

    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!
    Last edited by Lykurg; 4th February 2011 at 07:54.

  18. The following user says thank you to Lykurg for this useful post:

    sattu (4th February 2011)

  19. #15
    Join Date
    Sep 2010
    Location
    Bangalore
    Posts
    169
    Thanks
    59
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: copying between MSSql and a SQLite database

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

    Qt Code:
    1. void MainWindow::insert_remote()
    2. {
    3. QSqlQueryModel query_remote; ///////////db is for mssql
    4. QSqlQuery query(db2); ///////////db2 is for sqlite
    5. int id;
    6. QString name;
    7. query_remote.setQuery("select * from user20",db); ///////////user20 is the table of db
    8. rowcount= query_remote.rowCount(); ///////////user200 is the table of db2
    9. int j=0;
    10. while(j!=rowcount)
    11. {
    12. id = query_remote.record(j).value("ID").toInt();
    13. name = query_remote.record(j).value("NAME").toString();
    14. query.exec("insert into user200(Id,Name) values("+QString::number(id)+",'"+name+"')");
    15. j++;
    16. }
    17. query_remote.clear();
    18. }
    To copy to clipboard, switch view to plain text mode 

    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:
    Qt Code:
    1. qGetStringData: Error while fetching data ( "[FreeTDS][SQL Server]Program type out of range" )
    To copy to clipboard, switch view to plain text mode 

    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?

    thanking in advance,

  20. #16
    Join Date
    Jan 2006
    Location
    Belgium
    Posts
    1,938
    Thanked 268 Times in 268 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows
    Wiki edits
    20

    Default Re: copying between MSSql and a SQLite database


  21. The following user says thank you to tbscope for this useful post:

    sattu (5th February 2011)

  22. #17
    Join Date
    Sep 2010
    Location
    Bangalore
    Posts
    169
    Thanks
    59
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: copying between MSSql and a SQLite database

    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?

    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.

  23. #18
    Join Date
    Sep 2010
    Location
    Bangalore
    Posts
    169
    Thanks
    59
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: copying between MSSql and a SQLite database

    hi lykurg!

    sorry, now we had 2 days off from office.

    Upto now, i have done this:

    Qt Code:
    1. QSqlQuery query_rem(db);
    2. bool ok = false;
    3. ok = db.transaction();
    4. QSqlQuery query(db2);
    5.  
    6.  
    7.  
    8. query.prepare("INSERT INTO user200 (Id,Name) "
    9. "VALUES (:id,:name)");
    10. query.bindValue(0, 1007);
    11. query.bindValue(1, "Bibhu_gigin");
    12. query.exec();
    13. ok = db.commit();
    To copy to clipboard, switch view to plain text mode 

    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
    Last edited by sattu; 7th February 2011 at 16:12.

  24. #19
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: copying between MSSql and a SQLite database

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

  25. #20
    Join Date
    Sep 2010
    Location
    Bangalore
    Posts
    169
    Thanks
    59
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: copying between MSSql and a SQLite database

    Quote Originally Posted by ChrisW67 View Post
    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:
    Qt Code:
    1. QSqlQuery query(db); /////////db is for remote mssql
    2. QSqlQuery query2(db2); /////////db2 is for local sqlite
    3. int id;
    4. QString name;
    5. query.exec("select * from user20"); ///////user20 is the table of db(remote)
    6. bool ok = false;
    7. ok = db2.transaction();
    8. query2.prepare("INSERT INTO user200 (Id,Name) VALUES (:id,:name)");
    9.  
    10. while(query.next())
    11. {
    12. id = query.record().value(0).toInt();
    13. name = query.record().value(1).toString();
    14. query2.bindValue(0, id);
    15. query2.bindValue(1, name);
    16. }
    17. query2.exec();
    18. ok = db.commit()
    To copy to clipboard, switch view to plain text mode 

    Is this the way, you want me to execute?

Similar Threads

  1. Replies: 6
    Last Post: 25th November 2010, 22:02
  2. Use QT to set password for SQLITE database. help...!
    By Kevin Hoang in forum Qt Programming
    Replies: 1
    Last Post: 11th March 2010, 08:28
  3. SQLITE ATTACH database
    By drescherjm in forum Qt Programming
    Replies: 8
    Last Post: 9th December 2009, 08:25
  4. SQLITE database problems
    By phoenix in forum Newbie
    Replies: 3
    Last Post: 30th April 2007, 22:38
  5. [QT4][SQLITE] Database and query
    By agent007se in forum Newbie
    Replies: 10
    Last Post: 12th July 2006, 23:16

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.