QSql Database

[addu]

Hi All

I want to use QSql in my application to call History..

I have gone through the qt examples and documents and i tried small examples based the examples given with Sdk.

I want to store call history..

In examples i did like

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if (!db.open()) {
               return;
    }
 QSqlQuery query;
    //query.exec("create table yuvaraj ( id int , name varchar(20000))");
    query.exec("insert into yuvaraj values(1000,'"+QString(str)+"')");
    model = new QSqlRelationalTableModel(this);
    model->setTable("yuvaraj");
    model->select();
 QMessageBox::information(0,"1",QString("yuvarj"));
    mapper = new QDataWidgetMapper(this);
    mapper->setModel(model);
    mapper->addMapping(ui->textBrowser, model->fieldIndex("name"));
    QMessageBox::information(0,"2",QString("yuvarj"));
    mapper->toFirst();

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if (!db.open()) {
               return;
    }
 QSqlQuery query;
    //query.exec("create table yuvaraj ( id int , name varchar(20000))");
    query.exec("insert into yuvaraj values(1000,'"+QString(str)+"')");
    model = new QSqlRelationalTableModel(this);
    model->setTable("yuvaraj");
    model->select();
 QMessageBox::information(0,"1",QString("yuvarj"));
    mapper = new QDataWidgetMapper(this);
    mapper->setModel(model);
    mapper->addMapping(ui->textBrowser, model->fieldIndex("name"));
    QMessageBox::information(0,"2",QString("yuvarj"));
    mapper->toFirst();

To copy to clipboard, switch view to plain text mode 

there isan another way to create own database

QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
     db.setHostName("customer");
     db.setDatabaseName("customdb");
     db.setUserName("yuvaraj");
     db.setPassword("yuvaraj");
     bool ok = db.open();

QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
     db.setHostName("customer");
     db.setDatabaseName("customdb");
     db.setUserName("yuvaraj");
     db.setPassword("yuvaraj");
     bool ok = db.open();

To copy to clipboard, switch view to plain text mode 

i have one doubt .which method i have to use.if declare this code at constructor every time database and table is creating..

Where can i find good example based one QSql apart from examples.

Can you please suggest me

Thanks

Yuvaraj R

[freezeblue]

Here is my example:
I create one thread for every database connection. That works nice! And the UI will not get blocked when there is a heavy database operation.

the header:

#ifndef DBTHREAD_H
#define DBTHREAD_H
 
#include "WorkerThread.h"
#include <QSqlDatabase>
 
class DbThread : public WorkerThread
{
public:
	DbThread(QObject *parent = 0, 
		QThread::Priority priority = QThread::NormalPriority);
	virtual ~DbThread();
 
protected:
	void setDbServer
		(const char* dbtype, const char* connection, 
		const char* hostname, const char* username, 
		const char* password, int port);
	bool openDb(const char* dbname);
	void closeDb();
	QSqlDatabase& getDb(const char* connection);
 
private:
	QSqlDatabase db;	
};
 
#endif // DBTHREAD_H

#ifndef DBTHREAD_H
#define DBTHREAD_H

#include "WorkerThread.h"
#include <QSqlDatabase>

class DbThread : public WorkerThread
{
public:
	DbThread(QObject *parent = 0, 
		QThread::Priority priority = QThread::NormalPriority);
	virtual ~DbThread();

protected:
	void setDbServer
		(const char* dbtype, const char* connection, 
		const char* hostname, const char* username, 
		const char* password, int port);
	bool openDb(const char* dbname);
	void closeDb();
	QSqlDatabase& getDb(const char* connection);

private:
	QSqlDatabase db;	
};

#endif // DBTHREAD_H

To copy to clipboard, switch view to plain text mode 

the realization:

#include "DbThread.h"
 
DbThread::DbThread(QObject *parent, QThread::Priority priority)
: WorkerThread(parent, priority)
{
 
}
 
DbThread::~DbThread()
{
}
 
void DbThread::setDbServer(const char* dbtype, const char* connection, 
	const char* hostname, const char* username, const char* password, int port)
{
	db = QSqlDatabase::addDatabase(dbtype, connection);
 
	db.setHostName(hostname);
	db.setUserName(username);
	db.setPassword(password);
	db.setPort(port);
 
	db = QSqlDatabase::database(connection, false);
 
	DBHEX("add connection complete", connection);
}
 
bool DbThread::openDb(const char* dbname)
{
	db.setDatabaseName(dbname);
	return db.open();
}
 
void DbThread::closeDb()
{
	db.close();
}
 
QSqlDatabase& DbThread::getDb(const char* connection)
{
	return db;
}

#include "DbThread.h"

DbThread::DbThread(QObject *parent, QThread::Priority priority)
: WorkerThread(parent, priority)
{

}

DbThread::~DbThread()
{
}

void DbThread::setDbServer(const char* dbtype, const char* connection, 
	const char* hostname, const char* username, const char* password, int port)
{
	db = QSqlDatabase::addDatabase(dbtype, connection);

	db.setHostName(hostname);
	db.setUserName(username);
	db.setPassword(password);
	db.setPort(port);
	
	db = QSqlDatabase::database(connection, false);

	DBHEX("add connection complete", connection);
}

bool DbThread::openDb(const char* dbname)
{
	db.setDatabaseName(dbname);
	return db.open();
}

void DbThread::closeDb()
{
	db.close();
}

QSqlDatabase& DbThread::getDb(const char* connection)
{
	return db;
}

To copy to clipboard, switch view to plain text mode 

Hope it be useful for you

[PaceyIV]

Can you also post your WorkerThread class?

[freezeblue]

WorkerThread is my own Qt thread wrapper for my project. Every type of thread in my project will inherit it. The DbThread is one of the.

Here is the code of WorkerThread:

#ifndef WORKERTHREAD_H
#define WORKERTHREAD_H
 
#include "common.h"
#include <QThread>
#include <QMutex>
#include <QWaitCondition>
#include <list>
using namespace std;
class Message;
//class QEvent;
 
class WorkerThread  : public QThread
{
public:
	WorkerThread(QObject *parent = 0, 
		QThread::Priority priority = QThread::NormalPriority);
	~WorkerThread();
	void QueueAction(Message& action);
 
protected:
	void run();
	QMutex mutex;
	QWaitCondition bufferEmpty;
	list<Message> m_listActionBuffer;
	Message* m_tempMsg;
	virtual void WorkerThreadMain(Message& action) = 0;
	void postEvent(Message* ev, uint32 type);
 
private:
	QThread::Priority m_priority;
};
 
#endif // WORKERTHREAD_H
 
#include "WorkerThread.h"
#include <QCoreApplication>
#include <QEvent>
#include "TEvent.h"
#include "Message.h"
 
WorkerThread::WorkerThread(QObject *parent, QThread::Priority priority)
:QThread(parent),m_priority(priority),m_tempMsg(NULL)
{
 
}
 
WorkerThread::~WorkerThread()
{
	if(NULL != m_tempMsg)
		delete m_tempMsg;
}
 
void WorkerThread::QueueAction(Message& Action)
{
	if(!isRunning()) {
		start(m_priority);
	}
 
	mutex.lock();
	m_listActionBuffer.push_back(Action);
	mutex.unlock();
 
 
	mutex.lock();
	if(!m_listActionBuffer.empty())
		bufferEmpty.wakeOne();
	mutex.unlock();
}
 
void WorkerThread::run() {
	DBHEX("worker thread start@", (int)this);
	for(;;) {
		mutex.lock();
		if(m_listActionBuffer.empty())
			bufferEmpty.wait(&mutex);
		mutex.unlock();
 
		mutex.lock();
		Message Action = m_listActionBuffer.front();
		m_listActionBuffer.pop_front();
		mutex.unlock();
 
		WorkerThreadMain(Action);
	}
}
 
void WorkerThread::postEvent(Message* ev, uint32 type)
{
	QEvent* qev= new TEvent((QEvent::Type)type, ev);
	QCoreApplication::postEvent(parent(), qev,Qt::HighEventPriority);
}

#ifndef WORKERTHREAD_H
#define WORKERTHREAD_H

#include "common.h"
#include <QThread>
#include <QMutex>
#include <QWaitCondition>
#include <list>
using namespace std;
class Message;
//class QEvent;

class WorkerThread  : public QThread
{
public:
	WorkerThread(QObject *parent = 0, 
		QThread::Priority priority = QThread::NormalPriority);
	~WorkerThread();
	void QueueAction(Message& action);

protected:
	void run();
	QMutex mutex;
	QWaitCondition bufferEmpty;
	list<Message> m_listActionBuffer;
	Message* m_tempMsg;
	virtual void WorkerThreadMain(Message& action) = 0;
	void postEvent(Message* ev, uint32 type);

private:
	QThread::Priority m_priority;
};

#endif // WORKERTHREAD_H

#include "WorkerThread.h"
#include <QCoreApplication>
#include <QEvent>
#include "TEvent.h"
#include "Message.h"

WorkerThread::WorkerThread(QObject *parent, QThread::Priority priority)
:QThread(parent),m_priority(priority),m_tempMsg(NULL)
{

}

WorkerThread::~WorkerThread()
{
	if(NULL != m_tempMsg)
		delete m_tempMsg;
}

void WorkerThread::QueueAction(Message& Action)
{
	if(!isRunning()) {
		start(m_priority);
	}

	mutex.lock();
	m_listActionBuffer.push_back(Action);
	mutex.unlock();


	mutex.lock();
	if(!m_listActionBuffer.empty())
		bufferEmpty.wakeOne();
	mutex.unlock();
}

void WorkerThread::run() {
	DBHEX("worker thread start@", (int)this);
	for(;;) {
		mutex.lock();
		if(m_listActionBuffer.empty())
			bufferEmpty.wait(&mutex);
		mutex.unlock();

		mutex.lock();
		Message Action = m_listActionBuffer.front();
		m_listActionBuffer.pop_front();
		mutex.unlock();

		WorkerThreadMain(Action);
	}
}

void WorkerThread::postEvent(Message* ev, uint32 type)
{
	QEvent* qev= new TEvent((QEvent::Type)type, ev);
	QCoreApplication::postEvent(parent(), qev,Qt::HighEventPriority);
}

To copy to clipboard, switch view to plain text mode 

And an example of one database connection thread:

///////////////////////////////////////////////////////////
//  BmDbThread.h
//  Implementation of the Class BmDbThread
//  Created on:      05-五月-2009 11:40:31
//  Original author: Tim Kuo
///////////////////////////////////////////////////////////
 
#if !defined(EA_6594F28D_DB37_4636_8095_CAF8A14A53D9__INCLUDED_)
#define EA_6594F28D_DB37_4636_8095_CAF8A14A53D9__INCLUDED_
 
#include "DbThread.h"
class Business;
class BusinessType;
class QByteArray;
class Message;
#include <list>
using namespace std;
 
class BmDbThread : public DbThread
{
public:
	BmDbThread(QObject *parent = 0, 
		QThread::Priority priority = QThread::NormalPriority);
	virtual ~BmDbThread();
 
protected:
	virtual void WorkerThreadMain(Message& action);
 
private:
	bool addBusiness(Business* data);
	bool addBusinessType(BusinessType* data);
	bool addImage(uint32 id, QByteArray& data);
	Business* getBusiness(uint32 id);
	list<BusinessType>* getBusinessTypes();
	QByteArray* getImage(uint32 id);
	bool modifyBusiness(Business* data);
	bool modifyBusinessType(BusinessType* data);
	bool removeBusiness(uint32 id);
	bool removeBusinessType(uint32 id);
	bool removeImage(uint32 id);
	list<Business>* getAllBusiness();
 
};
#endif // !defined(EA_6594F28D_DB37_4636_8095_CAF8A14A53D9__INCLUDED_)
///////////////////////////////////////////////////////////
//  BmDbThread.cpp
//  Implementation of the Class BmDbThread
//  Created on:      05-五月-2009 11:40:31
//  Original author: Tim Kuo
///////////////////////////////////////////////////////////
 
#include "BmDbThread.h"
#include "Business.h"
#include "BusinessType.h"
#include "Message.h"
#include "messagedef.h"
#include <QByteArray>
#include <QSqlQuery>
#include <QVariant>
#include "dbquery.h"
 
BmDbThread::BmDbThread(QObject *parent, QThread::Priority priority)
:DbThread(parent, priority)
{
	setDbServer("QSQLITE", DBCONNECTION_BM, "", "", "", 0);
}
 
 
BmDbThread::~BmDbThread(){
 
}
 
void BmDbThread::WorkerThreadMain(Message& action)
{
	DBHEX("bm db thread processing action: ", action.type());
	switch(action.type()) {
		case ACTION_GETBUSINESSTYPE:
		{
			m_tempMsg = new Message(EVENT_BUSINESSTYPE, getBusinessTypes());
			break;
		}
		case ACTION_GETALLBUSINESS:
		{
			m_tempMsg = new Message(EVENT_ALLBUSINESS, getAllBusiness());
			break;
		}
		case ACTION_GETBUSINESS:
		{
			uint32* id = static_cast<uint32*>(action.data());
			m_tempMsg = new Message(EVENT_BUSINESS, getBusiness(*id));
			delete id;
			break;
		}
		case ACTION_GETBUSINESSPIC:
		{
			uint32* id = static_cast<uint32*>(action.data());
			m_tempMsg = new Message(EVENT_BUSINESSPIC, getImage(*id));
			delete id;
			break;
		}
		case ACTION_ADDBUSINESS:
		{
			Business* business = static_cast<Business*>(action.data());
			QByteArray* image = static_cast<QByteArray*>(action.data2());
			Business* addedBusiness = NULL;
			m_tempMsg = new Message(EVENT_ADDBUSINESS);
			if(addBusiness(business)) {
				addedBusiness = getBusiness(business->id());
				m_tempMsg->setData(addedBusiness);
				if(NULL != addedBusiness && NULL != image && !image->isEmpty()) {
					if(addImage(addedBusiness->id(), *image))
						m_tempMsg->setData2(getImage(addedBusiness->id()));
					image->clear();
				}
			}
			delete business;
			delete image;
			break;
		}
		case ACTION_REMOVEBUSINESS:
		{
			uint32* id = static_cast<uint32*>(action.data());
			uint32* result = new uint32(*id);
			if(!removeBusiness(*id))
				*result = 0;
			m_tempMsg = new Message(EVENT_REMOVEBUSINESS, result);
			delete id;
			break;
		}
		case ACTOIN_MODIFYBUSINESS:
		{
			Business* toModify = static_cast<Business*>(action.data());
			QByteArray* image = static_cast<QByteArray*>(action.data2());
			Business* modified = NULL;
			m_tempMsg = new Message(EVENT_MODIFYBUSINESS);
			if(modifyBusiness(toModify)) {
				modified = getBusiness(toModify->id());
				m_tempMsg->setData(modified);
				if(NULL != image && !image->isEmpty() && toModify->id() != 0) {
					if(addImage(modified->id(), *image))
						m_tempMsg->setData2(getImage(modified->id()));
				}
			}
			delete toModify;
			break;
		}
		case ACTION_VIEWBUSINESSRECORD:
		{
			break;
		}
		case ACTION_SETBUSINESSTYPE:
		{
			list<BusinessType>* toSet = 
				static_cast<list<BusinessType>*>(action.data());
			list<BusinessType>* result = new list<BusinessType>;
			list<BusinessType>::iterator it = toSet->begin();
			while(toSet->end() != it) {
				if(!addBusinessType(&(*it)))
					result->push_back(*it);
				it++;
			}
			delete toSet;
			m_tempMsg = new Message(EVENT_SETBUSINESSTYPE, result);
			break;
		}
		case ACTION_REMOVEBUSINESSTYPE:
		{
			list<BusinessType>* toRemove = 
				static_cast<list<BusinessType>*>(action.data());
			list<BusinessType>* result = new list<BusinessType>;
			list<BusinessType>::iterator it = toRemove->begin();
			while(toRemove->end() != it) {
				if(!removeBusinessType(it->getId()))
					result->push_back(*it);
				it++;
			}
			delete toRemove;
			m_tempMsg = new Message(EVENT_REMOVEBUSINESSTYPE, result);
			break;
 
		}
		default: break;	
	}
	if(NULL != m_tempMsg) {
		postEvent(m_tempMsg, EventDb);
		m_tempMsg = NULL;
	}
}

///////////////////////////////////////////////////////////
//  BmDbThread.h
//  Implementation of the Class BmDbThread
//  Created on:      05-五月-2009 11:40:31
//  Original author: Tim Kuo
///////////////////////////////////////////////////////////

#if !defined(EA_6594F28D_DB37_4636_8095_CAF8A14A53D9__INCLUDED_)
#define EA_6594F28D_DB37_4636_8095_CAF8A14A53D9__INCLUDED_

#include "DbThread.h"
class Business;
class BusinessType;
class QByteArray;
class Message;
#include <list>
using namespace std;

class BmDbThread : public DbThread
{
public:
	BmDbThread(QObject *parent = 0, 
		QThread::Priority priority = QThread::NormalPriority);
	virtual ~BmDbThread();
	
protected:
	virtual void WorkerThreadMain(Message& action);

private:
	bool addBusiness(Business* data);
	bool addBusinessType(BusinessType* data);
	bool addImage(uint32 id, QByteArray& data);
	Business* getBusiness(uint32 id);
	list<BusinessType>* getBusinessTypes();
	QByteArray* getImage(uint32 id);
	bool modifyBusiness(Business* data);
	bool modifyBusinessType(BusinessType* data);
	bool removeBusiness(uint32 id);
	bool removeBusinessType(uint32 id);
	bool removeImage(uint32 id);
	list<Business>* getAllBusiness();

};
#endif // !defined(EA_6594F28D_DB37_4636_8095_CAF8A14A53D9__INCLUDED_)
///////////////////////////////////////////////////////////
//  BmDbThread.cpp
//  Implementation of the Class BmDbThread
//  Created on:      05-五月-2009 11:40:31
//  Original author: Tim Kuo
///////////////////////////////////////////////////////////

#include "BmDbThread.h"
#include "Business.h"
#include "BusinessType.h"
#include "Message.h"
#include "messagedef.h"
#include <QByteArray>
#include <QSqlQuery>
#include <QVariant>
#include "dbquery.h"

BmDbThread::BmDbThread(QObject *parent, QThread::Priority priority)
:DbThread(parent, priority)
{
	setDbServer("QSQLITE", DBCONNECTION_BM, "", "", "", 0);
}


BmDbThread::~BmDbThread(){

}

void BmDbThread::WorkerThreadMain(Message& action)
{
	DBHEX("bm db thread processing action: ", action.type());
	switch(action.type()) {
		case ACTION_GETBUSINESSTYPE:
		{
			m_tempMsg = new Message(EVENT_BUSINESSTYPE, getBusinessTypes());
			break;
		}
		case ACTION_GETALLBUSINESS:
		{
			m_tempMsg = new Message(EVENT_ALLBUSINESS, getAllBusiness());
			break;
		}
		case ACTION_GETBUSINESS:
		{
			uint32* id = static_cast<uint32*>(action.data());
			m_tempMsg = new Message(EVENT_BUSINESS, getBusiness(*id));
			delete id;
			break;
		}
		case ACTION_GETBUSINESSPIC:
		{
			uint32* id = static_cast<uint32*>(action.data());
			m_tempMsg = new Message(EVENT_BUSINESSPIC, getImage(*id));
			delete id;
			break;
		}
		case ACTION_ADDBUSINESS:
		{
			Business* business = static_cast<Business*>(action.data());
			QByteArray* image = static_cast<QByteArray*>(action.data2());
			Business* addedBusiness = NULL;
			m_tempMsg = new Message(EVENT_ADDBUSINESS);
			if(addBusiness(business)) {
				addedBusiness = getBusiness(business->id());
				m_tempMsg->setData(addedBusiness);
				if(NULL != addedBusiness && NULL != image && !image->isEmpty()) {
					if(addImage(addedBusiness->id(), *image))
						m_tempMsg->setData2(getImage(addedBusiness->id()));
					image->clear();
				}
			}
			delete business;
			delete image;
			break;
		}
		case ACTION_REMOVEBUSINESS:
		{
			uint32* id = static_cast<uint32*>(action.data());
			uint32* result = new uint32(*id);
			if(!removeBusiness(*id))
				*result = 0;
			m_tempMsg = new Message(EVENT_REMOVEBUSINESS, result);
			delete id;
			break;
		}
		case ACTOIN_MODIFYBUSINESS:
		{
			Business* toModify = static_cast<Business*>(action.data());
			QByteArray* image = static_cast<QByteArray*>(action.data2());
			Business* modified = NULL;
			m_tempMsg = new Message(EVENT_MODIFYBUSINESS);
			if(modifyBusiness(toModify)) {
				modified = getBusiness(toModify->id());
				m_tempMsg->setData(modified);
				if(NULL != image && !image->isEmpty() && toModify->id() != 0) {
					if(addImage(modified->id(), *image))
						m_tempMsg->setData2(getImage(modified->id()));
				}
			}
			delete toModify;
			break;
		}
		case ACTION_VIEWBUSINESSRECORD:
		{
			break;
		}
		case ACTION_SETBUSINESSTYPE:
		{
			list<BusinessType>* toSet = 
				static_cast<list<BusinessType>*>(action.data());
			list<BusinessType>* result = new list<BusinessType>;
			list<BusinessType>::iterator it = toSet->begin();
			while(toSet->end() != it) {
				if(!addBusinessType(&(*it)))
					result->push_back(*it);
				it++;
			}
			delete toSet;
			m_tempMsg = new Message(EVENT_SETBUSINESSTYPE, result);
			break;
		}
		case ACTION_REMOVEBUSINESSTYPE:
		{
			list<BusinessType>* toRemove = 
				static_cast<list<BusinessType>*>(action.data());
			list<BusinessType>* result = new list<BusinessType>;
			list<BusinessType>::iterator it = toRemove->begin();
			while(toRemove->end() != it) {
				if(!removeBusinessType(it->getId()))
					result->push_back(*it);
				it++;
			}
			delete toRemove;
			m_tempMsg = new Message(EVENT_REMOVEBUSINESSTYPE, result);
			break;

		}
		default: break;	
	}
	if(NULL != m_tempMsg) {
		postEvent(m_tempMsg, EventDb);
		m_tempMsg = NULL;
	}
}

To copy to clipboard, switch view to plain text mode 

I omitted other function in BmDbThread
A concrete database thread class realize the virtual void WorkerThreadMain(Message& action)，and code to do what it wants when the actions come into QueueAction(Message& action)

[addu]

Hi All

I have used Qsettings instead of QSql..it is woking fine
What is the difference between Qsql and Qsettings ?
if i use Qsettings to save Chat history,will saved data in settings load with application?
where the saved data will be stored ?
How can i store the Datas in file and read from file using Qsql ?

can anybody provide me a example


Thanks

Yuvaraj R

[nish]

Hi All

I have used Qsettings instead of QSql..it is woking fine
What is the difference between Qsql and Qsettings ?
if i use Qsettings to save Chat history,will saved data in settings load with application?
where the saved data will be stored ?
How can i store the Datas in file and read from file using Qsql ?

can anybody provide me a example


Thanks

Yuvaraj R

READ THE MANUAL

[addu]

I read the manual

And one more thing ..

How do we store the List using Qsettings

Thanks

Yuvaraj R