Something good to read about QSqlDatabase

[Morea]

Is there any good tutorial on QSqlDatabase?
I find it a little tricky to understand how to use it.

[spirit]

what are you confused?

[Morea]

Well it started with
db->isValid() // this works
db->isOpen() // this fails with segfault

so how do I check if I have a connection to the database?

Also, is it possible to create more persistant connections? I use PostgreSQL.

[spirit]

Well it started with
db->isValid() // this works
db->isOpen() // this fails with segfault

so how do I check if I have a connection to the database?

can you show compilable code with this seg-fault?

Also, is it possible to create more persistant connections? I use PostgreSQL.

yes, it is. you can create several database connection using,

QSqlDatabase QSqlDatabase::addDatabase ( const QString & type, const QString & connectionName = QLatin1String( defaultConnection ) )

QSqlDatabase QSqlDatabase::addDatabase ( const QString & type, const QString & connectionName = QLatin1String( defaultConnection ) )

To copy to clipboard, switch view to plain text mode 

but you must to set unique connectionName for needed connection

[Morea]

The code would be this (and the apply code is called when clicking on the button

#ifndef CONFIG_H
#define CONFIG_H
 
#include <QtSql>
#include <QtCore>
#include "ui_config.h"
 
class Config : public QDialog
{
	Q_OBJECT
public:
	Config(QWidget * parent = 0);
private:
	Ui::Config ui;
	QSqlDatabase* db;
	QSettings* settings;
	bool createDatabaseTable();
private slots:
	void apply();
	void noapply();
public slots:
};
 
#endif

#ifndef CONFIG_H
#define CONFIG_H

#include <QtSql>
#include <QtCore>
#include "ui_config.h"

class Config : public QDialog
{
	Q_OBJECT
public:
	Config(QWidget * parent = 0);
private:
	Ui::Config ui;
	QSqlDatabase* db;
	QSettings* settings;
	bool createDatabaseTable();
private slots:
	void apply();
	void noapply();
public slots:
};

#endif

To copy to clipboard, switch view to plain text mode 

#include "config.h"
#include <QtGui>
#include <QtCore>
#include "global.h"
 
Config::Config(QWidget *parent)
   : QDialog(parent, 0)
{
	setModal(true);
	ui.setupUi(this);
	connect(ui.apply,SIGNAL(clicked()),this,SLOT(apply()));
	connect(ui.noapply,SIGNAL(clicked()),this,SLOT(noapply()));
 
	settings = new QSettings(ORGANISATIONNAME,APPLICATIONNAME,this);
	// Read configured values, if they exist
	ui.hostname->setText(settings->value("hostname","").toString());
	ui.port->setText(settings->value("port","").toString());
	ui.database->setText(settings->value("database","").toString());
	ui.username->setText(settings->value("username","").toString());
	ui.password->setText(settings->value("password","").toString());
 
	db->QSqlDatabase::addDatabase("QPSQL");
	qDebug()<<db;
	qDebug()<<db->isValid();
	qDebug()<<"constructor done";
}
 
bool Config::createDatabaseTable()
{
	bool ok=false;
	if (! db->isOpen())
	{
		db->setHostName(settings->value("hostname","").toString());
		QString tmp = settings->value("port").toString();
		if (tmp != "");
			db->setPort(tmp.toInt());
		db->setDatabaseName(settings->value("database","").toString());
		db->setUserName(settings->value("username","").toString());
		db->setPassword(settings->value("password","").toString());
		ok = db->open();
	}
	else
	{
		ok=true;
	}
	if (ok)
	{
		QSqlQuery result = db->exec("SELECT * FROM DomainTable");
		if (result.isValid())
		{
			// table already exist.
			return true;	
		}
		else
		{
			// goahead and try to create the database table
				return false;
		}
	}
	else
	{
		QMessageBox::critical( this, "Failed to connect to database", "Edit connection settings again and apply");
		return false;
	}
}
 
void Config::noapply()
{
	this->done(0);
}
 
void Config::apply()
{
	settings->setValue("hostname",ui.hostname->text());
	settings->setValue("database",ui.database->text());
	settings->setValue("port",ui.port->text());
	settings->setValue("username",ui.username->text());
	settings->setValue("password",ui.password->text());
	settings->sync();
qDebug()<<"Appply";
qDebug()<<"valid "<<db->isValid();
//qDebug()<<"open "<<db->isOpen();
qDebug()<<"continue";
	bool ok=false;
	if (! db->isOpen())
	{
		db->setHostName(settings->value("hostname","").toString());
		QString tmp = settings->value("port").toString();
		if (tmp != "");
			db->setPort(tmp.toInt());
		db->setDatabaseName(settings->value("database","").toString());
		db->setUserName(settings->value("username","").toString());
		db->setPassword(settings->value("password","").toString());
		ok = db->open();
	}
	else
     	ok=true;
	if (ok)
	{
		ok = createDatabaseTable();
		if (ok)
			this->done(1);
		else
			QMessageBox::critical( this, "Failed to create the database table", "Check database log");
	}
	else
	{
		QMessageBox::critical( this, "Failed to connect to database", "Edit connection settings");
	}
	// else continue to work...
}

#include "config.h"
#include <QtGui>
#include <QtCore>
#include "global.h"

Config::Config(QWidget *parent)
   : QDialog(parent, 0)
{
	setModal(true);
	ui.setupUi(this);
	connect(ui.apply,SIGNAL(clicked()),this,SLOT(apply()));
	connect(ui.noapply,SIGNAL(clicked()),this,SLOT(noapply()));
	
	settings = new QSettings(ORGANISATIONNAME,APPLICATIONNAME,this);
	// Read configured values, if they exist
	ui.hostname->setText(settings->value("hostname","").toString());
	ui.port->setText(settings->value("port","").toString());
	ui.database->setText(settings->value("database","").toString());
	ui.username->setText(settings->value("username","").toString());
	ui.password->setText(settings->value("password","").toString());
	
	db->QSqlDatabase::addDatabase("QPSQL");
	qDebug()<<db;
	qDebug()<<db->isValid();
	qDebug()<<"constructor done";
}

bool Config::createDatabaseTable()
{
	bool ok=false;
	if (! db->isOpen())
	{
		db->setHostName(settings->value("hostname","").toString());
		QString tmp = settings->value("port").toString();
		if (tmp != "");
			db->setPort(tmp.toInt());
		db->setDatabaseName(settings->value("database","").toString());
		db->setUserName(settings->value("username","").toString());
		db->setPassword(settings->value("password","").toString());
		ok = db->open();
	}
	else
	{
		ok=true;
	}
	if (ok)
	{
		QSqlQuery result = db->exec("SELECT * FROM DomainTable");
		if (result.isValid())
		{
			// table already exist.
			return true;	
		}
		else
		{
			// goahead and try to create the database table
				return false;
		}
	}
	else
	{
		QMessageBox::critical( this, "Failed to connect to database", "Edit connection settings again and apply");
		return false;
	}
}

void Config::noapply()
{
	this->done(0);
}

void Config::apply()
{
	settings->setValue("hostname",ui.hostname->text());
	settings->setValue("database",ui.database->text());
	settings->setValue("port",ui.port->text());
	settings->setValue("username",ui.username->text());
	settings->setValue("password",ui.password->text());
	settings->sync();
qDebug()<<"Appply";
qDebug()<<"valid "<<db->isValid();
//qDebug()<<"open "<<db->isOpen();
qDebug()<<"continue";
	bool ok=false;
	if (! db->isOpen())
	{
		db->setHostName(settings->value("hostname","").toString());
		QString tmp = settings->value("port").toString();
		if (tmp != "");
			db->setPort(tmp.toInt());
		db->setDatabaseName(settings->value("database","").toString());
		db->setUserName(settings->value("username","").toString());
		db->setPassword(settings->value("password","").toString());
		ok = db->open();
	}
	else
     	ok=true;
	if (ok)
	{
		ok = createDatabaseTable();
		if (ok)
			this->done(1);
		else
			QMessageBox::critical( this, "Failed to create the database table", "Check database log");
	}
	else
	{
		QMessageBox::critical( this, "Failed to connect to database", "Edit connection settings");
	}
	// else continue to work...
}

To copy to clipboard, switch view to plain text mode 

<ui version="4.0" >
 <class>Config</class>
 <widget class="QDialog" name="Config" >
  <property name="geometry" >
   <rect>
    <x>0</x>
    <y>0</y>
    <width>561</width>
    <height>306</height>
   </rect>
  </property>
  <property name="sizePolicy" >
   <sizepolicy vsizetype="Fixed" hsizetype="Fixed" >
    <horstretch>0</horstretch>
    <verstretch>0</verstretch>
   </sizepolicy>
  </property>
  <property name="windowTitle" >
   <string>Database settings</string>
  </property>
  <widget class="QPushButton" name="apply" >
   <property name="geometry" >
    <rect>
     <x>70</x>
     <y>230</y>
     <width>81</width>
     <height>28</height>
    </rect>
   </property>
   <property name="text" >
    <string>Apply</string>
   </property>
  </widget>
  <widget class="QPushButton" name="noapply" >
   <property name="geometry" >
    <rect>
     <x>250</x>
     <y>230</y>
     <width>188</width>
     <height>28</height>
    </rect>
   </property>
   <property name="text" >
    <string>Do NOT apply values above</string>
   </property>
  </widget>
  <widget class="QLineEdit" name="username" >
   <property name="geometry" >
    <rect>
     <x>100</x>
     <y>130</y>
     <width>451</width>
     <height>24</height>
    </rect>
   </property>
  </widget>
  <widget class="QLabel" name="label" >
   <property name="geometry" >
    <rect>
     <x>20</x>
     <y>40</y>
     <width>81</width>
     <height>18</height>
    </rect>
   </property>
   <property name="text" >
    <string>Hostname</string>
   </property>
  </widget>
  <widget class="QLabel" name="label_2" >
   <property name="geometry" >
    <rect>
     <x>20</x>
     <y>70</y>
     <width>62</width>
     <height>18</height>
    </rect>
   </property>
   <property name="text" >
    <string>Port</string>
   </property>
  </widget>
  <widget class="QLabel" name="label_3" >
   <property name="geometry" >
    <rect>
     <x>20</x>
     <y>100</y>
     <width>62</width>
     <height>18</height>
    </rect>
   </property>
   <property name="text" >
    <string>Database</string>
   </property>
  </widget>
  <widget class="QLabel" name="label_4" >
   <property name="geometry" >
    <rect>
     <x>20</x>
     <y>130</y>
     <width>81</width>
     <height>18</height>
    </rect>
   </property>
   <property name="text" >
    <string>Username</string>
   </property>
  </widget>
  <widget class="QLineEdit" name="hostname" >
   <property name="geometry" >
    <rect>
     <x>100</x>
     <y>40</y>
     <width>451</width>
     <height>24</height>
    </rect>
   </property>
  </widget>
  <widget class="QLineEdit" name="port" >
   <property name="geometry" >
    <rect>
     <x>100</x>
     <y>70</y>
     <width>113</width>
     <height>24</height>
    </rect>
   </property>
  </widget>
  <widget class="QLineEdit" name="database" >
   <property name="geometry" >
    <rect>
     <x>100</x>
     <y>100</y>
     <width>451</width>
     <height>24</height>
    </rect>
   </property>
  </widget>
  <widget class="QLineEdit" name="password" >
   <property name="geometry" >
    <rect>
     <x>100</x>
     <y>160</y>
     <width>451</width>
     <height>24</height>
    </rect>
   </property>
  </widget>
  <widget class="QLabel" name="label_5" >
   <property name="geometry" >
    <rect>
     <x>20</x>
     <y>160</y>
     <width>62</width>
     <height>18</height>
    </rect>
   </property>
   <property name="text" >
    <string>Password</string>
   </property>
  </widget>
  <widget class="QLabel" name="label_6" >
   <property name="geometry" >
    <rect>
     <x>40</x>
     <y>200</y>
     <width>501</width>
     <height>18</height>
    </rect>
   </property>
   <property name="text" >
    <string>Notice that hitting apply will access the database and create database tables!</string>
   </property>
  </widget>
 </widget>
 <tabstops>
  <tabstop>hostname</tabstop>
  <tabstop>port</tabstop>
  <tabstop>database</tabstop>
  <tabstop>username</tabstop>
  <tabstop>password</tabstop>
  <tabstop>apply</tabstop>
  <tabstop>noapply</tabstop>
 </tabstops>
 <resources/>
 <connections/>
</ui>

<ui version="4.0" >
 <class>Config</class>
 <widget class="QDialog" name="Config" >
  <property name="geometry" >
   <rect>
    <x>0</x>
    <y>0</y>
    <width>561</width>
    <height>306</height>
   </rect>
  </property>
  <property name="sizePolicy" >
   <sizepolicy vsizetype="Fixed" hsizetype="Fixed" >
    <horstretch>0</horstretch>
    <verstretch>0</verstretch>
   </sizepolicy>
  </property>
  <property name="windowTitle" >
   <string>Database settings</string>
  </property>
  <widget class="QPushButton" name="apply" >
   <property name="geometry" >
    <rect>
     <x>70</x>
     <y>230</y>
     <width>81</width>
     <height>28</height>
    </rect>
   </property>
   <property name="text" >
    <string>Apply</string>
   </property>
  </widget>
  <widget class="QPushButton" name="noapply" >
   <property name="geometry" >
    <rect>
     <x>250</x>
     <y>230</y>
     <width>188</width>
     <height>28</height>
    </rect>
   </property>
   <property name="text" >
    <string>Do NOT apply values above</string>
   </property>
  </widget>
  <widget class="QLineEdit" name="username" >
   <property name="geometry" >
    <rect>
     <x>100</x>
     <y>130</y>
     <width>451</width>
     <height>24</height>
    </rect>
   </property>
  </widget>
  <widget class="QLabel" name="label" >
   <property name="geometry" >
    <rect>
     <x>20</x>
     <y>40</y>
     <width>81</width>
     <height>18</height>
    </rect>
   </property>
   <property name="text" >
    <string>Hostname</string>
   </property>
  </widget>
  <widget class="QLabel" name="label_2" >
   <property name="geometry" >
    <rect>
     <x>20</x>
     <y>70</y>
     <width>62</width>
     <height>18</height>
    </rect>
   </property>
   <property name="text" >
    <string>Port</string>
   </property>
  </widget>
  <widget class="QLabel" name="label_3" >
   <property name="geometry" >
    <rect>
     <x>20</x>
     <y>100</y>
     <width>62</width>
     <height>18</height>
    </rect>
   </property>
   <property name="text" >
    <string>Database</string>
   </property>
  </widget>
  <widget class="QLabel" name="label_4" >
   <property name="geometry" >
    <rect>
     <x>20</x>
     <y>130</y>
     <width>81</width>
     <height>18</height>
    </rect>
   </property>
   <property name="text" >
    <string>Username</string>
   </property>
  </widget>
  <widget class="QLineEdit" name="hostname" >
   <property name="geometry" >
    <rect>
     <x>100</x>
     <y>40</y>
     <width>451</width>
     <height>24</height>
    </rect>
   </property>
  </widget>
  <widget class="QLineEdit" name="port" >
   <property name="geometry" >
    <rect>
     <x>100</x>
     <y>70</y>
     <width>113</width>
     <height>24</height>
    </rect>
   </property>
  </widget>
  <widget class="QLineEdit" name="database" >
   <property name="geometry" >
    <rect>
     <x>100</x>
     <y>100</y>
     <width>451</width>
     <height>24</height>
    </rect>
   </property>
  </widget>
  <widget class="QLineEdit" name="password" >
   <property name="geometry" >
    <rect>
     <x>100</x>
     <y>160</y>
     <width>451</width>
     <height>24</height>
    </rect>
   </property>
  </widget>
  <widget class="QLabel" name="label_5" >
   <property name="geometry" >
    <rect>
     <x>20</x>
     <y>160</y>
     <width>62</width>
     <height>18</height>
    </rect>
   </property>
   <property name="text" >
    <string>Password</string>
   </property>
  </widget>
  <widget class="QLabel" name="label_6" >
   <property name="geometry" >
    <rect>
     <x>40</x>
     <y>200</y>
     <width>501</width>
     <height>18</height>
    </rect>
   </property>
   <property name="text" >
    <string>Notice that hitting apply will access the database and create database tables!</string>
   </property>
  </widget>
 </widget>
 <tabstops>
  <tabstop>hostname</tabstop>
  <tabstop>port</tabstop>
  <tabstop>database</tabstop>
  <tabstop>username</tabstop>
  <tabstop>password</tabstop>
  <tabstop>apply</tabstop>
  <tabstop>noapply</tabstop>
 </tabstops>
 <resources/>
 <connections/>
</ui>

To copy to clipboard, switch view to plain text mode 

[spirit]

the first:
what is this?

...
db->QSqlDatabase::addDatabase("QPSQL");
...

...
db->QSqlDatabase::addDatabase("QPSQL");
...

To copy to clipboard, switch view to plain text mode 

I didn't see where you create object using operator new.

the second:
you shouldn't use pointer to database, because QSqlDatabase class is shared.
so, I suggest you to revise you code: refuse using pointer to database (but if you prefer
to use pointer then you must use new operator)

you can also not to keep variable for database like a class member, you can always get a needed database object using

QSqlDatabase QSqlDatabase::database ( const QString & connectionName = QLatin1String( defaultConnection ), bool open = true )

QSqlDatabase QSqlDatabase::database ( const QString & connectionName = QLatin1String( defaultConnection ), bool open = true )

To copy to clipboard, switch view to plain text mode 

conclusion:
you can use the following code

void someObject::initDatabse
{
     QSqlDatabese db = QSqlDatabse::addDatabase("QPSQL", "myConnection");
     .....//do connection stuff
}
 
void someObject::execQuery(const QString &queryText)
{
    QSqlDatabse db = QSqlDatabase::database("myConnection");
    QSqlQuery query(db);
    query.prepare(queryText);
    ....
}

void someObject::initDatabse
{
     QSqlDatabese db = QSqlDatabse::addDatabase("QPSQL", "myConnection");
     .....//do connection stuff
}

void someObject::execQuery(const QString &queryText)
{
    QSqlDatabse db = QSqlDatabase::database("myConnection");
    QSqlQuery query(db);
    query.prepare(queryText);
    ....
}

To copy to clipboard, switch view to plain text mode 

[caduel]

• in Qt4 QSqlDatabase is a value type: no need to handle with pointers like QSqlDatabase*
• you did not initialize your db pointer (well, you should replace it by a QSqlDatabase anyway)

so replace QSqlDatabase *db; by QSqlDatabase db;
and db->QSqlDatabase::addDatabase("QPSQL"); by db = QSqlDatabase::addDatabase("QPSQL");

HTH

[Morea]

I'm currently editing

[spirit]

probaly you have set incorrect parameters for a database.

#include <QApplication>
#include <QtSql>
 
int main(int argc, char **argv)
{
    QApplication app(argc, argv);
 
    QSqlDatabase db2 = QSqlDatabase::addDatabase("QPSQL", "MyConnection");
    db2.setHostName("localhost");    
    db2.setDatabaseName("curriculum");
    db2.setUserName("postgres");
    db2.setPassword("sysdba");
 
    qDebug() << db2;//res == QSqlDatabase(driver=""QPSQL"", database=""curriculum"", host=""localhost"", port=-1, user=""postgres"", open=false) 
    qDebug() << QSqlDatabase::connectionNames();//res == MyConnection
    QSqlDatabase db = QSqlDatabase::database("MyConnection");
    qDebug() << db;//res == QSqlDatabase(driver=""QPSQL"", database=""curriculum"", host=""localhost"", port=-1, user=""postgres"", open=true) 
 
 
    return 0;
}

#include <QApplication>
#include <QtSql>

int main(int argc, char **argv)
{
    QApplication app(argc, argv);
	
    QSqlDatabase db2 = QSqlDatabase::addDatabase("QPSQL", "MyConnection");
    db2.setHostName("localhost");    
    db2.setDatabaseName("curriculum");
    db2.setUserName("postgres");
    db2.setPassword("sysdba");
    
    qDebug() << db2;//res == QSqlDatabase(driver=""QPSQL"", database=""curriculum"", host=""localhost"", port=-1, user=""postgres"", open=false) 
    qDebug() << QSqlDatabase::connectionNames();//res == MyConnection
    QSqlDatabase db = QSqlDatabase::database("MyConnection");
    qDebug() << db;//res == QSqlDatabase(driver=""QPSQL"", database=""curriculum"", host=""localhost"", port=-1, user=""postgres"", open=true) 


    return 0;
}

To copy to clipboard, switch view to plain text mode 

this code works fine.

[Morea]

with that code and setting port to -1 helped alot!