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

[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));

[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);

[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?

[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

[SGaist]

Hi and welcome to devnet,

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

[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;

[SGaist]

I meant QSqlQuery::lastQuery.

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

[arost]

@Christian-Ehrlicher

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

[Christian Ehrlicher]

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.

[ChrisW67]

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

[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);

[JonB]

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