QThread and PostgreSQL - a problem

[Lesiok]

I'm building some QT application to convert DBF files to SQL database.
I have a class derived from QThread to convert one DBF file containing information from one month. This class is defined as :

class KonwertJedenMiesiac :
public QThread
{
Q_OBJECT
public:
KonwertJedenMiesiac(QString katDBF,QString bazaDBF);
~KonwertJedenMiesiac(void);
void KonwertJedenMiesiac::stopKonwerter( void );
void zrobIncProgressBarOperacja( void );
signals:
void incProgressBarOperacja( void );
protected:
inline long nowyKod( char litera, long kod )
{ if(litera == ' ' )
return 0;
return(litera*1000000L + kod);
}
protected:
bool run_konwerter;
mutable QMutex mutex;
QSqlDatabase db;
Code4 cb;
QDate d88;
QString katDBF, bazaDBF;
};
The constructor look s like :


KonwertJedenMiesiac::KonwertJedenMiesiac(QString katDBF, QString bazaDBF )
{
run_konwerter = true;
d88 = QDate(1988,01,01);
this->bazaDBF = bazaDBF;
this->katDBF = katDBF;

// wczytujemy parametry bazy danych
QSettings settings( "dworzec.ini", QSettings::IniFormat);
settings.beginGroup("DbSetup");

db = QSqlDatabase::addDatabase(settings.value("DbType").toString(),bazaDBF);

db.setHostName(settings.value("HostName").toString());
db.setDatabaseName(settings.value("DatabaseName").toString());
db.setUserName(settings.value("UserName").toString());
db.setPassword(settings.value("Password").toString());
}

SQL database is opened in method run().
All monthly DBF files must be merged to one SQL table.
When I create only one thread KonwerterJedenMiesiac all is working correctly. But when I create more than one thread from time to time INSERT rows generated in program are broken like this :


QSqlError(-1, "", "")
"INSERT INTO pozycje_paragonow (litera,kod,pozycja,id_towaru,nazwa_towaru,jm,ilos c,cena,kod_vat,stawka_vat,rabat1,rabat2,bonifikata ,kwota_ulgi,sww) VALUES ('F',7429,1,65000031,'A

I don't observe this behavior when SQL database is Firebird.

Is something special to do with PostgreSQL driver ?

Qt 4.3.3 OpenSource on Windows XP compiled with Visual C++ 2005 Express Edition. PostgreSQL developer library v.8.2.

[jacek]

Make sure that each thread uses an unique connection name.

[Lesiok]

Make sure that each thread uses an unique connection name.

200% yes. DBF file name is used as connection name and for each thread it is unique.

[jacek]

How do you start those threads? Did you implement the run() method?

[Lesiok]

How do you start those threads? Did you implement the run() method?

Yes, as I wrote this same code is working perfect with Firebird.

[jpn]

I don't see KonwertJedenMiesiac::run() being implemented anywhere. Furthermore, you're calling QSqlDatabase::addDatabase() in KonwertJedenMiesiac constructor which is NOT executed in the newly created thread. You might want to consult QThread docs for how to use QThread. There is an example in the detailed description.

[Lesiok]

I don't see KonwertJedenMiesiac::run() being implemented anywhere. Furthermore, you're calling QSqlDatabase::addDatabase() in KonwertJedenMiesiac constructor which is NOT executed in the newly created thread. You might want to consult QThread docs for how to use QThread. There is an example in the detailed description.

KonwertJedenMiesiac is parent class for next class which have method run(). One of experiments was calling QSqlDatabase::addDatabase() in run() method but no difference.
Here is a definition of class derived from KonwertJedenMiesiac

class KonwertBJM :
public KonwertJedenMiesiac
{
public:
KonwertBJM(QString katDBF, QString bazaDBF);
~KonwertBJM(void);
protected:
void run(void);
void robKonwersje( char *kat_dbf, char *miesiac );
};

and implementation of method run()

void KonwertBJM::run(void)
{
if( !db.open() )
{
qDebug() << "KonwerterBJM"
<< QObject::tr("Can't connect to database")
<< db.lastError().text();
return;
}
char miesiac[7];

strcpy_s(miesiac,sizeof(miesiac),bazaDBF.mid(1,6). toLocal8Bit().data());
robKonwersje(katDBF.toLocal8Bit().data(),miesiac);

db.close();
}

[jpn]

Even if this had nothing to do with the original problem, you are calling QSqlDatabase::addDatabase() in one thread and QSqlDatabase::open() in another thread. You should not do so when it's the same connection in question.

Threads and the SQL Module (http://doc.trolltech.com/4.3/threads.html#threads-and-the-sql-module):


A connection can only be used from within the thread that created it. Moving connections between threads or creating queries from a different thread is not supported.

[Lesiok]

OK, all code related to creating connection to database is moved to run() method :


void KonwertBJM::run(void)
{

QSettings settings( "dworzec.ini", QSettings::IniFormat);
settings.beginGroup("DbSetup");
db_connection_name = UniqueDbName();
db = QSqlDatabase::addDatabase(settings.value("DbType").toString(),db_connection_name);

db.setHostName(settings.value("HostName").toString());
db.setDatabaseName(settings.value("DatabaseName").toString());
db.setUserName(settings.value("UserName").toString());
db.setPassword(settings.value("Password").toString());
if( !db.open() )
{
qDebug() << "KonwerterBJM"
<< QObject::tr("Can't connect to database")
<< db.lastError().text();
return;
}
char miesiac[7];

strcpy_s(miesiac,sizeof(miesiac),bazaDBF.mid(1,6 ).toLocal8Bit().data());
robKonwersje(katDBF.toLocal8Bit().data(),miesiac);

db.close();
}No difference.

[jpn]

char miesiac[7];
strcpy_s(miesiac,sizeof(miesiac),bazaDBF.mid(1,6). toLocal8Bit().data());


Doesn't this leave the last character uninitialized? What does KonwertBJM::robKonwersje() do? Why does it take char* and not QString in the first place?

[Lesiok]

Doesn't this leave the last character uninitialized? What does KonwertBJM::robKonwersje() do? Why does it take char* and not QString in the first place?

1. No, strcpy_s writes to the new location string WITH ending 0x00 byte.

2. KonwertBJM::robKonwersje() is a main method to doing conversion from DBF to SQL. It opens 3 DBF files named as Byyyymm.DBF, KByyyymm.DBF and Pyyyymm.DBF, where yyyymm is saved in var miesiac, and record by record convert it to SQL INSERT statements. A few records from KB and P is related to one record in B. So every record from B produce some INSERT statements in one transaction.

As I wrote in post opening this thread, on Firebird I can do this in many threads and all is OK. Problem is only with PostgreSQL.

[C167]

In addition, the third party libraries used by the QSqlDrivers can impose further restrictions on using the SQL Module in a multithreaded program. Consult the manual of your database client for more informationThis may be the problem

[Lesiok]

This may be the problem

Yes I know.
Theoretically libpq is compiled in thread-safe mode. PQisthreadsafe() returns 1 (the libpq is thread-safe).