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
-
- why are you not using SqlQueryModel?
- 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 changingJS.potentieldated(2018-01-02)toJS.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
rowRemovedsignal 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
