How to get data from intersystems cache db through ODBC?

Question

This is how i try to get a data from db:

#include <QCoreApplication>
#include <QtCore>
#include <QtSql>
#include "iostream"

int main(int argc, char *argv[])
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    db.setDatabaseName("DRIVER={InterSystems ODBC};SERVER=localhost;PORT=1972;DATABASE=USER;UID=_system;PWD=SYS; Unicode SQLTypes=1;");

    if (!db.open())
    {
        std::cout << "Error opening database" << std::endl;
        return -1;
    }
    else
    {
        QSqlQuery query;
        if(query.exec("SELECT * FROM ACCOUNTS")){
            std::cout << "Select succses!" << std::endl;
        }

        while (query.next())
        {
            std::cout << "Getting results..." << std::endl;
            std::cout << query.value(0).toString().toStdString() << std::endl;
        }

        std::cout << "EXIT!" << std::endl;
        return 0;
    }
}

And after query.exec(...) query.next() is always false but I really know that there is a data in the table. This behavior reproduce in the case when I try to get data from sample tables of Cache DB. What did i do wrong?

Thanks for your help.

I'm unfamiliar with this API, but shouldn't the query object be related in some way to the database object?... unless the API only allows one single connection at a time. — jsantander, Jun 06 '14 at 11:58
`exec()` returns false or true? What does `QSqlQuery::lastError()` return? — UldisK, Jun 06 '14 at 11:58
exec() returns true. lastError() returns: [Cache ODBC][State : S1106][Native Code 468] [C:\Qt\Tools\QtCreator\bin\build-OD BC-Desktop_Qt_5_3_0_MSVC2012_OpenGL_32bit-Deb] Fetch type out of range QODBC3: U nable to fetch firstEXIT! — Daynin, Jun 06 '14 at 12:39

score 2 · Accepted Answer · answered Jun 08 '14 at 10:26

The problem was that connection config is wrong:

 QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
 db.setDatabaseName("DRIVER={InterSystems ODBC};SERVER=localhost;PORT=1972;DATABASE=USER;UID=_system;PWD=SYS; Unicode SQLTypes=1;");

it should be:

 QSqlDatabase db = QSqlDatabase::addDatabase("QODBC3");
 db.setDatabaseName("DRIVER={InterSystems ODBC35};SERVER=localhost;PORT=1972;DATABASE=USER;UID=_system;PWD=SYS; Unicode SQLTypes=1;");

score 0 · Answer 2 · answered Jun 06 '14 at 12:05

[as I said in the comments, I'm not familiar with this API Just looking through the documentation as I answer]

the documentation for the constructor of QSqlQuery

Constructs a QSqlQuery object using the SQL query and the database db. If db is not specified, or is invalid, the application's default database is used. If query is not an empty string, it will be executed.

This means your query is opened on the default database (whatever that means).

Looking at the QSqlDatabase documentation:

QSqlDatabase also supports the concept of a default connection, which is the unnamed connection. To create the default connection, don't pass the connection name argument when you call addDatabase().

however, you're giving a name argument in your addDatabase():

QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");

... that means it is not the default connection.

My guess is that you should either:

QSqlDatabase db = QSqlDatabase::addDatabase();

or

QSqlQuery query=QSqlQuery(db);

@Daynin check the comment from UldisK in the original question... that might give a hint. — jsantander, Jun 06 '14 at 12:18

How to get data from intersystems cache db through ODBC?

2 Answers2