new issue with sqlite archive event fucntion copies data from one db to another

[jfinn88]

I'm having an issue with a function that copies data from one database to another then deletes form the original database

for some reason I'm having issue with SELECT * FROM userLogEvents it seems like

I try to access a query value() and get error

I need to check the dates from the original database (userLog.db table: userLogEvents) and compare them to with the current date minus 30 days

Since sqlite doesn't have a dat/time data type I set my dateTime column to TEXT
Also I don’t use the suggested European style time format when inserting into database I want it to be MM-DD-YYYY HH:MM:ss but not sure if this will be allowed cause I need to compare my dates later for archiving but will use c++ if() statement to check lessthan....

sql database code


CREATE TABLE userlogevents(id INTEGER PRIMARY KEY AUTOINCREMENT, userName TEXT NOT NULL, eventMessage TEXT NOT NULL, dateTime TEXT NOT NULL);


I went back and added a time aspect to my dates and now having issues

I don't want the delete script to run unless the copy/insert was executed successfully (not just pushing blank data) so I created an if() loop above my while() loop.

I'm not sure where I went wrong but need little help cleaning up this function so it runs properly

c++ code


bool UserEventLog::archiveEvent(){
//---connect to DB---//
dbConnect();

//---shows loading symbol QMl side---//
emit showBusy(true);

//---attach database to existing connection---//
QSqlQuery attachDbQry(m_selectDataBase);
QString sql_str = "ATTACH DATABASE '" + Paths::root() + "/userLogArchive.db' as db2";
attachDbQry.prepare(sql_str);

qDebug() << "UserEventLog::archiveEvent() attached database path: "+sql_str;

//---execute attach DB---//
if(attachDbQry.exec()){
qDebug()<<"UserEventLog::archiveEvent() attached database succesfully";
}
else{
emit xmui->alertMsg(QMessageBox::Warning, "Database Error Message 2", "Error searching database..."+attachDbQry.lastError().text());
return attachDbQry.exec();
}

//---start DB transaction---//
//m_selectDataBase.transaction();

//---get date values of MsgLogEvents--//
QSqlQuery dateQry(m_selectDataBase);
dateQry.prepare("SELECT * FROM userlogevents");

if(dateQry.exec()) {
qDebug() << "UserEventLog::archiveEvent() dateQry: " << dateQry.value(3).toString();
}
else {
qDebug() << "UserEventLog::archiveEvent()" << dateQry.lastError().text();
}

//---get currentDate minus 30days---//
QString dateStr;
QString archiveDateStr = QDateTime::currentDateTime().addDays(-30).toString("MM-dd-yyyy HH:MM:ss");
QDateTime archiveDate = QDateTime::fromString(archiveDateStr, "MM-dd-yyyy HH:MM:ss");

qDebug() << "UserEventLog::archiveEvent() archiveDate: " << archiveDate.toString();

//---copy from one Db table to another---//
QSqlQuery copyDataQry(m_selectDataBase);
bool prepareSqlBool;
prepareSqlBool = copyDataQry.prepare("INSERT INTO db2.userlogarchive (userName, eventMessage, dateTime) SELECT userName, eventMessage, dateTime FROM main.userlogevents WHERE dateTime < ?");
copyDataQry.addBindValue(archiveDate);

while(copyDataQry.next())
{
qDebug() << "UserEventLog::archiveEevnt() copDataQry next value: " << copyDataQry.value(3).toString();
}

//---check for value---//
qDebug() << "UserEventLog::archiveEvent() archive check: " << copyDataQry.value(3).toBool();

//---prepare sql copy---//
if(prepareSqlBool){
qDebug()<<"UserEventLog::archiveEvent() prepare copy sql statement exicuted fine";
}
else{
emit xmui->alertMsg(QMessageBox::Warning, "Database Error Message 3", "Error: prepare script..."+copyDataQry.lastError().text());
return false;
}

//---Execute copDataQry----//
copyDataQry.exec();

//---Check copySql Bool value---//
bool copySqlBool;
copySqlBool = copyDataQry.value(3).toBool();
qDebug() << "UserEventLog::archiveEvent() copySqlBool: " << copySqlBool;

QSqlQuery archiveDataQry(m_selectDataBase);
QDateTime userEventMsgDate;

//---Only enter while loop if copySql executes---//
if(copySqlBool){
qDebug()<<"UserEventLog::archiveEvent() insert copy sql statement exicuted fine";

//---Copy rows to archive then delete old rows---//
while(dateQry.next()){
//---Get date value from dateQry---//
dateStr = dateQry.value(3).toString();
userEventMsgDate = QDateTime::fromString(dateStr, "MM-dd-yyyy HH:MM:ss");
qDebug() << "UserEventLog::archiveEvent() msgDate & archiveDate: " << userEventMsgDate.toString("MM-dd-yyyy HH:MM:ss") << " < " << archiveDate.toString("MM-dd-yyyy HH:MM:ss");

//---Executes only if copy qry executes---//
if(userEventMsgDate < archiveDate){
//---Sql delete statement---//
archiveDataQry.prepare("DELETE FROM userlogevents WHERE dateTime < ?");
archiveDataQry.addBindValue(archiveDate);

//---execute delete---//
if(archiveDataQry.exec()){
qDebug()<<"UserEventLog::archiveEvent() delete sql statement exicuted fine";
}
else{
emit xmui->alertMsg(QMessageBox::Warning, "Database Error Message 4", "Error: deleting old data..."+archiveDataQry.lastError().text());
m_selectDataBase.rollback();
return archiveDataQry.exec();
}
}
else{
qDebug() << "UserEventLog::archiveEvent() no old records to delete msg 1";
//m_selectDataBase.rollback();
return false;
}
}
}
else{
//emit xmui->alertMsg(QMessageBox::Warning, "Database Error Message 5", "Error: copy/insert archive data script..."+copyDataQry.lastError().text());
qDebug() << "UserEventLog::archiveEvent() no old records to delete msg 2";
//m_selectDataBase.rollback();
return copySqlBool;
}

//---commit transaction & close---//
//m_selectDataBase.commit();
m_selectDataBase.close();
emit showBusy(false);
return copySqlBool;
}


out put from function



"database connect path: /home/amet/git/xm-controller/userLog.db"
connected to DB
"UserEventLog::archiveEvent() attached database path: ATTACH DATABASE '/home/amet/git/xm-controller/userLogArchive.db' as db2"
UserEventLog::archiveEvent() attached database succesfully
QSqlQuery::value: not positioned on a valid record
UserEventLog::archiveEvent() dateQry: ""
UserEventLog::archiveEvent() archiveDate: "Tue Sep 6 15:00:44 2016"
QSqlQuery::value: not positioned on a valid record
UserEventLog::archiveEvent() archive check: false
UserEventLog::archiveEvent() prepare copy sql statement exicuted fine
QSqlQuery::value: not positioned on a valid record
UserEventLog::archiveEvent() copySqlBool: false
UserEventLog::archiveEvent() no old records to delete msg 2


just in case this is how I insert into orginal database


//---------Inserts data into database (XMUI Global Funciton)--------------//
bool XMUI::insertLogMessage(QString userName, QString msg){
QString dateStr = QDateTime::currentDateTime().toString("MM-dd-yyyy HH:MM:ss");
qDebug() << "insert date: " << dateStr;

//---checks is database is connected---//
if(!m_insertDataBase.isValid()){
qDebug() <<"error in opening DB";
m_insertDataBase = QSqlDatabase::addDatabase("QSQLITE", "conn1");
m_insertDataBase.setDatabaseName(Paths::root() + "/userLog.db");

}
if(!m_insertDataBase.open()){
qDebug()<< "database cannot be opened";
}

QSqlQuery m_insertQuery(m_insertDataBase);
m_insertQuery.prepare("INSERT INTO userlogevents (userName, eventMessage, dateTime) VALUES(:userName, :eventMessage, :dateTime)");
m_insertQuery.bindValue(":userName", userName);
m_insertQuery.bindValue(":eventMessage", msg);
m_insertQuery.bindValue(":dateTime", dateStr);
if(m_insertQuery.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << m_insertQuery.lastError();
}
m_insertDataBase.close();
return m_insertQuery.exec();
}

[jefftee]

Please see this (http://www.qtcentre.org/threads/67000-issue-with-sqlite-select-qry-by-date-amp-time-for-24-hour-period?p=294360#post294360http://) reply to your other post on the same subject and avoid starting new threads when you already have active threads on the same topic.

I have no idea why you want to make this so hard either. In a prior post, I showed you how to attach a database to an existing SQLITE connection (your requirement was that the archive is in a separate database file) and then perform the archive by simply doing something like:



attach database '/path/to/archive/db.sqlite' as arch;
insert into arch.userlogevent select * from main.userlogevent where dateTime < datetime('now','localtime','-30 days');
delete from main.userlogevent where dateTime < datetime('now','localtime','-30 days');


Needless to say, you should check success/failure of each SQL statement above before executing the next, i.e. you don't want to delete the records from the primary table if there were not inserted into the archive successfully, etc.

All done in SQL, no loops required, no QDateTime comparisons needed at all.

[jfinn88]

I apologize for the posting same issue twice when I started the second post I thought it would be a separate issue however it wasn't

Jeff just want to say thank you for your multiple post and you previous help with attaching and archiving database (using same connection established) I realize it can be frustrating working with newbie. For some reason I tend to complicate things as I'm new an unsure of some of the steps to accomplish what I'm looking for. I had some issues understanding some of the sqlite stuff but thanks to your posts and SQLite-tutorialspoint site helped me clear up some of my issues.

one issue I was having is I was unsure how sqlite was comparing the dates an overlooked the fact the the between call in the sql script and the dates passed is a comparison with the dates in the DB. Another issues I was having was how the formatting for dates worked in sqlite. I was thinking I was only comparing dates in c++ but really was comparing dates in sqlite scripts as well. I wasn’t sure how to format the date to display in my listView from my model they way I needed however I figure that out.

Here is my updated code for the archive function I check for success/failure ( it seems my copyQry.exec() returns true even if it doesn't copy anything over to the other database so work around I check number of rows affect by copyQry then exec delete statement)



bool UserEventLog::archiveEvent(){
dbConnect();
emit showBusy(true);

QSqlQuery attachDbQry(m_selectDataBase);
QString sql_str = "ATTACH DATABASE '" + Paths::root() + "/userLogArchive.db' as db2";
bool attachDbBool;
bool copyDbBool;
bool deleteDbBool;

//---Prepare attach DB---//
if(attachDbQry.prepare(sql_str)) {
attachDbBool = attachDbQry.exec();
}
else {
qDebug() << "UserEventLog::archiveEvent() Error with prepare statment attaching database";
}

//---execute attach DB---//
if(attachDbBool){
qDebug()<<"UserEventLog::archiveEvent() attached database succesfully";
}
else{
qDebug() << "UserEventLog::archiveEvent() Error with executing attached database";
return attachDbBool;
}

//---start DB transaction---//
m_selectDataBase.transaction();

//---Archive old data--//
QSqlQuery deleteQry(m_selectDataBase);
QSqlQuery copyQry(m_selectDataBase);
copyDbBool = copyQry.prepare("INSERT INTO db2.userlogarchive (userName, eventMessage, dateTime) SELECT userName, eventMessage, dateTime FROM main.userlogevents WHERE dateTime < datetime('now', 'localtime', '-30 days')");

//---If copyQry prepare returns true---//
if(copyDbBool){
copyDbBool = copyQry.exec();
qDebug() << "UserEventLog::archiveEvent() CopyQry ran" << " " << copyQry.lastError().text();

//---If the number of rows affected by copyAry is greater than 0---///
if(copyQry.numRowsAffected() > 0) {
deleteDbBool = deleteQry.prepare("DELETE FROM main.userlogevents WHERE dateTime < datetime('now','localtime','-30 days')");
qDebug() << "UserEventLog::archiveEvent() deleteQry.prepare() ran" << " " << deleteQry.lastError().text();

//---If deleteQry prepare returns true---//
if(deleteDbBool){
//---execute deleteQry---//
deleteDbBool = deleteQry.exec();
qDebug () << "UserEventLog::archiveEvent() deleteQry.exec() ran" << " " << deleteQry.lastError();
}
else {
qDebug() << "UserEventLog::archiveEvent() Error with prepare delete statment" << " " << deleteQry.lastError().text();
m_selectDataBase.rollback();
return deleteDbBool;
}
}
else {
qDebug() << "UserEventLog::archiveEvent() CopyQry didn't affect any rows" << " " << copyQry.lastError().text();
m_selectDataBase.rollback();
return false;
}
}
else {
qDebug() << "UserEventLog::archiveEvent() CopyQry didn't run" << " " << copyQry.lastError().text();
m_selectDataBase.rollback();
return copyDbBool;
}

//---commit transaction & close---//
m_selectDataBase.commit();
m_selectDataBase.close();
emit showBusy(false);
return copyDbBool;
}


for selecting data for one day with format = dateTime("yyy-MM-dd HH:MM:ss")

updated code!


bool UserEventLog::selectEvent(){
dbConnect();
//QString beginDate = QDateTime::currentDateTime().toLocalTime().toStrin g("yyyy-MM-dd 00:00:00");
//QString endDate = QDateTime::currentDateTime().toLocalTime().toStrin g("yyyy-MM-dd 23:59:59");

//qDebug() << "UserEventLog::selectEvent() beginDate: " << beginDate;
//qDebug() << "UserEventLog::selectEvent() endDate: " << endDate;

emit showBusy(true);

QSqlQuery selectQuery("SELECT * FROM userlogevents WHERE dateTime BETWEEN date('now','localtime')||' 00:00:00.000' and date('now','localtime')|| '23:59:59.999'", m_selectDataBase);
if(selectQuery.exec()){
//selectQuery.addBindValue(beginDate);
//selectQuery.addBindValue(endDate);
if(selectQuery.exec()){
qDebug()<<"UserEventLog::selectEvent() sql statement executed fine";
}
else{
emit xmui->alertMsg(QMessageBox::Warning, "Database Error Message 1", "Error: sql select script..."+selectQuery.lastError().text());
qDebug()<<"UserEventLog::selectEvent() Error with sql statement execution";
return selectQuery.exec();
}

userEventLogMsg msg;
beginResetModel();
m_userEventList.clear();
while (selectQuery.next()){
msg.id = selectQuery.value(0).toString();
msg.username = selectQuery.value(1).toString();
msg.eventmessage = selectQuery.value(2).toString();
msg.datetime = selectQuery.value(3).toDateTime();
addEvent(msg);
}
endResetModel();
emit showBusy(false);
m_selectDataBase.close();
return selectQuery.exec();
}


code for inserting data in to database (record user events)


bool XMUI::insertLogMessage(QString userName, QString msg){

//---checks if database is connected---//
if(!m_insertDataBase.isValid()){
qDebug() <<"error in coneecting DB";
m_insertDataBase = QSqlDatabase::addDatabase("QSQLITE", "conn1");
m_insertDataBase.setDatabaseName(Paths::root() + "/userLog.db");
m_insertDataBase.open();
}

//---Check if database is open---//
if(!m_insertDataBase.open()){
qDebug()<< "XMUI::insertLogMessage() database is either locked or cannot be opened";
//insertLogMessage(userName, msg);
//return m_insertDataBase.open();
}

//---Prepare sqlite query---//
QSqlQuery m_insertQuery(m_insertDataBase);
m_insertQuery.prepare("INSERT INTO userlogevents (userName, eventMessage, dateTime) VALUES(:userName, :eventMessage, dateTime('now', 'localtime'))");
m_insertQuery.bindValue(":userName", userName);
m_insertQuery.bindValue(":eventMessage", msg);

//---Verify query executes---//
if(m_insertQuery.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "XMUI::insertLogMessage() Errors accured with sql statement";
qDebug() << m_insertQuery.lastError();
}

//---close database conenction and exit fucniton---//
m_insertDataBase.close();
return m_insertQuery.exec();
}


C++ model data() function (format date for display in model ("MM-dd-yyyy HH:MM:ss")


QVariant UserEventLog::data(const QModelIndex &index, int role) const{
if (index.row() < 0 || index.row() >= m_userEventList.count()){
return QVariant();
}

QVariant text;

if(role == idRole){
userEventLogMsg msg = m_userEventList.at(index.row());
text = msg.id;
}
else if(role == nameRole){
userEventLogMsg msg = m_userEventList.at(index.row());
text = msg.username;
}
else if(role == msgRole){
userEventLogMsg msg = m_userEventList.at(index.row());
text = msg.eventmessage;
}
else if(role == dateRole){
userEventLogMsg msg = m_userEventList.at(index.row());
//---format date---//
text = msg.datetime.toLocalTime().toString("MM-dd-yyyy HH:MM:ss" );
}
return text;
}

[jefftee]

Glad you got it all working!

If your userlogevents table is large, you will see a significant improvement in performance if you create an index on the datetime field. Without the index, SQLITE will have to perform a full table scan (read all records) to see if the datetime matches your between values.

General advise is to use indexes for "WHERE" conditions in your SQL statements. To create an index on that field, you'd want to do:



create index if not exists ix_userlogevents_datetime on userlogevents ( datetime asc );

You can do the same for your archive database, etc.

One more thing you may want to consider, you are getting the current date/time twice in lines 3-4 in your selectEvent method. It's possible, but unlikely, that you'd get a different date if those two calls were done just before and just after midnight. The way I would write that is:



QDate now = QDate::currentDate();
QString beginDate = now.toString("yyyy-MM-dd 00:00:00");
QString endDate = now.toString("yyyy-MM-dd 23:59:59");

This will ensure that the begin date and end date are the same.

[jfinn88]

having issue searching records in Database after incorporating time component into database...

QML search condition


//---Action for Search Button---//
Action {
id: action_search
enabled:!inSequence
onTriggered:{
/* Condition statement to search by:
* userName,
* singleDate,
* dateRange using textFields,
* userName & signleDate,
* userName & dateRange using TextFieldStyle
*/

//---Hides Calendars on Search---//
calendarRect1.visible = false
calendarRect2.visible = false
searchBtn.focus = false;
endDateTextField.focus = false;

//---Search by userName---/
if(beginDateTextField.text === "" && endDateTextField === "" && userNameDropDown.currentText !== ""){
UserEventLog.searchUserName(userNameDropDown.curre ntText);
console.log("Search by userName: ", userNameDropDown.currentText)
}

//---Search by signle date: beginDate OR endDate---//
else if(beginDateTextField.text !== "" && endDateTextField.text === "" && userNameDropDown.currentText === "" ||
beginDateTextField.text === "" && endDateTextField.text !=="" && userNameDropDown.currentText ===""){

console.log("Search by signle date beginDate: ", beginDateTextField.text.toString());
if(beginDateTextField.text !== "" && endDateTextField.text === ""){
UserEventLog.searchDate(beginDateTextField.text.to String("yyyy-MM-dd"));
}
else if(beginDateTextField.text === "" && endDateTextField.text !== ""){
UserEventLog.searchDate(endDateTextField.text.toSt ring("yyyy-MM-dd"));
}
}

//---Search by beginDate & endDate text fields---//
else if(beginDateTextField.text !== "" && endDateTextField.text !== "" && userNameDropDown.currentText === ""){
console.log("Search by beginDate & endDate text fields: ", endDateTextField.text.toString());
console.log("Search by beginDate & endDate text fields: ", beginDateTextField.text.toString());
UserEventLog.searchDateRange(beginDateTextField.te xt.toString("yyyy-MM-dd"), endDateTextField.text.toString("yyyy-MM-dd"));
}

//---Search by userName & Single date (beginDate OR endDate)---//
else if(beginDateTextField.text !== "" && endDateTextField.text ==="" && userNameDropDown.currentText !== "" ||
beginDateTextField.text == "" && endDateTextField.text !=="" && userNameDropDown.currentText !== "" ){

console.log("Search by userName & beginDate text field: ", userNameDropDown.currentText);
console.log("Search by userName & beginDate text field: ", Qt.formatDateTime(beginDateTextField.text, "yyyy-MM-dd hh:mm:ss"));


if(beginDateTextField.text !== "" && endDateTextField.text === ""){
UserEventLog.searchUserNameDateText(userNameDropDo wn.currentText, beginDateTextField.text);
//UserEventLog.searchUserNameDateText(userNameDropDo wn.currentText, Qt.formatDateTime(beginDateTextField.text, "yyyy-MM-dd hh:mm:ss"));
}
else if(beginDateTextField.text === "" && endDateTextField.text !== ""){
UserEventLog.searchUserNameDateText(userNameDropDo wn.currentText, endDateTextField.text);
//UserEventLog.searchUserNameDateText(userNameDropDo wn.currentText, Qt.formatDateTime(beginDateTextField.text, "yyyy-MM-dd hh:mm:ss"));
}
}

//---Search by userName, & beginDate, endDate text fields---//
else if(beginDateTextField.text !== "" && endDateTextField.text !== "" && userNameDropDown.currentText !== ""){
console.log("Search by userName, & beginDate, endDate text fields: ", userNameDropDown.currentText);
console.log("Search by userName, & beginDate, endDate text fields: ", beginDateTextField.text.toString());
console.log("Search by userName, & beginDate, endDate text fields: ", endDateTextField.text.toString());
UserEventLog.searchDateRange(userNameDropDown.curr entText, beginDateTextField.text, endDateTextField.text);

}

//---if search fails---//
else{
console.log("enter in date range or search by user name or both!");
}

//---Prepare listView: set focus, set index, set position---//
listView.forceActiveFocus();
listView.currentIndex = 0;
listView.positionViewAtBeginning();
}
}


c++ function that performs sql script
update: updated if condition in fcn



bool UserEventLog::searchUserNameDateText(QString userNameText, QDate dateText){
qDebug() << "UserEventLog::searchUserNameDateText() User Name: " << userNameText;
qDebug() << "UserEventLog::searchUserNameDateText() Date: " << dateText.toString();

QString begin = dateText.toString("yyyy-MM-dd 00:00:00");
QString end = dateText.toString("yyyy-MM-dd 23:59:59");

qDebug() << "UserEventLog::searchUserNameDateText() beginDate: " << begin;
qDebug() << "UserEventLog::searchUserNameDateText() endDate: " << end;

dbConnect();

emit showBusy(true);

QSqlQuery selectQuery(m_selectDataBase);
QString selectQueryString = "SELECT id, userName, eventMessage, dateTime FROM userlogevents WHERE userName = ? AND dateTime BETWEEN ? and ?";
selectQuery.addBindValue(userNameText);
selectQuery.addBindValue(begin);
selectQuery.addBindValue(end);

bool prepareSqlBool = selectQuery.prepare(selectQueryString);
bool selectSqlBool;


if(prepareSqlBool){
selectSqlBool = selectQuery.exec();
if(selectSqlBool){
qDebug() << "UserEventLog::searchUserNameDateText() searchUserNameDateText query executed fine!";
}
else{
return selectSqlBool;
}
}
else{
emit xmui->alertMsg(QMessageBox::Warning, "Database Error Message 9", "Error: searching database..."+selectQuery.lastError().text());
qDebug() << "UserEventLog::searchUserNameDateText() searchUserNameDateText query dind't execute";
return prepareSqlBool;
}

userEventLogMsg msg;
beginResetModel();
m_userEventList.clear();

while (selectQuery.next()){
msg.id = selectQuery.value(0).toString();
msg.username = selectQuery.value(1).toString();
msg.eventmessage = selectQuery.value(2).toString();
msg.datetime = selectQuery.value(3).toDateTime();
addEvent(msg);
}
endResetModel();
m_selectDataBase.close();
emit showBusy(false);
return selectSqlBool;
}


update: query used to push data into database


m_insertQuery.prepare("INSERT INTO userlogevents (userName, eventMessage, dateTime) VALUES(:userName, :eventMessage, dateTime('now', 'localtime'))");


before I added in time component in to the dateTime I didn't seem to have any issue passing the date entered in the textField to the function call


UserEventLog.searchUserNameDateText(userNameDropDo wn.currentText, beginDateTextField.text);

I have tried passing it different ways e.g.


beginDateTextField.text;
beginDateTextField.text.toString();
beginDateTextField.text.toString("yyyy-MM-dd");
beginDateTextField.text.toString("yyyy-MM-dd hh:mm:ss);


I tried creating a local var and try passing that as a Date type e.g.


var myVar = new Date();
myVar.setDate(beginDateTextField.text, "yyyy-MM-dd");

var mydate = new Date();
mydate.setDate(Qt.formatDateTime(beginDateTextFiel d.text, "yyyy-MM-dd hh:mm:ss"));

var locale = Qt.locale();
var mydate = Date.fromLocaleString(locale, beginDateTextField.text, "yyyy-MM-dd hh:mm:ss");

UserEventLog.searchUserNameDateText(userNameDropDo wn.currentText, mydate);

I have tried messing with the formating


UserEventLog.searchUserNameDateText(userNameDropDo wn.currentText, Qt.formatDateTime(beginDateTextField.text, "yyyy-MM-dd hh:mm:ss"));


I'm not quite sure what I'm doing wrong, I figured I could just pass the text the user entered into the date text field as I did before beginDateTextField.text and on the C++ side convert the dates to string to re-format them and add in generic time stamps however when I debug the date passed to the function nothing out puts.... I'm not sure if passing a text(string) from the qml side is right but that’s how I did it before and it worked... I tried changing the functions parameters data types from QDate to QString but that didn't work either (its just a plain date being passed as a string from QML it worked before not sure why its not now)



QString begin = dateText.toString("yyyy-MM-dd 00:00:00");
QString end = dateText.toString("yyyy-MM-dd 23:59:59");


I was reading about qml Date type and found this in the doc:
When integrating with C++, note that any QDate value passed into QML from C++ is automatically converted into a date value, and vice-versa.

Not sure Why I wasn’t having a problem before the time aspect got in cooperated and not sure what the cause of my issue is now, if you can help that be great

[jfinn88]

I tried changing the fcn parameter to a QString and convert that string into a date type:


bool UserEventLog::searchUserNameDateText(QString userNameText, QString dateText){
qDebug() << "UserEventLog::searchUserNameDateText() User Name: " << userNameText;
qDebug() << "UserEventLog::searchUserNameDateText() Date: " << dateText;

QDateTime begin = QDateTime::fromString(dateText, "yyyy-MM-dd 00:00:00");
QDateTime end = QDateTime::fromString(dateText, "yyyy-MM-dd 23:59:59");

qDebug() << "UserEventLog::searchUserNameDateText() beginDate: " << begin.toString();
qDebug() << "UserEventLog::searchUserNameDateText() endDate: " << end.toString();

dbConnect();

emit showBusy(true);

QSqlQuery selectQuery(m_selectDataBase);
QString selectQueryString = "SELECT id, userName, eventMessage, dateTime FROM userlogevents WHERE userName = ? AND dateTime BETWEEN ? AND ?";

bool prepareSqlBool = selectQuery.prepare(selectQueryString);
bool selectSqlBool;

selectQuery.addBindValue(userNameText);
selectQuery.addBindValue(begin);
selectQuery.addBindValue(end);

if(prepareSqlBool){
selectSqlBool = selectQuery.exec();
if(selectSqlBool){
qDebug() << "UserEventLog::searchUserNameDateText() searchUserNameDateText query executed fine!";
}
else{
qDebug() << "UserEventLog::searchUserNameDateText() searchUserNameDateText query did not execute!" << selectQuery.lastError();
return selectSqlBool;
}
}
else{
emit xmui->alertMsg(QMessageBox::Warning, "Database Error Message 9", "Error: searching database..."+selectQuery.lastError().text());
qDebug() << "UserEventLog::searchUserNameDateText() searchUserNameDateText query dind't execute";
return prepareSqlBool;
}

[jfinn88]

solution:

change c++ fcn parameters for dates to QStrings being that’s what’s getting passes from QML text field
take that string date convert it to a QDateTime obj then I set the time of that obj
I then switch the date(s) passed back to strings to reformat for searching the database
I think my main issue was fromString() function call I thought I was setting the formatting in this function call but I was in correct I need to covert back to string and use toString() to get it in the format I need. the fromString() function needs the format its all ready in I fixed these issue and it seems to display the searched data in the model now...

here is the update part of my function:


//---Search userEventLog databse by: user name & single date---//
bool UserEventLog::searchUserNameDateText(QString userNameText, QString dateText){

QDateTime beginDate, endDate;
beginDate = QDateTime::fromString(dateText, "MM-dd-yyyy");
endDate = QDateTime::fromString(dateText, "MM-dd-yyyy");

beginDate.setTime(QTime(0,0,0));
endDate.setTime(QTime(23,59,59));

qDebug() << "UserEventLog::searchUserNameDateText() User Name: " << userNameText;
qDebug() << "UserEventLog::searchUserNameDateText() Date: " << dateText;

QDateTime beginStr = QDateTime::fromString(dateText, "yyyy-MM-dd 00:00:00");
QDateTime endStr = QDateTime::fromString(dateText, "yyyy-MM-dd 23:59:59");

qDebug() << "UserEventLog::searchUserNameDateText() beginDate: " << beginStr;
qDebug() << "UserEventLog::searchUserNameDateText() endDate: " << endStr;

[jefftee]

Please show the output you get from the qDebug() statements in UserEventLog::searchUserNameDateText as well as the data from your SQLITE database from something like:


sqlite3 your.db "select * from userlogevents limit 20"

Edit: Also, when you are debugging, are beginDate.isValid() and endDate.isValid() both true?

[jfinn88]

Here is the data being stored in my database


sqlite3 userLog.db "select * from userlogevents limit 20;"
913|root|User entered userEventDialog|2016-10-10 09:51:22
914|root|User currently logged in as: root|2016-10-10 09:52:33
915|root|User entered userEventDialog|2016-10-10 09:52:35
916|root|User entered userEventDialog|2016-10-10 09:53:59
917|root|User entered userEventDialog|2016-10-10 09:54:41
918|root|User currently logged in as: root|2016-10-10 10:02:16
919|root|User entered userEventDialog|2016-10-10 10:02:18
920|root|User currently logged in as: root|2016-10-10 10:20:02
921|root|User entered userEventDialog|2016-10-10 10:20:05
922|root|User entered userEventDialog|2016-10-10 10:21:13
923|root|User currently logged in as: root|2016-10-10 10:28:12
924|root|User entered userEventDialog|2016-10-10 10:28:14
925|root|User entered userEventDialog|2016-10-10 10:28:27
926|root|User currently logged in as: root|2016-10-10 10:38:16
927|root|User entered userEventDialog|2016-10-10 10:38:18
928|root|User currently logged in as: root|2016-10-10 10:42:30
929|root|User entered userEventDialog|2016-10-10 10:42:31
930|welder-b|User currently logged in as: welder-b|2016-10-10 10:44:32
931|Welder-B|User currently logged in as: Welder-B|2016-10-10 10:44:52
932|Welder-B|User entered userEventDialog|2016-10-10 10:44:54


Here is the output from my debug statements added in one to see the parameters passed in and another to check if the dates converted are valid


//---Search userEventLog databse by: user name & date range---//
bool UserEventLog::searchDateRange(const QString &userName, QString beginDate, QString endDate){
qDebug() << "userName, beginDate, endDate parameters:" << userName << beginDate << endDate;
QDateTime begin, end;
begin = QDateTime::fromString(beginDate, "MM-dd-yyyy");
end = QDateTime::fromString(endDate, "MM-dd-yyyy");
begin.setTime(QTime(0,0,0));
end.setTime(QTime(23,59,59));

qDebug() << "begin is valid:" << begin.isValid();
qDebug() << "end is valid:" << end.isValid();

if(end >= begin){
qDebug() << "UserEventLog::searchDateRange() userName: " << userName;
qDebug() << "UserEventLog::searchDateRange() beginDate: " << begin.toString();
qDebug() << "UserEventLog::searchDateRange() endDate: " << end.toString();
dbConnect();

QString beginStr = begin.toString("yyyy-MM-dd 00:00:00");
QString endStr = end.toString("yyyy-MM-dd 23:59:59");

qDebug() << "UserEventLog::searchDateRange() begin: " << beginStr;
qDebug() << "UserEventLog::searchDateRange() end: " << endStr;

----------------debug out put---------------------

userName, beginDate, endDate parameters: "Welder-B" "10-10-2016" "10-12-2016"
begin is valid: true
end is valid: true
UserEventLog::searchDateRange() userName: "Welder-B"
UserEventLog::searchDateRange() beginDate: "Mon Oct 10 00:00:00 2016"
UserEventLog::searchDateRange() endDate: "Wed Oct 12 23:59:59 2016"
connected to DB
UserEventLog::searchDateRange() begin: "2016-10-10 00:00:00"
UserEventLog::searchDateRange() end: "2016-10-12 23:59:59"


edit: should I change the date type of my DateTime column from text to integer ? sounds like it would improve search time... but I don't want changes to mess up my model or search functionality

[jefftee]

edit: should I change the date type of my DateTime column from text to integer ? sounds like it would improve search time... but I don't want changes to mess up my model or search functionality
It shouldn't matter, you could even call it type "datetime".

The last begin/end times your output shows on line 34-35 seem to be properly formatted and matches your database field contents, so what is the actual issue you're having? Are you not getting the records you expect or getting too many, or the right records are returned but it's too slow? Sorry if I missed that in your post, but it's not clear to me what the actual problem is.

[jfinn88]

It seem to be working fine everything works now I was getting confused on how fromString() and toString() handled there formatting I have it resolved and posted my solution above, thanks for the advise on the data type for the date column.

"I think my main issue was fromString() function call I thought I was setting the formatting in this function call but I was in correct I need to covert back to string and use toString() to get it in the format I need. the fromString() function needs the format its all ready in I fixed these issue and it seems to display the searched data in the model now"