Results 1 to 11 of 11

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

  1. 6th October 2016, 22:48 #1
    jfinn88
    jfinn88 is offline Intermediate user
    Join Date
    Jun 2016
    Posts
    99
    Thanks
    18
    Thanked 1 Time in 1 Post
    Qt products
    Qt5
    Platforms
    Unix/X11

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

    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
    Qt Code: 
    1. CREATE TABLE userlogevents(id INTEGER PRIMARY KEY AUTOINCREMENT, userName TEXT NOT NULL, eventMessage TEXT NOT NULL, dateTime TEXT NOT NULL);
    To copy to clipboard, switch view to plain text mode 

    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
    Qt Code: 
    1. bool UserEventLog::archiveEvent(){
    2.     //---connect to DB---//
    3.     dbConnect();
    4.  
    5.     //---shows loading symbol QMl side---//
    6.     emit showBusy(true);
    7.  
    8.     //---attach database to existing connection---//
    9.     QSqlQuery attachDbQry(m_selectDataBase);
    10.     QString sql_str = "ATTACH DATABASE '" + Paths::root() + "/userLogArchive.db' as db2";
    11.     attachDbQry.prepare(sql_str);
    12.  
    13.     qDebug() << "UserEventLog::archiveEvent() attached database path: "+sql_str;
    14.  
    15.     //---execute attach DB---//
    16.     if(attachDbQry.exec()){
    17.         qDebug()<<"UserEventLog::archiveEvent() attached database succesfully";
    18.     }
    19.     else{
    20.         emit xmui->alertMsg(QMessageBox::Warning, "Database Error Message 2", "Error searching database..."+attachDbQry.lastError().text());
    21.         return attachDbQry.exec();
    22.     }
    23.  
    24.     //---start DB transaction---//
    25.     //m_selectDataBase.transaction();
    26.  
    27.     //---get date values of MsgLogEvents--//
    28.     QSqlQuery dateQry(m_selectDataBase);
    29.     dateQry.prepare("SELECT * FROM userlogevents");
    30.  
    31.     if(dateQry.exec()) {
    32.         qDebug() << "UserEventLog::archiveEvent() dateQry: " << dateQry.value(3).toString();
    33.     }
    34.     else {
    35.         qDebug() << "UserEventLog::archiveEvent()" << dateQry.lastError().text();
    36.     }
    37.  
    38.     //---get currentDate minus 30days---//
    39.     QString dateStr;
    40.     QString archiveDateStr = QDateTime::currentDateTime().addDays(-30).toString("MM-dd-yyyy HH:MM:ss");
    41.     QDateTime archiveDate = QDateTime::fromString(archiveDateStr, "MM-dd-yyyy HH:MM:ss");
    42.  
    43.     qDebug() << "UserEventLog::archiveEvent() archiveDate: " << archiveDate.toString();
    44.  
    45.     //---copy from one Db table to another---//
    46.     QSqlQuery copyDataQry(m_selectDataBase);
    47.     bool prepareSqlBool;
    48.     prepareSqlBool = copyDataQry.prepare("INSERT INTO db2.userlogarchive (userName, eventMessage, dateTime) SELECT userName, eventMessage, dateTime FROM main.userlogevents WHERE dateTime < ?");
    49.     copyDataQry.addBindValue(archiveDate);
    50.  
    51.     while(copyDataQry.next())
    52.     {
    53.         qDebug() << "UserEventLog::archiveEevnt() copDataQry next value: " << copyDataQry.value(3).toString();
    54.     }
    55.  
    56.     //---check for value---//
    57.     qDebug() << "UserEventLog::archiveEvent() archive check: " << copyDataQry.value(3).toBool();
    58.  
    59.     //---prepare sql copy---//
    60.     if(prepareSqlBool){
    61.         qDebug()<<"UserEventLog::archiveEvent() prepare copy sql statement exicuted fine";
    62.     }
    63.     else{
    64.         emit xmui->alertMsg(QMessageBox::Warning, "Database Error Message 3", "Error: prepare script..."+copyDataQry.lastError().text());
    65.         return false;
    66.     }
    67.  
    68.     //---Execute copDataQry----//
    69.     copyDataQry.exec();
    70.  
    71.     //---Check copySql Bool value---//
    72.     bool copySqlBool;
    73.     copySqlBool = copyDataQry.value(3).toBool();
    74.     qDebug() << "UserEventLog::archiveEvent() copySqlBool: " << copySqlBool;
    75.  
    76.     QSqlQuery archiveDataQry(m_selectDataBase);
    77.     QDateTime userEventMsgDate;
    78.  
    79.     //---Only enter while loop if copySql executes---//
    80.     if(copySqlBool){
    81.         qDebug()<<"UserEventLog::archiveEvent() insert copy sql statement exicuted fine";
    82.  
    83.         //---Copy rows to archive then delete old rows---//
    84.         while(dateQry.next()){
    85.             //---Get date value from dateQry---//
    86.             dateStr = dateQry.value(3).toString();
    87.             userEventMsgDate = QDateTime::fromString(dateStr, "MM-dd-yyyy HH:MM:ss");
    88.             qDebug() << "UserEventLog::archiveEvent() msgDate & archiveDate: " << userEventMsgDate.toString("MM-dd-yyyy HH:MM:ss") << " < " << archiveDate.toString("MM-dd-yyyy HH:MM:ss");
    89.  
    90.             //---Executes only if copy qry executes---//
    91.             if(userEventMsgDate < archiveDate){
    92.                 //---Sql delete statement---//
    93.                 archiveDataQry.prepare("DELETE FROM userlogevents WHERE dateTime < ?");
    94.                 archiveDataQry.addBindValue(archiveDate);
    95.  
    96.                 //---execute delete---//
    97.                 if(archiveDataQry.exec()){
    98.                   qDebug()<<"UserEventLog::archiveEvent() delete sql statement exicuted fine";
    99.                 }
    100.                 else{
    101.                     emit xmui->alertMsg(QMessageBox::Warning, "Database Error Message 4", "Error: deleting old data..."+archiveDataQry.lastError().text());
    102.                     m_selectDataBase.rollback();
    103.                     return archiveDataQry.exec();
    104.                 }
    105.             }
    106.             else{
    107.                 qDebug() << "UserEventLog::archiveEvent() no old records to delete msg 1";
    108.                 //m_selectDataBase.rollback();
    109.                 return false;
    110.             }
    111.         }
    112.     }
    113.     else{
    114.         //emit xmui->alertMsg(QMessageBox::Warning, "Database Error Message 5", "Error: copy/insert archive data script..."+copyDataQry.lastError().text());
    115.         qDebug() << "UserEventLog::archiveEvent() no old records to delete msg 2";
    116.         //m_selectDataBase.rollback();
    117.         return copySqlBool;
    118.     }
    119.  
    120.     //---commit transaction & close---//
    121.     //m_selectDataBase.commit();
    122.     m_selectDataBase.close();
    123.     emit showBusy(false);
    124.     return copySqlBool;
    125. }
    To copy to clipboard, switch view to plain text mode 

    out put from function

    Qt Code: 
    1. "database connect path: /home/amet/git/xm-controller/userLog.db"
    2. connected to DB
    3. "UserEventLog::archiveEvent() attached database path: ATTACH DATABASE '/home/amet/git/xm-controller/userLogArchive.db' as db2"
    4. UserEventLog::archiveEvent() attached database succesfully
    5. QSqlQuery::value: not positioned on a valid record
    6. UserEventLog::archiveEvent() dateQry:  ""
    7. UserEventLog::archiveEvent() archiveDate:  "Tue Sep 6 15:00:44 2016"
    8. QSqlQuery::value: not positioned on a valid record
    9. UserEventLog::archiveEvent() archive check:  false
    10. UserEventLog::archiveEvent() prepare copy sql statement exicuted fine
    11. QSqlQuery::value: not positioned on a valid record
    12. UserEventLog::archiveEvent() copySqlBool:  false
    13. UserEventLog::archiveEvent() no old records to delete msg 2
    To copy to clipboard, switch view to plain text mode 

    just in case this is how I insert into orginal database
    Qt Code: 
    1. //---------Inserts data into database (XMUI Global Funciton)--------------//
    2. bool XMUI::insertLogMessage(QString userName, QString msg){
    3.     QString dateStr = QDateTime::currentDateTime().toString("MM-dd-yyyy HH:MM:ss");
    4.     qDebug() << "insert date: " << dateStr;
    5.  
    6.     //---checks is database is connected---//
    7.     if(!m_insertDataBase.isValid()){
    8.        qDebug() <<"error in opening DB";
    9.        m_insertDataBase = QSqlDatabase::addDatabase("QSQLITE", "conn1");
    10.        m_insertDataBase.setDatabaseName(Paths::root() + "/userLog.db");
    11.  
    12.     }
    13.     if(!m_insertDataBase.open()){
    14.         qDebug()<< "database cannot be opened";
    15.     }
    16.  
    17.     QSqlQuery m_insertQuery(m_insertDataBase);
    18.     m_insertQuery.prepare("INSERT INTO userlogevents (userName, eventMessage, dateTime) VALUES(:userName, :eventMessage, :dateTime)");
    19.     m_insertQuery.bindValue(":userName", userName);
    20.     m_insertQuery.bindValue(":eventMessage", msg);
    21.     m_insertQuery.bindValue(":dateTime", dateStr);
    22.     if(m_insertQuery.exec()){
    23.         qDebug()<<"sql statement exicuted fine";
    24.     }
    25.     else{
    26.         qDebug() << "Errors accured with sql statement";
    27.         qDebug() << m_insertQuery.lastError();
    28.     }
    29.     m_insertDataBase.close();
    30.     return m_insertQuery.exec();
    31. }
    To copy to clipboard, switch view to plain text mode 
    Last edited by jfinn88; 7th October 2016 at 00:06.
    Reply With Quote Reply With Quote
  2. 7th October 2016, 00:30 #2
    jefftee
    jefftee is offline Advanced user
    Join Date
    Dec 2009
    Location
    New Orleans, Louisiana
    Posts
    791
    Thanks
    13
    Thanked 153 Times in 150 Posts
    Qt products
    Qt5
    Platforms
    MacOS X

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

    Please see this 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:

    Qt Code: 
    1. attach database '/path/to/archive/db.sqlite' as arch;
    2. insert into arch.userlogevent select * from main.userlogevent where dateTime < datetime('now','localtime','-30 days');
    3. delete from main.userlogevent where dateTime < datetime('now','localtime','-30 days');
    To copy to clipboard, switch view to plain text mode 

    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.
    Last edited by jefftee; 7th October 2016 at 00:49.
    I write the best type of code possible, code that I want to write, not code that someone tells me to write!
    Reply With Quote Reply With Quote

  3. The following user says thank you to jefftee for this useful post:

    jfinn88 (7th October 2016)
  4. 7th October 2016, 18:55 #3
    jfinn88
    jfinn88 is offline Intermediate user
    Join Date
    Jun 2016
    Posts
    99
    Thanks
    18
    Thanked 1 Time in 1 Post
    Qt products
    Qt5
    Platforms
    Unix/X11

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

    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)

    Qt Code: 
    1. bool UserEventLog::archiveEvent(){
    2.     dbConnect();
    3.     emit showBusy(true);
    4.  
    5.     QSqlQuery attachDbQry(m_selectDataBase);
    6.     QString sql_str = "ATTACH DATABASE '" + Paths::root() + "/userLogArchive.db' as db2";
    7.     bool attachDbBool;
    8.     bool copyDbBool;
    9.     bool deleteDbBool;
    10.  
    11.     //---Prepare attach DB---//
    12.     if(attachDbQry.prepare(sql_str)) {
    13.         attachDbBool = attachDbQry.exec();
    14.     }
    15.     else {
    16.         qDebug() << "UserEventLog::archiveEvent() Error with prepare statment attaching database";
    17.     }
    18.  
    19.     //---execute attach DB---//
    20.     if(attachDbBool){
    21.         qDebug()<<"UserEventLog::archiveEvent() attached database succesfully";
    22.     }
    23.     else{
    24.         qDebug() << "UserEventLog::archiveEvent() Error with executing attached database";
    25.         return attachDbBool;
    26.     }
    27.  
    28.     //---start DB transaction---//
    29.     m_selectDataBase.transaction();
    30.  
    31.     //---Archive old data--//
    32.     QSqlQuery deleteQry(m_selectDataBase);
    33.     QSqlQuery copyQry(m_selectDataBase);
    34.     copyDbBool = copyQry.prepare("INSERT INTO db2.userlogarchive (userName, eventMessage, dateTime) SELECT userName, eventMessage, dateTime FROM main.userlogevents WHERE dateTime < datetime('now', 'localtime', '-30 days')");
    35.  
    36.     //---If copyQry prepare returns true---//
    37.     if(copyDbBool){
    38.         copyDbBool = copyQry.exec();
    39.         qDebug() << "UserEventLog::archiveEvent() CopyQry ran" << " " << copyQry.lastError().text();
    40.  
    41.         //---If the number of rows affected by copyAry is greater than 0---///
    42.         if(copyQry.numRowsAffected() > 0) {
    43.             deleteDbBool = deleteQry.prepare("DELETE FROM main.userlogevents WHERE dateTime < datetime('now','localtime','-30 days')");
    44.             qDebug() << "UserEventLog::archiveEvent() deleteQry.prepare() ran" << " " << deleteQry.lastError().text();
    45.  
    46.             //---If deleteQry prepare returns true---//
    47.             if(deleteDbBool){
    48.                 //---execute deleteQry---//
    49.                 deleteDbBool = deleteQry.exec();
    50.                 qDebug () << "UserEventLog::archiveEvent() deleteQry.exec() ran" << " " << deleteQry.lastError();
    51.             }
    52.             else {
    53.                 qDebug() << "UserEventLog::archiveEvent() Error with prepare delete statment" << " " << deleteQry.lastError().text();
    54.                 m_selectDataBase.rollback();
    55.                 return deleteDbBool;
    56.             }
    57.         }
    58.         else {
    59.             qDebug() << "UserEventLog::archiveEvent() CopyQry didn't affect any rows" << " " << copyQry.lastError().text();
    60.             m_selectDataBase.rollback();
    61.             return false;
    62.         }
    63.     }
    64.     else {
    65.         qDebug() << "UserEventLog::archiveEvent() CopyQry didn't run"  << " " << copyQry.lastError().text();
    66.         m_selectDataBase.rollback();
    67.         return copyDbBool;
    68.     }
    69.  
    70.     //---commit transaction & close---//
    71.     m_selectDataBase.commit();
    72.     m_selectDataBase.close();
    73.     emit showBusy(false);
    74.     return copyDbBool;
    75. }
    To copy to clipboard, switch view to plain text mode 

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

    updated code!
    Qt Code: 
    1. bool UserEventLog::selectEvent(){
    2.     dbConnect();
    3.     //QString beginDate = QDateTime::currentDateTime().toLocalTime().toString("yyyy-MM-dd 00:00:00");
    4.     //QString endDate = QDateTime::currentDateTime().toLocalTime().toString("yyyy-MM-dd 23:59:59");
    5.  
    6.     //qDebug() << "UserEventLog::selectEvent() beginDate: " << beginDate;
    7.     //qDebug() << "UserEventLog::selectEvent() endDate: " << endDate;
    8.  
    9.     emit showBusy(true);
    10.  
    11.     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);
    12.     if(selectQuery.exec()){
    13.     //selectQuery.addBindValue(beginDate);
    14.     //selectQuery.addBindValue(endDate);
    15.     if(selectQuery.exec()){
    16.         qDebug()<<"UserEventLog::selectEvent() sql statement executed fine";
    17.     }
    18.     else{
    19.         emit xmui->alertMsg(QMessageBox::Warning, "Database Error Message 1", "Error: sql select script..."+selectQuery.lastError().text());
    20.         qDebug()<<"UserEventLog::selectEvent() Error with sql statement execution";
    21.         return selectQuery.exec();
    22.     }
    23.  
    24.     userEventLogMsg msg;
    25.     beginResetModel();
    26.     m_userEventList.clear();
    27.     while (selectQuery.next()){
    28.         msg.id = selectQuery.value(0).toString();
    29.         msg.username = selectQuery.value(1).toString();
    30.         msg.eventmessage = selectQuery.value(2).toString();
    31.         msg.datetime = selectQuery.value(3).toDateTime();
    32.         addEvent(msg);
    33.     }
    34.     endResetModel();
    35.     emit showBusy(false);
    36.     m_selectDataBase.close();
    37.     return selectQuery.exec();
    38. }
    To copy to clipboard, switch view to plain text mode 

    code for inserting data in to database (record user events)
    Qt Code: 
    1. bool XMUI::insertLogMessage(QString userName, QString msg){
    2.  
    3.     //---checks if database is connected---//
    4.     if(!m_insertDataBase.isValid()){
    5.        qDebug() <<"error in coneecting DB";
    6.        m_insertDataBase = QSqlDatabase::addDatabase("QSQLITE", "conn1");
    7.        m_insertDataBase.setDatabaseName(Paths::root() + "/userLog.db");
    8.        m_insertDataBase.open();
    9.     }
    10.  
    11.     //---Check if database is open---//
    12.     if(!m_insertDataBase.open()){
    13.         qDebug()<< "XMUI::insertLogMessage() database is either locked or cannot be opened";
    14.         //insertLogMessage(userName, msg);
    15.         //return m_insertDataBase.open();
    16.     }
    17.  
    18.     //---Prepare sqlite query---//
    19.     QSqlQuery m_insertQuery(m_insertDataBase);
    20.     m_insertQuery.prepare("INSERT INTO userlogevents (userName, eventMessage, dateTime) VALUES(:userName, :eventMessage, dateTime('now', 'localtime'))");
    21.     m_insertQuery.bindValue(":userName", userName);
    22.     m_insertQuery.bindValue(":eventMessage", msg);
    23.  
    24.     //---Verify query executes---//
    25.     if(m_insertQuery.exec()){
    26.         qDebug()<<"sql statement exicuted fine";
    27.     }
    28.     else{
    29.         qDebug() << "XMUI::insertLogMessage() Errors accured with sql statement";
    30.         qDebug() << m_insertQuery.lastError();
    31.     }
    32.  
    33.     //---close database conenction and exit fucniton---//
    34.     m_insertDataBase.close();
    35.     return m_insertQuery.exec();
    36. }
    To copy to clipboard, switch view to plain text mode 

    C++ model data() function (format date for display in model ("MM-dd-yyyy HH:MM:ss")
    Qt Code: 
    1. QVariant UserEventLog::data(const QModelIndex &index, int role) const{
    2.     if (index.row() < 0 || index.row() >= m_userEventList.count()){
    3.         return QVariant();
    4.     }
    5.  
    6.     QVariant text;
    7.  
    8.     if(role == idRole){
    9.         userEventLogMsg msg = m_userEventList.at(index.row());
    10.         text = msg.id;
    11.     }
    12.     else if(role == nameRole){
    13.         userEventLogMsg msg = m_userEventList.at(index.row());
    14.         text = msg.username;
    15.     }
    16.     else if(role == msgRole){
    17.         userEventLogMsg msg = m_userEventList.at(index.row());
    18.         text = msg.eventmessage;
    19.     }
    20.     else if(role == dateRole){
    21.         userEventLogMsg msg = m_userEventList.at(index.row());
    22.         //---format date---//
    23.         text = msg.datetime.toLocalTime().toString("MM-dd-yyyy HH:MM:ss" );
    24.     }
    25.     return text;
    26. }
    To copy to clipboard, switch view to plain text mode 
    Last edited by jfinn88; 7th October 2016 at 19:21.
    Reply With Quote Reply With Quote
  5. 7th October 2016, 19:33 #4
    jefftee
    jefftee is offline Advanced user
    Join Date
    Dec 2009
    Location
    New Orleans, Louisiana
    Posts
    791
    Thanks
    13
    Thanked 153 Times in 150 Posts
    Qt products
    Qt5
    Platforms
    MacOS X

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

    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:

    Qt Code: 
    1. create index if not exists ix_userlogevents_datetime on userlogevents ( datetime asc );
    To copy to clipboard, switch view to plain text mode 
    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:

    Qt Code: 
    1. QDate now = QDate::currentDate();
    2. QString beginDate = now.toString("yyyy-MM-dd 00:00:00");
    3. QString endDate = now.toString("yyyy-MM-dd 23:59:59");
    To copy to clipboard, switch view to plain text mode 
    This will ensure that the begin date and end date are the same.
    I write the best type of code possible, code that I want to write, not code that someone tells me to write!
    Reply With Quote Reply With Quote

  6. The following user says thank you to jefftee for this useful post:

    jfinn88 (7th October 2016)
  7. 11th October 2016, 17:08 #5
    jfinn88
    jfinn88 is offline Intermediate user
    Join Date
    Jun 2016
    Posts
    99
    Thanks
    18
    Thanked 1 Time in 1 Post
    Qt products
    Qt5
    Platforms
    Unix/X11

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

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

    QML search condition
    Qt Code: 
    1. //---Action for Search Button---//
    2.         Action {
    3.             id: action_search
    4.             enabled:!inSequence
    5.             onTriggered:{
    6.                 /* Condition statement to search by:
    7.                 *  userName,
    8.                 *  singleDate,
    9.                 *  dateRange using textFields,
    10.                 *  userName & signleDate,
    11.                 *  userName & dateRange using TextFieldStyle
    12.                 */
    13.  
    14.                 //---Hides Calendars on Search---//
    15.                 calendarRect1.visible = false
    16.                 calendarRect2.visible = false
    17.                 searchBtn.focus = false;
    18.                 endDateTextField.focus = false;
    19.  
    20.                 //---Search by userName---/
    21.                 if(beginDateTextField.text === "" && endDateTextField === "" && userNameDropDown.currentText !== ""){
    22.                     UserEventLog.searchUserName(userNameDropDown.currentText);
    23.                     console.log("Search by userName: ", userNameDropDown.currentText)
    24.                 }
    25.  
    26.                 //---Search by signle date: beginDate OR endDate---//
    27.                 else if(beginDateTextField.text !== "" && endDateTextField.text === "" &&  userNameDropDown.currentText === "" ||
    28.                         beginDateTextField.text === "" && endDateTextField.text !=="" && userNameDropDown.currentText ===""){
    29.  
    30.                     console.log("Search by signle date beginDate: ", beginDateTextField.text.toString());
    31.                     if(beginDateTextField.text !== "" && endDateTextField.text === ""){
    32.                         UserEventLog.searchDate(beginDateTextField.text.toString("yyyy-MM-dd"));
    33.                     }
    34.                     else if(beginDateTextField.text === "" && endDateTextField.text !== ""){
    35.                         UserEventLog.searchDate(endDateTextField.text.toString("yyyy-MM-dd"));
    36.                     }
    37.                 }
    38.  
    39.                 //---Search by beginDate & endDate text fields---//
    40.                 else if(beginDateTextField.text !== "" && endDateTextField.text !== "" && userNameDropDown.currentText === ""){
    41.                     console.log("Search by beginDate & endDate text fields: ", endDateTextField.text.toString());
    42.                     console.log("Search by beginDate & endDate text fields: ", beginDateTextField.text.toString());
    43.                     UserEventLog.searchDateRange(beginDateTextField.text.toString("yyyy-MM-dd"), endDateTextField.text.toString("yyyy-MM-dd"));
    44.                 }
    45.  
    46.                 //---Search by userName & Single date (beginDate OR endDate)---//
    47.                 else if(beginDateTextField.text !== "" && endDateTextField.text ==="" && userNameDropDown.currentText !== "" ||
    48.                         beginDateTextField.text == "" && endDateTextField.text !=="" && userNameDropDown.currentText !== "" ){
    49.  
    50.                     console.log("Search by userName & beginDate text field: ", userNameDropDown.currentText);
    51.                     console.log("Search by userName & beginDate text field: ", Qt.formatDateTime(beginDateTextField.text, "yyyy-MM-dd hh:mm:ss"));
    52.  
    53.  
    54.                     if(beginDateTextField.text !== "" && endDateTextField.text === ""){
    55. UserEventLog.searchUserNameDateText(userNameDropDown.currentText, beginDateTextField.text);                        
    56. //UserEventLog.searchUserNameDateText(userNameDropDown.currentText, Qt.formatDateTime(beginDateTextField.text,               "yyyy-MM-dd hh:mm:ss"));
    57.                     }
    58.                     else if(beginDateTextField.text === "" && endDateTextField.text !== ""){
    59. UserEventLog.searchUserNameDateText(userNameDropDown.currentText, endDateTextField.text);                        
    60. //UserEventLog.searchUserNameDateText(userNameDropDown.currentText, Qt.formatDateTime(beginDateTextField.text, "yyyy-MM-dd hh:mm:ss"));
    61.                     }
    62.                 }
    63.  
    64.                 //---Search by userName, & beginDate, endDate text fields---//
    65.                 else if(beginDateTextField.text !== "" && endDateTextField.text !== "" && userNameDropDown.currentText !== ""){
    66.                     console.log("Search by userName, & beginDate, endDate text fields: ", userNameDropDown.currentText);
    67.                     console.log("Search by userName, & beginDate, endDate text fields: ", beginDateTextField.text.toString());
    68.                     console.log("Search by userName, & beginDate, endDate text fields: ", endDateTextField.text.toString());
    69.                     UserEventLog.searchDateRange(userNameDropDown.currentText, beginDateTextField.text, endDateTextField.text);
    70.  
    71.                 }
    72.  
    73.                 //---if search fails---//
    74.                 else{
    75.                     console.log("enter in date range or search by user name or both!");
    76.                 }
    77.  
    78.                 //---Prepare listView: set focus, set index, set position---//
    79.                 listView.forceActiveFocus();
    80.                 listView.currentIndex = 0;
    81.                 listView.positionViewAtBeginning();
    82.             }
    83.         }
    To copy to clipboard, switch view to plain text mode 

    c++ function that performs sql script
    update: updated if condition in fcn
    Qt Code: 
    1. bool UserEventLog::searchUserNameDateText(QString userNameText, QDate dateText){
    2.     qDebug() << "UserEventLog::searchUserNameDateText() User Name: " << userNameText;
    3.     qDebug() << "UserEventLog::searchUserNameDateText() Date: " << dateText.toString();
    4.  
    5.     QString begin = dateText.toString("yyyy-MM-dd 00:00:00");
    6.     QString end = dateText.toString("yyyy-MM-dd 23:59:59");
    7.  
    8.     qDebug() << "UserEventLog::searchUserNameDateText() beginDate: " << begin;
    9.     qDebug() << "UserEventLog::searchUserNameDateText() endDate: " << end;
    10.  
    11.     dbConnect();
    12.  
    13.     emit showBusy(true);
    14.  
    15.     QSqlQuery selectQuery(m_selectDataBase);
    16.     QString selectQueryString = "SELECT id, userName, eventMessage, dateTime FROM userlogevents WHERE userName = ? AND dateTime BETWEEN ? and ?";
    17.     selectQuery.addBindValue(userNameText);
    18.     selectQuery.addBindValue(begin);
    19.     selectQuery.addBindValue(end);
    20.  
    21.     bool prepareSqlBool = selectQuery.prepare(selectQueryString);
    22.     bool selectSqlBool;
    23.  
    24.  
    25.     if(prepareSqlBool){
    26.         selectSqlBool = selectQuery.exec();
    27.         if(selectSqlBool){
    28.             qDebug() << "UserEventLog::searchUserNameDateText() searchUserNameDateText query executed fine!";
    29.         }
    30.         else{
    31.             return selectSqlBool;
    32.         }
    33.     }
    34.     else{
    35.         emit xmui->alertMsg(QMessageBox::Warning, "Database Error Message 9", "Error: searching database..."+selectQuery.lastError().text());
    36.         qDebug() << "UserEventLog::searchUserNameDateText() searchUserNameDateText query dind't execute";
    37.         return prepareSqlBool;
    38.     }
    39.  
    40.     userEventLogMsg msg;
    41.     beginResetModel();
    42.     m_userEventList.clear();
    43.  
    44.     while (selectQuery.next()){
    45.         msg.id = selectQuery.value(0).toString();
    46.         msg.username = selectQuery.value(1).toString();
    47.         msg.eventmessage = selectQuery.value(2).toString();
    48.         msg.datetime = selectQuery.value(3).toDateTime();
    49.         addEvent(msg);
    50.     }
    51.     endResetModel();
    52.     m_selectDataBase.close();
    53.     emit showBusy(false);
    54.     return selectSqlBool;
    55. }
    To copy to clipboard, switch view to plain text mode 

    update: query used to push data into database
    Qt Code: 
    1. m_insertQuery.prepare("INSERT INTO userlogevents (userName, eventMessage, dateTime) VALUES(:userName, :eventMessage, dateTime('now', 'localtime'))");
    To copy to clipboard, switch view to plain text mode 

    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

    Qt Code: 
    1. UserEventLog.searchUserNameDateText(userNameDropDown.currentText, beginDateTextField.text);
    To copy to clipboard, switch view to plain text mode 

    I have tried passing it different ways e.g.
    Qt Code: 
    1. beginDateTextField.text;
    2. beginDateTextField.text.toString();
    3. beginDateTextField.text.toString("yyyy-MM-dd");
    4. beginDateTextField.text.toString("yyyy-MM-dd hh:mm:ss);
    To copy to clipboard, switch view to plain text mode 

    I tried creating a local var and try passing that as a Date type e.g.
    Qt Code: 
    1. var myVar = new Date(); 
    2. myVar.setDate(beginDateTextField.text, "yyyy-MM-dd");
    3.  
    4. var mydate = new Date();
    5. mydate.setDate(Qt.formatDateTime(beginDateTextField.text, "yyyy-MM-dd hh:mm:ss"));
    6.  
    7. var locale = Qt.locale();
    8. var mydate = Date.fromLocaleString(locale, beginDateTextField.text, "yyyy-MM-dd hh:mm:ss");
    9.  
    10. UserEventLog.searchUserNameDateText(userNameDropDown.currentText, mydate);
    To copy to clipboard, switch view to plain text mode 
    I have tried messing with the formating
    Qt Code: 
    1. UserEventLog.searchUserNameDateText(userNameDropDown.currentText, Qt.formatDateTime(beginDateTextField.text, "yyyy-MM-dd hh:mm:ss"));
    To copy to clipboard, switch view to plain text mode 

    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)

    Qt Code: 
    1. QString begin = dateText.toString("yyyy-MM-dd 00:00:00");
    2.     QString end = dateText.toString("yyyy-MM-dd 23:59:59");
    To copy to clipboard, switch view to plain text mode 

    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
    Last edited by jfinn88; 11th October 2016 at 19:52.
    Reply With Quote Reply With Quote
  8. 11th October 2016, 20:59 #6
    jfinn88
    jfinn88 is offline Intermediate user
    Join Date
    Jun 2016
    Posts
    99
    Thanks
    18
    Thanked 1 Time in 1 Post
    Qt products
    Qt5
    Platforms
    Unix/X11

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

    I tried changing the fcn parameter to a QString and convert that string into a date type:
    Qt Code: 
    1. bool UserEventLog::searchUserNameDateText(QString userNameText, QString dateText){
    2.     qDebug() << "UserEventLog::searchUserNameDateText() User Name: " << userNameText;
    3.     qDebug() << "UserEventLog::searchUserNameDateText() Date: " << dateText;
    4.  
    5.     QDateTime begin = QDateTime::fromString(dateText, "yyyy-MM-dd 00:00:00");
    6.     QDateTime end = QDateTime::fromString(dateText, "yyyy-MM-dd 23:59:59");
    7.  
    8.     qDebug() << "UserEventLog::searchUserNameDateText() beginDate: " << begin.toString();
    9.     qDebug() << "UserEventLog::searchUserNameDateText() endDate: " << end.toString();
    10.  
    11.     dbConnect();
    12.  
    13.     emit showBusy(true);
    14.  
    15.     QSqlQuery selectQuery(m_selectDataBase);
    16.     QString selectQueryString = "SELECT id, userName, eventMessage, dateTime FROM userlogevents WHERE userName = ? AND dateTime BETWEEN ? AND ?";
    17.  
    18.     bool prepareSqlBool = selectQuery.prepare(selectQueryString);
    19.     bool selectSqlBool;
    20.  
    21.     selectQuery.addBindValue(userNameText);
    22.     selectQuery.addBindValue(begin);
    23.     selectQuery.addBindValue(end);
    24.  
    25.     if(prepareSqlBool){
    26.         selectSqlBool = selectQuery.exec();
    27.         if(selectSqlBool){
    28.             qDebug() << "UserEventLog::searchUserNameDateText() searchUserNameDateText query executed fine!";
    29.         }
    30.         else{
    31.             qDebug() << "UserEventLog::searchUserNameDateText() searchUserNameDateText query did not execute!" << selectQuery.lastError();
    32.             return selectSqlBool;
    33.         }
    34.     }
    35.     else{
    36.         emit xmui->alertMsg(QMessageBox::Warning, "Database Error Message 9", "Error: searching database..."+selectQuery.lastError().text());
    37.         qDebug() << "UserEventLog::searchUserNameDateText() searchUserNameDateText query dind't execute";
    38.         return prepareSqlBool;
    39.     }
    To copy to clipboard, switch view to plain text mode 
    Reply With Quote Reply With Quote
  9. 11th October 2016, 23:27 #7
    jfinn88
    jfinn88 is offline Intermediate user
    Join Date
    Jun 2016
    Posts
    99
    Thanks
    18
    Thanked 1 Time in 1 Post
    Qt products
    Qt5
    Platforms
    Unix/X11

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

    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:
    Qt Code: 
    1. //---Search userEventLog databse by: user name & single date---//
    2. bool UserEventLog::searchUserNameDateText(QString userNameText, QString dateText){
    3.  
    4.     QDateTime beginDate, endDate;
    5.     beginDate = QDateTime::fromString(dateText, "MM-dd-yyyy");
    6.     endDate = QDateTime::fromString(dateText, "MM-dd-yyyy");
    7.  
    8.     beginDate.setTime(QTime(0,0,0));
    9.     endDate.setTime(QTime(23,59,59));
    10.  
    11.     qDebug() << "UserEventLog::searchUserNameDateText() User Name: " << userNameText;
    12.     qDebug() << "UserEventLog::searchUserNameDateText() Date: " << dateText;
    13.  
    14.     QDateTime beginStr = QDateTime::fromString(dateText, "yyyy-MM-dd 00:00:00");
    15.     QDateTime endStr = QDateTime::fromString(dateText, "yyyy-MM-dd 23:59:59");
    16.  
    17.     qDebug() << "UserEventLog::searchUserNameDateText() beginDate: " << beginStr;
    18.     qDebug() << "UserEventLog::searchUserNameDateText() endDate: " << endStr;
    To copy to clipboard, switch view to plain text mode 
    Reply With Quote Reply With Quote
  10. 12th October 2016, 00:00 #8
    jefftee
    jefftee is offline Advanced user
    Join Date
    Dec 2009
    Location
    New Orleans, Louisiana
    Posts
    791
    Thanks
    13
    Thanked 153 Times in 150 Posts
    Qt products
    Qt5
    Platforms
    MacOS X

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

    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:
    Qt Code: 
    1. sqlite3 your.db "select * from userlogevents limit 20"
    To copy to clipboard, switch view to plain text mode 
    Edit: Also, when you are debugging, are beginDate.isValid() and endDate.isValid() both true?
    Last edited by jefftee; 12th October 2016 at 00:19.
    I write the best type of code possible, code that I want to write, not code that someone tells me to write!
    Reply With Quote Reply With Quote
  11. 12th October 2016, 16:23 #9
    jfinn88
    jfinn88 is offline Intermediate user
    Join Date
    Jun 2016
    Posts
    99
    Thanks
    18
    Thanked 1 Time in 1 Post
    Qt products
    Qt5
    Platforms
    Unix/X11

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

    Here is the data being stored in my database
    Qt Code: 
    1. sqlite3 userLog.db "select * from userlogevents limit 20;"
    2. 913|root|User entered userEventDialog|2016-10-10 09:51:22
    3. 914|root|User currently logged in as: root|2016-10-10 09:52:33
    4. 915|root|User entered userEventDialog|2016-10-10 09:52:35
    5. 916|root|User entered userEventDialog|2016-10-10 09:53:59
    6. 917|root|User entered userEventDialog|2016-10-10 09:54:41
    7. 918|root|User currently logged in as: root|2016-10-10 10:02:16
    8. 919|root|User entered userEventDialog|2016-10-10 10:02:18
    9. 920|root|User currently logged in as: root|2016-10-10 10:20:02
    10. 921|root|User entered userEventDialog|2016-10-10 10:20:05
    11. 922|root|User entered userEventDialog|2016-10-10 10:21:13
    12. 923|root|User currently logged in as: root|2016-10-10 10:28:12
    13. 924|root|User entered userEventDialog|2016-10-10 10:28:14
    14. 925|root|User entered userEventDialog|2016-10-10 10:28:27
    15. 926|root|User currently logged in as: root|2016-10-10 10:38:16
    16. 927|root|User entered userEventDialog|2016-10-10 10:38:18
    17. 928|root|User currently logged in as: root|2016-10-10 10:42:30
    18. 929|root|User entered userEventDialog|2016-10-10 10:42:31
    19. 930|welder-b|User currently logged in as: welder-b|2016-10-10 10:44:32
    20. 931|Welder-B|User currently logged in as: Welder-B|2016-10-10 10:44:52
    21. 932|Welder-B|User entered userEventDialog|2016-10-10 10:44:54
    To copy to clipboard, switch view to plain text mode 

    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
    Qt Code: 
    1. //---Search userEventLog databse by: user name & date range---//
    2. bool UserEventLog::searchDateRange(const QString &userName, QString beginDate, QString endDate){
    3.     qDebug() << "userName, beginDate, endDate parameters:" << userName << beginDate << endDate;
    4.     QDateTime begin, end;
    5.     begin = QDateTime::fromString(beginDate, "MM-dd-yyyy");
    6.     end = QDateTime::fromString(endDate, "MM-dd-yyyy");
    7.     begin.setTime(QTime(0,0,0));
    8.     end.setTime(QTime(23,59,59));
    9.  
    10.     qDebug() << "begin is valid:" << begin.isValid();
    11.     qDebug() << "end is valid:" << end.isValid();
    12.  
    13.     if(end >= begin){
    14.         qDebug() << "UserEventLog::searchDateRange() userName: " << userName;
    15.         qDebug() << "UserEventLog::searchDateRange() beginDate: " << begin.toString();
    16.         qDebug() << "UserEventLog::searchDateRange() endDate: " << end.toString();
    17.         dbConnect();
    18.  
    19.         QString beginStr = begin.toString("yyyy-MM-dd 00:00:00");
    20.         QString endStr = end.toString("yyyy-MM-dd 23:59:59");
    21.  
    22.         qDebug() << "UserEventLog::searchDateRange() begin: " << beginStr;
    23.         qDebug() << "UserEventLog::searchDateRange() end: " << endStr;
    24.  
    25. ----------------debug out put---------------------
    26.  
    27. userName, beginDate, endDate parameters: "Welder-B" "10-10-2016" "10-12-2016"
    28. begin is valid: true
    29. end is valid: true
    30. UserEventLog::searchDateRange() userName:  "Welder-B"
    31. UserEventLog::searchDateRange() beginDate:  "Mon Oct 10 00:00:00 2016"
    32. UserEventLog::searchDateRange() endDate:  "Wed Oct 12 23:59:59 2016"
    33. connected to DB
    34. UserEventLog::searchDateRange() begin:  "2016-10-10 00:00:00"
    35. UserEventLog::searchDateRange() end:  "2016-10-12 23:59:59"
    To copy to clipboard, switch view to plain text mode 

    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
    Last edited by jfinn88; 12th October 2016 at 16:48.
    Reply With Quote Reply With Quote
  12. 12th October 2016, 23:23 #10
    jefftee
    jefftee is offline Advanced user
    Join Date
    Dec 2009
    Location
    New Orleans, Louisiana
    Posts
    791
    Thanks
    13
    Thanked 153 Times in 150 Posts
    Qt products
    Qt5
    Platforms
    MacOS X

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

    Quote Originally Posted by jfinn88 View Post
    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.
    I write the best type of code possible, code that I want to write, not code that someone tells me to write!
    Reply With Quote Reply With Quote
  13. 13th October 2016, 00:02 #11
    jfinn88
    jfinn88 is offline Intermediate user
    Join Date
    Jun 2016
    Posts
    99
    Thanks
    18
    Thanked 1 Time in 1 Post
    Qt products
    Qt5
    Platforms
    Unix/X11

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

    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"
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Similar Threads

  1. issue with sqlite select qry by date & time for 24 hour period
    By jfinn88 in forum Newbie
    Replies: 8
    Last Post: 7th October 2016, 19:14
  2. SQLite reading Chinese strings issue
    By yazwas in forum Qt Programming
    Replies: 1
    Last Post: 6th April 2011, 08:37
  3. Protecting SQLite Data
    By zim in forum Newbie
    Replies: 23
    Last Post: 25th March 2011, 23:50
  4. QDevelop SQLite Driver Issue
    By Phan Sin Tian in forum Qt-based Software
    Replies: 4
    Last Post: 1st August 2010, 11:47
  5. data not being retained in sqlite DB
    By sticcino in forum Qt Programming
    Replies: 2
    Last Post: 2nd July 2008, 10:42

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules

Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.