Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. LIKE '%search_item%' query works as a case sensitive LIKE 'search_item%' in QMYSQL QSqlDatabase connection
Forum Updated to NodeBB v4.3 + New Features

LIKE '%search_item%' query works as a case sensitive LIKE 'search_item%' in QMYSQL QSqlDatabase connection

Scheduled Pinned Locked Moved Solved General and Desktop
13 Posts 5 Posters 653 Views 1 Watching
  • 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.
  • A Offline
    A Offline
    arost
    wrote on last edited by
    #1

    Hi all,

    While the LIKE '%search_item%' query works excellent in local mysql xamp phpmyadmin and returns all case insensitive results; But in Qt C++ 6.5 Windows 10 OS on the same machine the LIKE '%search_item%' query works as a case sensitive LIKE 'search_item%' in QMYSQL QSqlDatabase stablished connection

    Qt C++ 6.5
    Windows Version: Enterprise 64-bit
    XAMPP Version: 8.2.12
    Control Panel Version: 3.3.0
    MariaDB 10.4.32-MariaDB source revision c4143f909528e3fab0677a28631d10389354c491

    used code:

    localMainDBWorker = new MySQL_DB("LocalMainDB");
    localMainDBWorker->DatabaseName = "parts_a";
    localMainDBWorker->TableName = "stuff";
    localMainDBWorker->HostName = "localhost";//"127.0.0.1"
    localMainDBWorker->UserName = "newuser";
    localMainDBWorker->Password = "password";
    ...
    db = QSqlDatabase::addDatabase("QMYSQL", ConnectName);

    db.setDatabaseName(DatabaseName);
    db.setHostName(HostName);
    db.setPort(PortNumber);
    db.setUserName(UserName);
    db.setPassword(Password);
    
    if (!db.open()) {
        connected = false;
        qDebug() << "connect2LocalMainDB() Error: "+ConnectionName+" " << db.lastError().text();
        emit sigSendConnectionStatus(-1);
        return 0;
    }else{
        emit sigSendConnectionStatus(1);
    }
    

    ...
    query.prepare("SELECT code FROM stuff WHERE code LIKE '%"+text+"%'");

        //query.prepare("SELECT code FROM stuff WHERE code LIKE '%%"+text+"%%'");
    
        //query.prepare("SELECT code FROM stuff WHERE code LIKE ?");
        //query.addBindValue("%"+text+"%");
    
        //query.prepare("SELECT code FROM stuff WHERE code LIKE :input");
        //query.bindValue(":input", QString("%%1%").arg(text));
    
    JonBJ 1 Reply Last reply
    0
    • A Offline
      A Offline
      arost
      wrote on last edited by arost
      #12

      Thanks All,

      Unfortunately, the problem was default filter in the model and completer used to show the result in the app.

      model = new QStringListModel(this);
      completer = new QCompleter(this);
      completer->setModel(model);
      // This will ensure that the completer does not filter the data at all.
      completer->setFilterMode(Qt::MatchFlags(Qt::MatchFlag::MatchContains));
      completer->setCaseSensitivity(Qt::CaseInsensitive);

      JonBJ 1 Reply Last reply
      0
      • A arost

        Hi all,

        While the LIKE '%search_item%' query works excellent in local mysql xamp phpmyadmin and returns all case insensitive results; But in Qt C++ 6.5 Windows 10 OS on the same machine the LIKE '%search_item%' query works as a case sensitive LIKE 'search_item%' in QMYSQL QSqlDatabase stablished connection

        Qt C++ 6.5
        Windows Version: Enterprise 64-bit
        XAMPP Version: 8.2.12
        Control Panel Version: 3.3.0
        MariaDB 10.4.32-MariaDB source revision c4143f909528e3fab0677a28631d10389354c491

        used code:

        localMainDBWorker = new MySQL_DB("LocalMainDB");
        localMainDBWorker->DatabaseName = "parts_a";
        localMainDBWorker->TableName = "stuff";
        localMainDBWorker->HostName = "localhost";//"127.0.0.1"
        localMainDBWorker->UserName = "newuser";
        localMainDBWorker->Password = "password";
        ...
        db = QSqlDatabase::addDatabase("QMYSQL", ConnectName);

        db.setDatabaseName(DatabaseName);
        db.setHostName(HostName);
        db.setPort(PortNumber);
        db.setUserName(UserName);
        db.setPassword(Password);
        
        if (!db.open()) {
            connected = false;
            qDebug() << "connect2LocalMainDB() Error: "+ConnectionName+" " << db.lastError().text();
            emit sigSendConnectionStatus(-1);
            return 0;
        }else{
            emit sigSendConnectionStatus(1);
        }
        

        ...
        query.prepare("SELECT code FROM stuff WHERE code LIKE '%"+text+"%'");

            //query.prepare("SELECT code FROM stuff WHERE code LIKE '%%"+text+"%%'");
        
            //query.prepare("SELECT code FROM stuff WHERE code LIKE ?");
            //query.addBindValue("%"+text+"%");
        
            //query.prepare("SELECT code FROM stuff WHERE code LIKE :input");
            //query.bindValue(":input", QString("%%1%").arg(text));
        
        JonBJ Online
        JonBJ Online
        JonB
        wrote on last edited by JonB
        #2

        @arost said in LIKE '%search_item%' query works as a case sensitive LIKE 'search_item%' in QMYSQL QSqlDatabase connection:

        //query.prepare("SELECT code FROM stuff WHERE code LIKE '%%"+text+"%%'");

        Let's start with this is wrong. Why have you doubled the %%s here, that stops it being a wildcard for LIKE pattern?

        You are not checking your prepare() return results --- they return a value for a reason. If you did you may find that the driver does not accept bound values for LIKE clauses --- I don't know.

        I would be "surprised" if the Qt QMYSQL driver altered the database default for LIKE being case-insensitive, if you claim that is what it is outside. Is it possible that "local mysql xamp phpmyadmin" changes a case-sensitive default to case insensitive instead?

        A 1 Reply Last reply
        0
        • JonBJ JonB

          @arost said in LIKE '%search_item%' query works as a case sensitive LIKE 'search_item%' in QMYSQL QSqlDatabase connection:

          //query.prepare("SELECT code FROM stuff WHERE code LIKE '%%"+text+"%%'");

          Let's start with this is wrong. Why have you doubled the %%s here, that stops it being a wildcard for LIKE pattern?

          You are not checking your prepare() return results --- they return a value for a reason. If you did you may find that the driver does not accept bound values for LIKE clauses --- I don't know.

          I would be "surprised" if the Qt QMYSQL driver altered the database default for LIKE being case-insensitive, if you claim that is what it is outside. Is it possible that "local mysql xamp phpmyadmin" changes a case-sensitive default to case insensitive instead?

          A Offline
          A Offline
          arost
          wrote on last edited by arost
          #3

          @JonB

          query.prepare("SELECT code FROM stuff WHERE code LIKE '%"+text+"%'");

          //query.prepare("SELECT code FROM stuff WHERE code LIKE '%%"+text+"%%'");
          
          //query.prepare("SELECT code FROM stuff WHERE code LIKE ?");
          //query.addBindValue("%"+text+"%");
          
          //query.prepare("SELECT code FROM stuff WHERE code LIKE :input");
          //query.bindValue(":input", QString("%%1%").arg(text));
          

          all the above code (first line or commented in the next lines) return the same results (as LIKE text%) without any error

          SGaistS 1 Reply Last reply
          0
          • A arost

            @JonB

            query.prepare("SELECT code FROM stuff WHERE code LIKE '%"+text+"%'");

            //query.prepare("SELECT code FROM stuff WHERE code LIKE '%%"+text+"%%'");
            
            //query.prepare("SELECT code FROM stuff WHERE code LIKE ?");
            //query.addBindValue("%"+text+"%");
            
            //query.prepare("SELECT code FROM stuff WHERE code LIKE :input");
            //query.bindValue(":input", QString("%%1%").arg(text));
            

            all the above code (first line or commented in the next lines) return the same results (as LIKE text%) without any error

            SGaistS Offline
            SGaistS Offline
            SGaist
            Lifetime Qt Champion
            wrote on last edited by
            #4

            Hi and welcome to devnet,

            Did you try printing the query generated to see what is actually sent to the database ?

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

            A 1 Reply Last reply
            0
            • SGaistS SGaist

              Hi and welcome to devnet,

              Did you try printing the query generated to see what is actually sent to the database ?

              A Offline
              A Offline
              arost
              wrote on last edited by arost
              #5

              @SGaist

              Yes, Thx
              when the text is "5" and "55", the prepared queries are
              "SELECT code FROM stuff WHERE code LIKE '%5%'"
              "SELECT code FROM stuff WHERE code LIKE '%55%'"
              respectively.

                  QString qry = "SELECT code FROM stuff WHERE code LIKE '%"+text+"%'";
                  query.prepare(qry);
                  qDebug() << qry;
              
              SGaistS C 2 Replies Last reply
              0
              • A arost

                @SGaist

                Yes, Thx
                when the text is "5" and "55", the prepared queries are
                "SELECT code FROM stuff WHERE code LIKE '%5%'"
                "SELECT code FROM stuff WHERE code LIKE '%55%'"
                respectively.

                    QString qry = "SELECT code FROM stuff WHERE code LIKE '%"+text+"%'";
                    query.prepare(qry);
                    qDebug() << qry;
                
                SGaistS Offline
                SGaistS Offline
                SGaist
                Lifetime Qt Champion
                wrote on last edited by
                #6

                I meant QSqlQuery::lastQuery.

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

                A 1 Reply Last reply
                0
                • SGaistS SGaist

                  I meant QSqlQuery::lastQuery.

                  A Offline
                  A Offline
                  arost
                  wrote on last edited by
                  #7

                  @SGaist said in LIKE '%search_item%' query works as a case sensitive LIKE 'search_item%' in QMYSQL QSqlDatabase connection:

                  QSqlQuery::lastQuery.

                      QSqlQuery query(sdb->db);
                      query.prepare("SELECT code FROM stuff WHERE code LIKE '%"+text+"%'");
                      qDebug() << query.lastQuery();
                  

                  12:31:04: Starting F:\data\store\build-store-Desktop_Qt_6_5_0_MinGW_64_bit-Debug\store.exe...
                  "SELECT code FROM stuff WHERE code LIKE '%5%'"
                  "SELECT code FROM stuff WHERE code LIKE '%55%'"
                  12:31:12: F:\data\store\build-store-Desktop_Qt_6_5_0_MinGW_64_bit-Debug\store.exe exited with code 0

                  Christian EhrlicherC 1 Reply Last reply
                  0
                  • A arost

                    @SGaist said in LIKE '%search_item%' query works as a case sensitive LIKE 'search_item%' in QMYSQL QSqlDatabase connection:

                    QSqlQuery::lastQuery.

                        QSqlQuery query(sdb->db);
                        query.prepare("SELECT code FROM stuff WHERE code LIKE '%"+text+"%'");
                        qDebug() << query.lastQuery();
                    

                    12:31:04: Starting F:\data\store\build-store-Desktop_Qt_6_5_0_MinGW_64_bit-Debug\store.exe...
                    "SELECT code FROM stuff WHERE code LIKE '%5%'"
                    "SELECT code FROM stuff WHERE code LIKE '%55%'"
                    12:31:12: F:\data\store\build-store-Desktop_Qt_6_5_0_MinGW_64_bit-Debug\store.exe exited with code 0

                    Christian EhrlicherC Online
                    Christian EhrlicherC Online
                    Christian Ehrlicher
                    Lifetime Qt Champion
                    wrote on last edited by
                    #8

                    I doubt this has anything to do with the Qt mysql driver as the query is simply passed to the mysql lib. Therefore please provide a minimal, compilable example including the table creation & fill. One record should be enough.

                    Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                    Visit the Qt Academy at https://academy.qt.io/catalog

                    A 1 Reply Last reply
                    0
                    • Christian EhrlicherC Christian Ehrlicher

                      I doubt this has anything to do with the Qt mysql driver as the query is simply passed to the mysql lib. Therefore please provide a minimal, compilable example including the table creation & fill. One record should be enough.

                      A Offline
                      A Offline
                      arost
                      wrote on last edited by
                      #9

                      @Christian-Ehrlicher

                      it is an instance from a web hosted mysql mariadb database on my laptop as local host

                      9b8c430b-760b-4b8c-ac9b-71bd17a70a96-image.png

                      Christian EhrlicherC 1 Reply Last reply
                      0
                      • A arost

                        @Christian-Ehrlicher

                        it is an instance from a web hosted mysql mariadb database on my laptop as local host

                        9b8c430b-760b-4b8c-ac9b-71bd17a70a96-image.png

                        Christian EhrlicherC Online
                        Christian EhrlicherC Online
                        Christian Ehrlicher
                        Lifetime Qt Champion
                        wrote on last edited by
                        #10

                        This does not matter - I can use a local mysql instance but need a minimal, reproducible example where you create a table, fill it and then do your problematic selects.

                        Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                        Visit the Qt Academy at https://academy.qt.io/catalog

                        1 Reply Last reply
                        1
                        • A arost

                          @SGaist

                          Yes, Thx
                          when the text is "5" and "55", the prepared queries are
                          "SELECT code FROM stuff WHERE code LIKE '%5%'"
                          "SELECT code FROM stuff WHERE code LIKE '%55%'"
                          respectively.

                              QString qry = "SELECT code FROM stuff WHERE code LIKE '%"+text+"%'";
                              query.prepare(qry);
                              qDebug() << qry;
                          
                          C Offline
                          C Offline
                          ChrisW67
                          wrote on last edited by
                          #11

                          @arost Case sensitivity in MariaDB/MySql depends on the character set and collation set on the column, table, database, server, and/or client-server connection. We cannot see any of these in your examples, but at least the last item in that list is the variable here. All the default character set collations are case insensitive but can be overridden. If a certain column or table should always compare with/without case-sensitivity then you should probably declare it that way at creation time.

                          If your code needs to be portable to another database system, e.g. Oracle or Sql Server, then it is safest to code generically using ANSI standard SQL functions:

                          select * from blah where lower(col) like '%foo%';
                          

                          and make sure the user input "foo" is forced to lower case (in your code or in the SQL).

                          The examples, '%55%' and '%5%', are completely unaffected by case-sensitivity (There being no such beast as upper or lower-case numerals). If you intend to demonstrate case-sensitivity issues then don't use these in the minimal, compilable example including the table creation & fill requested by @Christian-Ehrlicher.

                          1 Reply Last reply
                          3
                          • A Offline
                            A Offline
                            arost
                            wrote on last edited by arost
                            #12

                            Thanks All,

                            Unfortunately, the problem was default filter in the model and completer used to show the result in the app.

                            model = new QStringListModel(this);
                            completer = new QCompleter(this);
                            completer->setModel(model);
                            // This will ensure that the completer does not filter the data at all.
                            completer->setFilterMode(Qt::MatchFlags(Qt::MatchFlag::MatchContains));
                            completer->setCaseSensitivity(Qt::CaseInsensitive);

                            JonBJ 1 Reply Last reply
                            0
                            • A arost has marked this topic as solved on
                            • A arost

                              Thanks All,

                              Unfortunately, the problem was default filter in the model and completer used to show the result in the app.

                              model = new QStringListModel(this);
                              completer = new QCompleter(this);
                              completer->setModel(model);
                              // This will ensure that the completer does not filter the data at all.
                              completer->setFilterMode(Qt::MatchFlags(Qt::MatchFlag::MatchContains));
                              completer->setCaseSensitivity(Qt::CaseInsensitive);

                              JonBJ Online
                              JonBJ Online
                              JonB
                              wrote on last edited by
                              #13

                              @arost
                              So it was always working correctly. This is why you should always be prepared to show a minimal example of your code.

                              1 Reply Last reply
                              1

                              • Login

                              • Login or register to search.
                              • First post
                                Last post
                              0
                              • Categories
                              • Recent
                              • Tags
                              • Popular
                              • Users
                              • Groups
                              • Search
                              • Get Qt Extensions
                              • Unsolved