Qt QSqlQuery scope issues

[Wer_Bn]

Hello

I'm trying to follow procedure as stated in
http://doc.qt.io/qt-4.8/qsqlquery.html

I take this as reference

QSqlQuery query;
    query.prepare("INSERT INTO person (id, forename, surname) "
                  "VALUES (:id, :forename, :surname)");
    query.bindValue(":id", 1001);
    query.bindValue(":forename", "Bart");
    query.bindValue(":surname", "Simpson");
    query.exec();

QSqlQuery query;
    query.prepare("INSERT INTO person (id, forename, surname) "
                  "VALUES (:id, :forename, :surname)");
    query.bindValue(":id", 1001);
    query.bindValue(":forename", "Bart");
    query.bindValue(":surname", "Simpson");
    query.exec();

To copy to clipboard, switch view to plain text mode 

This query item, in my case, is a pointer declared as member of a class.
With this query pointer, I do in the class constructor, the following:

mydb.setDatabaseName("BlaBla.db");
mydb.open();
 
query = new QSqlQuery(mydb);
 
query->exec("create table MY_TABLE "
              "(column integer)");
 
 
query->prepare("INSERT INTO  MY_TABLE(column) VALUES(:holder)");

mydb.setDatabaseName("BlaBla.db");
mydb.open();

query = new QSqlQuery(mydb);

query->exec("create table MY_TABLE "
              "(column integer)");


query->prepare("INSERT INTO  MY_TABLE(column) VALUES(:holder)");

To copy to clipboard, switch view to plain text mode 

With mydb being initialized as QSqlDatabase::addDatabase("QSQLITE")

Ok, so far so good, the table is created.
Now, I want to use this query in a efficient way.
I want to prepare it, and use it several times to add an entry to the database. That's why, at the end of the constructor, I prepare it already.

So after I do this, I call a method that uses this query, from within the mainwindow thread. Please notice that the database is also created when the mainwindow thread is initialized.

So, the method, inside the SAME class, is

query->bindValue(":holder", receivedVal);
 if (query->exec())
            qDebug() << "Record Inserted";
        else
            qDebug() << "Record not inserted";

query->bindValue(":holder", receivedVal);
 if (query->exec())
            qDebug() << "Record Inserted";
        else
            qDebug() << "Record not inserted";

To copy to clipboard, switch view to plain text mode 

In this function I always get "Record not inserted"
Now, for the funny fact:
If, inside this method, I create a LOCAL query with the same command, the data will be successfully added. Problem is, I have to prepare it ALWAYS, and this is the reason it gets really slow, I believe. UI gets really slow and freezing.

I've searched a lot already, but all I can find is scope ideas, that I believe I'm not missing.
So, I appreciate your help

Thank you

[jefftee]

What is the QSqlError you receive when the query->exec() returns false? Are you doing a query->finish() anywhere in your code? If you are, that will invalidate the prepared statement as well.

[Wer_Bn]

Hi jefftee,

The error I get is:

last Error: QSqlError("", "Parameter count mismatch", "")

last Error: QSqlError("", "Parameter count mismatch", "")

To copy to clipboard, switch view to plain text mode 

I am not doing any finish(), and I can see that the „query string“ is still the one I defined on prepare:

"INSERT INTO MY_TABLE(column) VALUES(:holder)"

"INSERT INTO MY_TABLE(column) VALUES(:holder)"

To copy to clipboard, switch view to plain text mode 

Also I can see on the „variables and expressions“ field that the value passed on bind stays on the query structure.

In images (here I have qry2 instead of query):
Structure after prepare:
After prepare.png
Structure after bind:
After bind.png
Structure after exec:
after exec.png

Thanks

[jefftee]

According to the SQLITE doc, "column" is a reserved keyword. Can you change the name of your column to a non-reserved keyword and see if that has anything to do with your error?

[loccus]

Hi jefftee!

I'm working with Wer_Bn in this project, and tried with a different name and the result is the same.

after exec2.PNG

Tanks

[jefftee]

In your first post, you show that you're creating the query on the heap and passing the db instance to the query constructor (which is correct). The QSqlDatabase instance seems to be allocated on the stack.

Has your QSqlDatabase named mydb gone out of scope by chance?

[loccus]

Hi,

I checked "mydb" and is remains on he scope all the way.
On constructor:
mydb on contstructor.PNG
Between bind and exec.
mydb before exec.PNG

Also tried to use a pointer to the data base on the constructor, and the result is the same.

Regarding the Portability note on http://doc.qt.io/qt-5/qsqlquery.html#prepare:

Some databases choose to delay preparing a query until it is executed the first time. In this case, preparing a syntactically wrong query succeeds, but every consecutive exec() will fail.

To test this I've inserted successfully a first value on the database immediately after the prepare, on the constructor, to test if there is any error on query. So no syntactically error on query.


I can not find any example of how to use the prepare on a first call, and the bind() & exec() repeatedly on another methods, although on http://doc.qt.io/qt-4.8/sql-sqlstatements.html says that:

When inserting multiple records, you only need to call QSqlQuery:repare() once. Then you call bindValue() or addBindValue() followed by exec() as many times as necessary

I am wondering if my approach is even possible...

Thanks again,

[jefftee]

I am wondering if my approach is even possible...

I am not a Qt 4.8 user, but I certainly prepare and subsequently bindValue/exec thousands of queries reusing the query that was previously prepared using Qt 5.x. Can you share more of your actual code? A problem like this is almost certainly going to be a head slapper once we find the problem...

[Wer_Bn]

We will try a simpler approach using the database directly on the MainWindow, instead of creating the database on a separate class.
Then it will be simpler for you to analize the code.
Then we will come and reply with the results

[Wer_Bn]

Meanwhile, we found a VERY interesting fact.

We changed the database to the MainWindow class:
- Database and query instance in the MainWindow Class (previously we had a database wrapper class)
- Both variables are initialized in MainWindow Class constructor
- query is being prepared in the contructor, after the database is created and opened
- Upon a button pressed action, we had new entry to the database, through bind and exec. It TOTALLY works. We get as many entries as many times we press the button.

So...
Next step is to check if the use of DLL libraries is jeopardizing all of this. The class that used to have the database and query instances is in this DLL...
Any feedback on this?

[jefftee]

Should be no issues with having code in your app exe or the DLL I wouldn't think. That said, I haven't had to use Windows in many years now, so perhaps someone else can chime in.