Results 1 to 4 of 4

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

  1. #1
    Join Date
    Apr 2010
    Location
    United States
    Posts
    16
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

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

    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:
    Qt Code:
    1. void MainWindow::on_pbFetch_clicked()
    2. {
    3. //ZDataTable* results = new ZDataTable(myDB->FetchSQL(ui->txtQuery->toPlainText()));
    4.  
    5. // TEST CASE
    6. QSqlTableModel* results = new QSqlTableModel(this, *myDB);
    7. results->setTable("SystemConfig"); // fails to get fields correctly
    8. results->setEditStrategy(QSqlTableModel::OnManualSubmit);
    9.  
    10. if (!results->select())
    11. {
    12. QMessageBox::critical(this, "Error", results->lastError().text());
    13. QMessageBox::information(this, "Query", results->query().lastQuery());
    14. }
    15.  
    16. results->setHeaderData(0, Qt::Horizontal, QObject::tr("ID"));
    17. results->setHeaderData(1, Qt::Horizontal, QObject::tr("Name"));
    18. results->setHeaderData(2, Qt::Horizontal, QObject::tr("Value"));
    19.  
    20. QTableView* view = InitDBView(results);
    21. view->show();
    22. // END TEST CASE
    23. }
    24.  
    25. QTableView* MainWindow::InitDBView(QSqlTableModel* model)
    26. {
    27. QTableView* view = new QTableView;
    28. view->setModel(model);
    29. view->setWindowTitle("Result Table");
    30. return view;
    31. }
    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?
    Last edited by ajg85; 6th April 2010 at 22:02.

  2. #2
    Join Date
    Apr 2010
    Location
    United States
    Posts
    16
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

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

    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:
    Qt Code:
    1. QList<QSqlRecord> results;
    2. QSqlQuery cmd(db); // db is open & valid QSqlDatabase using QODBC through test DSN
    3. cmd.prepare(sqlStatement); // QString sqlStatement = "SELECT * FROM TypeTest"
    4. if (db.isOpen)
    5. {
    6. if (cmd.exec())
    7. {
    8. while (cmd.next())
    9. {
    10. results.push_back(cmd.record());
    11. }
    12. }
    13. }
    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.
    Last edited by ajg85; 7th April 2010 at 19:28.

  3. #3
    Join Date
    Apr 2010
    Location
    United States
    Posts
    16
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

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

    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.

  4. #4
    Join Date
    Apr 2010
    Location
    United States
    Posts
    16
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

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

    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:
    Qt Code:
    1. TARGET = qsqlodbc
    2.  
    3. HEADERS = ../../../sql/drivers/odbc/qsql_odbc.h
    4. SOURCES = main.cpp \
    5. ../../../sql/drivers/odbc/qsql_odbc.cpp
    6.  
    7. unix {
    8. !contains( LIBS, .*odbc.* ) {
    9. LIBS *= $$QT_LFLAGS_ODBC
    10. }
    11. DEFINES += Q_ODBC_VERSION_2
    12. }
    13.  
    14. win32 {
    15. !win32-borland:LIBS *= -lodbc32
    16. win32-borland:LIBS *= $(BCB)/lib/PSDK/odbc32.lib
    17. }
    18.  
    19. 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!
    Last edited by ajg85; 8th April 2010 at 21:46.

Similar Threads

  1. QSqlDatabase::open() fails with QODBC
    By grzywacz in forum Qt Programming
    Replies: 4
    Last Post: 7th April 2010, 04:27
  2. QSqlTableModel::select()
    By vladozar in forum Qt Programming
    Replies: 4
    Last Post: 29th April 2009, 18:51
  3. Queued connection fails on own class
    By ^NyAw^ in forum Qt Programming
    Replies: 3
    Last Post: 2nd December 2008, 19:50
  4. QODBC, QSqlTableModel, and submit problems
    By darkadept in forum Qt Programming
    Replies: 3
    Last Post: 18th May 2008, 13:46
  5. Qt 4 commercial with Visual C++ 2005 Express Edition: Works half, debug fails
    By axeljaeger in forum Installation and Deployment
    Replies: 4
    Last Post: 5th July 2006, 06:38

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.