App crashes after SQL query.

[bclay]

I am trying to do a basic query of an existing Postgres9.x database but the app crashes repeatedly. I have tried some of the samples I could find on line such as the SQL Query Model on http://doc.qt.io/qt-5/sql-model.html.

I successfully connect to the database with the following code

QSqlError err;
mSqlDatabase = QSqlDatabase::addDatabase("QPSQL");
mSqlDatabase.setDatabaseName(dbName);
mSqlDatabase.setHostName(host);
mSqlDatabase.setPort(port);
if (!mSqlDatabase.open(user, passwd))
{
	err = mSqlDatabase.lastError();
}

Then I make the query using

QSqlQueryModel model;
model.setQuery("SELECT member_if, last_name, first_name, club_name FROM gcf_2016");

for (int i = 0; i < model.rowCount(); ++i) {
    int id = model.record(i).value("id").toInt();
    QString name = model.record(i).value("name").toString();
    qDebug() << id << name;
}

I connect ok and get one record back ok but the app crashes on the second time through the loop.

I tried a different method and was able to get all of the data but the app crashed leaving the method

Method 2:

///////////////////////////////////////////////////////////////////////////////
int GcfProjects::CreateWeighinDataFile(const char *filename, const char *dbTableName,
const char *projectName)
{
int status = -1;
QString queryResults;
char tempBuf[255];
FILE *fdes;
std::string tempString;

fdes = fopen(filename, "w");

if (fdes != NULL)
{
	fprintf(fdes, "member_id\tlast_name\t first_name\tclub_name\n");

	try
	{
		QSqlQuery searchQuery(mSqlDatabase);

		if ((filename != NULL) && (dbTableName != NULL) && (projectName != NULL))
		{
			sprintf(tempBuf, "SELECT member_id,last_name,first_name,club_name FROM %s "
				"where project_name like '%%%s%%'", dbTableName, projectName);

			searchQuery.exec(tempBuf);

			if (searchQuery.isActive())
			{
				while (searchQuery.next())
				{
					QSqlRecord record = searchQuery.record();

					int id = record.value("member_id").toInt();

					QString lastName = record.value("last_name").toString();
					QString firstName = record.value("first_name").toString();
					QString clubName = record.value("club_name").toString();

					fprintf(fdes, "%d\t%s\t%s\t%s\n", id, lastName.toStdString().c_str(),
						lastName.toStdString().c_str(), clubName.toStdString().c_str());
				}

				status = 0;
			}
		}

		fclose(fdes);
	}
	catch (...)
	{
		status = status;
	}
}
return(status);

}

I every method I tried it worked the first time through the loop and all but the last method shown above it died getting the data from the second row. It seems like there is a scoping issue but I can not find where.

I tried with both QT 5.5 and QT 5.6 using Visual Studio 2013

And thought would be greatly appreciated

Bruce

[SGaist]

Hi,

You should add checks for error.when calling your queries.

On a side note, your method number 2 is more complicated than needed, QString and QFile can be used.

[bclay]

@bclay Thanks for your reply. Not sure where to add an error check. setQuery returns void and searchQuery.exec retuirns bool. I will try converting to QFile to see if that has any effect.

thanks again

Bruce

[cheezus]

@bclay A common pattern is the following (or at least I use it):

query.prepare("WHATEVER");
if(!query.exec() || !query.next())
    qCritical() << "OMG" << query.lastError();
else
   qDebug() << "YAY" << query.record().value(0).toInt();

[bclay]

@bclay Thank you for the feedback.

I made a few changes based on the suggestions and trial and error. It seems the problem is one of scoping. When the static instance of QSqlQuery went out of scope the program crashed so I changed that to a dynamic instance. I can now make repeated calls to the method but I can not close and reconnect without crashing. If I compile the code below with OPEN_LOCALLY defined then the app will crash on removeDatabase which by the way is a very scary name for a database method. Simply calling database close does not allow us to reconnect because we get an error message stating that it is in use

///////////////////////////////////////////////////////////////////////////////
int GcfProjects::TestDataRetrieval(const char *filename, const char *dbTableName,
const char *projectName)
{
int status = -1;
std::string tempString;
bool connected = false;
QSqlError err;
QString errMsg;
char tempBuf[512];
bool dbOpened = false;

#define OPEN_LOCALLY
#ifdef OPEN_LOCALLY
mSqlDatabase = QSqlDatabase::addDatabase("QPSQL", "testConnect");
mSqlDatabase.setDatabaseName("irl");
mSqlDatabase.setHostName("localhost");
mSqlDatabase.setPort(5434);

dbOpened = mSqlDatabase.open("postgres", "password");
if (dbOpened != true)
{
	err = mSqlDatabase.lastError();

	if (err.type() != QSqlError::NoError)
	{
		qDebug() << "Could not connect to database; check password and try again";
	}
}

#else
dbOpened = mSqlDatabase.isOpen();
#endif // OPEN_LOCALLY

if (dbOpened == true)
{
	QSqlQuery *searchQuery = new QSqlQuery(mSqlDatabase);

	if (searchQuery != NULL)
	{
		if ((dbTableName != NULL) && (projectName != NULL))
		{
			sprintf(tempBuf, "SELECT member_id,last_name,first_name,club_name FROM %s "
				"where project_name like '%%%s%%'", dbTableName, projectName);

			searchQuery->prepare(tempBuf);

			QSqlError sqlError = 0;
			int errNum;
			QString dbError;
			QString driverErr;

			if (!searchQuery->exec() || !searchQuery->next())
			{
				err = searchQuery->lastError();
				errMsg = err.databaseText();
				qDebug() << errMsg;
			}
			else
			{
				qDebug() << "memberId" << searchQuery->record().value(0).toInt();
				qDebug() << "lastName" << searchQuery->record().value(1).toString();
				qDebug() << "firstName" << searchQuery->record().value(2).toString();
				qDebug() << "clubName" << searchQuery->record().value(3).toString();
			}

			if (searchQuery->isActive())
			{
				while (searchQuery->next())
				{
					QSqlRecord record = searchQuery->record();

					qDebug() << "memberId" << searchQuery->record().value(0).toInt();
					qDebug() << "lastName" << searchQuery->record().value(1).toString();
					qDebug() << "firstName" << searchQuery->record().value(2).toString();
					qDebug() << "clubName" << searchQuery->record().value(3).toString();

				}

				status = 0;
			}
			status = 0;
		}
	}
	searchQuery->finish();

#ifdef OPEN_LOCALLY
mSqlDatabase.close();

	mSqlDatabase.removeDatabase("testConnect");

#endif // OPEN_LOCALLY

}
status = 0;

return(status);

}

[SGaist]

That's because you have mSqlDatabase that is still referencing that database. By the way, removeDatabase is a static method.

There's no need to keep database object around. When you want to run a query on a specific database just retrieve it in your method and be done with it.

QSqlDatabase db = QSqlDatabase::database("testConnect");
QSqlQuery query(db);
query.exec("SELECT * FROM whatever");
//etc.

You can open and close the database in each function if you want to, but there's no need to redo the setup each time.