Qt Forum

    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search
    • Unsolved

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

    General and Desktop
    sql
    3
    11
    1622
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • G4bandit
      G4bandit last edited by 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).

      1 Reply Last reply Reply Quote 0
      • SGaist
        SGaist Lifetime Qt Champion last edited by

        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.

        Interested in AI ? www.idiap.ch
        Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

        1 Reply Last reply Reply Quote 1
        • G4bandit
          G4bandit last edited by 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 1 Reply Last reply Reply Quote 0
          • JonB
            JonB @G4bandit last edited by

            @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.

            1 Reply Last reply Reply Quote 0
            • G4bandit
              G4bandit last edited by 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 1 Reply Last reply Reply Quote 0
              • JonB
                JonB @G4bandit last edited by

                @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....

                1 Reply Last reply Reply Quote 0
                • G4bandit
                  G4bandit last edited by 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 1 Reply Last reply Reply Quote 0
                  • JonB
                    JonB @G4bandit last edited by 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.

                    1 Reply Last reply Reply Quote 0
                    • G4bandit
                      G4bandit last edited by G4bandit

                      @JonB
                      You mean special cases like this:

                      0_1516267498078_3d6cf37b-b8e7-45e8-b80c-a46fb1156a87-grafik.png
                      OR
                      0_1516267515693_3f004981-1d9d-4c45-bb8b-db4652e191c9-grafik.png

                      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.

                      JonB 1 Reply Last reply Reply Quote 0
                      • G4bandit
                        G4bandit last edited by 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.

                        1 Reply Last reply Reply Quote 0
                        • JonB
                          JonB @G4bandit last edited by 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.

                          1 Reply Last reply Reply Quote 0
                          • First post
                            Last post