Sqlite3 Select statment return no result

MrLibya

Hello
i've this strange problem ! , well i wil let the code explain

    QSqlQuery query;
    if(!query.exec(QString("SELECT * FROM salarys WHERE date = '%1'").arg(ui->deMonth->date().toString(MONTHFORMAT)))){
        qDebug()<<"sql statment faild";
        return;
    }
    qDebug()<<query.lastQuery();
    qDebug()<<query.lastError().text();

    if(!query.next()){
        qDebug()<<"select statment no rows affected";
        return;
    }

output:

"SELECT * FROM salarys WHERE date = '10/2021'"
" "
select statment no rows affected

so i've try to run the query on navicat :
alt text

so what is the problem ? (

SGaist

Hi,

Did you check what you got with the same query written by hand rather than using a parameter ?

What exact type is the date column in your database ?

How many rows contain this date ?

MrLibya

@SGaist
yes i've try this :

query.exec(QString("SELECT * FROM salarys WHERE date = '10/2021'"))

and it's work , but it's the same query that executed when i used parameter.
column type is TEXT and theres 12 row for this date ( 44 row in total, with different date )

MrLibya

ok i found the problem ,it cuz of the arabic numbers !
let's see the date is : 10/2021 in the arabic number ١٠/٢٠٢١ , the QDate is uses the arabic number ( cuz i use RTL ) so it gives the number as ١٠/٢٠٢١ not 10/2021 .
so this is Sqlite3 bug ! , to try the fix i've change of the rows date from 10/2021 - > ١٠/٢٠٢١ then test it and i've got 1 rows !

even thought this is not a real fix , it should recognize that is the same ! , or qt make QDate auto convert to normal numbers

note : QString can handle this , so if u write :

QString test = "١٠/٢٠٢١";
    qDebug()<<test; // output : "10/2021"

but if u send test value to db or anyway it will send the real value ("١٠/٢٠٢١" and not "10/2021")

but Sqlite3 can't handle it !

SGaist

I wouldn't say it's an Sqlite3 bug (I might be wrong though). From your description you are trying to search in a ASCII column with Arabic chars. You should first convert your Arabic representation of the date into something that's understandable by your database backend.

MrLibya

@SGaist No it's not arabic chars ! it's arabic number , read this.

it's the QDate that uses arabic numbers ( even though i've change it to english and it write in english, but it still uses the arabic numbers ! ).

this video is show everything : https://youtu.be/hS7v1KFhOds

SGaist

Are you also inserting data in your database through Qt ?

MrLibya

@SGaist yep using QDateEdit and it insert it as arabic digits , but for this table i'm using normal number ( didn't input from Qt )

SGaist

Did you create that database using another sqlite application ?

I'd test again with a database created completely with Qt.

MrLibya

@SGaist I think u got lost there :D , and my english isn't that good anyway i will try explain more :
east numbers ( west numbers ) : ٠ (0) , ١ (1), ٢ (2), ٣ (3), ٤ (4), ٥ (5), ٦ (6), ٧ (7), ٨ (8) , ٩ (9) .

QDateEdit uses east numbers by default i think that cuz my computer is arabic ! , even if i change the QDateEdit prototype to english the numbers are changed to english but when insert to db it gives in east numbers ( as u can see the video above ) .
QString can recognize tha east numbers
so if u write :

QString test = "٢٠١٧"; // output will be 2017

so here we put east numbers in QString but when try to print with qDebug() or anything else it will give us the numbers in west form , but if u try to insert the value of test in db it will insert the real value ! the east form .
Sqlite3 can't recognize the east numbers , it will treat it as some char's no more !

the table i'm work on it now it uses different method to insert the date it's not uses any QDateEdit ...etc .
but when i want to read i want the user insert the date from QDateEdit , but QDateEdit will use the east numbers and in the table the date column it uses west numbers !

VRonin

What type is date in your database?

MrLibya

@VRonin Text

VRonin

so build it as text...

QLocale arabLocal(QLocale::Arabic);
const QString dateText = arabLocal.Tostring(ui->deMonth->date().month())
+ '/' + arabLocal.Tostring(ui->deMonth->date().year());

or

QLocale englLocal(QLocale::English);
const QString dateText = englLocal.Tostring(ui->deMonth->date().month())
+ '/' + englLocal.Tostring(ui->deMonth->date().year());

Depending on whether you want the numbers in arabic or western format

P.S.
never, ever build your queries concatenating unescaped user input. see https://www.w3schools.com/sql/sql_injection.asp

MrLibya

@VRonin that's didn't work , also the output is :

"1/2,022" // the date was 01/2022

VRonin

@MrLibya My bad:

QLocale englLocal(QLocale::English);
englLocal.setNumberOptions(QLocale::OmitGroupSeparator | englLocal.numberOptions());
QString dateText = englLocal.Tostring(ui->deMonth->date().month());
if(dateText.size()==1) dateText.prepend('0');
dateText += '/' + englLocal.Tostring(ui->deMonth->date().year());

MrLibya

@VRonin Thanks too much :)

VRonin

Actually scrap that, it's much easier, don't know what I was thinking...

const QString dateText = QLocale(QLocale::English).toString(ui->deMonth->date(),QStringLiteral("MM/yyyy"));