SQL WHERE Clause In QSqlQuery::setQuery( )

Donald9307

I have a SQLITE3 database connected to my Qt6 application. I can pass a SQL SELECT statement to setQuery( ) and have the query execute as long as the statement doesn’t contain a WHERE clause with a variable expression. For example, if the SQL statement has a WHERE clause something like,

SELECT * FROM table_name WHERE col-name = 3;

If my query name is query, I can pass the query to my model and It works just fine. But if the col_name isn’t hard-coded into the statement, something like

SELECT * FROM table_name WHERE col-name = var;

in which var is the name of a variable, query.exec( ) fails.

I want to be able to modify my TableView by using the CurrentText( ) of a comboBox which can be changed while the program is running so that my TableView only shows a subset of the table records based on the SQL WHERE clause.

Does anyone know how you can use a variable in a setQuery( ) function containing a SQL WHERE clause in Qt C++?

JonB

@Donald9307
SELECT statements with WHERE work just fine. And you can have WHERE col-name = var, so long as col-name is a literal (correct) column while var can be bound to a variable.

Show your code where you specify and build this SELECT * FROM table_name WHERE col-name = var; and the return result or any error from executing it.

But if the col_name isn’t hard-coded into the statement

Not sure what you mean here. You do know which column you are searching so it should be hard-coded into the statement. And needs to be. You can bind the var variable to take on the value from your combobox, but you cannot have a "variable" for the column name.

Pl45m4

@Donald9307 said in SQL WHERE Clause In QSqlQuery::setQuery( ):

Does anyone know how you can use a variable in a setQuery( ) function containing a SQL WHERE clause in Qt C++?

With bindings, as mentioned by @JonB , it would look like this:

QString comboBoxText = yourComboBox->currentText();


QSqlQuery query("SELECT * FROM table_name WHERE col-name = :colName");
query.bindValue(":colName", comboBoxText);

[Edit: Untested code]

JonB

@Pl45m4
Umm, did you actually test this?? I haven't used SQLite but would be surprised, nay astounded, if this is acceptable....

Christian Ehrlicher

Actually binding a query will not help here since the op wants to change the bound value dynamically. This can not work because you've no access to the QSqlQuery after QSqlQueryModel::setQuery(). So either pass an updated query after the combobox change our use a QSortFilterProxyModel and change the filter after every change.

Pl45m4

@JonB said in SQL WHERE Clause In QSqlQuery::setQuery( ):

did you actually test this??

No :) But IIRC at least binding (by name) works like this ;)

@Christian-Ehrlicher I missed the model part, which @Donald9307 also forgot to mention :)

Christian Ehrlicher

@Pl45m4 said in SQL WHERE Clause In QSqlQuery::setQuery( ):

which @Donald9307 also forgot to mention :)

It's just a wild guess due to 'I can pass a SQL SELECT statement to setQuery( )'

JonB

@Pl45m4 said in SQL WHERE Clause In QSqlQuery::setQuery( ):

No :) But IIRC at least binding (by name) works like this ;)

[Untested.] You cannot bind a table name to a variable in a query any more than you can bind a column name. You can bind a value for a column, e.g. in a WHERE clause or INSERT ... VALUES() statement. So not :table_name in yours.

JonB

@Christian-Ehrlicher said in SQL WHERE Clause In QSqlQuery::setQuery( ):

wants to change the bound value dynamically. This can not work because you've no access to the QSqlQuery after QSqlQueryModel::setQuery(). So either pass an updated query after the combobox change

Yes, you have to call QSqlQueryModel::setQuery() afresh each time you want to change the filter value. Shame, but does it matter for this case? But OP can re-call same function which binds variable value. Of course if there a few rows/values it might be much simpler and better to read in and use QSortFilterProxyModel.

If you read carefully OP actually wrote

If my query name is query, I can pass the query to my model and It works just fine. But if the col_name isn’t hard-coded into the statement, something like
SELECT * FROM table_name WHERE col-name = var;
in which var is the name of a variable, query.exec( ) fails.

They state it is col_name they wish to have as variable/bind. That would mean the combobox would contain column names rather than possible values for a particular column. We need @Donald9307 to clarify this before the correct answer for the SQL statement can be given.

Donald9307

@Pl45m4 The bindings approach with my table column and variable worked!! Thanks for your help.

Pl45m4

@Donald9307

Just to clear the confusion, do you have any model or in which way do you use your query?
Because there is no QSqlQuery::setQuery() (as in the title).

But good to hear that it worked for you :)