SQLITE selection with date not working on my QT project even following SQLite documentation..



  • Hello,

    Using qt and js, I'm trying to make query like:

    'SELECT  rowid, registration, date_trip, time_used,  total_time from trip_log where date_trip < ("2017-10-04") order by date_trip asc limit 1'
    
    

    but it's not working...

    I did the same with registration and it's working, what is wrong with date?... I try to insert date_trip in TEXT, INT or REAL and no effect...

    Thank you very much for you help



  • Maybe the trip was not so nice.. :)

    I normally use a timestamp field (with date and time) and the queries are working perfectly.



  • @mrdebug what do you mean with timestamp field? because my understanding of timestamp is automatic date time column for each entry but it's not what I need



  • for example
    "2017-12-15 21:11:35"



  • I see no C++/QML code here. could you please post what you are calling?



  • @mrdebug oh ok, but I have not time recorded, only date



  • @VRonin Hello, yes, here the js

    function potentieldated(Pdateinstallation)
        {
    
    
            var db = dbGetHandle()
            db.transaction(function (tx) {
                var results = tx.executeSql(
                            'SELECT  rowid, immatriculation, date_etape, pilot1,  pilot2,  pilot3,  photo1,  photo2,  photo3,  aeroport_depart,  aeroport_arrivee,  heure_depart, heure_arrivee,  temps_bloc_etape,  cycle_etape, aircrafttype,numerodeserie,dateconstruction,numerocertificatimmat,numerocertificatLSA,dateLSA,numeroCDN,dateCDN,datecertificatassurance,datepesee,marquemoteur,modelmoteur,marquehelice,modelhelice,serialmoteur1,serialmoteur2,serialhelice1,serialhelice2,heurecellule,cyclecellule,heure_SN_moteur_1,heure_SO_moteur_1,heure_SH_moteur_1,cycle_SN_moteur_1,cycle_SO_moteur_1,cycle_SH_moteur_1,heure_SN_moteur_2,heure_SO_moteur_2,heure_SH_moteur_2,cycle_SN_moteur_2,cycle_SO_moteur_2,cycle_SH_moteur_2,heure_SO_helice_1,cycle_SO_helice_1,heure_SO_helice_2,cycle_SO_helice_2 from flight_log where date_etape < ? order by date_etape desc limit 1' , [Pdateinstallation])
                for (var i = 0; i < results.rows.length; i++) {
                    listModel.append({
                                         id: results.rows.item(i).rowid,
                                         immatriculation: results.rows.item(i).immatriculation,
                                         date_etape:results.rows.item(i).date_etape,
                                         pilot1:results.rows.item(i).pilot1,
                                         pilot2:results.rows.item(i).pilot2,
                                         pilot3:results.rows.item(i).pilot3,
                                         photo1:results.rows.item(i).photo1,
                                         photo2:results.rows.item(i).photo2,
                                         photo3:results.rows.item(i).photo3,
                                         aeroport_depart:results.rows.item(i).aeroport_depart,
                                         aeroport_arrivee:results.rows.item(i).aeroport_arrivee,
                                         heure_depart:results.rows.item(i).heure_depart,
                                         heure_arrivee:results.rows.item(i).heure_arrivee,
                                         temps_bloc_etape:results.rows.item(i).temps_bloc_etape,
                                         cycle_etape:results.rows.item(i).cycle_etape,
                                         aircrafttype: results.rows.item(i).aircrafttype,
                                         numerodeserie: results.rows.item(i).numerodeserie,
                                         dateconstruction: results.rows.item(i).dateconstruction,
                                         numerocertificatimmat: results.rows.item(i).numerocertificatimmat,
                                         numerocertificatLSA: results.rows.item(i).numerocertificatLSA,
                                         dateLSA: results.rows.item(i).dateLSA,
                                         numeroCDN: results.rows.item(i).numeroCDN,
                                         dateCDN: results.rows.item(i).dateCDN,
                                         datecertificatassurance: results.rows.item(i).datecertificatassurance,
                                         datepesee: results.rows.item(i).datepesee,
                                         marquemoteur: results.rows.item(i).marquemoteur,
                                         modelmoteur: results.rows.item(i).modelmoteur,
                                         marquehelice: results.rows.item(i).marquehelice,
                                         modelhelice: results.rows.item(i).modelhelice,
                                         serialmoteur1: results.rows.item(i).serialmoteur1,
                                         serialmoteur2: results.rows.item(i).serialmoteur2,
                                         serialhelice1: results.rows.item(i).serialhelice1,
                                         serialhelice2: results.rows.item(i).serialhelice2,
                                         heurecellule: results.rows.item(i).heurecellule,
                                         cyclecellule: results.rows.item(i).cyclecellule,
                                         heure_SN_moteur_1: results.rows.item(i).heure_SN_moteur_1,
                                         heure_SO_moteur_1: results.rows.item(i).heure_SO_moteur_1,
                                         heure_SH_moteur_1: results.rows.item(i).heure_SH_moteur_1,
                                         cycle_SN_moteur_1: results.rows.item(i).cycle_SN_moteur_1,
                                         cycle_SO_moteur_1: results.rows.item(i).cycle_SO_moteur_1,
                                         cycle_SH_moteur_1: results.rows.item(i).cycle_SH_moteur_1,
                                         heure_SN_moteur_2: results.rows.item(i).heure_SN_moteur_2,
                                         heure_SO_moteur_2: results.rows.item(i).heure_SO_moteur_2,
                                         heure_SH_moteur_2: results.rows.item(i).heure_SH_moteur_2,
                                         cycle_SN_moteur_2: results.rows.item(i).cycle_SN_moteur_2,
                                         cycle_SO_moteur_2: results.rows.item(i).cycle_SO_moteur_2,
                                         cycle_SH_moteur_2: results.rows.item(i).cycle_SH_moteur_2,
                                         heure_SO_helice_1: results.rows.item(i).heure_SO_helice_1,
                                         cycle_SO_helice_1:results.rows.item(i).cycle_SO_helice_1,
                                         heure_SO_helice_2: results.rows.item(i).heure_SO_helice_2,
                                         cycle_SO_helice_2:results.rows.item(i).cycle_SO_helice_2
    
                                     })
                }
            })
        }
    

    here the qml

    //Item {
    
            width: parent.width
            height: parent.height
    
    
            ColumnLayout {
                width: parent.width
                height: parent.height
    
                ListView {
                    id: listView
                    //Layout.fillWidth: true
                    model: ListModel {
                        id: listModel
                        Component.onCompleted:
                        {
                            JS.potentieldated(2018-01-02)
                        }
                    }
                    delegate: Item {
                        width: parent.width
                        height: parent.height
    
    
                        GridLayout {
                            id: gridLayout
                            rows: 19
                            flow: GridLayout.TopToBottom
                            //anchors.fill: parent
                            columnSpacing: Screen.width / 30
    
                            Text {
                                text: "Immatriculation"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Type"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Numero de serie"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Date de construction"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Numéro du certificat d'immatriculation"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Numero de la LSA"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                id:datelsa
                                text: "Date de validité de la LSA"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Numero du certificat de navigabilité"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Date de validité du CDN"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Date de validité du certificat d'assurance"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Date de validité de la pesée"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Marque Moteur"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Model Moteur"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Marque Helice"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Model Helice"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "SN Moteur 1"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "SN Moteur 2"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
    
                            Text {
                                text: "SN Helice 1"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "SN Helice 2"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
    
                            Text {
                                id: rimmatriculation
                                text:immatriculation
                                font.pixelSize: 22}
                            Text {
                                id: raircrafttype
                                text:aircrafttype
                                font.pixelSize: 22
                            }
                            Text {
                                id: rnumerodeserie
                                text:numerodeserie
                                font.pixelSize: 22
                            }
                            Text {
                                id: rdateconstruction
                                text:dateconstruction
                                font.pixelSize: 22
                            }
                            Text {
                                id: rnumerocertificatimmat
                                text:numerocertificatimmat
                                font.pixelSize: 22
                            }
                            Text {
                                id: rnumerocertificatLSA
                                text: numerocertificatLSA
                                font.pixelSize: 22
                            }
                            Text {
                                id: rdateLSA
                                text:dateLSA
                                font.pixelSize: 22
                            }
                            Text {
                                id: rnumeroCDN
                                text:numeroCDN
                                font.pixelSize: 22
                            }
                            Text {
                                id: rdateCDN
                                text:dateCDN
                                font.pixelSize: 22
                            }
                            Text {
                                id: rdatecertificatassurance
                                text:datecertificatassurance
                                font.pixelSize: 22
                            }
                            Text {
                                id: rdatepesee
                                text:datepesee
                                font.pixelSize: 22
                            }
                            Text {
                                id: rmarquemoteur
                                text:marquemoteur
                                font.pixelSize: 22
                            }
                            Text {
                                id: rmodelmoteur
                                text:modelmoteur
                                font.pixelSize: 22
                            }
                            Text {
                                id: rmarquehelice
                                text:marquehelice
                                font.pixelSize: 22
                            }
                            Text {
                                id: rmodelhelice
                                text:modelhelice
                                font.pixelSize: 22
                            }
                            Text {
                                id: rserialmoteur1
                                text:serialmoteur1
                                font.pixelSize: 22
                            }
                            Text {
                                id: rserialmoteur2
                                text:serialmoteur2
                                font.pixelSize: 22
                            }
                            Text {
                                id: rserialhelice1
                                text:serialhelice1
                                font.pixelSize: 22
                            }
                            Text {
                                id: rserialhelice2
                                text:serialhelice2
                                font.pixelSize: 22
                            }
    
                            Text {
                                text: "Heure Cellule"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Cycle Cellule"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Heure Moteur 1 SNEW"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Heure Moteur 1 SOVL"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Heure Moteur 1 SHSI"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Cycle Moteur 1 SNEW"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Cycle Moteur 1 SOVL"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Cycle Moteur 1 SHSI"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Heure Moteur 2 SNEW"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Heure Moteur 2 SOVL"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Heure Moteur 2 SHSI"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Cycle Moteur 2 SNEW"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Cycle Moteur 2 SOVL"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Cycle Moteur 2 SHSI"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Heure Helice 1 SOVL"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Cycle Helice 1 SOVL"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Heure Helice 2 SOVL"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: "Cycle Helice 2 SOVL"
                                font.pixelSize: 22
                                rightPadding: 10
                            }
                            Text {
                                text: ""
                                font.pixelSize: 22
                                rightPadding: 10
                            }
    
    
                            Text {
                                id: rheurecellule
                                text:heurecellule
                                font.pixelSize: 22
                            }
                            Text {
                                id: rcyclecellule
                                text: cyclecellule
                                font.pixelSize: 22
                            }
    
                            Text {
                                id: rheure_SN_moteur_1
                                text:heure_SN_moteur_1
                                font.pixelSize: 22
                            }
                            Text {
                                id: rheure_SO_moteur_1
                                text:heure_SO_moteur_1
                                font.pixelSize: 22
                            }
                            Text {
                                id: rheure_SH_moteur_1
                                text:heure_SH_moteur_1
                                font.pixelSize: 22
                            }
                            Text {
                                id: rcycle_SN_moteur_1
                                text:cycle_SN_moteur_1
                                font.pixelSize: 22
                            }
                            Text {
                                id: rcycle_SO_moteur_1
                                text:cycle_SO_moteur_1
                                font.pixelSize: 22
                            }
                            Text {
                                id: rcycle_SH_moteur_1
                                text:cycle_SH_moteur_1
                                font.pixelSize: 22
                            }
                            Text {
                                id: rheure_SN_moteur_2
                                text:heure_SN_moteur_2
                                font.pixelSize: 22
                            }
                            Text {
                                id: rheure_SO_moteur_2
                                text:heure_SO_moteur_2
                                font.pixelSize: 22
    
                            }
                            Text {
                                id: rheure_SH_moteur_2
                                text:heure_SH_moteur_2
                                font.pixelSize: 22
                            }
                            Text {
                                id: rcycle_SN_moteur_2
                                text:cycle_SN_moteur_2
                                font.pixelSize: 22
                            }
                            Text {
                                id: rcycle_SO_moteur_2
                                text:cycle_SO_moteur_2
                                font.pixelSize: 22
                            }
                            Text {
                                id: rcycle_SH_moteur_2
                                text:cycle_SH_moteur_2
                                font.pixelSize: 22
                            }
                            Text {
                                id: rheure_SO_helice_1
                                text:heure_SO_helice_1
                                font.pixelSize: 22
                            }
                            Text {
                                id: rcycle_SO_helice_1
                                text:cycle_SO_helice_1
                                font.pixelSize: 22
                            }
                            Text {
                                id: rheure_SO_helice_2
                                text:heure_SO_helice_2
                                font.pixelSize: 22
                            }
                            Text {
                                id: rcycle_SO_helice_2
                                text:cycle_SO_helice_2
                                font.pixelSize: 22
                            }
    
                        }
                    }
                }
            }
        }
        Component.onCompleted:
        {
            JS.dbInitflightlog()
        }
    }
    
    
    

    What I need at the end is only the row corresponding on the desired date or the nearest when no record on the desired date



    1. why are you not using SqlQueryModel?
    2. what is [Pdateinstallation]?


  • @VRonin I'm using this method because it's the first time I'm using SQLite with QT and I was the method used on example http://doc.qt.io/qt-5/qtquick-localstorage-example.html

    [Pdateinstallation] is an arbitrary name used to be able to use many time the function on qml form with different date



  • Sorry, I missed the first line of your code.
    did you try changing JS.potentieldated(2018-01-02) to JS.potentieldated('2018-01-02')



  • @VRonin yes I did, no effect



  • @VRonin oh no my mistake, I did try with ("2018-01-02") not ('2018-01-02'), it's working with JS.potentieldated('2018-01-02')



  • thank you very much!!



  • @VRonin just in case, with my method, do you know how can I update all value of one column when I delete one row?

    the example is I'm doing heurecellule = tempsbloc + previous heurecellule when I insert new row.

    But if I have to delete a row, I would like to make heurecellule - tempsbloc on all upper row from the deleted row



  • just connect a slot to rowRemoved signal of the model.

    You are doing a lot of logic on the QML side and it's really not advisable. You should consider moving the model functionality to c++



  • @VRonin thank you but I know nothing in c++ ;-) and qml language talk to me, c++ not at all


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.