Results 1 to 20 of 27

Thread: copying between MSSql and a SQLite database

Hybrid View

Previous Post Previous Post   Next Post Next Post
  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?

Similar Threads

  1. Replies: 6
    Last Post: 25th November 2010, 21: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, 07:28
  3. SQLITE ATTACH database
    By drescherjm in forum Qt Programming
    Replies: 8
    Last Post: 9th December 2009, 07:25
  4. SQLITE database problems
    By phoenix in forum Newbie
    Replies: 3
    Last Post: 30th April 2007, 21:38
  5. [QT4][SQLITE] Database and query
    By agent007se in forum Newbie
    Replies: 10
    Last Post: 12th July 2006, 22: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.