[Closed, Not Solved] completely lost : bad code? odbc? MinGW? windows? bug? what?

antseq

I've already posted a question about this problem (http://qt-project.org/forums/viewthread/28174/) but unfortunately I have not got any answers to my questions.

I've decided to break my initial application to small pieces and build a very small QT application, from scratch, to make sure the problem exists and to have an example that I could share and see if someone helps me to understand what I am doing wrong or if it is some QT feature / limitation / bug or whatever.

The "problem":

I've got one window showing records from a database table.
If somewhere in the application (testOkClicked), I have to update the database without using 'transactions' everything works ok.
If somewhere in the application (testFailClicked), I have to update the database using 'transactions' the records showing in the window (1) (*) disappeared and become blank records.

*) If I've got more windows (mdi) showing several tables, all windows, all tables records go BLANK simultaneously.

I am using : QT 5.0.2 + MinGW 4.7 + 32 bits + ODBC driver + Windows 7 (32) + SQL Server 2012 Express

Can someone help me to understand why using transaction()/commit() makes all records (all forms) BLANK?

Is it because I am using windows? ODBC? MinGW? QT!?

thanks in advance for any help.

Source Code:

main.cpp
@
int main(int argc, char *argv[])
{
QApplication a(argc, argv);

QString dbServer = "W7B32QT5\\SQLEXPRESS";
QString dbDatabase = "MYDB";
QString dbUser = "sa";
QString dbPass = "masterkey";

//QString connectionStr = QString::fromLocal8Bit("DRIVER={SQL SERVER};SERVER=%1;DATABASE=%2;UID=%3;PWD=%4;Trusted_Connection=Yes").arg(dbServer,dbDatabase,dbUser,dbPass);

QString connectionStr = QString::fromLocal8Bit("DRIVER={SQL Server Native Client 11.0};SERVER=%1;DATABASE=%2;UID=%3;PWD=%4;Trusted_Connection=Yes").arg(dbServer,dbDatabase,dbUser,dbPass);

QSqlDatabase db;
db = QSqlDatabase::addDatabase("QODBC");
db.setDatabaseName(connectionStr);
if (! db.open()){
    qDebug() << db.lastError();
}

MainWindow w;
w.show();

return a.exec&#40;&#41;;

}
@

mainwindow.h
@
#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QDialog>

class QTableView;
class QSqlQueryModel;
class QVBoxLayout;
class QHBoxLayout;

class MainWindow : public QDialog
{
Q_OBJECT

public:
explicit MainWindow(QWidget *parent = 0);

signals:

public slots:
void testOkClicked();
void testFailClicked();

private slots:

private:
QPushButton *testOkButton;
QPushButton *testFailButton;
QPushButton *closeButton;

QTableView *tblView;

QSqlQueryModel *sqlModel;

QVBoxLayout *mainLayout;
QHBoxLayout *buttonLayout;

void createGUI();
void setupSQL();

};

#endif // MAINWINDOW_H
@

mainwindow.cpp
@
#include "mainwindow.h"
#include <QtWidgets>
#include <QtSql>
#include <QSqlQueryModel>

MainWindow::MainWindow(QWidget *parent) :
QDialog(parent)
{
createGUI();
setupSQL();
}

void MainWindow::createGUI()
{
tblView = new QTableView;

testOkButton  = new QPushButton(tr("TEST OK"));
testFailButton  = new QPushButton(tr("TEST FAIL : Blank Records"));
closeButton = new QPushButton(tr("Close"));

connect(testOkButton, SIGNAL(clicked()), this, SLOT(testOkClicked()));
connect(testFailButton, SIGNAL(clicked()), this, SLOT(testFailClicked()));
connect(closeButton, SIGNAL(clicked()), this, SLOT(close()));

buttonLayout = new QHBoxLayout;
buttonLayout->addWidget(testOkButton);
buttonLayout->addWidget(testFailButton);
buttonLayout->addStretch();
buttonLayout->addWidget(closeButton);

mainLayout = new QVBoxLayout;
mainLayout->addWidget(tblView);
mainLayout->addLayout(buttonLayout);

setLayout(mainLayout);
setWindowTitle(tr("TEST2 Dialog"));

}

void MainWindow::setupSQL()
{
sqlModel = new QSqlQueryModel;
sqlModel->setQuery("SELECT artigoid, descricao FROM artigos");

tblView->resizeColumnsToContents();
tblView->setAlternatingRowColors(true);
tblView->setSelectionMode(QAbstractItemView::SingleSelection);
tblView->setSelectionBehavior(QAbstractItemView::SelectRows);
tblView->setModel(sqlModel);
tblView->selectRow(0);
tblView->setFocus();

}

void MainWindow::testOkClicked()
{
QSqlQuery query;
query.prepare("DELETE FROM artigos WHERE artigoid = :artigoid");
query.bindValue(":artigoid", "abc");
query.exec();
}

void MainWindow::testFailClicked()
{
QSqlDatabase::database().transaction();

QSqlQuery query;
query.prepare("DELETE FROM artigos WHERE artigoid = :artigoid");
query.bindValue(":artigoid", "abc");
query.exec&#40;&#41;;

QSqlDatabase::database(&#41;.commit();

}
@

Jeroentjehome

Hi,
Never used the QsqlDatabase before, but your code seems oke. The only think I found in de docs is that for some databases you need to stop the query before the commit() function. The transaction() function also return a bool. Check the value and through an exception or a warning box if false? It has a reason that functions return a bool, USE IT!!
Greetz

antseq

Thank you for your suggestions, even though you had never used QSqlDatabase before.

I've done everything you had suggested + a few more tests.

I've changed my function to:

test if driver support transactions
test if 'transaction' succeed
both 'finish' and 'clear' the QSqlQuery
test if 'comit' succeed

New code:
@
void MainWindow::testFailClicked()
{
QSqlDriver *driver = QSqlDatabase::database().driver();

if (driver->hasFeature(QSqlDriver::Transactions)){
    qDebug() << "transaction()";
    if (QSqlDatabase::database().transaction()) {
        qDebug() << "transaction():operation succeeded";
    }
    else{
        qDebug() << "transaction():error:" << QSqlDatabase::database().lastError();
    }
}

QSqlQuery query;
query.prepare("DELETE FROM artigos WHERE artigoid = :artigoid");
query.bindValue(":artigoid", "abc");
query.exec&#40;&#41;;
query.finish(&#41;;
query.clear(&#41;;

if (driver->hasFeature(QSqlDriver::Transactions)){
    qDebug() << "comit()";
    if (QSqlDatabase::database().commit()){
        qDebug() << "comit():operation succeeded";
    }
    else{
        qDebug() << "comit():error:" << QSqlDatabase::database().lastError();
    }
}

}
@

Output:
@
transaction()
transaction():operation succeeded
comit()
comit():operation succeeded
@

Unfortunately, everything succeeds normally (as previously expected), and there again I have no clue how to fix this 'problem'.

Correct me if I am wrong...

I don't see too many people here using QT + MinGW + Windows programming + ODBC + Windows database like SQL Server
I cannot imagine a business management application without a database and without transactions
how come nobody had this problem / issue before !?
am I the first one to try this combination !?

I was trying to learn QT, and at the same time I was building a small application to find out if QT capabilities would fit my project needs.

I really was enjoying programming with QT, but now I stuck here…

Does anyone have any more suggestions?

panosk

Hi,

First, don't despair :) . Most people here, including me, use very often databases with Qt. I use transactions very often with various combinations of Qt, OS, database, etc and never had a problem, but I haven't used ODBC and QSqlQueryModel.

QSqlQueryModel is read only so maybe this is the reason for your problem because you change the underlying data. You could try use QSqlTableModel instead and see what happens. Also, can you share your database schema?

antseq

Thank you for your suggestions and giving me some hope for this situation.

I've changed my 'setupSQL()' function to use QSqlTableModel instead of QSqlQueryModel but once again the QTableView shows BLANK records.

New code:
@
void MainWindow::setupSQL()
{
tableModel = new QSqlTableModel(this);
tableModel->setTable("artigos");
tableModel->select();

tblView->resizeColumnsToContents();
tblView->setAlternatingRowColors(true);
tblView->setSelectionMode(QAbstractItemView::SingleSelection);
tblView->setSelectionBehavior(QAbstractItemView::SelectRows);
tblView->setModel(tableModel);
tblView->selectRow(0);
tblView->setFocus();

/*
sqlModel = new QSqlQueryModel;
sqlModel->setQuery("SELECT artigoid, descricao FROM artigos");

tblView->resizeColumnsToContents();
tblView->setAlternatingRowColors(true);
tblView->setSelectionMode(QAbstractItemView::SingleSelection);
tblView->setSelectionBehavior(QAbstractItemView::SelectRows);
tblView->setModel(sqlModel);
tblView->selectRow(0);
tblView->setFocus();
*/

}
@

To be honest I personally avoid anything with 'Table' name, because generally means that 'thing' will retrieve all fields from the database table and (even worst) sometimes all records too.

Because of that, I decided to check with "SQL Server Profiler" what happens on Server side when someone uses QSqlTableModel and I think I have "found" the (my) problem on Server but I am not sure where exactly is the problem on my / QT side:

when the window opens, QSqlTableModel (I think) creates a cursor "exec sp_cursoropen 180150003" on server
retrieves some records "exec sp_cursorfetch 180150003"
if the window repaint/refresh retrieves again some records "exec sp_cursorfetch 180150003"
after executing the "transaction" someone (who?) closes the cursor being used (I think) by QSqlTableModel "exec sp_cursorclose 180150003"
after that, there are no more entries of "exec sp_cursorfetch...." That's why (I think) the QTableView show BLANK records.

NOTE: On my first post I had MDI subwindows showing each one of them a completely different and unrelated table. After the "transaction()" all subwindows/QTableView automatically show BLANK records. I've checked now with "SQL Server Profiler" and after the "transaction()" there as many "exec sp_cursorfetch" as opened subwindows.

It seems that all "cursors" are automatically closed, no matter if they are related (table) with the updates being made.

Assuming that the "problem" is that\those "exec sp_cursorclose", does anyone have an idea why that\those (exec sp_cursorclose) automatically happens:

Is it a ODBC (generally speaking) limitation?
Is it a ODBC (QT implementation) limitation?
Is it a ODBC (MSSQL implementation) limitation?
Is it a QSqlxxxxxx implementation/limitation?

What am I missing here?

Any suggestions or comments would be greatly appreciated

panosk

Have a look at these links, they seem relevant:
http://msdn.microsoft.com/en-us/library/ms131281.aspx
http://msdn.microsoft.com/en-us/library/ms131266.aspx

antseq

THANK YOU!
I think that you solved the mystery.

The msdn documentation says that with "SQL_COPT_SS_PRESERVE_CURSORS" default value "SQL_PC_OFF" : Cursors are closed when transaction is committed or rolled back...

I've tried to set this specific option before connecting to database, but unfortunatelly SqlDatabase::setConnectOptions does not support that specific parameter reporting a warning :

@db.setConnectOptions("SQL_COPT_SS_PRESERVE_CURSORS=SQL_PC_ON")

QODBCDriver::open: Unknown connection attribute ' "SQL_COPT_SS_PRESERVE_CURSORS" '@

Opening the source code of "qsql_odbc.cpp" it seems that setConnectOptions only accepts the parameters described in documentation (http://qt-project.org/doc/qt-5.0/qtsql/qsqldatabase.html#setConnectOptions)

It seems that I won't have transactions with ODBC + SQL Server :(

I don't know (I'm new here) if someone from QT "development" read these forums, but I will leave here a few suggestions:

you could save a lot of time to programmers (like me), by adding a "note" on documentation about this situation (QODBC + SQL Server + Transactions => Close All Cursors => All visible Tables/Grids/Views BLANK)
Could you add this parameter in a future version!? or have an overload "setConnectOptions" (advanced users) to let us call any parameters, even those not implemented!?
Maybe, there is a reason for not supporting this parameter, for instance (I'm guessing) that parameter could have (even worst) secondary effects!?

Thank you again for helping me with this issue.

panosk

Usually devs don't watch these forums for bugs/requests. I think "Qt bug tracker":https://bugreports.qt-project.org/secure/Dashboard.jspa is the right place.

If/when you post it, please post here the link so I can keep track of it too. I may have to deal with ODBC+SQL Server in the near future, as much as I dislike to mess up with it :)

antseq

Thanks for the "Qt bug tracker" link.

Here is the link (It was reported as a "Suggestion"):
https://bugreports.qt-project.org/browse/QTBUG-31587

regards,

antseq

From the “Qt bug tracker” I didn't get anything useful... I think that they don't realize what I suggested (maybe my fault) and/or If they really "read" my suggestions... I was not reporting a bug, but only trying to : first (documentation) help other (future) users with this issue, second (suggestion) se if in the future (no matter when) they could improve the odbc framework to accept new parameters...

For the record... Initially I was going to buy the QT commercial version, but I was a little skeptical about it... so I wrote an email to Digia asking a lot of questions about QT, if it would do this, would do that, ..., and among them was my concern of not seeing too many people (here) programming with windows + sql server... and surprisingly they answered "yes" + "positive" to all (100%) my questions\concerns which let me wondering how could that be possible if all the programming languages\platforms I've used in the past always had some issues and none of them would\will be 100% perfect.

That why I decided to "try" the open-source version for awhile and check myself all the QT capabilities and how far could I go "before buying".

At least there is one thing I'm glad :) I didn't buy it :)

As I mentioned on "bug report" (QTBUG-31587) I'll check my project priorities and expectations and see what can (still) be done.

Big thank you for everyone in the forum.

regards,