QSqlTableModel select() fails with QODBC connection to SQL Express?

[ajg85]

EDIT: The skinny version is I need to know if this is supported with freeTDS ODBC drivers. If not I'd appreciate any open source unix ODBC driver recommendation that will play nice with QT

First I'm using QT 4.6.2 on Ubuntu. My test app connects through freeTDS odbc to a SQL Server Express instance running on a Windows 7 machine. Now I can connect and run queries just fine with this setup under normal circumstances using QT.

I recently decided to play with using QSqlTableModel to display the results of a query in a QTableView however I ran into some odd behavior. It appears the select() method is failing because the query syntax is incorrect. The query appears to be missing the column names which means the setTable method failed to retrieve the field information correctly although it does seem to know there are 3 columns in the table judging by the commas generated despite the empty strings.

Code Snippet:

void MainWindow::on_pbFetch_clicked()
{
    //ZDataTable* results = new ZDataTable(myDB->FetchSQL(ui->txtQuery->toPlainText()));
 
    // TEST CASE
    QSqlTableModel* results = new QSqlTableModel(this, *myDB);
    results->setTable("SystemConfig"); // fails to get fields correctly
    results->setEditStrategy(QSqlTableModel::OnManualSubmit);
 
    if (!results->select())
    {
        QMessageBox::critical(this, "Error", results->lastError().text());
        QMessageBox::information(this, "Query", results->query().lastQuery());
    }
 
    results->setHeaderData(0, Qt::Horizontal, QObject::tr("ID"));
    results->setHeaderData(1, Qt::Horizontal, QObject::tr("Name"));
    results->setHeaderData(2, Qt::Horizontal, QObject::tr("Value"));
 
    QTableView* view = InitDBView(results);
    view->show();
    // END TEST CASE
}
 
QTableView* MainWindow::InitDBView(QSqlTableModel* model)
{
    QTableView* view = new QTableView;
    view->setModel(model);
    view->setWindowTitle("Result Table");
    return view;
}

void MainWindow::on_pbFetch_clicked()
{
    //ZDataTable* results = new ZDataTable(myDB->FetchSQL(ui->txtQuery->toPlainText()));

    // TEST CASE
    QSqlTableModel* results = new QSqlTableModel(this, *myDB);
    results->setTable("SystemConfig"); // fails to get fields correctly
    results->setEditStrategy(QSqlTableModel::OnManualSubmit);

    if (!results->select())
    {
        QMessageBox::critical(this, "Error", results->lastError().text());
        QMessageBox::information(this, "Query", results->query().lastQuery());
    }

    results->setHeaderData(0, Qt::Horizontal, QObject::tr("ID"));
    results->setHeaderData(1, Qt::Horizontal, QObject::tr("Name"));
    results->setHeaderData(2, Qt::Horizontal, QObject::tr("Value"));

    QTableView* view = InitDBView(results);
    view->show();
    // END TEST CASE
}

QTableView* MainWindow::InitDBView(QSqlTableModel* model)
{
    QTableView* view = new QTableView;
    view->setModel(model);
    view->setWindowTitle("Result Table");
    return view;
}

To copy to clipboard, switch view to plain text mode 

Here is the screen cap of the message boxes from above showing the error and the attempted query:



My question is this a limitation because I'm using SQL Server Express through ODBC, a bug with QT, or something I'm doing wrong in my code?

[ajg85]

New update and possibly a new problem with using QT with ODBC and SQL Server Express. I changed my code and reverted to using simple QSqlQuery objects. The queries execute successfully across the open QSqlDatabase connection same as above but it appears the QSqlRecord returned isn't populating string values ... stranger still is that inserts and updates work fine. This is leading me to believe there is some kind of collation problem.

I created a test table defined as below:



I added a single row to the table to test with:



Test code snippet:

QList<QSqlRecord> results;
QSqlQuery cmd(db); // db is open & valid QSqlDatabase using QODBC through test DSN
cmd.prepare(sqlStatement); // QString sqlStatement = "SELECT * FROM TypeTest"
if (db.isOpen)
{
     if (cmd.exec())
     {
          while (cmd.next())
          {
                results.push_back(cmd.record());
           }
      }
}

QList<QSqlRecord> results;
QSqlQuery cmd(db); // db is open & valid QSqlDatabase using QODBC through test DSN
cmd.prepare(sqlStatement); // QString sqlStatement = "SELECT * FROM TypeTest"
if (db.isOpen)
{
     if (cmd.exec())
     {
          while (cmd.next())
          {
                results.push_back(cmd.record());
           }
      }
}

To copy to clipboard, switch view to plain text mode 

Here's what the record object looks like in debug:



At this point I'm going to download and install PostGres and give that a shot because this is the second "what the f...?" moment trying to use ODBC with QT. If anyone has had similar problems or wants to shed some enlightenment I'm all ears because at some point I am going to need a cross-platform library which can use a custom atomix/informix driver through ODBC and I was hoping to be able to wrap QT to do this.

[ajg85]

I traced QSqlRecord down into QT to see how it populates QVariant of type QString and caught it getting this error:

qGetStringData: Error while fetching data ( "[FreeTDS][SQL Server]Program type out of range" )

According to MSDN:

HY003
Program type out of range

(DM) The argument TargetType was not a valid data type, SQL_C_DEFAULT, SQL_ARD_TYPE (in case of retrieving column data), or SQL_APD_TYPE (in case of retrieving parameter data).

(DM) The argument Col_or_Param_Num was 0, and the argument TargetType was not SQL_C_BOOKMARK for a fixed-length bookmark or SQL_C_VARBOOKMARK for a variable-length bookmark.

So this is failing on a basic SQLGetData on line 340 of qsql_odbc.cpp ... it would be nice if this error got reported back up to the calling application instead of just issuing a clear on the field and moving on (at least it solves the mystery of the empty strings)

Know I'm thinking this has to do with Unicode. Windows and SQL Server use UCS-2 for things like table metadata (which could explain the setTable failure) and storing like nvarchar (which could explain the invalid target data type)

I've tried pointing my DSN to the freetds.conf where I specify a server entry that dials back TDS version from 8.0 to 7.0 and specifies to convert results to a charset of UTF-8 but it didn't work. In theory I thought this would pipe all results through iconv.exe and thus translate from UCS-2 to UTF-8 making my results unix/linux friendly so SQLGetData would stop failing.

Inside QT it was still unicode=true and still got the same error. I'm going to attempt to recompile the QT odbc driver with Q_ODBC_VERSION_2 to disable the unicode support entirely and see if that resolves it but I'm just about out of ideas.

[ajg85]

We have liftoff!

Both problems are resolved by modifying odbc.pro and adding the Q_ODBC_VERSION_2 define to exclude all unicode support for unix:

TARGET	 = qsqlodbc
 
HEADERS		= ../../../sql/drivers/odbc/qsql_odbc.h
SOURCES		= main.cpp \
		  ../../../sql/drivers/odbc/qsql_odbc.cpp
 
unix {
	!contains( LIBS, .*odbc.* ) {
	    LIBS 	*= $$QT_LFLAGS_ODBC
	}
        DEFINES += Q_ODBC_VERSION_2
}
 
win32 {
	!win32-borland:LIBS	*= -lodbc32
    	win32-borland:LIBS	*= $(BCB)/lib/PSDK/odbc32.lib
}
 
include(../qsqldriverbase.pri)

TARGET	 = qsqlodbc

HEADERS		= ../../../sql/drivers/odbc/qsql_odbc.h
SOURCES		= main.cpp \
		  ../../../sql/drivers/odbc/qsql_odbc.cpp

unix {
	!contains( LIBS, .*odbc.* ) {
	    LIBS 	*= $$QT_LFLAGS_ODBC
	}
        DEFINES += Q_ODBC_VERSION_2
}

win32 {
	!win32-borland:LIBS	*= -lodbc32
    	win32-borland:LIBS	*= $(BCB)/lib/PSDK/odbc32.lib
}

include(../qsqldriverbase.pri)

To copy to clipboard, switch view to plain text mode 

The setTable call is now able to grab metadata from the table and populates all string fields correctly so QSqlTableModel and QTableView work as expected:


Also QSqlQuery no longer fails on SQLGetData and retrieves string data from select statements and store procedures as desired.

OMG I wish I would have found and tried this on Monday ... it's always something stupid and simple!