QSqlDatabase and "CREATE DATABASE ..." ??

[codematic]

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

[Lesiok]

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

[spirit]

take a look at QSqlQuery.

[JohannesMunk]

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:



QSqlDatabase* db;
..
db = new QSqlDatabase(QSqlDatabase::addDatabase("QMYSQL",CDBIndex));
db->setPort(Port);
db->setHostName(hostname);
db->setUserName(username);
db->setPassword(password);

Then the setup:

QSqlQuery query(*db);
bool sr;
sr = query.exec("CREATE DATABASE IF NOT EXISTS "+dbname);
if (!sr) {ShowError(QObject::tr("Database Error")+" "+db->lastError().text(),true);}
sr = query.exec("USE "+dbname);
// Make sure the correct Table Setup is present in DB
// Entries: id, caption, propmask, properties
sr = query.exec("CREATE TABLE IF NOT EXISTS Entries (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, "
"caption VARCHAR(255) UNICODE, propmask LONGBLOB, properties LONGBLOB) ENGINE=InnoDB");
if (!sr) {ShowError(QObject::tr("Database Error")+" "+db->lastError().text(),true);}


Hope it helps! Good luck!

Joh

[wysota]

QSqlDatabase* db;
..
db = new QSqlDatabase(QSqlDatabase::addDatabase("QMYSQL",CDBIndex));



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.

[JohannesMunk]

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

[wysota]

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.

[Lesiok]

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.

[wysota]

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.

[JohannesMunk]

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.


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!


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:




// server connection - as stated in the docu
// without db.setDatabaseName("customdb");
{
// Parameter to addDatabase specifies the db-server-type / driver
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("acidalia");
db.setUserName("mojito");
db.setPassword("J0a1m8");
// you can check here if all settings work..
bool ok = db.open();
}

// Setup the db and start using it somewhere after successfully connecting to the server..
{
// Get back the default database connection.
QSqlDatabase db = QSqlDatabase::database();
// Create a new query on it
QSqlQuery query(db);
bool sr;
// Create your database if it does not exist already
sr = query.exec("CREATE DATABASE IF NOT EXISTS "+dbname);
if (!sr) {ShowError(QObject::tr("Database Error")+" "+db.lastError().text(),true);}

// Start using that database..
sr = query.exec("USE "+dbname);

// Make sure the correct Table Setup is present in DB..
sr = query.exec("CREATE TABLE IF NOT EXISTS Entries (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, "
"caption VARCHAR(255) UNICODE, propmask LONGBLOB, properties LONGBLOB) ENGINE=InnoDB");
if (!sr) {ShowError(QObject::tr("Database Error")+" "+db.lastError().text(),true);}
...
}

// somewhere you'll need:
{
QSqlDatabase db = QSqlDatabase::database();
db.close();
}


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

[wysota]

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.

[JohannesMunk]

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

[Lesiok]

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.

[Lesiok]

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:




// server connection - as stated in the docu
// without db.setDatabaseName("customdb");
{
// Parameter to addDatabase specifies the db-server-type / driver
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("acidalia");
db.setUserName("mojito");
db.setPassword("J0a1m8");
// you can check here if all settings work..
bool ok = db.open();
}

And on PostgreSQL here
ok == false and error from server is

database "mojito" not exists
QPSQL: Unable to connect

So I think that this is DB dependet.

[wysota]

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.

[JohannesMunk]

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

[Lesiok]

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
SELECT datname FROM pg_catalog.pg_database You retrieve list of all databases.
Very simple and effective mechanism.

[Lesiok]

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.

[codematic]

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

[sophister]

Maybe SQLite is easier than MYSQL to use.

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