Results 1 to 11 of 11

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

Threaded View

Previous Post Previous Post   Next Post Next Post
  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
« 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.