LIKE '%search_item%' query works as a case sensitive LIKE 'search_item%' in QMYSQL QSqlDatabase connection
-
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 c4143f909528e3fab0677a28631d10389354c491used 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));
-
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); -
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 c4143f909528e3fab0677a28631d10389354c491used 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 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 forLIKE
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 forLIKE
clauses --- I don't know.I would be "surprised" if the Qt
QMYSQL
driver altered the database default forLIKE
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 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 forLIKE
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 forLIKE
clauses --- I don't know.I would be "surprised" if the Qt
QMYSQL
driver altered the database default forLIKE
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?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
-
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
Hi and welcome to devnet,
Did you try printing the query generated to see what is actually sent to the database ?
-
Hi and welcome to devnet,
Did you try printing the query generated to see what is actually sent to the database ?
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;
-
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;
I meant QSqlQuery::lastQuery.
-
I meant QSqlQuery::lastQuery.
@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 -
@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 0I 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.
-
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.
it is an instance from a web hosted mysql mariadb database on my laptop as local host
-
it is an instance from a web hosted mysql mariadb database on my laptop as local host
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.
-
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;
@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.
-
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); -
-
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);