Welcome!

Welcome to the official BlackBerry Support Community Forums.

This is your resource to discuss support topics with your peers, and learn from each other.

inside custom component

Native Development

Reply
Trusted Contributor
smjose
Posts: 134
Registered: ‎04-12-2013
My Carrier: Telenor

Extract a value from database using select query

Hi,

I am facing an issue with select query in C++. I have done creation of database, insertion and updation through c++ code.  Now, when I use select query, I am not able to retrieve the data from this query result. please see my code below.

 

void dbHelper::createOrUpdateRecord(const QString sqlquery) {

    QSqlQuery query(database);
    query.prepare(sqlquery);
    qDebug()<<"Query is "<<sqlquery;
    if (query.exec()) {
		int fieldNo = query.record().indexOf("title");
		qDebug()<<"field number"<<fieldNo;
		qDebug()<<"field count"<<query.record().count();

		if(query.last()){
			do{
				if(fieldNo == 1){
			        QString country = query.value(fieldNo).toString();
				qDebug()<<"Value "<<country;
				}

			}while(query.previous());
		}

	} else {
		const QSqlError error = query.lastError();
		qDebug() << "Error: " << error.text() << "\nQuery :" << sqlquery
				<< " not executed";
	}


}

 And I call this function here:

        QString createOrUpdateQuery = QString("select * from how_to");
	dbHelp.createOrUpdateRecord(createOrUpdateQuery);

 I have searched the whole forum and google and I got a lot of solutions, but none worked for me. Everyone suggested to use query.next , but that dint work for me. It wasnt even entering to the if(query.next()) condition. So, when I changed it to query.last, at least it entered( only when I wrote query.setForwardOnly() before prepare statement. Weired !!! ). 

 

My database does contain this table and the value of the filed"title" I am trying to retrieve exists in the database. 

 

And when I run this program I get the output as below.

 

Query is  "select * from how_to" 

field number 1 

field number 13

 

I have already spent two whole days for this. Could someone help me?

Please like this if you are answered.
Please use plain text.
Developer
simon_hain
Posts: 15,558
Registered: ‎07-29-2008
My Carrier: O2 Germany

Re: Extract a value from database using select query

i would suggest that you break down your code as much as possible.
i check my statements using sqlitemanager (firefox plugin), you can copy the db from the device using the target file system navigator.

query.next works just fine for me.
----------------------------------------------------------
feel free to press the like button on the right side to thank the user that helped you.
please mark posts as solved if you found a solution.
@SimonHain on twitter
Please use plain text.
Trusted Contributor
smjose
Posts: 134
Registered: ‎04-12-2013
My Carrier: Telenor

Re: Extract a value from database using select query

Thanks Simon.

I use sqlite manager too to check the database. The query works fine in the sqlitemanager tool. 

Or, is there another way to get a value from database in c++ code ? I dont know why it doesnt work for me :smileysad:

Please like this if you are answered.
Please use plain text.
Developer
simon_hain
Posts: 15,558
Registered: ‎07-29-2008
My Carrier: O2 Germany

Re: Extract a value from database using select query

you can also use sqldataaccess which provides a simpler (but, in my experience, limited and slower) interface.

looking at your code again it seems you are trying to access the result of the query before you call next(), this will not work.
add "while (query.next()) directly inside the exec.

----------------------------------------------------------
feel free to press the like button on the right side to thank the user that helped you.
please mark posts as solved if you found a solution.
@SimonHain on twitter
Please use plain text.
Developer
YamilBracho
Posts: 710
Registered: ‎05-31-2010
My Carrier: Movistar

Re: Extract a value from database using select query

I recommed you to use the debug and step by step execution in your method to see what's going on...

Please use plain text.
Trusted Contributor
smjose
Posts: 134
Registered: ‎04-12-2013
My Carrier: Telenor

Re: Extract a value from database using select query

[ Edited ]

At a quick look, I see that, query.next() never returns true for me.  Also, isValid() never returns true too..

Please like this if you are answered.
Please use plain text.
Developer
YamilBracho
Posts: 710
Registered: ‎05-31-2010
My Carrier: Movistar

Re: Extract a value from database using select query

I use a method like this :

/**
 * Version de SqlDataAccess usando QSqlQuery
 */
QVariantList* DataLayer::executeQuery(const QString &sql)
{
	QSqlQuery *pQuery = new QSqlQuery(m_database);
	QVariantList *pResult = new QVariantList();

	pQuery->setForwardOnly(true);
	if (pQuery->exec(sql))
	{
		while (pQuery->next())
		{
			QSqlRecord rec = pQuery->record();
			int numFields = rec.count();
			QMap<QString, QVariant> map;
			for (int nField = 0; nField < numFields; nField++)
			{
				QString key = rec.fieldName(nField);
				QVariant value = rec.value(nField);
				QString valueString = value.toString();
				map.insert(key, value);
			}

			pResult->append(map);
		}
	}
	else
	{
		QSqlError err = pQuery->lastError();
		if (err.isValid())
		{
			setErrorMessage(sql, err);
		}
	}

	if (pQuery)
	{
		delete pQuery;
		pQuery = NULL;
	}

	return pResult;
}

 To use it I do:

 

/**
 * Retorna lista de Consultas
 */
QList<ConsultaXMLBean*> * DataLayer::getListConsulta()
{
	QList<ConsultaXMLBean*> *pResult = new QList<ConsultaXMLBean *>();
	QVariantList *pList = executeQuery("SELECT fecha, xml FROM consulta ORDER BY fecha DESC");

	for (int i = 0, len = pList->size(); i < len; i++)
	{
		QVariant datos = pList->at(i);
		QMap<QString, QVariant> map = datos.toMap();
		QString fecha = map["fecha"].toString();
		QString xml = map["xml"].toString();

		ConsultaXMLBean *p = new ConsultaXMLBean(fecha, xml);
		pResult->append(p);
	}

	return pResult;
}

 

Please use plain text.
Developer
simon_hain
Posts: 15,558
Registered: ‎07-29-2008
My Carrier: O2 Germany

Re: Extract a value from database using select query

can you post an updated code snippet?
----------------------------------------------------------
feel free to press the like button on the right side to thank the user that helped you.
please mark posts as solved if you found a solution.
@SimonHain on twitter
Please use plain text.
Trusted Contributor
smjose
Posts: 134
Registered: ‎04-12-2013
My Carrier: Telenor

Re: Extract a value from database using select query

Simon,

 

This is my updated code,

void dbHelper::createOrUpdateRecord(const QString sqlquery) {
    QSqlQuery query(database);
	query.setForwardOnly(true);
    query.prepare(sqlquery);
    qDebug()<<"Query is "<<sqlquery;
    if (query.exec()) {
		int fieldNo = query.record().indexOf("title");
		qDebug()<<"field number"<<fieldNo;
		qDebug()<<"field number1"<<query.record().count();
		qDebug()<<"field number2"<<query.first()<<query.last();
		while(query.next()){
			if(query.isActive())
			{
				QString value = query.value(fieldNo).toString();			}
		    }

	} else {
		const QSqlError error = query.lastError();
		qDebug() << "Error: " << error.text() << "\nQuery :" << sqlquery
				<< " not executed";
	}


}

 

and the output is 

 

Query is  "select * from how_to" 

field number 1 

field number1 13 

field number2 false true 

Please like this if you are answered.
Please use plain text.
Developer
simon_hain
Posts: 15,558
Registered: ‎07-29-2008
My Carrier: O2 Germany

Re: Extract a value from database using select query

if you call last() you retrieve the last record and position the query on that record.
then you call next(), and as you are already on the last record there is no next record.
see
https://developer.blackberry.com/native/reference/cascades/qsqlquery.html#last
----------------------------------------------------------
feel free to press the like button on the right side to thank the user that helped you.
please mark posts as solved if you found a solution.
@SimonHain on twitter
Please use plain text.