QSqlQueryModel doesnt hold results

[Edder]

Hello community,

Im trying to access an intersystems Caché database and every statements besides select statements are working fine.
I just don't get a result in my QSqlQueryModel and I can't figure out why.

Has anyone ever worked with caché and QSql and got select statements returning results?

[ChrisW67]

Why not share the error message you are probably getting from QSqlQueryModel::lastError()? If other statements are getting executed then the underlying database interface is clearly functional.

[Edder]

Thats actually a pretty good idea thank you, haven't thought of this.

LasError is: QSqlError(-1, "Forward-only queries cannot be used in a data model", "")
Using setForwardOnly() doesnt work, is there another way solve this problem?

[wysota]

Can you post a minimal compilable example reproducing the problem?

[ChrisW67]

You don't say how you are accessing the Cache database from Qt. Which driver? I notice, for example, that CacheDataReader provides forward-only access, so using this in the underlying driver will probably not work.

[Edder]

You don't say how you are accessing the Cache database from Qt. Which driver?

I'm using the QODBC driver and the DSN driver is "InterSystems ODBC35" driver and Driver Version = 2012.1.2.702.0.

An how I fetch the result:

void ExecuteQuery()
{
	QSqlDatabase m_db = QSqlDatabase::addDatabase("QODBC3", "test");
	m_db.setDatabaseName("InterSystems Caché DSN");
	m_db.open();
 
        QSqlQuery *m_pQuery = new QSqlQuery(m_db);
	if (!m_pQuery->exec("select * from Aviation.Aircraft")) // table of the SAMPLES database of an caché instance
	{
		QString sError = m_pQuery->lastError().text();
		//m_sLogText = QString("<table><tr><td><b>%1</b></td><td><font color='#FF0000'>%2</font></td></tr></table>").arg(QDateTime::currentDateTime().toString("(hh:mm:ss)"), sError.replace('<', "&lt;").replace('>', "&gt;"));
		//ODBC_Logging::getInstance()->WriteLog(ERROR, sError);
                qDebug() << sError;
	}
	else
	{
		m_pSqlQueryModel = new QSqlQueryModel();
		m_pSqlQueryModel->setQuery(*m_pQuery);
		QAbstractItemModel *m_pSQLResultTable = m_pSqlQueryModel;
		qDebug() << m_pSqlQueryModel->lastError();
         }
}

void ExecuteQuery()
{
	QSqlDatabase m_db = QSqlDatabase::addDatabase("QODBC3", "test");
	m_db.setDatabaseName("InterSystems Caché DSN");
	m_db.open();

        QSqlQuery *m_pQuery = new QSqlQuery(m_db);
	if (!m_pQuery->exec("select * from Aviation.Aircraft")) // table of the SAMPLES database of an caché instance
	{
		QString sError = m_pQuery->lastError().text();
		//m_sLogText = QString("<table><tr><td><b>%1</b></td><td><font color='#FF0000'>%2</font></td></tr></table>").arg(QDateTime::currentDateTime().toString("(hh:mm:ss)"), sError.replace('<', "&lt;").replace('>', "&gt;"));
		//ODBC_Logging::getInstance()->WriteLog(ERROR, sError);
                qDebug() << sError;
	}
	else
	{
		m_pSqlQueryModel = new QSqlQueryModel();
		m_pSqlQueryModel->setQuery(*m_pQuery);
		QAbstractItemModel *m_pSQLResultTable = m_pSqlQueryModel;
		qDebug() << m_pSqlQueryModel->lastError();
         }
}

To copy to clipboard, switch view to plain text mode 

I'm sorry I didn't provided these infos directly, I thought someone already worked with qt and the caché database and knows the problems when working with caché.

[wysota]

And which of these calls returns the error you mentioned earlier?

BTW. don't create QSqlQuery using the new operator, you are leaking memory there.

[Edder]

And which of these calls returns the error you mentioned earlier?

m_pSqlQueryModel->setQuery(*m_pQuery); does that.
lastError() holds the error in this case I posted above.

BTW. don't create QSqlQuery using the new operator, you are leaking memory there.

Yea I know, I'm deleting QSqlQuery in my ConnectionClosed() function, I made up these function to show a quick example.

[wysota]

m_pSqlQueryModel->setQuery(*m_pQuery); does that.
lastError() holds the error in this case I posted above.

So the original query works but it fails if you set it to the model?

How about this:

void ExecuteQuery()
{
	QSqlDatabase m_db = QSqlDatabase::addDatabase("QODBC3", "test");
	m_db.setDatabaseName("InterSystems Caché DSN");
	m_db.open();
 
        m_pSqlQueryModel = new QSqlQueryModel(this);
	m_pSqlQueryModel->setQuery("select * from Aviation.Aircraft", m_db);
	QAbstractItemModel *m_pSQLResultTable = m_pSqlQueryModel;
        qDebug() << m_pSqlQueryModel->lastError().text();
}

void ExecuteQuery()
{
	QSqlDatabase m_db = QSqlDatabase::addDatabase("QODBC3", "test");
	m_db.setDatabaseName("InterSystems Caché DSN");
	m_db.open();

        m_pSqlQueryModel = new QSqlQueryModel(this);
	m_pSqlQueryModel->setQuery("select * from Aviation.Aircraft", m_db);
	QAbstractItemModel *m_pSQLResultTable = m_pSqlQueryModel;
        qDebug() << m_pSqlQueryModel->lastError().text();
}

To copy to clipboard, switch view to plain text mode 

Yea I know, I'm deleting QSqlQuery in my ConnectionClosed() function, I made up these function to show a quick example.

Just create the query on the stack, that's quicker than writing "new"

[Edder]

I tested your code and the query in the QSqlQueryModel doesn't properly execute since I'm getting this error on trying: Forward-only queries cannot be used in a data model
And this without even using a QSqlQuery.

[ChrisW67]

It is possible that your Odbc driver does not support bi-directional cursors. It may also be an issue with the driver when told to act as a version 2.0 ODBC. See http://doc.qt.io/qt-4.8/sql-driver.html#qodbc

[Edder]

Thanks Chris, I think its the bidirectional cursor issue.
I tried setting the driver to a version 3.0 ODBC driver and that also doesn't work.

Is there another way to fetch the results from the QSqlQuery w/o using a data model?

[wysota]

Is there another way to fetch the results from the QSqlQuery w/o using a data model?

Yes, of course. You can use QSqlQuery::hasNext(), QSqlQuery::next() and QSqlQuery::value().