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"


  • Qt Champions 2018

    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


  • Qt Champions 2018

    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


  • Qt Champions 2018

    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


  • Qt Champions 2018

    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.