Results 1 to 20 of 20

Thread: QSqlDatabase and "CREATE DATABASE ..." ??

  1. #1
    Join Date
    Feb 2009
    Posts
    8
    Thanks
    1

    Default QSqlDatabase and "CREATE DATABASE ..." ??

    Greetings,

    How do i execute the "CREATE DATABASE tablename" when using the QSqlDatabase class ?

    It seems that you have to successfully "open" the database via the Open() call before you can perform any queries...

    So im confused...

    Any ideas anyone ?

    Thanks,
    -Lenny

  2. #2
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,531
    Thanked 281 Times in 276 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlDatabase and "CREATE DATABASE ..." ??

    Usually You must connect to "service database". On PostgreSQL it is database named postgres.

  3. #3
    Join Date
    Aug 2008
    Location
    Ukraine, Krivoy Rog
    Posts
    1,963
    Thanked 370 Times in 336 Posts
    Qt products
    Qt3 Qt4 Qt5
    Platforms
    MacOS X Unix/X11 Windows

    Default Re: QSqlDatabase and "CREATE DATABASE ..." ??

    take a look at QSqlQuery.
    Qt Assistant -- rocks!
    please, use tags [CODE] & [/CODE].

  4. #4
    Join Date
    Feb 2007
    Location
    Karlsruhe, Germany
    Posts
    469
    Thanks
    17
    Thanked 90 Times in 88 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Lightbulb Re: QSqlDatabase and "CREATE DATABASE ..." ??

    Hi!

    Yeah.. the naming is a bit confusing. QSqlDatabase.. connects to a server.

    You can create databases.. with queries:

    query.exec("CREATE DATABASE IF NOT EXISTS "+dbname);

    When you want to start using a specific database you do:
    query.exec("USE "+dbname);

    in code:

    Qt Code:
    1. ..
    2. db = new QSqlDatabase(QSqlDatabase::addDatabase("QMYSQL",CDBIndex));
    3. db->setPort(Port);
    4. db->setHostName(hostname);
    5. db->setUserName(username);
    6. db->setPassword(password);
    To copy to clipboard, switch view to plain text mode 
    Then the setup:
    Qt Code:
    1. QSqlQuery query(*db);
    2. bool sr;
    3. sr = query.exec("CREATE DATABASE IF NOT EXISTS "+dbname);
    4. if (!sr) {ShowError(QObject::tr("Database Error")+" "+db->lastError().text(),true);}
    5. sr = query.exec("USE "+dbname);
    6. // Make sure the correct Table Setup is present in DB
    7. // Entries: id, caption, propmask, properties
    8. sr = query.exec("CREATE TABLE IF NOT EXISTS Entries (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, "
    9. "caption VARCHAR(255) UNICODE, propmask LONGBLOB, properties LONGBLOB) ENGINE=InnoDB");
    10. if (!sr) {ShowError(QObject::tr("Database Error")+" "+db->lastError().text(),true);}
    To copy to clipboard, switch view to plain text mode 

    Hope it helps! Good luck!

    Joh
    Last edited by wysota; 6th April 2009 at 22:19. Reason: reformatted to look better

  5. The following user says thank you to JohannesMunk for this useful post:

    codematic (11th April 2009)

  6. #5
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,365
    Thanks
    3
    Thanked 5,014 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: QSqlDatabase and "CREATE DATABASE ..." ??

    Quote Originally Posted by JohannesMunk View Post
    Qt Code:
    1. ..
    2. db = new QSqlDatabase(QSqlDatabase::addDatabase("QMYSQL",CDBIndex));
    To copy to clipboard, switch view to plain text mode 
    Hmm... I suggest looking at the docs. QSqlDatabase is a value-based class. You should create it on the stack and not on the heap. And you don't need to store pointers to it as you can always retrieve the object by name nor you should use the constructor directly.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  7. #6
    Join Date
    Feb 2007
    Location
    Karlsruhe, Germany
    Posts
    469
    Thanks
    17
    Thanked 90 Times in 88 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Question Re: QSqlDatabase and "CREATE DATABASE ..." ??

    Quote Originally Posted by wysota View Post
    Hmm... I suggest looking at the docs. QSqlDatabase is a value-based class. You should create it on the stack and not on the heap. And you don't need to store pointers to it as you can always retrieve the object by name nor you should use the constructor directly.
    Yes. I read that. But why should I do a named lookup for every query when I can store the pointer instead? I stored it as a class member in my database abstraction class. Works perfectly. Where is there going to be a problem?

    Joh

  8. #7
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,365
    Thanks
    3
    Thanked 5,014 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: QSqlDatabase and "CREATE DATABASE ..." ??

    Quote Originally Posted by JohannesMunk View Post
    Yes. I read that. But why should I do a named lookup for every query when I can store the pointer instead?
    The funny thing is you don't have to store the connection anywhere because Qt will automatically pick up the default connection. The name lookup is hash-based so it yields almost no overhead (apart from using a mutex to lock the dictionary).

    I stored it as a class member in my database abstraction class. Works perfectly. Where is there going to be a problem?
    You have to pass *db everywhere which is awkward. And you have to delete the instance of the object somewhere.

    Besides, there is a rule of a thumb that implicitly shared classes are to be created on the stack. There is nothing wrong in keeping a pointer to a database - if you want to do it and you have a reason, do it, but please don't teach others to follow Passing pointers around is not a good C++ habit.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  9. The following user says thank you to wysota for this useful post:

    JohannesMunk (7th April 2009)

  10. #8
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,531
    Thanked 281 Times in 276 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlDatabase and "CREATE DATABASE ..." ??

    Sorry JohannesMunk but Yours solution is not working on PostgreSQL (and on another DB too I think). Before executing some query You must call QSqlDatabase::open() method. This method connecting to the real database not to the server.

  11. #9
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,365
    Thanks
    3
    Thanked 5,014 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: QSqlDatabase and "CREATE DATABASE ..." ??

    Quote Originally Posted by Lesiok View Post
    Sorry JohannesMunk but Yours solution is not working on PostgreSQL (and on another DB too I think). Before executing some query You must call QSqlDatabase::open() method. This method connecting to the real database not to the server.
    There is always a real database called "postgresql" on each postgresql server. Maybe you don't have privileges to connect to it? And I'm not convinced you have to open any databases to be able to create other databases. Maybe that's a permission problem as well? Connecting to the server itself should be enough.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  12. #10
    Join Date
    Feb 2007
    Location
    Karlsruhe, Germany
    Posts
    469
    Thanks
    17
    Thanked 90 Times in 88 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Post Re: QSqlDatabase and "CREATE DATABASE ..." ??

    Quote Originally Posted by wysota View Post
    The funny thing is you don't have to store the connection anywhere because Qt will automatically pick up the default connection. The name lookup is hash-based so it yields almost no overhead (apart from using a mutex to lock the dictionary). You have to pass *db everywhere which is awkward. And you have to delete the instance of the object somewhere.
    Ok. That's what I figured, but that overhead is to much for me. >>1 dbs and very frequent queries. I think having a hidden global db connection list, with named lookup could be regarded as equally awkward. But if thats the proper way to go, then so be it. And if you only have one db, I guess it's a very convenient way to go, too.

    Quote Originally Posted by wysota View Post
    Besides, there is a rule of a thumb that implicitly shared classes are to be created on the stack. There is nothing wrong in keeping a pointer to a database - if you want to do it and you have a reason, do it, but please don't teach others to follow Passing pointers around is not a good C++ habit.
    Allright!

    Quote Originally Posted by Lesiok View Post
    Sorry JohannesMunk but Yours solution is not working on PostgreSQL (and on another DB too I think). Before executing some query You must call QSqlDatabase::open() method. This method connecting to the real database not to the server.
    No.. You can either open the database manually, or let QT do that for you, when you first use it:

    From the docu:

    QSqlDatabase::database ( const QString & connectionName = QLatin1String( defaultConnection ), bool open = true )
    Returns the database connection called connectionName. The database connection must have been previously added with addDatabase(). If open is true (the default) and the database connection is not already open it is opened now[/CODE]

    But you can also open it directly, which gives you direct feedback, if your connection settings are ok.

    I give a shot at a more complete and correct (not pointer optimized) solution:

    Qt Code:
    1. // server [B]connection[/B] - as stated in the docu
    2. // without db.setDatabaseName("customdb");
    3. {
    4. // Parameter to addDatabase specifies the db-server-type / driver
    5. QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    6. db.setHostName("acidalia");
    7. db.setUserName("mojito");
    8. db.setPassword("J0a1m8");
    9. // you can check here if all settings work..
    10. bool ok = db.open();
    11. }
    12.  
    13. // [B]Setup[/B] the db and start using it somewhere after successfully connecting to the server..
    14. {
    15. // Get back the default database connection.
    16. QSqlDatabase db = QSqlDatabase::database();
    17. // Create a new query on it
    18. QSqlQuery query(db);
    19. bool sr;
    20. // Create your database if it does not exist already
    21. sr = query.exec("CREATE DATABASE IF NOT EXISTS "+dbname);
    22. if (!sr) {ShowError(QObject::tr("Database Error")+" "+db.lastError().text(),true);}
    23.  
    24. // Start using that database..
    25. sr = query.exec("USE "+dbname);
    26.  
    27. // Make sure the correct Table Setup is present in DB..
    28. sr = query.exec("CREATE TABLE IF NOT EXISTS Entries (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, "
    29. "caption VARCHAR(255) UNICODE, propmask LONGBLOB, properties LONGBLOB) ENGINE=InnoDB");
    30. if (!sr) {ShowError(QObject::tr("Database Error")+" "+db.lastError().text(),true);}
    31. ...
    32. }
    33.  
    34. // somewhere you'll need:
    35. {
    36. QSqlDatabase db = QSqlDatabase::database();
    37. db.close();
    38. }
    To copy to clipboard, switch view to plain text mode 

    if you need more than one connection, just pass a 2nd parameter to QSqlDatabase::addDatabase("QMYSQL","internal connection name"); and pass it to the following QSqlDatabase::database("internal connection name") calls aswell.

    @codematic: Problem solved?

    Joh

  13. #11
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,365
    Thanks
    3
    Thanked 5,014 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: QSqlDatabase and "CREATE DATABASE ..." ??

    Quote Originally Posted by JohannesMunk View Post
    Ok. That's what I figured, but that overhead is to much for me. >>1 dbs and very frequent queries.
    Don't be silly. How many concurrent connections do you have? 1000000? With only a few connections there is no overhead. Hash has an amortised O(1) lookup complexity.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  14. #12
    Join Date
    Feb 2007
    Location
    Karlsruhe, Germany
    Posts
    469
    Thanks
    17
    Thanked 90 Times in 88 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Post Re: QSqlDatabase and "CREATE DATABASE ..." ??

    Quote Originally Posted by wysota View Post
    Don't be silly. How many concurrent connections do you have? 1000000? With only a few connections there is no overhead. Hash has an amortised O(1) lookup complexity.
    Let's keep friendly, shall we? You are right. I could probably afford the waste of computation power. But why should I? Because I can? Thats never enough of a reason :->

    For me, a string based hash table is last resort if I can't solve it straight. And as I have a class encapsulating db-access for every server/connection anyway, it can handle that direct pointer too. But I guess it's philosophy, if you like pointers or not.

    Cheers!

    Joh

  15. #13
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,531
    Thanked 281 Times in 276 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlDatabase and "CREATE DATABASE ..." ??

    Quote Originally Posted by wysota View Post
    There is always a real database called "postgresql" on each postgresql server. Maybe you don't have privileges to connect to it? And I'm not convinced you have to open any databases to be able to create other databases. Maybe that's a permission problem as well? Connecting to the server itself should be enough.
    1. Please read my first post in this thread...
    2. I'm trying to connect to server on account with full privileges.

  16. #14
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,531
    Thanked 281 Times in 276 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlDatabase and "CREATE DATABASE ..." ??

    Quote Originally Posted by JohannesMunk View Post
    Allright!

    No.. You can either open the database manually, or let QT do that for you, when you first use it:

    From the docu:

    QSqlDatabase::database ( const QString & connectionName = QLatin1String( defaultConnection ), bool open = true )
    Returns the database connection called connectionName. The database connection must have been previously added with addDatabase(). If open is true (the default) and the database connection is not already open it is opened now.

    But you can also open it directly, which gives you direct feedback, if your connection settings are ok.

    I give a shot at a more complete and correct (not pointer optimized) solution:

    Qt Code:
    1. // server [B]connection[/B] - as stated in the docu
    2. // without db.setDatabaseName("customdb");
    3. {
    4. // Parameter to addDatabase specifies the db-server-type / driver
    5. QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    6. db.setHostName("acidalia");
    7. db.setUserName("mojito");
    8. db.setPassword("J0a1m8");
    9. // you can check here if all settings work..
    10. bool ok = db.open();
    11. }
    To copy to clipboard, switch view to plain text mode 
    And on PostgreSQL here
    Qt Code:
    1. ok == false
    To copy to clipboard, switch view to plain text mode 
    and error from server is
    Qt Code:
    1. database "mojito" not exists
    2. QPSQL: Unable to connect
    To copy to clipboard, switch view to plain text mode 

    So I think that this is DB dependet.

  17. #15
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,365
    Thanks
    3
    Thanked 5,014 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: QSqlDatabase and "CREATE DATABASE ..." ??

    From what I see postgresql by default tries to connect to the database called the same as the database user (so if you connect on behalf of user "xyz", you will be connected to database "xyz"). At least that's what the default client does. Probably if you don't specify the database name, you'll end up trying to connect to the default database which fails because no such db exists. I don't know if Qt behaves the same way (it's easy to check in the source code) but I assume it delegates it to psql client library which could behave this way if the default client behaves that way as well.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  18. #16
    Join Date
    Feb 2007
    Location
    Karlsruhe, Germany
    Posts
    469
    Thanks
    17
    Thanked 90 Times in 88 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Question Re: QSqlDatabase and "CREATE DATABASE ..." ??

    Just out of curiosity: How would you programatically create a database in postgresql then?

  19. #17
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,531
    Thanked 281 Times in 276 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlDatabase and "CREATE DATABASE ..." ??

    Quote Originally Posted by JohannesMunk View Post
    Just out of curiosity: How would you programatically create a database in postgresql then?
    As I say in my first post in this thread. On PostgreSQL server allways exist "service database" called postgres. In this database is saved dictionary of databases, tables, function, users etc.
    With this query
    Qt Code:
    1. SELECT datname FROM pg_catalog.pg_database
    To copy to clipboard, switch view to plain text mode 
    You retrieve list of all databases.
    Very simple and effective mechanism.

  20. The following user says thank you to Lesiok for this useful post:

    JohannesMunk (7th April 2009)

  21. #18
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,531
    Thanked 281 Times in 276 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlDatabase and "CREATE DATABASE ..." ??

    Quote Originally Posted by wysota View Post
    From what I see postgresql by default tries to connect to the database called the same as the database user (so if you connect on behalf of user "xyz", you will be connected to database "xyz"). At least that's what the default client does. Probably if you don't specify the database name, you'll end up trying to connect to the default database which fails because no such db exists. I don't know if Qt behaves the same way (it's easy to check in the source code) but I assume it delegates it to psql client library which could behave this way if the default client behaves that way as well.
    Yes. QPSQLDriver::open method do nothing with parameters. If dbName is empty than dbName parameter for PostgreSQL driver is empty too. And farther all is DB dependet.

  22. #19
    Join Date
    Feb 2009
    Posts
    8
    Thanks
    1

    Default Re: QSqlDatabase and "CREATE DATABASE ..." ??

    Ooh Ok... That makes sense... i'll try that... Thanks for the great examples.

  23. #20
    Join Date
    Apr 2009
    Location
    China
    Posts
    127
    Thanks
    30
    Thanked 4 Times in 4 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlDatabase and "CREATE DATABASE ..." ??

    Maybe SQLite is easier than MYSQL to use.

    And it seems that you do have to open the database before you perform any queries.

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.