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);
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
bool UserEventLog::archiveEvent(){
//---connect to DB---//
dbConnect();
//---shows loading symbol QMl side---//
emit showBusy(true);
//---attach database to existing connection---//
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--//
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 archiveDateStr
= QDateTime::currentDateTime().
addDays(-30).
toString("MM-dd-yyyy HH:MM:ss");
qDebug() << "UserEventLog::archiveEvent() archiveDate: " << archiveDate.toString();
//---copy from one Db table to another---//
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;
//---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;
}
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;
}
To copy to clipboard, switch view to plain text mode
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
"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
To copy to clipboard, switch view to plain text mode
just in case this is how I insert into orginal database
//---------Inserts data into database (XMUI Global Funciton)--------------//
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";
}
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();
}
//---------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();
}
To copy to clipboard, switch view to plain text mode
Bookmarks