QSqlDatabase and "CREATE DATABASE ..." ??

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

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

[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

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.

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

[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 [B]connection[/B] - 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();
}
 
// [B]Setup[/B] 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();
}

// server [B]connection[/B] - 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();
}

// [B]Setup[/B] 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();
}

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

[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]

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 [B]connection[/B] - 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();
}

// server [B]connection[/B] - 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();
}

To copy to clipboard, switch view to plain text mode 

And on PostgreSQL here

ok == false

ok == false

To copy to clipboard, switch view to plain text mode 

and error from server is

database "mojito" not exists
QPSQL: Unable to connect

database "mojito" not exists
QPSQL: Unable to connect

To copy to clipboard, switch view to plain text mode 

So I think that this is DB dependet.