Qt QSqlQueryModel possibilities regarding "SQL AND" and debugging at all regarding Qt Sql

[G4bandit]

I have this code

   QM = new QSqlQueryModel(this);
   
   QM->setQuery("SELECT Attribute1 "
                     "FROM [dbo].[Tbl1] "
                     "WHERE Attribute2 LIKE '%"+Filter1+"%' AND Attribute3 LIKE '%"+Filter2+"%'");

1. Does Qt Sql accept such "AND" on this place?
2. I know SQL query is working because tested parallel inside of SSMS. So my main problem which I alway have again and again how can I directly determine by debugging what is going on?

• Something with syntax for Qt Sql OR if here everything alright
• Than I would know that I have to check my Srtuctured Query Language Set.

I am always unsure what is allowed for the Qt SQL expressions, - of course except SQL at all. Something like is it ok when I have inside the expression instead of 1 WS 3 WS (Whitespace).

[SGaist]

Hi,

There's no Qt SQL language. You can use whatever your DB provides. As for your problem, I'd check the generated query. You might also need to double the % for the query to use it properly.

[G4bandit]

@SGaist thks

I tried with %%FilterExpr%%, did not helped.
Even this code

   QM->setQuery("SELECT Attribute1 "
                     "FROM [dbo].[Tbl1] "
                     "WHERE Attribute2 LIKE 'FilterExpr1' AND Attribute3 LIKE 'FilterExpr2'");

So with the difference not to pass variables inside the string constant.
Even this makes problems for Qt SQL or let say Qt Database treatment classes/functions.

[JonB]

@G4bandit

1. The correct syntax will be like your first attempt.

2. There is nothing wrong in your second attempt. However, it probably does not give the result set you are intending.

3. Nowhere have you said what the actual problem is? You get an error? You do not get the result set back you expected?

Tell us what the actual problem is, and try

   qDebug("SELECT Attribute1 "
                     "FROM [dbo].[Tbl1] "
                     "WHERE Attribute2 LIKE '%"+Filter1+"%' AND Attribute3 LIKE '%"+Filter2+"%'");

and copy & paste what exactly that expands to.

[G4bandit]

@JonB said in Qt QSqlQueryModel possibilities regarding "SQL AND" and debugging at all regarding Qt Sql:

Nowhere have you said what the actual problem is? You get an error? You do not get the result set back you expected?

The last expected, - I do not get the result set back like expected.

Luckily I found out what the problem was.

TblModel = new QSqlTableModel(this);

I have deactivated this line. An than the SQL selection worked like expected.
I guess here came out that I am bloody newby in c++ :(

[JonB]

@G4bandit
I have no idea where you had that line of code or what effect it had for your question. But if it's working now for you....

BTW, how do you generate whatever is in Filter1 & Filter2 ? Because if either of those contains a % or certain other special characters (e.g. _, by default) your SQL expression will be invalid....

[G4bandit]

@JonB said in Qt QSqlQueryModel possibilities regarding "SQL AND" and debugging at all regarding Qt Sql:

how do you generate whatever is in Filter1 & Filter2

void MainWindow::CB_Function(QString Filter1, QString Filter2)

[JonB]

@G4bandit
No, that does not answer what I am asking. I am saying to you: If your filter values could contain, say, % or _ characters, what action are you taking to "protect" those characters before inserting them literally into the LIKE clause? If you do nothing then it will go wrong if the filters contain those characters. If you wish to guarantee that the filters cannot contain those characters that it doesn't matter, else it does.

[G4bandit]

@JonB
You mean special cases like this:

OR

I understand what you mean but Whooooahaa
Thks a lot for this hint but at this point I do not have such a high requirement on my self / on the code to write it perfect.

[G4bandit]

@JonB said in Qt QSqlQueryModel possibilities regarding "SQL AND" and debugging at all regarding Qt Sql:

qDebug("SELECT Attribute1 "
"FROM [dbo].[Tbl1] "
"WHERE Attribute2 LIKE '%"+Filter1+"%' AND Attribute3 LIKE '%"+Filter2+"%'");

I am very interested in this possibility. But when I try I get this error:
"Error: C2664: QDebug const" : (My own translation to english "Conversion from X to Y") "const QString" in "const char *""

I tried to find quickly a solution by asking the oracle but it seems not that easy to fix.
If you have directly a hint I would be very thankful.

[JonB]

@G4bandit
For the filters, only you know what values they might have, I do not. In your table I see a value of 456_45. If you/the user passes that string to search for directly into your function as Filter1 or Filter2 and you generate SQL clause like:
WHERE Attribute2 LIKE '%"+Filter1+"%'
so that it expands to
WHERE Attribute2 LIKE '%456_45%'
it will not give the result it should do. Up to you whether you wish to deal with this.

For your compiler error, I have no idea, I don't use C++. All I was suggesting was that you write code to print out/display/whatever string you are passing to your original QM->setQuery(...) statement so that you/we can see what it's actually expanding to. Your compiler error will be to do with it mixing C strings & QStrings. Have a look at, say, https://stackoverflow.com/questions/18427191/how-to-print-string-literal-and-qstring-with-qdebug.