Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. Qt Creator and other tools
  4. Sqlite3 Select statment return no result
Forum Updated to NodeBB v4.3 + New Features

Sqlite3 Select statment return no result

Scheduled Pinned Locked Moved Solved Qt Creator and other tools
17 Posts 3 Posters 5.3k 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.
  • M Offline
    M Offline
    MrLibya
    wrote on last edited by
    #1

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

    1 Reply Last reply
    0
    • SGaistS Offline
      SGaistS Offline
      SGaist
      Lifetime Qt Champion
      wrote on last edited by
      #2

      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 ?

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

      M 1 Reply Last reply
      0
      • SGaistS 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 ?

        M Offline
        M Offline
        MrLibya
        wrote on last edited by MrLibya
        #3

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

        1 Reply Last reply
        0
        • M Offline
          M Offline
          MrLibya
          wrote on last edited by MrLibya
          #4

          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 !

          1 Reply Last reply
          0
          • SGaistS Offline
            SGaistS Offline
            SGaist
            Lifetime Qt Champion
            wrote on last edited by
            #5

            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.

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

            M 1 Reply Last reply
            1
            • SGaistS 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.

              M Offline
              M Offline
              MrLibya
              wrote on last edited by MrLibya
              #6

              @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

              1 Reply Last reply
              0
              • SGaistS Offline
                SGaistS Offline
                SGaist
                Lifetime Qt Champion
                wrote on last edited by
                #7

                Are you also inserting data in your database through Qt ?

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

                M 1 Reply Last reply
                0
                • SGaistS SGaist

                  Are you also inserting data in your database through Qt ?

                  M Offline
                  M Offline
                  MrLibya
                  wrote on last edited by
                  #8

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

                  1 Reply Last reply
                  0
                  • SGaistS Offline
                    SGaistS Offline
                    SGaist
                    Lifetime Qt Champion
                    wrote on last edited by
                    #9

                    Did you create that database using another sqlite application ?

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

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

                    M 1 Reply Last reply
                    0
                    • SGaistS SGaist

                      Did you create that database using another sqlite application ?

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

                      M Offline
                      M Offline
                      MrLibya
                      wrote on last edited by
                      #10

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

                      1 Reply Last reply
                      0
                      • VRoninV Offline
                        VRoninV Offline
                        VRonin
                        wrote on last edited by
                        #11

                        What type is date in your database?

                        "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
                        ~Napoleon Bonaparte

                        On a crusade to banish setIndexWidget() from the holy land of Qt

                        M 1 Reply Last reply
                        0
                        • VRoninV VRonin

                          What type is date in your database?

                          M Offline
                          M Offline
                          MrLibya
                          wrote on last edited by
                          #12

                          @VRonin Text

                          1 Reply Last reply
                          0
                          • VRoninV Offline
                            VRoninV Offline
                            VRonin
                            wrote on last edited by VRonin
                            #13

                            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

                            "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
                            ~Napoleon Bonaparte

                            On a crusade to banish setIndexWidget() from the holy land of Qt

                            M 1 Reply Last reply
                            1
                            • VRoninV 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

                              M Offline
                              M Offline
                              MrLibya
                              wrote on last edited by
                              #14

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

                              "1/2,022" // the date was 01/2022
                              
                              VRoninV 1 Reply Last reply
                              0
                              • M MrLibya

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

                                "1/2,022" // the date was 01/2022
                                
                                VRoninV Offline
                                VRoninV Offline
                                VRonin
                                wrote on last edited by VRonin
                                #15

                                @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());
                                

                                "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
                                ~Napoleon Bonaparte

                                On a crusade to banish setIndexWidget() from the holy land of Qt

                                M 1 Reply Last reply
                                3
                                • VRoninV 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());
                                  
                                  M Offline
                                  M Offline
                                  MrLibya
                                  wrote on last edited by
                                  #16

                                  @VRonin Thanks too much :)

                                  1 Reply Last reply
                                  0
                                  • VRoninV Offline
                                    VRoninV Offline
                                    VRonin
                                    wrote on last edited by
                                    #17

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

                                    "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
                                    ~Napoleon Bonaparte

                                    On a crusade to banish setIndexWidget() from the holy land of Qt

                                    1 Reply Last reply
                                    2

                                    • Login

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