using an isntance of QSqlDatabase for connection defiinition

[jfinn88]

I have a Sqlite database I need to connect to...

Instead of running the connection code every-time my method is called I would like to create an instance of the QSqlDatabase class

I'm new to QT and still learning C++

my understanding the code will run more efficiently and will "clean up" the code as well and make it so only create a connection once unless needed

if you can explain the how the instance of the class will allow me to do this that would be awesome

----------old way-----------


void myClass::insertLogMessage(QString msg)
{
//---Contects to a database | Uses QSQLITE driver---//
QSqlDatabase userEventDB = QSqlDatabase::addDatabase("QSQLITE");
//---Sepcifies database path---//
userEventDB.setDatabaseName("/home/amet/git/rnd/userLog.db");
//---Checks if database is open---//
if (userEventDB.open()) {
qDebug() << "Connected to userEventLog database";
}
else {
qDebug() << "Error: no connection was found!";
}
//---Opens database---//
userEventDB.open();

qDebug() << "insert Log Message called";
QSqlQuery query;
query.prepare("INSERT INTO userlogevents (firstName, lastName, userName, eventMessage, dateTime) VALUES('John', 'Doe', 'JohnnyD', '"+msg+"', datetime(current_timestamp))");
//---Executes Query Statement---//
query.exec();
}




------using instance (QSqlDatabase) variable-----------


void myClass::insertLogMessage(QString msg)
{
m_insertDataBase = QSqlDatabase::addDatabase("QSQLITE", "conn1");
m_insertDataBase = QSqlDatabase::database("conn1");
m_insertDataBase.setDatabaseName("/home/amet/userLog.db");
m_insertDataBase.open();

if(m_insertDataBase.isOpen()){

qDebug() <<"connected to DB" ;
}
else{
qDebug() <<"error in opening DB";
m_insertDataBase.open();
}

qDebug() << "insert Log Message called";
QSqlQuery insertQuery("INSERT INTO userlogevents (firstName, lastName, userName, eventMessage, dateTime) VALUES('John', 'Doe', 'JohnnyD', '"+msg+"', datetime(current_timestamp))", conn1);
insertQuery.exec();
m_insertDataBase.close();
}



--------Header file with instance of QSqlDatabase object---------


private:
QSqlDatabase m_insertDataBase;
};


I'm running into issues trying to implement an instance of the QSqlDatabase class (to connect to database just once) When I call my query with my database named connection ("conn1") I get 'conn1' was not declared in this scope I need to create an instance of this class (QSqlDatabase) to connect/open database once...? can any one help me get a better understanding of instances and creating a connection to a database with an instance of QSqlDatabase class so you only have to connect to it once or if you need to connect to it agian you just use the instance variable?

[Lesiok]

conn1 is the name of db connection not C++ variable. Variable name is m_insertDataBase - C++ ABC.
And line 4
m_insertDataBase = QSqlDatabase::database("conn1");is unnecessary.

[anda_skoa]

You also don't want to do the addDatabase() and setDatabaseName() calls every time, just once, when "m_insertDataBase" is not valid yet.
See QSqlDatabase:::isValid().

Also, as someone had already pointed out in another thread, don't concatenated an input string into a SQL Query, always use prepare() and bound values for proper escaping.

Cheers,
_

[jfinn88]

conn1 is the name of db connection not C++ variable. Variable name is m_insertDataBase - C++ ABC.
And line 4
m_insertDataBase = QSqlDatabase::database("conn1");is unnecessary.

It didn't seem to work when passing the driver and the connection name with the line of code above this one. So I tried setting the connection name again but kept getting "conn1 was not declared in this scope". I know conn1 is the db connection name I declare it in the code above and in this line and I declare the instance variable in my header file. I only used the above code seeing if setting the db connection name would work like this I realize its unnecessary, I was just trying something to get it to work. I'm not sure if I need to declare the instance variable under public or not in the header file?

Added after 4 minutes:


You also don't want to do the addDatabase() and setDatabaseName() calls every time, just once, when "m_insertDataBase" is not valid yet.
See QSqlDatabase:::isValid().

Also, as someone had already pointed out in another thread, don't concatenated an input string into a SQL Query, always use prepare() and bound values for proper escaping.

Cheers,
_
You are talking about binding my Msg variable correct? the parameter I'm using in my sql statement?

I was using a prepare statement before but took it out to try to short my code and it only takes one parameter wasn’t sure how to tell if its using the right connection. I will go back to using the prepare statement as suggested to allow proper escaping. How do I set the conn1 connection since The prepare() fcn only takes one argument can't pass it the connection name with sql statement do I just set the connection by


m_selectDataBase = QSqlDatabase::database("conn2");

I will look at documentation on isValid(). How do I only call those methods once when the instance is not valid? little explanation on how this works be great, you got a example?

[anda_skoa]

You are talking about binding my Msg variable correct? the parameter I'm using in my sql statement?

Yes



How do I set the conn1 connection since The prepare() fcn only takes one argument can't pass it the connection name with sql statement

You pass the QSqlDatabase handle to the QSqlQuery constructor.


QSqlQuery insertQuery(m_insertDataBase);
insertQuery.prepare(...);




I will look at documentation on isValid(). How do I only call those methods once when the instance is not valid? little explanation on how this works be great, you got a example?


if (!m_insertDataBase.isValid() {
// addDatabase, setDatabaseName
}


Cheers,
_

[jfinn88]

I have two classes I need to use an instance variable of the class QSqlDatabase to set database connections. I created an instance variable of the QSqlDatabase class in both classes under private:


--------------- Header file----------------


//Data struct for user event log
struct userEventLogMsg{
//hold all values for a single list entry,
QString id;
QString username;
QString eventmessage;
QString datetime;
};

//---Class UserEventDailyLog responsible for XMUI UserEvnetLog | Subed classed: QAbstractTableModel---//
class UserEventLog : public QAbstractListModel
{
Q_OBJECT

public:
explicit UserEventLog(QObject *parent = 0);

~UserEventLog();

enum userEventRoles {idRole= Qt::UserRole + 220, nameRole, msgRole, dateRole};

int rowCount(const QModelIndex & parent) const;

QHash<int, QByteArray> roleNames() const;

QVariant data(const QModelIndex &index, int role=Qt::DisplayRole) const;

Q_INVOKABLE void addEvent(const userEventLogMsg &msg);

void dbConnect();

void sqlSelect();

private:
QList<userEventLogMsg> m_userEventList;
QSqlDatabase m_selectDataBase;
};




//---------CPP file--------------//
//
// UserEventLogModel
//

//---Constructor---//
UserEventLog::UserEventLog(QObject *parent):QAbstractListModel(parent) {

}
//---Destructor---//
UserEventLog::~UserEventLog() {

}

int UserEventLog::rowCount(const QModelIndex &parent) const {
Q_UNUSED(parent);
qDebug()<< m_userEventList.count();
return m_userEventList.count();
}

QHash<int, QByteArray> UserEventLog::roleNames() const {
QHash<int, QByteArray> roleNames;
roleNames.insert(idRole, "id");
roleNames.insert(nameRole, "userName");
roleNames.insert(msgRole, "eventMessage");
roleNames.insert(dateRole, "dateTime");
qDebug()<< roleNames;
return roleNames;
}

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;
qDebug() << text;
}
else if(role == nameRole) {
userEventLogMsg msg = m_userEventList.at(index.row());
text = msg.username;
qDebug() << text;
}
else if(role == msgRole) {
userEventLogMsg msg = m_userEventList.at(index.row());
text = msg.eventmessage;
qDebug() << text;
}
if(role == dateRole) {
userEventLogMsg msg = m_userEventList.at(index.row());
text = msg.datetime;
qDebug() << text;
}
return text;
}

void UserEventLog::addEvent(const userEventLogMsg &msg) {

beginInsertRows(QModelIndex(), 0, 0);
m_userEventList.insert(0, msg);
endInsertRows();
}

void UserEventLog::dbConnect() {
m_selectDataBase = QSqlDatabase::addDatabase("QSQLITE", "conn2");
qDebug() << m_selectDataBase.isValid();
m_selectDataBase.setDatabaseName("/home/amet/userLog.db");
m_selectDataBase.open();

if(!m_selectDataBase.open()){
qDebug() <<"error in opening DB";
}
else{
qDebug() <<"connected to DB" ;
}
}
void UserEventLog::sqlSelect() {

//m_selectDataBase = QSqlDatabase::database("conn2");
qDebug() << m_selectDataBase.isValid();
m_selectDataBase.open();
QSqlQuery selectQuery;
selectQuery.prepare("SELECT id, userName, eventMessage, dateTime FROM userlogevents");
qDebug() <<selectQuery.lastError();

if(selectQuery.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() <<"Errors accured with sql statement";
qDebug() <<selectQuery.lastError();
}

while (selectQuery.next()){
userEventLogMsg msg;
UserEventLog model;
msg.id = selectQuery.value(0).toString();
msg.username = selectQuery.value(1).toString();
msg.eventmessage = selectQuery.value(2).toString();
msg.datetime = selectQuery.value(3).toString();
model.addEvent(msg);
}
//m_selectDataBase.close();
//m_selectDataBase.removeDatabase("conn2");
}
//----------------------------------------------------------------//


my other class that need a database connection to the same database

---------CPP file-----------


void XMUI::hdpiWindow()
{
mUserEventLogModel = new UserEventLog();
mUserEventLogModel->dbConnect();
mUserEventLogModel->sqlSelect();
m_QmlEngine->rootContext()->setContextProperty("UserEventLog", mUserEventLogModel);
}

void XMUI::insertLogMessage(QString msg)
{
m_insertDataBase = QSqlDatabase::addDatabase("QSQLITE", "conn1");
m_insertDataBase.setDatabaseName("/home/amet/userLog.db");
m_insertDataBase.open();

if(m_insertDataBase.isOpen()){

qDebug() <<"connected to DB" ;
}
else{
qDebug() <<"error in opening DB";
m_insertDataBase.open();
}

qDebug() << "insert Log Message called";
QSqlQuery insertQuery(m_insertDataBase);
insertQuery.prepare("INSERT INTO userlogevents (firstName, lastName, userName, eventMessage, dateTime) VALUES('John', 'Doe', 'JohnnyD', ':eventMessage', datetime(current_timestamp))");
insertQuery.bindValue(":eventMessage", msg);
insertQuery.exec();
//m_insertDataBase.close();
//m_insertDataBase.removeDatabase("conn1");
}



----header file--------


class XMUI : public QObject
{
Q_OBJECT

public:
explicit XMUI(QQuickWidget* _quickWidget);

void hdpiWindow();

Q_INVOKABLE void insertLogMessage(QString msg);

private:
QSqlDatabase m_insertDataBase;


Added after 18 minutes:


Yes


You pass the QSqlDatabase handle to the QSqlQuery constructor.


QSqlQuery insertQuery(m_insertDataBase);
insertQuery.prepare(...);



I was passing the name of my connection not the instance variable, changed it to instance variable.





if (!m_insertDataBase.isValid() {
// addDatabase, setDatabaseName
}


Cheers,
_

I moved the add and set methods inside an if condition to set them if the database is not valid. However I get confused because I need to connect to the database to begin with... If I'm only setting them when it not valid how do I initially set the connect the first time. set it in the constructor?



void XMUI::insertLogMessage(QString msg)
{
//qDebug() << m_insertDataBase.connectionNames();

if(!m_insertDataBase.isValid()){
qDebug() <<"error in opening DB";
m_insertDataBase = QSqlDatabase::addDatabase("QSQLITE", "conn1");
m_insertDataBase.setDatabaseName("/home/amet/userLog.db");
qDebug() << m_insertDataBase.connectionNames();
}
else{
qDebug() <<"connected to DB";
m_insertDataBase.open();
//qDebug() << m_insertDataBase.lastError();
}

m_insertDataBase.open();
QSqlQuery m_insertQuery(m_insertDataBase);
m_insertQuery.prepare("INSERT INTO userlogevents (firstName, lastName, userName, eventMessage, dateTime) VALUES('John', 'Doe', 'JohnnyD', ':eventMessage', datetime(current_timestamp))");
m_insertQuery.bindValue(":eventMessage", msg);
m_insertQuery.exec();
qDebug() << m_insertQuery.lastError();
//m_insertDataBase.close();
//QSqlDatabase::removeDatabase("conn1");
}

[anda_skoa]

You can call open inside the same if(), or after the whole if/else block.

There is no point in calling open() before it is valid and there is no point in passing the instance to QSqlQuery before it is opened.

There is also no point in using prepare if you are still just concenating strings instead of passing the msg value as via bindValue().

You also might want to think what your while loop is doing, in particular the life time of the "model" object and which class you are in.

Cheers,
_

[jfinn88]

There is also no point in using prepare if you are still just concenating strings instead of passing the msg value as via bindValue().


I updated this right before you posted this. I'm using the bind value method now with the prepare method



There is no point in calling open() before it is valid and there is no point in passing the instance to QSqlQuery before it is opened.


Now that you point it out that makes sense will move open() call inside if() statement, since the if statement is checking for validation would you also pass the instance to QSqlQuery inside the if() block?


//---------Inserts data into database (XMUI Global Funciton)--------------//
void XMUI::insertLogMessage(QString msg)
{
//qDebug() << m_insertDataBase.connectionNames();

if(!m_insertDataBase.isValid()){
qDebug() <<"error in opening DB";
m_insertDataBase = QSqlDatabase::addDatabase("QSQLITE", "conn1");
m_insertDataBase.setDatabaseName("/home/amet/userLog.db");
qDebug() << m_insertDataBase.connectionNames();
}
else{
qDebug() <<"connected to DB";
m_insertDataBase.open();
//qDebug() << m_insertDataBase.lastError();
}

m_insertDataBase.open();
QSqlQuery m_insertQuery(m_insertDataBase);
m_insertQuery.prepare("INSERT INTO userlogevents (firstName, lastName, userName, eventMessage, dateTime) VALUES('John', 'Doe', 'JohnnyD', :eventMessage, datetime(current_timestamp))");
m_insertQuery.bindValue(":eventMessage", msg);
m_insertQuery.exec();
qDebug() << m_insertQuery.lastError();
//m_insertDataBase.close();
//QSqlDatabase::removeDatabase("conn1");
}
//----------------------------------------------------------------//


Having trouble with connection to database... app crashes. I use a default connection for a user login and close it in the destructor, once the user logins in a series of method calls to insertLogMessage() takes place It seems like it never changes connection from default to conn1...

Tried placing some qdebug error checks:
qDebug() << m_insertDataBase.lastError(); & qDebug() << m_insertQuery.lastError(); but I dont get any errors back: QSqlError("", "", "") I list the connection names using
qDebug() << m_insertDataBase.connectionNames(); it shows the default connection and the conn1 connection | verified insert query pushes to database data is showing up now!

do I need to close or remove the database?

Now I need help fixing my function in my other class



void UserEventLog::dbConnect() {

if(!m_selectDataBase.isValid()){
qDebug() << "error in opening DB";
m_selectDataBase = QSqlDatabase::addDatabase("QSQLITE", "conn2");
m_selectDataBase.setDatabaseName("/home/amet/userLog.db");
}
else{
qDebug() <<"connected to DB" ;
}
m_selectDataBase.open();
}
void UserEventLog::sqlSelect() {

//---Check is Database is valid---//
if(!m_selectDataBase.isValid())
{
qDebug() << "error in opening DB";
m_selectDataBase = QSqlDatabase::addDatabase("QSQLITE", "conn2");
m_selectDataBase.setDatabaseName("/home/amet/userLog.db");
m_selectDataBase.open();
}
else{
qDebug()<<"connected to DB";
m_selectDataBase.open();
}

//---Check if database is open---//
if(!m_selectDataBase.open())
{
qDebug() << "database was closed";
m_selectDataBase.open();
}
else{
qDebug() << "database is open";
}

QSqlQuery selectQuery("SELECT id, userName, eventMessage, dateTime FROM userlogevents");

//---Check if Sql Query Ran---//
if(selectQuery.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << selectQuery.lastError();
}

while (selectQuery.next()){
//---Instance of userEventLogMsg Class Struct---//
userEventLogMsg msg;
//---Instance of userEventlog Class---//
UserEventLog model;
//---Add query data to the msg class struct---//
msg.id = selectQuery.value(0).toString();
msg.username = selectQuery.value(1).toString();
msg.eventmessage = selectQuery.value(2).toString();
msg.datetime = selectQuery.value(3).toString();
//---Use model object to access an call addEvent()---//
model.addEvent(msg);
}
//m_selectDataBase.close();
//QSqlDatabase::removeDatabase("conn2");
}

[anda_skoa]

Now that you point it out that makes sense will move open() call inside if() statement, since the if statement is checking for validation would you also pass the instance to QSqlQuery inside the if() block?

No, I would leave the QSqlQuery outside.



do I need to close or remove the database?

No, you should be able to keep more than one connection at any given time.


Now I need help fixing my function in my other class




QSqlQuery selectQuery("SELECT id, userName, eventMessage, dateTime FROM userlogevents");


You likely want to use the m_selectDataBase for this query, no?





while (selectQuery.next()){
//---Instance of userEventLogMsg Class Struct---//
userEventLogMsg msg;
//---Instance of userEventlog Class---//
UserEventLog model;
//---Add query data to the msg class struct---//
msg.id = selectQuery.value(0).toString();
msg.username = selectQuery.value(1).toString();
msg.eventmessage = selectQuery.value(2).toString();
msg.datetime = selectQuery.value(3).toString();
//---Use model object to access an call addEvent()---//
model.addEvent(msg);
}

This doesn't look at all what I assume you want.
This creates a new model instance in every loop iteration, adds one record and then destroys the model (when it goes out of scope).

Cheers,
_

[jfinn88]

No, I would leave the QSqlQuery outside.


Okay, That makes sense played with it and I declare it before the if() block.



No, you should be able to keep more than one connection at any given time.


Okay, so I'm fine with leaving conn1, and conn2 connected.



You likely want to use the m_selectDataBase for this query, no?


I can pass it with the sql statement correct ?



QSqlQuery selectQuery("SELECT id, userName, eventMessage, dateTime FROM userlogevents", m_selectDataBase);




This doesn't look at all what I assume you want.
This creates a new model instance in every loop iteration, adds one record and then destroys the model (when it goes out of scope).


you are correct I do not want to create a new object every time it goes threw while loop moved object declaration above while loop. Will calling addEvent () in the while loop be fine or should it be called once after the while loop is done putting data in struct?

I’m still little confused how the virtual functions get called... from my understanding is when the instance variable of my class is created (to pass the model to C++ by setting rootContext item) it makes a call to the constructor and when the constructor gets called the virtual functions get called (trying to clear any confuse I still have with models and virtual functions) ?


//---Instance of userEventLogMsg Class Struct---//
userEventLogMsg msg;

while (selectQuery.next()){
//---Add query data to the userEventLogMsg class struct---//
msg.id = selectQuery.value(0).toString();
msg.username = selectQuery.value(1).toString();
msg.eventmessage = selectQuery.value(2).toString();
msg.datetime = selectQuery.value(3).toString();
//---Use model object to access an call addEvent()---//
addEvent(msg);
}


Also I want to call dbConnect and selectQuery() methods from QML (button click)
in the header file I declare the function as Q_INVOKABLE now I know I could register the QML type to make the class accessible in QML and import it in the QML to call the function but I think I would have to make the class global object? is there a simpler way to do this ?

I have seen an example with Q_INVOKABLE and setting the context property, but I get confused on what engine object/class I need to use to set it. I don't quit understand the rootContext methodology yet and have a QML engine already defined and in use that I don't want to mess up, Im not sure if replacing what is currently on the engine stack is the right way todo it? or if using a new engine object is correct... would I need to create an instance object of a qml engine in my userEvetnLog class? (how can I tell what object this is pointing to?)



mUserEventLogModel = new UserEventLog();
m_QmlEngine->rootContext()->setContextProperty("UserEventLog", mUserEventLogModel);


In my QML I call my funtions



Action {
id: action_userEventLogBtn
enabled:!inSequence
onTriggered:{
//----Code to Load User Event Database into tableView-----//
input_loader.filename = ""
UserEventLog.dbConnect();
UserEventLog.sqlSelect();
weld_view.state = "USEREVENT"
onLoaded: console.log("User Event Log");


update: Okay I got my functions working in QML used previously defined QQmlEngine and instance of UserEventLog class that was used to make my model available to QML

update: made a stupid mistake when calling my addEvent function and now just realized it. Was getting a segfault fixed by removing UserEventLog object and just calling addEvent since Im in the class.

[anda_skoa]

I can pass it with the sql statement correct ?

Yes.



Will calling addEvent () in the while loop be fine or should it be called once after the while loop is done putting data in struct?

In every loop iteration.
You can basically keep your code, but instead of calling addEvent() on a different model you just call addEvent() of the current object.



I’m still little confused how the virtual functions get called... from my understanding is when the instance variable of my class is created (to pass the model to C++ by setting rootContext item) it makes a call to the constructor and when the constructor gets called the virtual functions get called (trying to clear any confuse I still have with models and virtual functions) ?

When you create an object, the constructor for that class is called.

Once the object is created, calls to virtual functions always end up in the most specific subclass.
E.g. if you overwrite roleNames() a call to roleNames() will end up calling your implementation.



Also I want to call dbConnect and selectQuery() methods from QML (button click)

For what purpose?
I can see a use case of re-running the query on demand, but dbConnect?
dbConnect() is really internal, no? It has to be called before database access happens, so the class itself should know when to call it.



in the header file I declare the function as Q_INVOKABLE now I know I could register the QML type to make the class accessible in QML and import it in the QML to call the function but I think I would have to make the class global object? is there a simpler way to do this ?

That is unnecessary.
Q_INVOKABLE methods and slots are already accessible as functions on an object set as a context property.
Registration of a type is only necessary if instances of that type need to be created from QML or if it has enum definitions where the QML code should be able to use the names.



I have seen an example with Q_INVOKABLE and setting the context property, but I get confused on what engine object/class I need to use to set it.

You don't have to change anything in your code.

Cheers,
_

[jfinn88]

Thank you for all the help anda_skoa,
Model seems to be working good now I will posted updated code
I'm now wanting to add some functionality to my tableView, Like to be able to search by date and userName... I want to pass text values back to my c++ function a would like to use the signal and slot mechanism to perform this action. However I get lost with what Object I need to pass the QObject::connect() method, for it to work.
I created two signals and two slots one for the date text field in qml and the other is for the userName text field in qml.
in C++ I created to slots to receive the qml signals and to out put the text data passed to the console using a debug() method.
I'm little confused on where to place the QObject::connect method in my project... main.cpp or sqliteModel.cpp in the constructor ?
I am unsure what objects to pass the connect() method for the signal to receive the slot ?
I would also Like to create a table in the database for each separate day for user event logging and delete old tables after 30 days
here is my code: (had to remove functions for model in cpp file to get it to fit)

=========== main.qml===============


import QtQuick 2.5
import QtQuick.Layouts 1.1
import QtQuick.Controls 1.3
import QtQuick.Window 2.2
import QtQuick.Dialogs 1.2
import QtQuick.Layouts 1.1
import QtQuick.Controls 1.4

Window {
signal submitDateText(string text)
signal submitUserNameText(string text)
visible: true
width: 760
height: 450
title: "User Event Log"
TableView {
width: 750;
height: 350;
anchors.centerIn: parent;
horizontalScrollBarPolicy: 0
frameVisible: true
model: sqliteModel
TableViewColumn {
role: "id"
title: "id"
width: 100
}
TableViewColumn {
role: "userName"
title: "User Name"
width: 200
}
TableViewColumn {
role: "eventMessage"
title: "Event Message"
width: 200
}
TableViewColumn {
role: "dateTime"
title: "Date Time"
width: 200
}
}
RowLayout {
id: row1
x: 201
y: 403
anchors.horizontalCenter: parent.horizontalCenter;
anchors.bottom: parent.bottom
width: 750
height: 47;
clip: false
opacity: 0.9
Button {
id: load_btn
text: qsTr("Load")
MouseArea{
anchors.fill: parent
onClicked: {
sqliteModel.dbConnect();
sqliteModel.sqlSelect();
}
}
}
Label {
id: userNameLabel
text: qsTr("User Name")
}
TextField {
id: userNameTextField
placeholderText: qsTr("User Name")
}
Label {
id: dateLabel
width: 39
height: 17
text: qsTr("Date")
}
TextField {
id: dateTextField
width: 125
height: 25
placeholderText: qsTr("mm//dd/yyyy")
}
Button {
id: searchBtn
text: qsTr("Search")
MouseArea{
anchors.fill: parent
onClicked: {
// emit the submitTextField signal
submitDateText(dateTextField.text);
submitUserNameText(userNameTextField.text);
}
}
}
Button {
id: exit_btn
text: qsTr("Exit")
MouseArea{
anchors.fill: parent
onClicked: close();
}
}
}
}


=========== main.cpp===============


#include <QGuiApplication>
#include <QQmlApplicationEngine>
#include <QSqlDatabase>
#include "sqlitemodel.h"
#include <QUrl>

int main(int argc, char *argv[])
{
QGuiApplication app(argc, argv);
sqliteModel *model = new sqliteModel;
QQmlApplicationEngine engine;
QQmlContext *contxt = engine.rootContext();
contxt->setContextProperty("sqliteModel", model);
engine.load(QUrl("qrc:/main.qml"));
return app.exec();
}


=========== sqliteModel.cpp==========


#include "sqlitemodel.h"

sqliteModel::sqliteModel(QObject *parent):QAbstractListModel(parent)
{
// connect our QML signal to our C++ slot
QObject::connect(/*QMLObject(?)*/, SIGNAL(submitDateText(QString)), this, SLOT(searchDateText(QString)));
}

sqliteModel::~sqliteModel()
{
}

void sqliteModel::createDailyTable()
{
dbConnect();
int addOne;
QSqlQuery createTableQry(m_selectDataBase);
createTableQry.prepare("CREATE TABLE userlogevents1 AS SELECT * FROM userlogevents WHERE 0");
createTableQry.exec();
m_selectDataBase.close();
}

void sqliteModel::deleteDailyTable()
{
dbConnect();
QSqlQuery selectTables(m_selectDataBase);
//---Selects all tables older than 30 days in database | Gets date created---//
selectTables.prepare("SELECT usereventlog, create_date FROM sys.tables WHERE DATEDIFF(day, create_date, getdate()) > 30");
selectTables.exec();
QString selectTableResult;
selectTableResult = selectTables.value(0).toString();
selectTableResult.append(selectTables.value(1).toS tring());
selectTableResult.append(selectTables.value(2).toS tring());
qDebug() << selectTableResult;

//--- If the table is older than 30 days drop it---//
QSqlQuery deleteTableQry(m_selectDataBase);
deleteTableQry.prepare("DROP TABLE userlogevetns");
deleteTableQry.exec();
m_selectDataBase.close();
}

void sqliteModel::searchDateFcn(QString dateText)
{
dbConnect();
QSqlQuery searchDateQry(m_selectDataBase);
searchDateQry.prepare("SELECT id, userName, eventMessage, dateTime FROM usereventlog WHERE userName = "+dateText);
searchDateQry.exec();
m_selectDataBase.close();
}

void sqliteModel::searchUserNameFcn(QString userNameText)
{
dbConnect();
QSqlQuery searchDateQry(m_selectDataBase);
searchDateQry.prepare("SELECT id, userName, eventMessage, dateTime FROM usereventlog WHERE userName = "+ userNameText);
searchDateQry.exec();
m_selectDataBase.close();
}

void sqliteModel::searchDateText(const QString &dateText)
{
qDebug() << "c++: sqliteModel::searchDateText:" << dateText;
}

void sqliteModel::searchUserNameText(const QString &userNameText)
{
qDebug() << "c++: sqliteModel::searchUserNameText:" << userNameText;
}


=========== sqliteModel.h===============


#ifndef SQLITEMODEL_H
#define SQLITEMODEL_H

#include <assert.h>
#include <list>
#include <QList>
#include <QColor>
#include <QObject>
#include <QDebug>
#include <QString>
#include <QFileInfo>
#include <QDateTime>
#include <QQmlError>
#include <QQmlApplicationEngine>
#include <QQmlEngine>
#include <QQmlContext>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>
#include <QtSql/QSqlRecord>
#include <QModelIndex>
#include <QAbstractListModel>

struct userEventLogMsg{
QString id;
QString username;
QString eventmessage;
QString datetime;
};

class sqliteModel:public QAbstractListModel
{
Q_OBJECT
public:
explicit sqliteModel(QObject *parent = 0);
~sqliteModel();
enum userEventRoles {idRole= Qt::UserRole + 220, nameRole, msgRole, dateRole};
int rowCount(const QModelIndex & parent) const;
QHash<int, QByteArray> roleNames() const;
QVariant data(const QModelIndex &index, int role=Qt::DisplayRole) const;
Q_INVOKABLE void addEvent(const userEventLogMsg &msg);
Q_INVOKABLE void dbConnect();
Q_INVOKABLE void sqlSelect();
void createDailyTable();
void deleteDailyTable();
void searchDateFcn(QString userDateText);
void searchUserNameFcn(QString userNameText);
public slots:
void searchDateText(const QString &dateIn);
void searchUserNameText(const QString &userNameIn);
private:
QList<userEventLogMsg> m_msgList;
QSqlDatabase m_selectDataBase;
QSqlQuery m_selectQuery;
};


I was able to pass the text form qml to my cpp slot by expose Qt slot to QML element. By using the context property I set for my model to be exposed to QML.

now my searchDateText fcn and searchUserName fcn can be called in qml by:


Button {
id: searchBtn
text: qsTr("Search")
MouseArea{
anchors.fill: parent
onClicked: {
sqliteModel.searchDateText(dateTextField.text);
sqliteModel.searchUserNameText(userNameTextField.t ext);
}
}
}


(?) I did the same for my dbConnect and sqlSelect fcns however I have to include Q_INVOKABLE when intializing them in the header file or for some reason or I cant call them in QML with out the Q_INVOKABLE keyword but my searchDateText & searchUserNameText fucntions do??? -> error: Property 'dbConnect' of object sqliteModel() is not a function

I would like to pass the QML text using a QML signal to a CPP slot using the QObject::connect() mainly for learning purposes... The objects that I need to pass the connect() method make more sense now, I need to pass a QML object for the qml signal() and pass a class object (sqliteModel) for the slot(), if I use the connect() method in my cpp class (in the constructor) I can use keyword "this" for the CPP object for the connect method?

[anda_skoa]

Like to be able to search by date and userName... I want to pass text values back to my c++ function a would like to use the signal and slot mechanism to perform this action. However I get lost with what Object I need to pass the QObject::connect() method, for it to work.
I created two signals and two slots one for the date text field in qml and the other is for the userName text field in qml.
in C++ I created to slots to receive the qml signals and to out put the text data passed to the console using a debug() method.
I'm little confused on where to place the QObject::connect method in my project... main.cpp or sqliteModel.cpp in the constructor ?
I am unsure what objects to pass the connect() method for the signal to receive the slot ?

You just call the slots directly.
Slots, like Q_INVOKABLE methods, can be called from QML.

Your QML code suggests that you want to pass both date and user name when clicking search, so a single slot with two arguments will do.



I would also Like to create a table in the database for each separate day for user event logging and delete old tables after 30 days
here is my code: (had to remove functions for model in cpp file to get it to fit)

I guess you have two options:
- use an additional table that maps from a date to a table name for that date
- encode the date in the table name



I was able to pass the text form qml to my cpp slot by expose Qt slot to QML element. By using the context property I set for my model to be exposed to QML.

Yes, exactly, though you probably want to use a single method if both inputs are to be used in the search.



(?) I did the same for my dbConnect and sqlSelect fcns however I have to include Q_INVOKABLE when intializing them in the header file or for some reason or I cant call them in QML with out the Q_INVOKABLE keyword but my searchDateText & searchUserNameText fucntions do??? -> error: Property 'dbConnect' of object sqliteModel() is not a function

They have to be slots or Q_INVOKABLE.

From the QML side's point of view there is actually no difference.
It is customary though to use Q_INVOKABLE when methods return something, as slots usually don't do that.

In your case all methods you have so far could be slots.



I would like to pass the QML text using a QML signal to a CPP slot using the QObject::connect() mainly for learning purposes...

I would advise against that, you would put effort into learning something that you then don't want to use.
I.e. you don't want your C++ code to be dependent on specific QML objects or their specific signals.



The objects that I need to pass the connect() method make more sense now, I need to pass a QML object for the qml signal() and pass a class object (sqliteModel) for the slot()

Yes, but you really don't want to do that.



if I use the connect() method in my cpp class (in the constructor) I can use keyword "this" for the CPP object for the connect method?
Theoretically yes, but the QML scene has not been loaded yet when the model's constructor runs, so the connect would have to happen from outside after both model and QML objects exist.

Cheers,
_

[jfinn88]

So I been reading an working on figuring out how to implement a sorting feature using sortProxyFilter class But I'm little confused on how it works with the model... I know its filters between the QML and the model data. I want to search by userName (separately) want to search by Date (separately) and then be able to search by userName and date... I was thinking at first I could use sql script just to select the data from the data base then display using model but not sure how that would work with re-running slq statement then refreshing model. Seems like every one uses a ProxyFilter class to filter tableView.

I started by creating a Proxy Filter class and subclass QSortProxyFilter and start of with overriding the sortAsspectRow() method but relaized that will just filter the column and not use the search fields given, can you help me get started in the right direction for what I need? I'm not sure if I should be passing a string to compare with the filter? I need to set the filterString and use it to filter tableView (evaluate the data based on a string)

============= Main.cpp ===========


#include <QGuiApplication>
#include <QQmlApplicationEngine>
#include <QSqlDatabase>
#include <QAbstractTableModel>
#include <QAbstractItemModel>
#include "sqlitemodel.h"
#include "sortproxyfilter.h"
#include <QUrl>

int main(int argc, char *argv[])
{
QGuiApplication app(argc, argv);

sqliteModel *model = new sqliteModel;

sortProxyFilter *myProxyFilter = new sortProxyFilter;
myProxyFilter->setSourceModel(model);

QQmlApplicationEngine engine;
QQmlContext *contxt = engine.rootContext();
contxt->setContextProperty("sqliteModel", model);
contxt->setContextProperty("proxyFilter", myProxyFilter);
engine.load(QUrl("qrc:/main.qml"));

//---Insert Proxy Model between model and view---//
//proxy = new QSortFilterProxyModel(parent);
//proxy->setSourceModel(model);
//engine.setModel(proxy);

return app.exec();
}


===========sortProxyFilter.cpp=============


#include "sortproxyfilter.h"
#include "sqlitemodel.h"

sortProxyFilter::sortProxyFilter(QObject *parent):QSortFilterProxyModel(parent)
{

}

sortProxyFilter::~sortProxyFilter()
{

}

//Need to figure out what functions I will need (filterString(), setFilterString etc...)



============sortProxyFilter.h============


#ifndef SORTPROXYFILTER_H
#define SORTPROXYFILTER_H
#include <QSortFilterProxyModel>


class sortProxyFilter:public QSortFilterProxyModel
{
public:
explicit sortProxyFilter(QObject *parent = 0);

~sortProxyFilter();

//add filter string functions

signals:

public slots:

private:
};
#endif // SORTPROXYFILTER_H



===========slqiteModel.cpp============


#include "sqlitemodel.h"

sqliteModel::sqliteModel(QObject *parent):QAbstractListModel(parent){

}

sqliteModel::~sqliteModel(){

}

int sqliteModel::rowCount(const QModelIndex &parent) const{
Q_UNUSED(parent);
return m_msgList.count();
qDebug()<< m_msgList.count();
}

QHash<int, QByteArray> sqliteModel::roleNames() const{
QHash<int, QByteArray> roleNames;
roleNames.insert(idRole, "id");
roleNames.insert(nameRole, "userName");
roleNames.insert(msgRole, "eventMessage");
roleNames.insert(dateRole, "dateTime");
qDebug()<< roleNames;
return roleNames;
}

QVariant sqliteModel::data(const QModelIndex &index, int role) const
{
if (index.row() < 0 || index.row() >= m_msgList.count()){
return QVariant();
}
QVariant text;
if(role == idRole){
userEventLogMsg msg = m_msgList[index.row()];
text = msg.id;
qDebug() << text;
}
else if(role == nameRole){
userEventLogMsg msg = m_msgList[index.row()];
text = msg.username;
qDebug() << text;
}
else if(role == msgRole){
userEventLogMsg msg = m_msgList[index.row()];
text = msg.eventmessage;
qDebug() << text;
}
if(role == dateRole){
userEventLogMsg msg = m_msgList[index.row()];
text = msg.datetime;
qDebug() << text;
}
return text;
}

void sqliteModel::addEvent(const userEventLogMsg &msg){
beginInsertRows(QModelIndex(), 0, 0);
m_msgList.insert(0, msg);
endInsertRows();
}

void sqliteModel::dbConnect() {
if(!m_selectDataBase.isValid()){
qDebug() << "error in opening DB";
m_selectDataBase = QSqlDatabase::addDatabase("QSQLITE", "conn2");
m_selectDataBase.setDatabaseName("/home/amet/userLog.db");
}
else{
qDebug() <<"connected to DB" ;
}
m_selectDataBase.open();
}
void sqliteModel::sqlSelect() {
dbConnect();
if(!m_selectDataBase.open())
{
qDebug() << "database was closed";
m_selectDataBase.open();
}
else{
qDebug() << "database is open";
}
QSqlQuery selectQuery("SELECT id, userName, eventMessage, dateTime FROM userlogevents", m_selectDataBase);
if(selectQuery.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << selectQuery.lastError();
}
userEventLogMsg msg;
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).toString();
addEvent(msg);
}
m_selectDataBase.close();
}

void sqliteModel::createDailyTable()
{
dbConnect();
QSqlQuery createTableQry(m_selectDataBase);
createTableQry.prepare("CREATE TABLE userlogevents1 AS SELECT * FROM userlogevents WHERE 0");
createTableQry.exec();
m_selectDataBase.close();
}

void sqliteModel::deleteDailyTable()
{
dbConnect();
QSqlQuery selectTables(m_selectDataBase);
//---Selects all tables older than 30 days in database | Gets date created---//
selectTables.prepare("SELECT usereventlog, create_date FROM sys.tables WHERE DATEDIFF(day, create_date, getdate()) > 30");
selectTables.exec();
QString selectTableResult;
selectTableResult = selectTables.value(0).toString();
selectTableResult.append(selectTables.value(1).toS tring());
selectTableResult.append(selectTables.value(2).toS tring());
qDebug() << selectTableResult;
//--- If the table is older than 30 days drop it---//
QSqlQuery deleteTableQry(m_selectDataBase);
deleteTableQry.prepare("DROP TABLE userlogevetns");
deleteTableQry.exec();
m_selectDataBase.close();
}

void sqliteModel::searchDateText(const QString &dateText)
{
qDebug() << "c++: sqliteModel::searchDateText:" << dateText;
dbConnect();
QSqlQuery searchDateQry(m_selectDataBase);
searchDateQry.prepare("SELECT id, userName, eventMessage, dateTime FROM usereventlog WHERE dateTime = "+ dateText);
searchDateQry.exec();
m_selectDataBase.close();
}

void sqliteModel::searchUserNameText(const QString &userNameText)
{
qDebug() << "c++: sqliteModel::searchUserNameText:" << userNameText;
dbConnect();
QSqlQuery searchDateQry(m_selectDataBase);
searchDateQry.prepare("SELECT id, userName, eventMessage, dateTime FROM usereventlog WHERE userName = "+ userNameText);
searchDateQry.exec();
m_selectDataBase.close();
}


===========sqliteModel.h===========


#ifndef SQLITEMODEL_H
#define SQLITEMODEL_H
#include <assert.h>
#include <list>
#include <QList>
#include <QColor>
#include <QObject>
#include <QDebug>
#include <QString>
#include <QDateTime>
#include <QQmlError>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>
#include <QtSql/QSqlRecord>
#include <QModelIndex>
#include <QAbstractListModel>

struct userEventLogMsg{
QString id;
QString username;
QString eventmessage;
QString datetime;
};

class sqliteModel:public QAbstractListModel
{
Q_OBJECT
public:
explicit sqliteModel(QObject *parent = 0);
~sqliteModel();
enum userEventRoles {idRole= Qt::UserRole + 220, nameRole, msgRole, dateRole};
int rowCount(const QModelIndex & parent) const;
QHash<int, QByteArray> roleNames() const;
QVariant data(const QModelIndex &index, int role=Qt::DisplayRole) const;
Q_INVOKABLE void addEvent(const userEventLogMsg &msg);
Q_INVOKABLE void dbConnect();
Q_INVOKABLE void sqlSelect();
void createDailyTable();
void deleteDailyTable();
public slots:
void searchDateText(const QString &dateIn);
void searchUserNameText(const QString &userNameIn);
private:
QList<userEventLogMsg> m_msgList;
QSqlDatabase m_selectDataBase;
QSqlQuery m_selectQuery;
};
#endif // SQLITEMODEL_H


=========Main QML============


import QtQuick 2.5
import QtQuick.Layouts 1.1
import QtQuick.Controls 1.3
import QtQuick.Window 2.2
import QtQuick.Dialogs 1.2
import QtQuick.Layouts 1.1
import QtQuick.Controls 1.4

Window {
signal submitDateText(string text)
signal submitUserNameText(string text)

visible: true
width: 760
height: 450
title: "User Event Log"
TableView {
model: sqliteModel
width: 750;
height: 350;
anchors.centerIn: parent;
horizontalScrollBarPolicy: 0
frameVisible: true
//sortIndicatorColumn : 1
//sortIndicatorVisible: true
TableViewColumn {
role: "id"
title: "id"
width: 100
}
TableViewColumn {
role: "userName"
title: "User Name"
width: 200
}
TableViewColumn {
role: "eventMessage"
title: "Event Message"
width: 200
}
TableViewColumn {
role: "dateTime"
title: "Date Time"
width: 200
}
}
RowLayout {
id: row1
x: 201
y: 403
anchors.horizontalCenter: parent.horizontalCenter;
anchors.bottom: parent.bottom
width: 750
height: 47;
clip: false
opacity: 0.9
Button {
id: load_btn
text: qsTr("Load")
MouseArea{
anchors.fill: parent
onClicked: {
sqliteModel.sqlSelect();
}
}
}
Label {
id: userNameLabel
text: qsTr("User Name")
}
TextField {
id: userNameTextField
placeholderText: qsTr("User Name")
}
Label {
id: dateLabel
width: 39
height: 17
text: qsTr("Date")
}
TextField {
id: dateTextField
width: 125
height: 25
placeholderText: qsTr("mm//dd/yyyy")
}
Button {
id: searchBtn
text: qsTr("Search")
MouseArea{
anchors.fill: parent
onClicked: {
//---emit the submitDateText & submitUserNameText signal---//
//sqliteModel.searchDateText(dateTextField.text);
//sqliteModel.searchUserNameText(userNameTextField.t ext);
}
}
}
Button {
id: exit_btn
text: qsTr("Exit")
MouseArea{
anchors.fill: parent
onClicked: close();
}
}
}
}

[jfinn88]

Updated function went back to using sql statements to filter results not sure if this is a good way however it works well for the most part... If this way seems fine is there a better way to refresh my view/model?



void sqliteModel::searchDateText(const QString &dateText)
{
qDebug() << "c++: sqliteModel::searchDateText:" << dateText;

dbConnect();

if(!m_selectDataBase.open())
{
qDebug() << "database was closed";
m_selectDataBase.open();
}
else{
qDebug() << "database is open";
}

QSqlQuery selectQuery(m_selectDataBase);
selectQuery.prepare("SELECT id, userName, eventMessage, dateTime FROM userlogevents WHERE userName = ?");
selectQuery.addBindValue(dateText);


if(selectQuery.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << selectQuery.lastError();
}

userEventLogMsg msg;

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).toString();
addEvent(msg);
}
m_selectDataBase.close();
}

void sqliteModel::searchUserNameText(const QString &userNameText)
{
qDebug() << "c++: sqliteModel::searchUserNameText:" << userNameText;

dbConnect();

if(!m_selectDataBase.open())
{
qDebug() << "database was closed";
m_selectDataBase.open();
}
else{
qDebug() << "database is open";
}

QSqlQuery selectQuery(m_selectDataBase);
selectQuery.prepare("SELECT id, userName, eventMessage, dateTime FROM userlogevents WHERE userName = ?");
selectQuery.addBindValue(userNameText);


if(selectQuery.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << selectQuery.lastError();
}

userEventLogMsg msg;

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).toString();
addEvent(msg);
}
m_selectDataBase.close();
}

[anda_skoa]

Both functions have a query that uses userName as the parameter, so both search for userName.

If you are replacing a model's content, the best way is to use begin/end reset model and just do all the data changes in betwee.



beginResetModel();

m_userEventList.clear(); // get rid of old content

// run query

while (... ) {
userEventLogMsg msg;
// fill msg
m_userEventList << msg;
}

endResetModel();


For the proxy model approach you would have to store the current filter strings, implement the filterAcceptsRow() function and use these strings and call invalidateFilter() whenever the strings change.

Cheers,
_

[jfinn88]

Both functions have a query that uses userName as the parameter, so both search for userName.


Yeah sorry was just copy error



If you are replacing a model's content, the best way is to use begin/end reset model and just do all the data changes in betwee.



beginResetModel();

m_userEventList.clear(); // get rid of old content

// run query

while (... ) {
userEventLogMsg msg;
// fill msg
m_userEventList << msg;
}

endResetModel();


For the proxy model approach you would have to store the current filter strings, implement the filterAcceptsRow() function and use these strings and call invalidateFilter() whenever the strings change.

Cheers,
_

Which approach would you recommend?

I added a comboBox to my QML to select the different tables (~30 days, one table for each day) from the database and display the table in the tableView and if the user needs to can search the table based on date user name would it be fine to pass the select item from the combo box to the select query to display the data or would it need to be refreshed like you describe above with resetModel?

I want to display the results of a certain query in the comboBox (list of database tables) based of the selection change the model...


selectTables.prepare("SELECT name, create_date FROM sqlite_master WHERE DATEDIFF(day, create_date, getdate()) > 30");

[anda_skoa]

It depends.

If you need to retain current item/selection, then resetting the model is out of question.

If you want to filter as you type, then I would filter in the model itself but implement filtering manually.

If you only need to apply the filter on a trigger, e.g. on button click, then the easiest way is to reset the model to that query and let the database do the filtering.

Cheers,
_

[jfinn88]

having issue with using a second model for display tableNames in comboBox

Think it has to do with tableNameComboBox() method that call my addTableName()

=====main.qml====


import QtQuick 2.5
import QtQuick.Layouts 1.1
import QtQuick.Controls 1.3
import QtQuick.Window 2.2
import QtQuick.Dialogs 1.2
import QtQuick.Layouts 1.1
import QtQuick.Controls 1.4

Window {
signal submitDateText(string text)
signal submitUserNameText(string text)
signal submitDatabaseTable(string text)

visible: true
width: 760
height: 450
title: "User Event Log"
RowLayout {
id: comboBoxRowLayout
anchors.topMargin: 25
anchors.leftMargin: 25
anchors.horizontalCenter: parent.horizontalCenter
Label {
id: comboLabel
width: 150
height: 25
text: qsTr("Select Database/Date")
verticalAlignment: Text.AlignVCenter
}
ComboBox {
id: dataBaseComboBox
width: 150
height: 25
model: tableNameModel
textRole: "tableName"
//MouseArea{
//anchors.fill: parent
//onClicked: {
//tableNameModel.tableNameComboBox();
//}
//}
}
}
TableView {
width: 750;
height: 350;
anchors.centerIn: parent;
horizontalScrollBarPolicy: 0
frameVisible: true
model: sqliteModel
//sortIndicatorColumn : 1
//sortIndicatorVisible: true
TableViewColumn {
role: "id"
title: "id"
width: 100
}
TableViewColumn {
role: "userName"
title: "User Name"
width: 200
}
TableViewColumn {
role: "eventMessage"
title: "Event Message"
width: 200
}
TableViewColumn {
role: "dateTime"
title: "Date Time"
width: 200
}
}
RowLayout {
id: searchRowLayout
x: 201
y: 403
anchors.horizontalCenter: parent.horizontalCenter;
anchors.bottom: parent.bottom
width: 750
height: 47;
clip: false
opacity: 0.9
Button {
id: load_btn
text: qsTr("Load")
MouseArea{
anchors.fill: parent
onClicked: {
sqliteModel.sqlSelect(tableName);
}
}
}
Label {
id: userNameLabel
text: qsTr("User Name")
}
TextField {
id: userNameTextField
placeholderText: qsTr("User Name")
}
Label {
id: dateLabel
width: 39
height: 17
text: qsTr("Date")
}
TextField {
id: dateTextField
width: 125
height: 25
placeholderText: qsTr("mm//dd/yyyy")
}
Button {
id: searchBtn
text: qsTr("Search")
MouseArea{
anchors.fill: parent
onClicked: {
//---emit the submitDateText & submitUserNameText signal---//
//sqliteModel.searchDateText(dateTextField.text);
//sqliteModel.searchUserNameText(userNameTextField.t ext);
sqliteModel.deleteDailyTable();
}
}
}
Button {
id: exit_btn
text: qsTr("Exit")
MouseArea{
anchors.fill: parent
onClicked: close();
}
}
}
}


======databasetables.cpp=====


#include "databasetables.h"

dataBaseTables::dataBaseTables(QObject *parent):QAbstractListModel(parent){

}

dataBaseTables::~dataBaseTables(){

}

int dataBaseTables::rowCount(const QModelIndex &parent) const{
Q_UNUSED(parent);
return m_tableNameList.count();
qDebug()<< m_tableNameList.count();
}

QHash<int, QByteArray> dataBaseTables::roleNames() const{
QHash<int, QByteArray> roleNames;
roleNames.insert(tableNameRole, "TableName");
qDebug()<< roleNames;
return roleNames;
}

QVariant dataBaseTables::data(const QModelIndex &index, int role) const{
if (index.row() < 0 || index.row() >= m_tableNameList.count()){
return QVariant();
}
QVariant text;
if(role == tableNameRole){
tableNames tables = m_tableNameList[index.row()];
text = tables.tableName;
qDebug() << text;
}
return text;
}

void dataBaseTables::addTableName(const tableNames &tables){
beginInsertRows(QModelIndex(), 0, 0);
m_tableNameList.insert(0, tables);
endInsertRows();
}

void dataBaseTables::dbConnect(){

if(!m_selectTableNames.isValid()){
qDebug() << "error in opening DB";
m_selectTableNames = QSqlDatabase::addDatabase("QSQLITE", "conn2");
m_selectTableNames.setDatabaseName("/home/amet/userLog.db");
}
else{
qDebug() <<"connected to DB" ;
}
m_selectTableNames.open();
}

QString dataBaseTables::tableNameComboBox(){
dbConnect();
QSqlQuery selectTables(m_selectTableNames);

//---Selects all tables older than 30 days in database | Gets date created---//
selectTables.prepare("SELECT name FROM sqlite_master WHERE type='table';");

if(selectTables.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << selectTables.lastError();
}

tableNames tables;
QString tableName;
while (selectTables.next()){
tables.tableName = selectTables.value(0).toString();
tableName = selectTables.value(0).toString();
qDebug() << "Table Results: "+tableName;
addTableName(tables);
}
m_selectTableNames.close();
return tableName;
}


=====databasetables.h===========


#ifndef DATABASETABLES_H
#define DATABASETABLES_H

#include <QString>
#include <QDebug>
#include <QQmlContext>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>
#include <QtSql/QSqlRecord>
#include <QModelIndex>
#include <QSqlDatabase>
#include <QAbstractListModel>
#include <QAbstractItemModel>

struct tableNames{
QString tableName;
};

class dataBaseTables: public QAbstractListModel
{
Q_OBJECT

public:
explicit dataBaseTables(QObject *parent = 0);

~dataBaseTables();

enum dataTableNameRoles {tableNameRole= Qt::UserRole + 220};

int rowCount(const QModelIndex & parent) const;

QHash<int, QByteArray> roleNames() const;

QVariant data(const QModelIndex &index, int role=Qt::DisplayRole) const;

Q_INVOKABLE void addTableName(const tableNames &tables);

Q_INVOKABLE void dbConnect();

Q_INVOKABLE QString tableNameComboBox();

public slots:

private:
QList<tableNames> m_tableNameList;
QSqlDatabase m_selectTableNames;
QSqlQuery m_tableNamesQuery;
};
#endif // DATABASETABLES_H


====main.cpp=====


#include <QGuiApplication>
#include <QQmlApplicationEngine>
#include <QSqlDatabase>
#include <QAbstractTableModel>
#include <QAbstractItemModel>
#include <QUrl>
#include "sqlitemodel.h"
#include "sortproxyfilter.h"
#include "databasetables.h"


int main(int argc, char *argv[])
{
QGuiApplication app(argc, argv);

sqliteModel *model = new sqliteModel;

dataBaseTables *model2 = new dataBaseTables;

QQmlApplicationEngine engine;
QQmlContext *contxt = engine.rootContext();
contxt->setContextProperty("sqliteModel", model);
contxt->setContextProperty("tableNameModel", model2);
engine.load(QUrl("qrc:/main.qml"));

return app.exec();
}

[anda_skoa]

Different capitalization in QML and C++ for the role: "tableName" vs. "TableName"

Cheers,
_

[jfinn88]

Fixed it, I thought that was the mistake but doesn't seem to fix the issue...


Different capitalization in QML and C++ for the role: "tableName" vs. "TableName"

Cheers,
_

still doesn't display...... run debugger and get seg fualt seems like its in my QML on my model

If I move my tableNameComboBox() method code to main it kinda works (seems like it only show one item)... need to figure out how to properly call function instead of using code in main

UPDATE: Looks Like it was a onCLick mouse area with my comboBox that was messing it up changed to onCurrentIndexChanged got rid of mouse area now list is showing in comboBox but thats with my code in main.cpp I would like to move it to a function call

I just call tableNameComboBox() method in main so I dont have to have all that code in main

so based off the table name the tableView should display the correct table. I Need to pass the table name selected in comboBox to my sqlSelect() method in my sqliteModel class (this function use the table name in sql the script to pull correct info from DB) but not quite sure how to do this

When I call the sqlSelect form comboBox onCurrentIndexChanged I get errors, do I pass the selection to the method correctly do I need to convert to string? or something, I think parameter mismatch is issue with number of columns in database compared to number columns passed by sql or mismatch column names from sql to db?


Errors accured with sql statement
QSqlError("", "Parameter count mismatch", "")


=====main.qml=====


import QtQuick 2.5
import QtQuick.Layouts 1.1
import QtQuick.Controls 1.3
import QtQuick.Window 2.2
import QtQuick.Dialogs 1.2
import QtQuick.Layouts 1.1
import QtQuick.Controls 1.4

Window {
id: window1
signal submitDateText(string text)
signal submitUserNameText(string text)
signal submitDatabaseTable(string text)

visible: true
width: 760
height: 450
title: "User Event Log"
Rectangle {
id: comboBoxRect
anchors.top: parent.top
anchors.horizontalCenter: parent.horizontalCenter
Label {
id: comboLabel
x: -181
y: 13
width: 150
height: 25
text: qsTr("Select Database/Date")
verticalAlignment: Text.AlignVCenter
}
ComboBox {
id: dataBaseComboBox
x: 0
width: 181
height: 25
anchors.top: parent.top
anchors.topMargin: 13
model: tableNameModel
textRole: "tableName"
onCurrentIndexChanged: {
sqliteModel.sqlSelect(currentIndex);
}
}
}
TableView {
width: 750;
height: 350;
anchors.centerIn: parent;
horizontalScrollBarPolicy: 0
frameVisible: true
model: sqliteModel
//sortIndicatorColumn : 1
//sortIndicatorVisible: true
TableViewColumn {
role: "id"
title: "id"
width: 100
}
TableViewColumn {
role: "userName"
title: "User Name"
width: 200
}
TableViewColumn {
role: "eventMessage"
title: "Event Message"
width: 200
}
TableViewColumn {
role: "dateTime"
title: "Date Time"
width: 200
}
}
RowLayout {
id: searchRowLayout
x: 201
y: 403
anchors.horizontalCenter: parent.horizontalCenter;
anchors.bottom: parent.bottom
width: 750
height: 47;
clip: false
opacity: 0.9
Button {
id: load_btn
text: qsTr("Load")
MouseArea{
anchors.fill: parent
onClicked: {
//sqliteModel.sqlSelect(tableName);
}
}
}
Label {
id: userNameLabel
text: qsTr("User Name")
}
TextField {
id: userNameTextField
placeholderText: qsTr("User Name")
}
Label {
id: dateLabel
width: 25
height: 15
text: qsTr("Date")
}
TextField {
id: dateTextField
width: 125
height: 25
placeholderText: qsTr("mm//dd/yyyy")
}
Button {
id: searchBtn
text: qsTr("Search")
MouseArea{
anchors.fill: parent
onClicked: {
//---emit the submitDateText & submitUserNameText signal---//
//sqliteModel.searchDateText(dateTextField.text);
//sqliteModel.searchUserNameText(userNameTextField.t ext);
//sqliteModel.deleteDailyTable();
}
}
}
Button {
id: exit_btn
text: qsTr("Exit")
MouseArea{
anchors.fill: parent
onClicked: close();
}
}
}
}


=====main.cpp=====


#include <QGuiApplication>
#include <QQmlApplicationEngine>
#include <QSqlDatabase>
#include <QAbstractTableModel>
#include <QAbstractItemModel>
#include <QUrl>
#include "sqlitemodel.h"
#include "sortproxyfilter.h"
#include "databasetables.h"


int main(int argc, char *argv[])
{
QGuiApplication app(argc, argv);

sqliteModel *model = new sqliteModel;

dataBaseTables *model2 = new dataBaseTables;

model2->tableNameComboBox();

QQmlApplicationEngine engine;
QQmlContext *contxt = engine.rootContext();
contxt->setContextProperty("sqliteModel", model);
contxt->setContextProperty("tableNameModel", model2);
engine.load(QUrl("qrc:/main.qml"));

return app.exec();
}

[anda_skoa]

I might have missed it but have you posted the code for the sqlSelect method somewhereß

Cheers,
_

[jfinn88]

yeah I think I did but ill put up latest version, I don't Think I'm getting the variable into the sql script correctly?

====sqliteModel.cpp=======


#include <QAbstractTableModel>
#include <QSortFilterProxyModel>
#include "sqlitemodel.h"

sqliteModel::sqliteModel(QObject *parent):QAbstractListModel(parent){

}

sqliteModel::~sqliteModel(){

}

int sqliteModel::rowCount(const QModelIndex &parent) const{
Q_UNUSED(parent);
return m_msgList.count();
qDebug()<< m_msgList.count();
}

QHash<int, QByteArray> sqliteModel::roleNames() const{
QHash<int, QByteArray> roleNames;
roleNames.insert(idRole, "id");
roleNames.insert(nameRole, "userName");
roleNames.insert(msgRole, "eventMessage");
roleNames.insert(dateRole, "dateTime");
qDebug()<< roleNames;
return roleNames;
}

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

QVariant text;

if(role == idRole){
userEventLogMsg msg = m_msgList[index.row()];
text = msg.id;
qDebug() << text;
}
else if(role == nameRole){
userEventLogMsg msg = m_msgList[index.row()];
text = msg.username;
qDebug() << text;
}
else if(role == msgRole){
userEventLogMsg msg = m_msgList[index.row()];
text = msg.eventmessage;
qDebug() << text;
}
if(role == dateRole){
userEventLogMsg msg = m_msgList[index.row()];
text = msg.datetime;
qDebug() << text;
}
return text;
}

void sqliteModel::addEvent(const userEventLogMsg &msg){
beginInsertRows(QModelIndex(), 0, 0);
m_msgList.insert(0, msg);
endInsertRows();
}

void sqliteModel::dbConnect(){

if(!m_selectDataBase.isValid()){
qDebug() << "error in opening DB";
m_selectDataBase = QSqlDatabase::addDatabase("QSQLITE", "conn2");
m_selectDataBase.setDatabaseName("/home/amet/userLog.db");
}
else{
qDebug() <<"connected to DB" ;
}
m_selectDataBase.open();
}
void sqliteModel::sqlSelect(QString tableName){

dbConnect();

if(!m_selectDataBase.open()){
qDebug() << "database was closed";
m_selectDataBase.open();
}
else{
qDebug() << "database is open";
}

QSqlQuery selectQuery(m_selectDataBase);
selectQuery.prepare("SELECT id, userName, eventMessage, dateTime FROM "+tableName+";");
selectQuery.addBindValue(tableName);

if(selectQuery.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << selectQuery.lastError();
}

userEventLogMsg msg;

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).toString();
addEvent(msg);
}
m_selectDataBase.close();
}


void sqliteModel::createDailyTable(QString tableName)
{
dbConnect();
QSqlQuery createTableQry(m_selectDataBase);
QString date = QDate::currentDate().toString();
createTableQry.prepare("CREATE TABLE userlogevents"+date+"AS SELECT * FROM ? WHERE 0");
createTableQry.addBindValue(tableName);
createTableQry.exec();
m_selectDataBase.close();
}

void sqliteModel::deleteDailyTable(QString tableName)
{
dbConnect();

//--- If the table is older than 30 days drop it---//
QSqlQuery deleteTableQry(m_selectDataBase);
deleteTableQry.prepare("DROP TABLE ?");
deleteTableQry.addBindValue(tableName);
if(deleteTableQry.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << deleteTableQry.lastError();
}
m_selectDataBase.close();
}

void sqliteModel::searchDateText(const QString &dateText)
{
qDebug() << "c++: sqliteModel::searchDateText:" << dateText;

dbConnect();

if(!m_selectDataBase.open())
{
qDebug() << "database was closed";
m_selectDataBase.open();
}
else{
qDebug() << "database is open";
}

QSqlQuery selectQuery(m_selectDataBase);
selectQuery.prepare("SELECT id, userName, eventMessage, dateTime FROM userlogevents WHERE dateText = ?");
selectQuery.addBindValue(dateText);


if(selectQuery.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << selectQuery.lastError();
}

userEventLogMsg msg;

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).toString();
addEvent(msg);
}
m_selectDataBase.close();
}

void sqliteModel::searchUserNameText(const QString &userNameText)
{
qDebug() << "c++: sqliteModel::searchUserNameText:" << userNameText;

dbConnect();

if(!m_selectDataBase.open())
{
qDebug() << "database was closed";
m_selectDataBase.open();
}
else{
qDebug() << "database is open";
}

QSqlQuery selectQuery(m_selectDataBase);
selectQuery.prepare("SELECT id, userName, eventMessage, dateTime FROM userlogevents WHERE userName = ?");
selectQuery.addBindValue(userNameText);


if(selectQuery.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << selectQuery.lastError();
}

userEventLogMsg msg;

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).toString();
addEvent(msg);
}
m_selectDataBase.close();
}


======sqliteModel.h=======


#ifndef SQLITEMODEL_H
#define SQLITEMODEL_H

#include <assert.h>
#include <list>
#include <QList>
#include <QColor>
#include <QObject>
#include <QDebug>
#include <QString>
#include <QFileInfo>
#include <QDateTime>
#include <QQmlError>
#include <QQmlApplicationEngine>
#include <QQmlEngine>
#include <QQmlContext>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>
#include <QtSql/QSqlRecord>
#include <QModelIndex>
#include <QAbstractTableModel>
#include <QAbstractListModel>
#include <QAbstractItemModel>
#include <QListIterator>

struct userEventLogMsg{
QString id;
QString username;
QString eventmessage;
QString datetime;
};

class sqliteModel:public QAbstractListModel
{
Q_OBJECT

public:
explicit sqliteModel(QObject *parent = 0);

~sqliteModel();

enum userEventRoles {idRole= Qt::UserRole + 220, nameRole, msgRole, dateRole};

int rowCount(const QModelIndex & parent) const;

QHash<int, QByteArray> roleNames() const;

QVariant data(const QModelIndex &index, int role=Qt::DisplayRole) const;

Q_INVOKABLE void addEvent(const userEventLogMsg &msg);

Q_INVOKABLE void dbConnect();

Q_INVOKABLE void sqlSelect(QString tableName);

Q_INVOKABLE void createDailyTable(QString tableName);

public slots:

void searchDateText(const QString &dateIn);

void searchUserNameText(const QString &userNameIn);

void deleteDailyTable(QString tableName);

private:
QList<userEventLogMsg> m_msgList;
QSqlDatabase m_selectDataBase;
QSqlQuery m_selectQuery;
};

#endif // SQLITEMODEL_H


====databasetables.cpp====


#include "databasetables.h"

dataBaseTables::dataBaseTables(QObject *parent):QAbstractListModel(parent){

}

dataBaseTables::~dataBaseTables(){

}

int dataBaseTables::rowCount(const QModelIndex &parent) const{
Q_UNUSED(parent);
return m_tableNameList.count();
qDebug()<< m_tableNameList.count();
}

QHash<int, QByteArray> dataBaseTables::roleNames() const{
QHash<int, QByteArray> roleNames;
roleNames.insert(tableNameRole, "tableName");
qDebug()<< roleNames;
return roleNames;
}

QVariant dataBaseTables::data(const QModelIndex &index, int role) const{
if (index.row() < 0 || index.row() >= m_tableNameList.count()){
return QVariant();
}
QVariant text;
if(role == tableNameRole){
tableNames tables = m_tableNameList[index.row()];
text = tables.tablename;
qDebug() << text;
}
return text;
}

void dataBaseTables::addTableName(const tableNames &tables){
beginInsertRows(QModelIndex(), 0, 0);
m_tableNameList.insert(0, tables);
endInsertRows();
}

void dataBaseTables::dbConnect(){

if(!m_selectTableNames.isValid()){
qDebug() << "error in opening DB";
m_selectTableNames = QSqlDatabase::addDatabase("QSQLITE", "conn3");
m_selectTableNames.setDatabaseName("/home/amet/userLog.db");
}
else{
qDebug() <<"connected to DB" ;
}
m_selectTableNames.open();
}

void dataBaseTables::tableNameComboBox(){
dbConnect();
QSqlQuery selectTables(m_selectTableNames);

//---Selects all tables older than 30 days in database | Gets date created---//
selectTables.prepare("SELECT name FROM sqlite_master WHERE type='table';");

if(selectTables.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << selectTables.lastError();
}

tableNames tables;
QString tableNameResults;

while (selectTables.next()){
tables.tablename = selectTables.value(0).toString();
tableNameResults = selectTables.value(0).toString();
qDebug() << "Table Results: "+tableNameResults;
addTableName(tables);
}
m_selectTableNames.close();
//return tableNameResults;
}



====databasetables.h=====


#ifndef DATABASETABLES_H
#define DATABASETABLES_H

#include <QString>
#include <QDebug>
#include <QQmlContext>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>
#include <QtSql/QSqlRecord>
#include <QModelIndex>
#include <QSqlDatabase>
#include <QAbstractListModel>
#include <QAbstractItemModel>

struct tableNames{
QString tablename;
};

class dataBaseTables: public QAbstractListModel
{
Q_OBJECT

public:
explicit dataBaseTables(QObject *parent = 0);

~dataBaseTables();

enum dataTableNameRoles {tableNameRole= Qt::UserRole + 220};

int rowCount(const QModelIndex & parent) const;

QHash<int, QByteArray> roleNames() const;

QVariant data(const QModelIndex &index, int role=Qt::DisplayRole) const;

Q_INVOKABLE void addTableName(const tableNames &tables);

Q_INVOKABLE void dbConnect();

Q_INVOKABLE void tableNameComboBox();

public slots:

private:
QList<tableNames> m_tableNameList;
QSqlDatabase m_selectTableNames;
QSqlQuery m_tableNamesQuery;
};
#endif // DATABASETABLES_H

[anda_skoa]

That looks mostly ok, I only saw

- in sqliteModel::sqlSelect() you have a addBindValue() but no parameter in the query

- you are always adding to the model's list, but maybe that is what you want

Cheers,
_

[jfinn88]

I'm not sure the proper way to pass the tableName variable into the sql script ? I thought that when you do pass a variable you must bind the value I thought that it would be mainly for update scripts however I figure I would see if that had any affect on exec the script... any suggestions ? I get little confused cause its not a WHERE statement its FROM and not sure if I'm setting up the sql script correctly...

Update: I added a debug statement in my sqlSelect() method to see what the tableName was passing in, and its an int value. I call the sqlSelect() method in the qml, I want to pass the text of the selected item I thought currentIndex would do that but after reading the documentation its an int value and thats not what I want todo

dug around the documentation and found currentText() should work lol I'll try this and see if it works and it dose :)

after reading around It seems like sqlite_master table doesn't store the create_date

need to append a current Date to table name when creating them... not sure if this is possible with sqlite saw a couple post that advise this is a bad away of doing it but it seems like a good logical way to do this task.

Task: Create user log event system.

What I have come up with:
-Create a database to store user log events (sqlite)
-created a global function to push user event messages to a database table throughout the app
-still need to figure out how to push to each daily table (pass tableName as parameter in sql script)
-create a table for every day to store daily user events
-use the curren date determine if you need to create a table for the current day
-use the date the table was created and if its older than 30 days (archive it instead of dropping not sure if sqlite supports archiving)
-search threw daily table by userName and possible dateTime
-created a drop down list that gets data from a model to display table names in the database to select different tables (by date) to display in the tableView based of the tablName passed to the fcn



onCurrentIndexChanged: {
sqliteModel.sqlSelect(currentText);
}


Added after 7 minutes:

"- you are always adding to the model's list, but maybe that is what you want"

I see what you mean by always adding to the list, thats not what I want to-do. I need it to restart the list or model but unsure how to do that... is there a list Clear() method I can call?

Added after 45 minutes:

Been working on my deleteDailyTable() function need some help comparing date of table created and currentDate minus 30days. I think I got the current date minus 30 days set to a variable to check in an if() condition. I'm having trouble getting the date of the table set to variable to compare to currentDate minus 30days....



void sqliteModel::deleteDailyTable()
{
dbConnect();
QSqlQuery deleteTableQry(m_selectDataBase);
QSqlQuery tableListQry(m_selectDataBase);
tableListQry.prepare("SELECT name FROM sqlite_master WHERE DATEDIFF(day, create_date, getdate()) > 10");
tableListQry.exec();

//---get the number of rows---//
int numberOfRows = 0;
if(tableListQry.last())
{
numberOfRows = tableListQry.at() + 1;
tableListQry.first();
tableListQry.previous();
}
qDebug() << "number of rows: "+numberOfRows;

//---Populate sql results in array---//
while(tableListQry.next()) {
for(int i =0; i<=numberOfRows; i++)
{
//---store the information in a array---//
QString results[numberOfRows];
results[i] = tableListQry.value(i).toString();
qDebug() << "results: "+results[i];
}
}

//---get current date minus 30days---//
QDate deleteDate = QDate::currentDate().addDays(-30);
qDebug() << "deleteDate: "+deleteDate.toString();

//---get table date---//
QDate tableDate;
//tableDate = results[0];

//--- If the table is older than 30 days drop it---//
if(deleteDate == tableDate){
deleteTableQry.prepare("DROP TABLE tableName");
if(deleteTableQry.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << deleteTableQry.lastError();
}
}
m_selectDataBase.close();
}

[anda_skoa]

"- you are always adding to the model's list, but maybe that is what you want"

I see what you mean by always adding to the list, thats not what I want to-do. I need it to restart the list or model but unsure how to do that... is there a list Clear() method I can call?

See comment #16



Been working on my deleteDailyTable() function need some help comparing date of table created and currentDate minus 30days.




QDate date = QDate::currentDate().addDays(-30);


Cheers,
_

[jfinn88]

I was able to figure this part out wasn’t hard, I updated the code after I figured it out



QDate date = QDate::currentDate().addDays(-30);


When back looked a post #16 implemented as is refreshing on username search! :)

However running into issue with getting date table is created or concatenating current date to table name....

after reading around It seems like sqlite_master table doesn't store the create_date

need to append a current Date to table name when creating them... not sure if this is possible with sqlite saw a couple post that advise this is a bad away of doing it but it seems like a good logical way to do this task.

Task: Create user log event system.

What I have come up with:
-Create a database to store user log events (sqlite)
-created a global function to push user event messages to a database table throughout the app
-still need to figure out how to push to each daily table (pass tableName as parameter in sql script)
-create a table for every day to store daily user events
-use the curren date determine if you need to create a table for the current day
-use the date the table was created and if its older than 30 days (archive it instead of dropping not sure if sqlite supports archiving)
-search threw daily table by userName and possible dateTime
-created a drop down list that gets data from a model to display table names in the database to select different tables (by date) to display in the tableView based of the tablName passed to the fcn

Thought about creating a table to store table names and dates created but not sure if that will work...

[jfinn88]

Created a new table to hold table names and table dates created.

trying to pull from that database a stick results in an array for use in a conditional loop to check table dates however I'm running into an issue with populating my array using a for loop.

I set my database connection using my QSqlQuery object
Set up a model to display the daily table in a tableView in QML (expose c++ by setting context property of engine)
set up a model for drop down box to display daily tables to change the tableView (refresh by clearing list an reset model)
I prepare an SELECT sql script to pull data from database
I check the qry ran correctly using an if loop
Then I get the size of my qry (since it is sqlite can't use size() or length() to get it) using a if statement that checks last item and adds one (since it starts at 0)
I create an array of the size of the qry
I loop threw a for loop to populate array using sqlQry.value()
I then create a variable to store the current date -30days
I have commented out the part where I compare dates and drop the table from the databae (would like to change to archiving but will start off by dropping table)



void sqliteModel::deleteDailyTable()
{
dbConnect();

QSqlQuery deleteTableQry(m_selectDataBase);
QSqlQuery tableNameDateQry(m_selectDataBase);

tableNameDateQry.prepare("SELECT name, tableDate FROM tableDates");

if(tableNameDateQry.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << tableNameDateQry.lastError();
}

//---get the number of rows---//
//int numberOfRows = 0;
//tableNameDateQry.last();
//qDebug() << tableNameDateQry.at() + 1;
//numberOfRows = tableNameDateQry.at() + 1;


int numberOfRows = 0;

//---get the number of rows---//
if(tableNameDateQry.exec())
{
tableNameDateQry.last();
numberOfRows = tableNameDateQry.at()+1;
qDebug() << numberOfRows;
tableNameDateQry.first();
tableNameDateQry.previous();
}

QString results[numberOfRows];

//---Populate sql results in array---//
if(tableNameDateQry.next()){
qDebug() << "made it inside while loop";
for(int i=0; i<=numberOfRows; i++){
qDebug() << "made it inside for loop";
//---store the information in a array---//
results[i] = tableNameDateQry.value(i).toString();
qDebug() << "results: "+results[i];
}
}

//---get current date minus 30days---//
QDate deleteDate = QDate::currentDate().addDays(-30);
qDebug() << "deleteDate: "+deleteDate.toString();

//---get table date---//
//QDate tableDate;
//tableDate = results[0];

//--- If the table is older than 30 days drop it---//
//if(deleteDate == tableDate){
//deleteTableQry.prepare("DROP TABLE tableName");
//if(deleteTableQry.exec()){
//qDebug()<<"sql statement exicuted fine";
//}
//else{
//qDebug() << "Errors accured with sql statement";
//qDebug() << deleteTableQry.lastError();
//}
//}
//m_selectDataBase.close();
}


I'm not sure if I'm setting the size of my array correctly also it runs into a seg fault after completing for loop. only the first two items from the query get populated into array the rest don't

update: setting the condition of the for loop upper bound <= seems to cause program to crash changed to just < array still only gets the first row of sql qry results...

update: I dont think an array is the way do to this going to try creating a struct to hold data of qry...

[anda_skoa]

C++ can't create a plain array with a size that is unknown at compile time.

In fact that could should not have compiled at all.

But you don't need an array, just use a QStringList and append to it in the normal query "next" loop.

Btw, you seem to be executing the "tableNameDateQry" twice (line 10 and line 28), for no apparent reason.

Cheers,
_

[jfinn88]

anda_skoa,

I have migrated away from using multiple tables it seems I’m able to do everything I need with just one table.

What I would like to implement now is searching by date range. I have added to calendars to my QML one for a begin date and one for an end date, I need to pass both parameters to a function in c++ but not sure how to emit two signals to one slot so I can pass both parameters

I have creaeted a function in c++

=====header file========


public slots:
void searchDateRange(QString &beginDate, QString &endDate);


======c++ file=====


void sqliteModel::searchDateRange(QString &beginDate, QString &endDate)
{
qDebug() << "c++: sqliteModel::searchDateRange beginDate:" << beginDate;
qDebug() << "c++: sqliteModel::searchDateRange endDate:" << endDate;
dbConnect();

if(!m_selectDataBase.open())
{
qDebug() << "database was closed";
m_selectDataBase.open();
}
else{
qDebug() << "database is open";
}

QSqlQuery selectQuery(m_selectDataBase);
selectQuery.prepare("SELECT id, userName, eventMessage, dateTime FROM userlogevents WHERE dateTime BETWEEN ? and ?");
selectQuery.addBindValue(beginDate);
selectQuery.addBindValue(endDate);


userEventLogMsg msg;
beginResetModel();
m_msgList.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).toString();
addEvent(msg);
}
endResetModel();
m_selectDataBase.close();
}


===qml=====


import QtQuick 2.5
import QtQuick.Layouts 1.1
import QtQuick.Controls 1.3
import QtQuick.Window 2.2
import QtQuick.Dialogs 1.2
import QtQuick.Layouts 1.1
import QtQuick.Controls.Styles 1.2

Window {
id: window1
signal submitDateText(string text)
signal submitUserNameText(string text)
signal submitDatabaseTable(string text)
signal submitBeginEndDate(string beginDate, string endDate)
//signal submitEndDate(string text)
visible: true
width: 1050
height: 600
title: "User Event Log"
ColumnLayout {
id: calendarColumnLayout
x: 8
y: 54
width: 259
height: 540
Rectangle {
id: calendarRect1
width: 247
height: 247
anchors.top: parent.top
Calendar {
id: calendar1
width: 247
height: 247
anchors.rightMargin: -11
anchors.bottomMargin: -8
anchors.leftMargin: 2
anchors.topMargin: 8
anchors.fill: parent
anchors.top: parent.top
style: CalendarStyle {
dayDelegate: Item {
Rectangle {
id: rect1
anchors.fill: parent
Label {
id: dayDelegateText1
text: styleData.date.getDate()
anchors.centerIn: parent
horizontalAlignment: Text.AlignRight
font.pixelSize: Math.min(parent.height/3, parent.width/3)
color: styleData.selected ? "red" : "black"
font.bold: styleData.selected
}
MouseArea {
anchors.horizontalCenter: parent.horizontalCenter
anchors.verticalCenter: parent.verticalCenter
width: styleData.selected ? parent.width / 2 : 0
height: styleData.selected ? parent.height / 2 : 0
Rectangle {
anchors.fill: parent
color: "transparent"
border.color: "darkorange"
}
}
}
}
}
}
}
Rectangle {
id: calendarRect2
x: 14
y: 350
width: 247
height: 247
anchors.bottomMargin: -11
anchors.bottom: parent.bottom
Calendar {
id: calendar2
width: 247
height: 247
anchors.rightMargin: -5
anchors.bottomMargin: 28
anchors.leftMargin: -5
anchors.topMargin: -28
anchors.fill: parent
anchors.bottom: parent.bottom
style: CalendarStyle {
dayDelegate: Item {
Rectangle {
id: rect2
anchors.fill: parent
Label {
id: dayDelegateText2
text: styleData.date.getDate()
anchors.centerIn: parent
horizontalAlignment: Text.AlignRight
font.pixelSize: Math.min(parent.height/3, parent.width/3)
color: styleData.selected ? "red" : "black"
font.bold: styleData.selected
}
MouseArea {
anchors.horizontalCenter: parent.horizontalCenter
anchors.verticalCenter: parent.verticalCenter
width: styleData.selected ? parent.width / 2 : 0
height: styleData.selected ? parent.height / 2 : 0
Rectangle {
anchors.fill: parent
color: "transparent"
border.color: "darkorange"
}
onClicked: {
//---emit the submitBegnDate signal---//
//sqliteModel.searchDateRange();
}
}
}
}
}
}
}
}
Label {
id: calendarLabel
x: 73
y: 8
text: qsTr("Select Date Range")
}

Label {
id: endDatelabel
x: 104
y: 309
text: qsTr("End Date")
}

Label {
id: beginDatelabel
x: 97
y: 31
text: qsTr("Begin Date")
}
}

[anda_skoa]

What I would like to implement now is searching by date range. I have added to calendars to my QML one for a begin date and one for an end date, I need to pass both parameters to a function in c++ but not sure how to emit two signals to one slot so I can pass both parameters

You don't need any signals at all, see comment #13

Also you probably want these slot parameters to be "const QString&" or just "QString", i.e. you don't change the values in the slot, right?

Cheers,
_

[jfinn88]

you are correct I don't need to change these values will I will changed it from passing by ref.

I want to use a date picker to select a date range to pass my function however I would like to start out a little simpler and just use two calendars. I'm confused though how to pass my function call the two dates selected from the calendar...
below is my c++ function and my QML with two calendars

calendar1.dayDelegateText1.text ? getDate() ?




void sqliteModel::searchDateRange(QString beginDate, QString endDate){
qDebug() << "c++: sqliteModel::searchDateRange beginDate:" << beginDate;
qDebug() << "c++: sqliteModel::searchDateRange endDate:" << endDate;
dbConnect();

if(!m_selectDataBase.open())
{
qDebug() << "database was closed";
m_selectDataBase.open();
}
else{
qDebug() << "database is open";
}

QSqlQuery selectQuery(m_selectDataBase);
selectQuery.prepare("SELECT id, userName, eventMessage, dateTime FROM userlogevents WHERE dateTime BETWEEN ? and ?");
selectQuery.addBindValue(beginDate);
selectQuery.addBindValue(endDate);


userEventLogMsg msg;
beginResetModel();
m_msgList.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).toString();
addEvent(msg);
}
endResetModel();
m_selectDataBase.close();
}


===QML=====


import QtQuick 2.5
import QtQuick.Layouts 1.1
import QtQuick.Controls 1.3
import QtQuick.Window 2.2
import QtQuick.Dialogs 1.2
import QtQuick.Layouts 1.1
import QtQuick.Controls.Styles 1.2

Window {
id: window1
visible: true
width: 1050
height: 600
title: "User Event Log"
RowLayout {
id: rowLayout1
x: 303
y: 8
width: 736
height: 36
anchors.horizontalCenterOffset: 146
anchors.topMargin: 8
anchors.top: parent.top
anchors.horizontalCenter: parent.horizontalCenter
Rectangle {
id: comboBoxRect

Label {
id: comboLabel
x: 121
y: 8
width: 150
height: 25
text: qsTr("Select Table to Display")
verticalAlignment: Text.AlignVCenter
}
ComboBox {
id: dataBaseComboBox
x: 325
width: 181
height: 25
anchors.top: parent.top
anchors.topMargin: 8
model: tableNameModel
textRole: "tableName"
onCurrentIndexChanged: {
sqliteModel.sqlSelect(currentText);
}
}
}
}
TableView {
width: 736
height: 500
anchors.verticalCenterOffset: 0
anchors.horizontalCenterOffset: 146
anchors.centerIn: parent;
horizontalScrollBarPolicy: 49
frameVisible: true
model: sqliteModel
//sortIndicatorColumn : 1
//sortIndicatorVisible: true
TableViewColumn {
role: "id"
title: "id"
width: 100
}
TableViewColumn {
role: "userName"
title: "User Name"
width: 200
}
TableViewColumn {
role: "eventMessage"
title: "Event Message"
width: 200
}
TableViewColumn {
role: "dateTime"
title: "Date Time"
width: 200
}
}
RowLayout {
id: searchRowLayout
x: 201
y: 556
anchors.horizontalCenter: parent.horizontalCenter;
anchors.bottom: parent.bottom
width: 736
height: 47;
anchors.horizontalCenterOffset: 146
anchors.bottomMargin: -3
clip: false
opacity: 0.9
Button {
id: load_btn
text: qsTr("Load")
MouseArea{
anchors.fill: parent
onClicked: {
sqliteModel.sqlSelect(dataBaseComboBox.currentText );
}
}
}
Label {
id: userNameLabel
text: qsTr("User Name")
}
TextField {
id: userNameTextField
placeholderText: qsTr("User Name")
}
Label {
id: dateLabel
width: 25
height: 15
text: qsTr("Date")
}
TextField {
id: dateTextField
width: 125
height: 25
placeholderText: qsTr("mm//dd/yyyy")
}
Button {
id: searchBtn
text: qsTr("Search")
MouseArea{
anchors.fill: parent
onClicked: {
//---Uses QML context to access c++ fcn---//
sqliteModel.searchDateRange()
}
}
}
Button {
id: exit_btn
text: qsTr("Exit")
MouseArea{
anchors.fill: parent
onClicked: close();
}
}
}

ColumnLayout {
id: calendarColumnLayout
x: 8
y: 54
width: 259
height: 540
Rectangle {
id: calendarRect1
width: 247
height: 247
anchors.top: parent.top
Calendar {
id: calendar1
width: 247
height: 247
anchors.rightMargin: -11
anchors.bottomMargin: -8
anchors.leftMargin: 2
anchors.topMargin: 8
anchors.fill: parent
anchors.top: parent.top
style: CalendarStyle {
dayDelegate: Item {
Rectangle {
id: rect1
anchors.fill: parent
Label {
id: dayDelegateText1
text: styleData.date.getDate()
anchors.centerIn: parent
horizontalAlignment: Text.AlignRight
font.pixelSize: Math.min(parent.height/3, parent.width/3)
color: styleData.selected ? "red" : "black"
font.bold: styleData.selected
}
MouseArea {
anchors.horizontalCenter: parent.horizontalCenter
anchors.verticalCenter: parent.verticalCenter
width: styleData.selected ? parent.width / 2 : 0
height: styleData.selected ? parent.height / 2 : 0
Rectangle {
anchors.fill: parent
color: "transparent"
border.color: "darkorange"
}
}
}
}
}
}
}
Rectangle {
id: calendarRect2
x: 14
y: 350
width: 247
height: 247
anchors.bottomMargin: -11
anchors.bottom: parent.bottom
Calendar {
id: calendar2
width: 247
height: 247
anchors.rightMargin: -5
anchors.bottomMargin: 28
anchors.leftMargin: -5
anchors.topMargin: -28
anchors.fill: parent
anchors.bottom: parent.bottom
style: CalendarStyle {
dayDelegate: Item {
Rectangle {
id: rect2
anchors.fill: parent
Label {
id: dayDelegateText2
text: styleData.date.getDate()
anchors.centerIn: parent
horizontalAlignment: Text.AlignRight
font.pixelSize: Math.min(parent.height/3, parent.width/3)
color: styleData.selected ? "red" : "black"
font.bold: styleData.selected
}
MouseArea {
anchors.horizontalCenter: parent.horizontalCenter
anchors.verticalCenter: parent.verticalCenter
width: styleData.selected ? parent.width / 2 : 0
height: styleData.selected ? parent.height / 2 : 0
Rectangle {
anchors.fill: parent
color: "transparent"
border.color: "darkorange"
}
onClicked: {
//---call the date range fcn---//
}
}
}
}
}
}
}
}
}

[anda_skoa]

I am afraid I don't understand.

You already call methods on your model object from QML, you just do the same for the new method.
Something like


sqliteModel.searchDateRange(calendar1.getSelectedD ate(), calendar2.getSelectedDate())


Cheers,
_

[jfinn88]

that is what I need thank you!

says its not a function getSelectedDate I didn't see it in the doc either ?

update: I think its just selectedDate trying this now

this is working its passing the selected date from the calendar...


calendar1.selectedDate

However I need to format the date passed... it is currently passing "Wed Sep 7 00:00:00 2016 GMT-0600" I need in format yyyy-MM-dd

would it be best to convert format in c++ or in qml before passing (or while passing) ?

update: format in qml ->
Qt.formatDate(calendar1.selectedDate,"yyyy-MM-dd")

[jfinn88]

need help loading qml component in seperate qml file...?

====userEventDialog.qml=======


Item {
//----User Event Log Component---//
Component {
id: usereventcomponent
Item {
id:userevent_item
Rectangle{
id: view_rect
implicitHeight: 550
implicitWidth: 1100
radius: 10
border.width:4
border.color: "black"
layer.enabled: true
Label {
id: userEventTitle_label
width: 200
color: "#000000"
text: qsTr("User Event Log")
anchors.leftMargin: 225
font.bold: true
anchors.topMargin: 5
anchors.top: parent.top
anchors.horizontalCenter: parent.horizontalCenter
font.pointSize: 22
}
layer.effect: DropShadow {
horizontalOffset: 8
verticalOffset: 8
radius: 8.0
samples: 16
color: "#80000000"
source: view_rect
}
gradient: Gradient {
GradientStop {
position: 0
color: "#ffffff"
}

GradientStop {
position: 1
color: "#262626"
}
}
enabled: true
opacity: enabled ? 1.0 : .3
TableView {
width: 750;
height: 450;
anchors.verticalCenter: parent.verticalCenter
anchors.left: parent.left
anchors.leftMargin: 10
horizontalScrollBarPolicy: 0
frameVisible: true
model: UserEventLog
//sortIndicatorVisible: true
//sortIndicatorColumn: 1
TableViewColumn {
role: "id"
title: "id"
width: 100
}
TableViewColumn {
role: "userName"
title: "User Name"
width: 100
}
TableViewColumn {
role: "eventMessage"
title: "Event Message"
width: 400
}
TableViewColumn {
role: "dateTime"
title: "Date Time"
width: 200
}
}
RowLayout {
id: row1
//anchors.horizontalCenter: parent.horizontalCenter;
anchors.bottom: parent.bottom;
anchors.left: parent.left
anchors.leftMargin: 10
spacing: 60;
width: 500
height: 50;
Label {
id: userNameLabel
width: 39
height: 25
color: "red";
text: qsTr("User Name")
}
TextField {
id: userNameTextField
width: 125
height: 5
style: TextFieldStyle {}
placeholderText: qsTr("User Name")
}
Button {
id: searchBtn
text: qsTr("Search")
//---Sets button Formatting---//
style: ButtonStyle {
background: Rectangle {
implicitWidth: 100
implicitHeight: 30
anchors.fill:parent
radius: 4
//---Changes butotn color when pressed--//
gradient: Gradient {
GradientStop { position: 0 ; color: control.pressed ? "#ccc" : "#eee" }
GradientStop { position: 1 ; color: control.pressed ? "#aaa" : "#ccc" }
}
//---Sets button Image---//
Image{
width: 16
height: 16
anchors.verticalCenter: parent.verticalCenter
anchors.left: parent.left
anchors.leftMargin: 5
source:"button_ok.png"

}
}
}
MouseArea{
anchors.fill: parent
onClicked: {
//---Uses QML context to access c++ fcn---//
//UserEventLog.searchUserNameFcn(userNameTextField.t ext);
UserEventLog.searchDateRange(Qt.formatDate(calenda r1.selectedDate,"yyyy-MM-dd"), Qt.formatDate(calendar2.selectedDate,"yyyy-MM-dd"))
//UserEventLog.deleteEvent();
}
}
}
Button {
id: refreshBtn
text: qsTr("Refresh")
//---Sets button Formatting---//
style: ButtonStyle {
background: Rectangle {
implicitWidth: 100
implicitHeight: 30
anchors.fill:parent
radius: 4
//---Changes butotn color when pressed--//
gradient: Gradient {
GradientStop { position: 0 ; color: control.pressed ? "#ccc" : "#eee" }
GradientStop { position: 1 ; color: control.pressed ? "#aaa" : "#ccc" }
}
//---Sets button Image---//
Image{
width: 16
height: 16
anchors.verticalCenter: parent.verticalCenter
anchors.left: parent.left
anchors.leftMargin: 5
source:"return.png"

}
}
}
MouseArea{
anchors.fill: parent
onClicked: {
//---Emit signal---//
UserEventLog.selectEvent();
userNameTextField.text = "";
}
}
}
}
}
}
//---------------------------End of User Event Log Component-------------------------------//
}


========qml that access userEventDialog========



//-----------Code to Load User Event Dialog qml for User Event Log Button------------//
Action {
id: action_userEventLogBtn
enabled:!inSequence
onTriggered:{
//----Code to Load User Event Dialog-----//
input_loader.filename = ""
UserEventLog.dbConnect();
UserEventLog.selectEvent();
weld_view.state = "USEREVENT"
onLoaded: console.log("User Event Log");

}
}
//----------------------------------------------------------------------//


states: [
//-----------State to load User Event Log Dialog-------------//
State {
name: "USEREVENT"
PropertyChanges {target: mask; visible:true; z: 1;}
PropertyChanges {target: input_loader; sourceComponent:userevent;}
}
//----------------------------------------------------------//
]
Component {
id: userevent
UserEventDialog {
id: userEventLog
}
}

[anda_skoa]

However I need to format the date passed... it is currently passing "Wed Sep 7 00:00:00 2016 GMT-0600" I need in format yyyy-MM-dd

would it be best to convert format in c++ or in qml before passing (or while passing) ?

update: format in qml ->
Qt.formatDate(calendar1.selectedDate,"yyyy-MM-dd")

You could also try passing it as a QDateTime instead of QString


need help loading qml component in seperate qml file...?

====userEventDialog.qml=======

If you want to instantiate that directly then the file name needs to start with a capital U.

Cheers,
_

[jfinn88]

You could also try passing it as a QDateTime instead of QString

Okay I will give that a try...



If you want to instantiate that directly then the file name needs to start with a capital U.

Cheers,
_

I noticed that shortly after posting change file name to begin with capital now highlights purple in the editor, but nothing displays... no errors do i need to set a particular object (parent) to visible or something?

===UserEventDialog===


Component {
id: usereventcomponent
UserEventDialog {
id: userEventLog
}
}


=====Action to call state====


Action {
id: action_userEventLogBtn
enabled:!inSequence
onTriggered:{
//----Code to Load User Event Dialog-----//
input_loader.filename = "" //UserEventDialog.qml
UserEventLog.dbConnect();
UserEventLog.selectEvent();
weld_view.state = "USEREVENT"
onLoaded: console.log("User Event Log");

}
}


====State for component====


State {
name: "USEREVENT"
PropertyChanges {target: mask; visible:true; z: 1;}
PropertyChanges {target: input_loader; sourceComponent:usereventcomponent;}
}


===loader for state====


Loader{
id: input_loader
property string filename: ""
width: 950
height: 500
anchors.centerIn :parent
sourceComponent: null
z: 1
}

[anda_skoa]

As far as I can tell your loaded Item is empty.

I.e. the root level Item in UserEventDialog.qml does not have any content.

My guess is that you don't want that Item at all and also don't want the Component in that file, but that Component element's main Item to be the top level

Cheers,
_

[jfinn88]

I think the issue is in my UserEventDialog.qml but not sure what is causing it I can change the outter most object from Item to Rectangle and get a blank rectangle to display I can change the source component to a different component and it displays so issue I think is in my user event dialog component file ?

update: issue resolved, issue was with Component object removed component object form userEventDialog .qml file works fine now

====UserEventDialog.qnl====


Item {
visible: true
//----User Event Log Component---//
Component {
id: usereventcomponent
Item {
id:userevent_item
Rectangle{
id: view_rect
implicitHeight: 550
implicitWidth: 1100
radius: 10
border.width:4
border.color: "black"
layer.enabled: true
Label {
id: userEventTitle_label
width: 200
color: "#000000"
text: qsTr("User Event Log")
anchors.leftMargin: 225
font.bold: true
anchors.topMargin: 5
anchors.top: parent.top
anchors.horizontalCenter: parent.horizontalCenter
font.pointSize: 22
}
layer.effect: DropShadow {
horizontalOffset: 8
verticalOffset: 8
radius: 8.0
samples: 16
color: "#80000000"
source: view_rect
}
gradient: Gradient {
GradientStop {
position: 0
color: "#ffffff"
}

GradientStop {
position: 1
color: "#262626"
}
}
enabled: true
opacity: enabled ? 1.0 : .3
TableView {
width: 750;
height: 450;
anchors.verticalCenter: parent.verticalCenter
anchors.left: parent.left
anchors.leftMargin: 10
horizontalScrollBarPolicy: 0
frameVisible: true
model: UserEventLog
//sortIndicatorVisible: true
//sortIndicatorColumn: 1
TableViewColumn {
role: "id"
title: "id"
width: 100
}
TableViewColumn {
role: "userName"
title: "User Name"
width: 100
}
TableViewColumn {
role: "eventMessage"
title: "Event Message"
width: 400
}
TableViewColumn {
role: "dateTime"
title: "Date Time"
width: 200
}
}
RowLayout {
id: row1
anchors.bottom: parent.bottom;
anchors.left: parent.left
anchors.leftMargin: 10
spacing: 60;
width: 500
height: 50;
Label {
id: userNameLabel
width: 39
height: 25
color: "red";
text: qsTr("User Name")
}
TextField {
id: userNameTextField
width: 125
height: 5
style: TextFieldStyle {}
placeholderText: qsTr("User Name")
}
Button {
id: searchBtn
text: qsTr("Search")
//---Sets button Formatting---//
style: ButtonStyle {
background: Rectangle {
implicitWidth: 100
implicitHeight: 30
anchors.fill:parent
radius: 4
//---Changes butotn color when pressed--//
gradient: Gradient {
GradientStop { position: 0 ; color: control.pressed ? "#ccc" : "#eee" }
GradientStop { position: 1 ; color: control.pressed ? "#aaa" : "#ccc" }
}
//---Sets button Image---//
Image{
width: 16
height: 16
anchors.verticalCenter: parent.verticalCenter
anchors.left: parent.left
anchors.leftMargin: 5
source:"button_ok.png"
}
}
}
MouseArea{
anchors.fill: parent
onClicked: {
//---Uses QML context to access c++ fcn---//
//UserEventLog.searchUserNameFcn(userNameTextField.t ext);
UserEventLog.searchDateRange(Qt.formatDate(calenda r1.selectedDate,"yyyy-MM-dd"), Qt.formatDate(calendar2.selectedDate,"yyyy-MM-dd"))
//UserEventLog.deleteEvent();
}
}
}
Button {
id: refreshBtn
text: qsTr("Refresh")
//---Sets button Formatting---//
style: ButtonStyle {
background: Rectangle {
implicitWidth: 100
implicitHeight: 30
anchors.fill:parent
radius: 4
//---Changes butotn color when pressed--//
gradient: Gradient {
GradientStop { position: 0 ; color: control.pressed ? "#ccc" : "#eee" }
GradientStop { position: 1 ; color: control.pressed ? "#aaa" : "#ccc" }
}
//---Sets button Image---//
Image{
width: 16
height: 16
anchors.verticalCenter: parent.verticalCenter
anchors.left: parent.left
anchors.leftMargin: 5
source:"return.png"

}
}
}
MouseArea{
anchors.fill: parent
onClicked: {
//---Emit signal---//
UserEventLog.selectEvent();
userNameTextField.text = "";
}
}
}
Button {
id: exit_btn
text: qsTr("Exit")
//---Sets button Formatting---//
style: ButtonStyle {
background: Rectangle {
implicitWidth: 100
implicitHeight: 30
anchors.fill:parent
radius: 4
//---Changes butotn color when pressed--//
gradient: Gradient {
GradientStop { position: 0 ; color: control.pressed ? "#ccc" : "#eee" }
GradientStop { position: 1 ; color: control.pressed ? "#aaa" : "#ccc" }
}
//---Sets button Image---//
Image{
width: 16
height: 16
anchors.verticalCenter: parent.verticalCenter
anchors.left: parent.left
anchors.leftMargin: 5
source:"button_cancel.png"
}
}
}
MouseArea{
anchors.fill: parent
onClicked: weld_view.state = ""
}
}
}

}
}
}
//---------------------------End of User Event Log Component-------------------------------//
}

[anda_skoa]

As I said before: the Item has no content. An Item itself doesn't display anything.

Cheers,
_

[jfinn88]

As far as I can tell your loaded Item is empty.

I.e. the root level Item in UserEventDialog.qml does not have any content.

My guess is that you don't want that Item at all and also don't want the Component in that file, but that Component element's main Item to be the top level

Cheers,
_

I got busy and didn’t see this reply, your totally right. Moved out competent in that file, thank you for explaining this.

[jfinn88]

yeah sorry didnt see your earlier post thanks for explaining this...

[jfinn88]

I want to be able to archive my old table rows older than certain number of days, I figured I could copy the data from one database table to another database table, not sure if this is the best way to do it ? could you possible help me get a proper archive setup?




void sqliteModel::dbConnect(){

if(!m_selectDataBase.isValid()){
qDebug() << "error in opening DB";
m_selectDataBase = QSqlDatabase::addDatabase("QSQLITE", "conn2");
m_selectDataBase.setDatabaseName("/home/amet/userLog.db");
}
else{
qDebug() <<"connected to DB" ;
}
m_selectDataBase.open();
}

void sqliteModel::archiveDbConnect(){

if(!m_archiveDataBase.isValid()){
qDebug() << "error in opening DB";
m_archiveDataBase = QSqlDatabase::addDatabase("QSQLITE", "conn3");
m_archiveDataBase.setDatabaseName("/home/amet/userLogArchive.db");
}
else{
qDebug() <<"connected to DB" ;
}
m_archiveDataBase.open();
}

void sqliteModel::archiveEvent(){
dbConnect();
//---archive db connecion---//
archiveDbConnect();

QSqlQuery archiveDataQry("SELECT * FROM userlogevents2", m_selectDataBase);
QSqlQuery deleteDataQry(m_selectDataBase);
if(archiveDataQry.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << archiveTableQry.lastError();
}

QDate rowDate;
QDate archiveDate = QDate::currentDate().addDays(-3);
qDebug() << "deleteDate: "+archiveDate.toString();

while(archiveDataQry.next()){
rowDate = archiveDataQry.value(3).toDate();
qDebug() << "results: "+rowDate.toString();
if(archiveDate < rowDate){
qDebug() << "made inside delete condition: ";
archiveDataQry.prepare("INSERT INTO userlogarchive FROM userlogevents2 WHERE dateTime < ?");
archiveDataQry.addBindValue(archiveDate);
deleteDataQry.prepare("DELETE FROM userlogevents2 WHERE dateTime < ?");
deleteDataQry.addBindValue(archiveDate);
if(archiveDataQry.exec()){
qDebug()<<"archive sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with archive sql statement";
qDebug() << archiveDataQry.lastError();
}
if(deleteDataQry.exec()){
qDebug()<<"delete sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with delete sql statement";
qDebug() << deleteDataQry.lastError();
}
}
}
m_selectDataBase.close();
}

[jefftee]

Much easier IMHO to attach your archive database to the main database so that you can insert the records into the archive table by selecting the records you want to select for archiving. A hypothetical example would be:


ATTACH DATABASE '/home/amet/userLogArchive.db' as arc;
INSERT INTO arc.userlogarchive select * from main.userlogevents2 where dateTime >= ?;
DELETE FROM main.userlogevents2 where dateTime >= ?;

[jfinn88]

I thought keeping the table in another database would be best in case one corrupts, however I can attach the database together to copy over old rows? what is arc?

[jefftee]

I thought keeping the table in another database would be best in case one corrupts, however I can attach the database together to copy over old rows?
The example I posted above will result in the archived records in a seperate db (userLogArchive.db). You'll want to check for successful execution of each of those statements above and I'd start a transaction before the insert and commit after the delete, etc.

[jfinn88]

I see what your saying... I just get little lost on implementing this concept, let me see what I can figure out with the attach database and the transaction()

Added after 51 minutes:

haven't looked into the ATTACH DATABASE yet been playing with using two separate connections little more but still having trouble... If I cant get this way to work I will try the attached database way...



void sqliteModel::dbConnect(){

if(!m_selectDataBase.isValid()){
qDebug() << "error in opening DB";
m_selectDataBase = QSqlDatabase::addDatabase("QSQLITE", "conn2");
m_selectDataBase.setDatabaseName("/home/amet/userLog.db");
}
else{
qDebug() <<"connected to DB" ;
}
m_selectDataBase.open();
}

void sqliteModel::archiveDbConnect(){

if(!m_archiveDataBase.isValid()){
qDebug() << "error in opening DB";
m_archiveDataBase = QSqlDatabase::addDatabase("QSQLITE", "conn3");
m_archiveDataBase.setDatabaseName("/home/amet/userLogArchive.db");
}
else{
qDebug() <<"connected to DB" ;
}





void sqliteModel::archiveEvent(){
dbConnect();
archiveDbConnect();

QSqlQuery archiveDataQry("SELECT * FROM userlogevents7", m_selectDataBase);
QSqlQuery copyDataQry(m_archiveDataBase);

int id;
QString userName;
QString eventMessage;
QString dateTime;

if(archiveDataQry.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << archiveDataQry.lastError();
}

QDate rowDate;
QDate archiveDate = QDate::currentDate().addDays(-30);
qDebug() << "archiveDate: "+archiveDate.toString();

m_selectDataBase.transaction();
copyDataQry.prepare("INSERT INTO userlogarchive (id, userName, eventMessage, dateTime) FROM userlogevents7 VALUES (:id, :userName, :eventMessage, :dateTime) WHERE dateTime < ?");

while(archiveDataQry.next()){
rowDate = archiveDataQry.value(3).toDate();
qDebug() << "results: "+rowDate.toString();
if(archiveDate < rowDate){
qDebug() << "made inside delete condition: ";

id = archiveDataQry.record().value(0).toInt();
userName = archiveDataQry.record().value(1).toString();
eventMessage = archiveDataQry.record().value(2).toString();
dateTime = archiveDataQry.record().value(3).toString();
copyDataQry.bindValue(0, id);
copyDataQry.bindValue(1, userName);
copyDataQry.bindValue(2, eventMessage);
copyDataQry.bindValue(3, dateTime);
copyDataQry.addBindValue(archiveDate);
}
}

//archiveDataQry.prepare("DELETE FROM userlogevents7 WHERE dateTime < ?");
//archiveDataQry.addBindValue(archiveDate);

//if(archiveDataQry.exec()){
// qDebug()<<"archive sql statement exicuted fine";
//}
//else{
// qDebug() << "Errors accured with archive sql statement";
// qDebug() << archiveDataQry.lastError();
//}

if(copyDataQry.exec()){
qDebug()<<"copy sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with copy sql statement";
qDebug() << copyDataQry.lastError();
}
m_selectDataBase.commit();
m_selectDataBase.close();
}

[jfinn88]

keep getting "Parameter count mismatch" seems to be with binding values I think...



void sqliteModel::dbConnect(){

if(!m_selectDataBase.isValid()){
qDebug() << "error in opening DB";
m_selectDataBase = QSqlDatabase::addDatabase("QSQLITE", "conn2");
m_selectDataBase.setDatabaseName("/home/amet/userLog.db");
}
else{
qDebug() <<"connected to DB" ;
}
m_selectDataBase.open();
}

void sqliteModel::archiveDbConnect(){

if(!m_archiveDataBase.isValid()){
qDebug() << "error in opening DB";
m_archiveDataBase = QSqlDatabase::addDatabase("QSQLITE", "conn4");
m_archiveDataBase.setDatabaseName("/home/amet/userLogArchive.db");
}
else{
qDebug() <<"connected to DB" ;
}
m_archiveDataBase.open();
}
void sqliteModel::archiveEvent(){
dbConnect();
archiveDbConnect();

QSqlQuery archiveDataQry("SELECT * FROM userlogevents7", m_selectDataBase);
QSqlQuery copyDataQry(m_archiveDataBase);

QString id;
QString userName;
QString eventMessage;
QDate dateTime;

if(archiveDataQry.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << archiveDataQry.lastError();
}

QDate rowDate;
QDate archiveDate = QDate::currentDate().addDays(-30);
qDebug() << "archiveDate: "+archiveDate.toString("yyyy-MM-dd");;

m_selectDataBase.transaction();
copyDataQry.prepare("INSERT INTO userlogarchive FROM userlogevents7 WHERE id > 0");

while(archiveDataQry.next()){
rowDate = archiveDataQry.value(3).toDate();
qDebug() << "results: "+rowDate.toString("yyyy-MM-dd");
//if(archiveDate < rowDate){
//qDebug() << "made inside delete condition: ";

id = archiveDataQry.record().value(0).toString();
qDebug() << "id: "+id;
userName = archiveDataQry.record().value(1).toString();
qDebug() << "user name: "+userName;
eventMessage = archiveDataQry.record().value(2).toString();
qDebug() << "event Message : "+eventMessage;
dateTime = archiveDataQry.record().value(3).toDate();
qDebug() << "date : "+dateTime.toString();
copyDataQry.bindValue(0,id);
copyDataQry.bindValue(1, userName);
copyDataQry.bindValue(2, eventMessage);
copyDataQry.bindValue(3, dateTime);
//copyDataQry.addBindValue(archiveDate);
if(copyDataQry.exec()){
qDebug()<<"copy sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with copy sql statement";
qDebug() << copyDataQry.lastError();
}

//archiveDataQry.prepare("DELETE FROM userlogevents7 WHERE dateTime < ?");
//archiveDataQry.addBindValue(archiveDate);

//if(archiveDataQry.exec()){
// qDebug()<<"archive sql statement exicuted fine";
//}
//else{
// qDebug() << "Errors accured with archive sql statement";
// qDebug() << archiveDataQry.lastError();
//}

//}
}
m_selectDataBase.commit();
m_selectDataBase.close();
}




class sqliteModel:public QAbstractListModel
{
Q_OBJECT

public:
explicit sqliteModel(QObject *parent = 0);

~sqliteModel();

enum userEventRoles {idRole= Qt::UserRole + 220, nameRole, msgRole, dateRole};

int rowCount(const QModelIndex & parent) const;

QHash<int, QByteArray> roleNames() const;

QVariant data(const QModelIndex &index, int role=Qt::DisplayRole) const;

Q_INVOKABLE void addEvent(const userEventLogMsg &msg);

Q_INVOKABLE void dbConnect();

Q_INVOKABLE void archiveDbConnect();

Q_INVOKABLE void sqlSelect(QString tableName);

Q_INVOKABLE void createDailyTable(QString tableName);

public slots:

void searchDateText(const QString &dateIn);

void searchUserNameText(const QString &userNameIn);

void archiveEvent();

void searchDateRange(const QString &beginDate, const QString &endDate);

void searchDateRange(const QString &userName, const QString &beginDate, const QString &endDate);

private:
QList<userEventLogMsg> m_msgList;
QSqlDatabase m_selectDataBase;
QSqlDatabase m_archiveDataBase;
QSqlQuery m_selectQuery;
};


Added after 48 minutes:

going to try attaching database to existing connection



void sqliteModel::archiveEvent(){
dbConnect();
//archiveDbConnect();

QSqlQuery archiveDataQry(m_selectDataBase);
archiveDataQry.prepare("ATTACH DATABASE '/home/amet/userLogArchive.db' as db2");
archiveDataQry.exec();

if(archiveDataQry.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << archiveDataQry.lastError();
}

m_selectDataBase.transaction();
archiveDataQry.prepare("INSERT INTO 'db2.usereventarchive' SELECT * FROM main.usereventlog7");

if(archiveDataQry.exec()){
qDebug()<<"copy sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with copy sql statement";
qDebug() << archiveDataQry.lastError();
}

m_selectDataBase.commit();
m_selectDataBase.close();
}


Added after 7 minutes:

been trying to copy table contents from one database table to a separate database table but having issue with sql statement.

I have a database connection established already and I'm trying to attach another database to that connection but not sure If I have the attach sql script correct....

I get error "unable to fetch row", "No Qry"



void sqliteModel::archiveEvent(){
dbConnect();

QSqlQuery archiveDataQry(m_selectDataBase);
archiveDataQry.prepare("ATTACH DATABASE '/home/amet/userLogArchive.db' as db2");

if(archiveDataQry.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << archiveDataQry.lastError();
}

m_selectDataBase.transaction();
archiveDataQry.prepare("INSERT INTO db2.usereventarchive SELECT * FROM main.usereventlog7");

if(archiveDataQry.exec()){
qDebug()<<"copy sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with copy sql statement";
qDebug() << archiveDataQry.lastError();
}

m_selectDataBase.commit();
m_selectDataBase.close();
}

[jefftee]

I get error "unable to fetch row", "No Qry"
Not clear at all which line of code produces that output, so please be specific regarding the error you're receiving and which line of code produces it...

A couple of other general comments about your code. QSqlQuery::prepare returns a bool, don't ignore the return value and assume everything worked. You also seem to be changing your table names from example to example, so I have no clue if you're using the right table names from the main database, same for your archive database. Double check those names to ensure you are specifying tables that exist and have the expected number of columns, etc.

Edit: Other portions of your post don't make any sense, i.e. you are using QSqlQuery::bindValue when your SQL statement has no positional or named arguments???

[jfinn88]

Not clear at all which line of code produces that output, so please be specific regarding the error you're receiving and which line of code produces it...

its the sql exec that throws this error in the if condition

if(archiveDataQry.exec()){
qDebug()<<"copy sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with copy sql statement";
qDebug() << archiveDataQry.lastError();
}



A couple of other general comments about your code. QSqlQuery::prepare returns a bool, don't ignore the return value and assume everything worked. You also seem to be changing your table names from example to example, so I have no clue if you're using the right table names from the main database, same for your archive database. Double check those names to ensure you are specifying tables that exist and have the expected number of columns, etc.


I messed around with the tablenames have them set now how I want them. The two databases are: userLog.db with table usereventlog7 and userLogArchive.db with table usereventarchive and there is only four fields id, userName eventMessage, dateTime, both tables are set up the same data types. databases and names are now setup correct



Edit: Other portions of your post don't make any sense, i.e. you are using QSqlQuery::bindValue when your SQL statement has no positional or named arguments???

I was trying to use two different connections at first and using the first connections query select values and binding them with the second connections insert statement.... I see what your saying doesn’t make sense with the prepare statement I have in the post above. Below is the statement I meant to include with that snippet of code but wasn't working



void sqliteModel::archiveEvent(){
dbConnect();
archiveDbConnect();

QSqlQuery archiveDataQry("SELECT * FROM userlogevents7", m_selectDataBase);
QSqlQuery copyDataQry(m_archiveDataBase);

QString id;
QString userName;
QString eventMessage;
QDate dateTime;

if(archiveDataQry.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << archiveDataQry.lastError();
}

m_selectDataBase.transaction();

copyDataQry.prepare("INSERT INTO userLogArchive.usereventarchive (id, userName, eventMessage, dateTime) VALUES (:id, :userName, :eventMessage, :dateTime) FROM userLog.userlogevents7");

while(archiveDataQry.next()){
id = archiveDataQry.record().value(0).toString();
userName = archiveDataQry.record().value(1).toString();
eventMessage = archiveDataQry.record().value(2).toString();
dateTime = archiveDataQry.record().value(3).toDate();
copyDataQry.bindValue(0,id);
copyDataQry.bindValue(1, userName);
copyDataQry.bindValue(2, eventMessage);
copyDataQry.bindValue(3, dateTime);
if(copyDataQry.exec()){
qDebug()<<"copy sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with copy sql statement";
qDebug() << copyDataQry.lastError();
}
}
m_selectDataBase.commit();
m_selectDataBase.close();
}


I tried setting it up using ATTACH DATABASE sqlite cmd but cant get it to work either get error on exec() in if condition for insert statement unable to fetch row


void sqliteModel::archiveEvent(){
dbConnect();

QSqlQuery archiveDataQry(m_selectDataBase);
archiveDataQry.prepare("ATTACH DATABASE '/home/amet/userLogArchive.db' as db2");

if(archiveDataQry.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << archiveDataQry.lastError();
}

m_selectDataBase.transaction();
archiveDataQry.prepare("INSERT INTO db2.usereventarchive SELECT * FROM main.usereventlog7");

if(archiveDataQry.exec()){
qDebug()<<"copy sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with copy sql statement";
qDebug() << archiveDataQry.lastError();
}

m_selectDataBase.commit();
m_selectDataBase.close();
}


Added after 6 minutes:

update the issues seems to be in my prepare statement its not able to find my usereventlog7 table and not sure why......


archiveDataQry.prepare("INSERT INTO db2.usereventarchive SELECT * FROM main.usereventlog7");

Added after 5 minutes:

update noticed simple typo fixed an works fine now thanks for suggesting checking bool value of prepare statement!

Added after 16 minutes:

update: Okay I got both ways working and found my mistakes in both of them where issue with prepare statement I think I will use the attach database way cleaner faster less code

===two connection=====


dbConnect();
archiveDbConnect();

QSqlQuery archiveDataQry("SELECT * FROM userlogevents7", m_selectDataBase);
QSqlQuery copyDataQry(m_archiveDataBase);

int id;
QString userName;
QString eventMessage;
QDate dateTime;

if(archiveDataQry.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << archiveDataQry.lastError();
}

m_selectDataBase.transaction();

if(copyDataQry.prepare("INSERT INTO usereventarchive (id, userName, eventMessage, dateTime) VALUES (:id, :userName, :eventMessage, :dateTime)"))
{
qDebug()<<"prepare sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with prepare sql statement";
qDebug() << copyDataQry.lastError();
}

while(archiveDataQry.next()){
id = archiveDataQry.record().value(0).toInt();
userName = archiveDataQry.record().value(1).toString();
eventMessage = archiveDataQry.record().value(2).toString();
dateTime = archiveDataQry.record().value(3).toDate();
copyDataQry.bindValue(0,id);
copyDataQry.bindValue(1, userName);
copyDataQry.bindValue(2, eventMessage);
copyDataQry.bindValue(3, dateTime);
if(copyDataQry.exec()){
qDebug()<<"copy sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with copy sql statement";
qDebug() << copyDataQry.lastError();
}
}
m_selectDataBase.commit();
m_selectDataBase.close();
}


====ATTACH DATABASE====


void sqliteModel::archiveEvent(){
//---connect to DB---//
dbConnect();

//---attach archive DB to connection---//
QSqlQuery archiveDataQry(m_selectDataBase);
archiveDataQry.prepare("ATTACH DATABASE '/home/amet/userLogArchive.db' as db2");

//---execute attach DB---//
if(archiveDataQry.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << archiveDataQry.lastError();
}

//---prepare sql copy---//
if(archiveDataQry.prepare("INSERT INTO db2.usereventarchive SELECT * FROM main.userlogevents7")){
qDebug()<<"prepare sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with prepare sql statement";
qDebug() << archiveDataQry.lastError();
}

//---execute sql copy---//
if(archiveDataQry.exec()){
qDebug()<<"copy sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with copy sql statement";
qDebug() << archiveDataQry.lastError();
}

//---commit & close---//
m_selectDataBase.commit();
m_selectDataBase.close();
}

[jfinn88]

update: here is my archive function works okay so far...

Copies rows from one database table that are older than 30 days and stores them in another database table for archiving.



void sqliteModel::archiveEvent(){
//---connect to DB---//
dbConnect();

//---attach archive DB to connection---//
QSqlQuery archiveDataQry(m_selectDataBase);
archiveDataQry.prepare("ATTACH DATABASE '/home/amet/userLogArchive.db' as db2");

//---execute attach DB---//
if(archiveDataQry.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << archiveDataQry.lastError();
}

m_selectDataBase.transaction();

//---get archive date vlaue--//
QSqlQuery dateQry("SELECT * FROM userlogevents7", m_selectDataBase);

QString dateStr;
QDate archiveDate = QDate::currentDate().addDays(-30);
//QDate::fromString(archiveDate, "yyyy-MM-dd");
qDebug() << "archiveDate: "+archiveDate.toString("yyyy-MM-dd");

bool prepareSqlBool;
prepareSqlBool = archiveDataQry.prepare("INSERT INTO db2.usereventarchive SELECT * FROM main.userlogevents7 WHERE dateTime < ?");
archiveDataQry.addBindValue(archiveDate);

//---prepare sql copy---//
if(prepareSqlBool){
qDebug()<<"prepare sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with prepare sql statement";
qDebug() << archiveDataQry.lastError();
}

//---execute sql copy---//
if(archiveDataQry.exec()){
qDebug()<<"copy sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with copy sql statement";
qDebug() << archiveDataQry.lastError();
}

//---delete old rows---//
while(dateQry.next())
{
dateStr = dateQry.value(3).toString();
QDate rowDate = QDate::fromString(dateStr, "yyyy-MM-dd");
//qDebug() << "results: "+rowDate.toString();

if(rowDate < archiveDate){
qWarning() << rowDate;
qDebug() << archiveDate;
qDebug() << "made inside delete condition: ";

archiveDataQry.prepare("DELETE FROM userlogevents7 WHERE dateTime < ?");
archiveDataQry.addBindValue(archiveDate);

//---execute delete---//
if(archiveDataQry.exec()){
qDebug()<<"delete sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with delete sql statement";
qDebug() << archiveDataQry.lastError();
}
}
else{
qDebug() << "no old records to delte";
}
}

//---commit & close---//
m_selectDataBase.commit();
m_selectDataBase.close();
}

[jfinn88]

having issue hiding and showing my calendar objects using if condition inside a mouseArea onclicked I set the calendar visibility to false and set it true onClciked however i cant get it to hide after showing.....

update: okay never mind just tried it again seems to be working able to show and hide calendar based of visible property and if condition



ColumnLayout {
id: calendarColumnLayout
x: 8
y: 54
width: 259
height: 540
Rectangle {
id: calendarRect1
width: 247
height: 247
anchors.top: parent.top
visible: false
Calendar {
id: calendar1
width: 247
height: 247
anchors.rightMargin: -11
anchors.bottomMargin: -8
anchors.leftMargin: 2
anchors.topMargin: 8
anchors.fill: parent
anchors.top: parent.top
style: CalendarStyle {
dayDelegate: Item {
Rectangle {
id: rect1
anchors.fill: parent
Label {
id: dayDelegateText1
text: styleData.date.getDate()
anchors.centerIn: parent
horizontalAlignment: Text.AlignRight
font.pixelSize: Math.min(parent.height/3, parent.width/3)
color: styleData.selected ? "red" : "black"
font.bold: styleData.selected
}
MouseArea {
anchors.horizontalCenter: parent.horizontalCenter
anchors.verticalCenter: parent.verticalCenter
width: styleData.selected ? parent.width / 2 : 0
height: styleData.selected ? parent.height / 2 : 0
Rectangle {
anchors.fill: parent
color: "transparent"
border.color: "darkorange"
}
}
}
}
}
}
}
Rectangle {
id: calendarRect2
x: 14
y: 350
width: 247
height: 247
anchors.bottomMargin: -11
anchors.bottom: parent.bottom
visible: false
Calendar {
id: calendar2
width: 247
height: 247
anchors.rightMargin: -5
anchors.bottomMargin: 28
anchors.leftMargin: -5
anchors.topMargin: -28
anchors.fill: parent
anchors.bottom: parent.bottom
style: CalendarStyle {
dayDelegate: Item {
Rectangle {
id: rect2
anchors.fill: parent
Label {
id: dayDelegateText2
text: styleData.date.getDate()
anchors.centerIn: parent
horizontalAlignment: Text.AlignRight
font.pixelSize: Math.min(parent.height/3, parent.width/3)
color: styleData.selected ? "red" : "black"
font.bold: styleData.selected
}
MouseArea {
anchors.horizontalCenter: parent.horizontalCenter
anchors.verticalCenter: parent.verticalCenter
width: styleData.selected ? parent.width / 2 : 0
height: styleData.selected ? parent.height / 2 : 0
Rectangle {
anchors.fill: parent
color: "transparent"
border.color: "darkorange"
}
onClicked: {
//---emit the submitBegnDate signal---//
//sqliteModel.searchDateRange();
}
}
}
}
}
}
}
}
Label {
id: calendarLabel
x: 73
y: 8
text: qsTr("Select Date Range")
}

Label {
id: endDatelabel
x: 104
y: 309
text: qsTr("End Date")
MouseArea {
id: endDateLabelMouseArea
anchors.fill: parent
onClicked: {
if(calendarRect2.visible == false)
{
calendarRect2.visible = true
}
else {
calendarRect2.visible = false
}
}
}
}

Label {
id: beginDatelabel
x: 97
y: 31
text: qsTr("Begin Date")
MouseArea {
id: beginDateLabelMouseArea
anchors.fill: parent
onClicked: {
if(calendarRect1.visible == false)
{
calendarRect1.visible = true
}
else {
calendarRect1.visible = false
}
}
}
}

[jefftee]

update: here is my archive function works okay so far...

Copies rows from one database table that are older than 30 days and stores them in another database table for archiving.
You should abort/rollback the transaction if you have an error with the insert for example, you would not want to continue and delete those records from the source table if they were not properly inserted into the archive table. While you do test success/failure you are only issuing success/failure messages but continue to fall through code to the next step, etc.

[jfinn88]

You should abort/rollback the transaction if you have an error with the insert for example, you would not want to continue and delete those records from the source table if they were not properly inserted into the archive table. While you do test success/failure you are only issuing success/failure messages but continue to fall through code to the next step, etc.

Thanks for bringing this up, I noticed this as well just a little bit ago and I'm using an if condition to check to make sure the insert sql statement gets executed correctly so it only executes delete if copy goes threw... here is updated code not sure if its the best way but it seem to be working fine wont delete tell copy is complete... I'll have to look into "abort/rollback" I have never done this before... I'm guessing you would jsut call the function in the else part of the clause
m_selectDataBase.rollBack();



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

//---attach archive DB to connection---//
QSqlQuery attachDbQry(m_selectDataBase);
attachDbQry.prepare("ATTACH DATABASE '/home/amet/git/rnd/userLogArchive.db' as db2");

//---execute attach DB---//
if(attachDbQry.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << attachDbQry.lastError();
}

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

//---get archive date vlaue--//
QSqlQuery dateQry("SELECT * FROM userlogevents", m_selectDataBase);

//---get currentDate minus 30days---//
QString dateStr;
QDate archiveDate = QDate::currentDate().addDays(-30);
qDebug() << "archiveDate: "+archiveDate.toString("yyyy-MM-dd");

//---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);

//---prepare sql copy---//
if(prepareSqlBool){
qDebug()<<"prepare sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with prepare sql statement";
qDebug() << copyDataQry.lastError();
}

bool copySqlBool;
copySqlBool = copyDataQry.exec();

//---execute sql copy---//
if(copySqlBool){
qDebug()<<"copy sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with copy sql statement";
qDebug() << copyDataQry.lastError();
}

QSqlQuery archiveDataQry(m_selectDataBase);
QDate rowDate;

//---Copy rows to archive then delete old rows---//
while(dateQry.next()){
//---Get date value from dateQry---//
dateStr = dateQry.value(3).toString();
rowDate = QDate::fromString(dateStr, "yyyy-MM-dd");


//---bool sql copy---//
if(copySqlBool){
//qDebug()<<"copy sql statement exicuted fine";

//---Executes only if copy qry executes---//
if(rowDate < archiveDate){
//qWarning() << rowDate;
//qDebug() << archiveDate;
//qDebug() << "made inside delete condition: ";

//---Sql delete statement---//
archiveDataQry.prepare("DELETE FROM userlogevents WHERE dateTime < ?");
archiveDataQry.addBindValue(archiveDate);

//---execute delete---//
if(archiveDataQry.exec()){
qDebug()<<"delete sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with delete sql statement";
qDebug() << archiveDataQry.lastError();
}
}
else{
//qDebug() << "no old records to delte";
//m_selectDataBase.rollBack();
}
}
else{
qDebug() << "Errors accured with copy sql statement";
qDebug() << archiveDataQry.lastError();
}
}

//---commit transaction & close---//
m_selectDataBase.commit();
m_selectDataBase.close();
}

[Lesiok]

I read the whole story and the question arises: is SQLite mandatory or you can use another database ?

[jfinn88]

I have been asked to do this in Sqlite3 I believe its used in another part of the project. I believe we pick sqlite for this project because it gets embedded inside the application, and do to its speed and efficiency is a better choice and we dont need different user to access the DB. This has been a good project for me because I don’t have much experience with C++ and have never used Qt frame work, IDE or QML

Added after 1 26 minutes:

I want to emit a mesageBox if any errors our thrown but messageBox is not showing up... calling it in my c++ fcn

===userEventLog.h====

signals:
void alertMsg(int icon, QString title, QString message);

and then call it like this but when I run the program messageBox wont display.... is there a stack setting needs to be set to 1 or something to put it on top level ?

====userEventLog.cpp====

emit alertMsg(QMessageBox::Warning, "Error pulling data from database...", "message");

need to connect signal in qml I tried

====UserEventDialog===


signal alertMsg(int icon, string title, string msg_text)

//---Get error here---//
onAlertMsg: {
msgDialog.text = msg_text
msgDialog.title = title
msgDialog.icon = icon
msgDialog.open()
}

Connections{
target: UserEventLog
onAlertMsg:{
alertDialog.msg_text = msg_text
alertDialog.title = title
alertDialog.icon = icon
viewRect.state = "ALERT"

}
}

states: [
State {
name: "ALERT"
PropertyChanges {target: userevent_item; enabled: false; opacity: .7}
PropertyChanges {target: alertDialog; visible: true}
}
]

AlertDialog{
id: alertDialog
anchors.verticalCenterOffset: -75
anchors.horizontalCenterOffset: -50
title: "Alert"
msg_text: ""
icon: 1
onClose:{
viewRect.state = ""

}
}

[jfinn88]

update: cleaned up qml code was getting confused on signal based off example I was using had dead code in it ...

====qml==========


Connections{
target: UserEventLog
onAlertDbMsg:{
alertDialog.msg_text = msg_text
alertDialog.title = title
alertDialog.icon = icon
view_rect.state = "ALERT"
console.log("made it inside alert");
}
}

states: [
State {
name: "ALERT"
PropertyChanges {target: view_rect; enabled: false; opacity: .7;}
PropertyChanges {target: alertDialog; visible: true;}

}
]

AlertDialog{
id: alertDialog
anchors.verticalCenterOffset: -75
anchors.horizontalCenterOffset: -50
title: "Alert"
msg_text: ""
icon: 1
onClose:{
view_rect.state = ""
}
}


=====c++=====


emit alertDbMsg(QMessageBox::Warning, "Database Error Message", "Error pulling data from database...");


=====header file=====


signals:
void alertDbMsg(int icon, QString title, QString msg_text);

[jfinn88]

not able to get a border around my table view ?



Rectangle {
id: tableViewRect
width: 880
height: 490
anchors.verticalCenter: parent.verticalCenter
anchors.left: parent.left
anchors.leftMargin: 10
border.width: 10
border.color: "lightsteelblue"
color: "transparent"
TableView {
id: tableView
anchors.fill: parent
sortIndicatorVisible: true
sortIndicatorOrder: 1
sortIndicatorColumn: 1
frameVisible: true
model: UserEventLog
style: TableViewStyle {
// frame: Rectangle {
// border.color: "lightsteelblue"
// border.width: 50
// }
headerDelegate: Rectangle {
height: textItem.implicitHeight * 1.2
width: textItem.implicitWidth
color: "lightsteelblue"
Text {
id: textItem
anchors.centerIn: parent
text: styleData.value
}
Rectangle {
anchors.right: parent.right
anchors.top: parent.top
anchors.bottom: parent.bottom
width: 3
color: "yellow"
}
}
}
TableViewColumn {
role: "id"
title: "id"
width: 100
}
TableViewColumn {
role: "userName"
title: "User Name"
width: 200
}
TableViewColumn {
role: "eventMessage"
title: "Event Message"
width: 372
}
TableViewColumn {
role: "dateTime"
title: "Date Time"
width: 201
}
}
}

[jfinn88]

found a solution: just messed around with my outer object rectangle and anchoring got a border around my tableView now! I made the rectangle little larger than tableView then enter tableView in the Rect.



Rectangle {
width: 880
height: 490
anchors.verticalCenter: parent.verticalCenter
anchors.left: parent.left
anchors.leftMargin: 10
color: "transparent"
radius: 7
border.color: "lightsteelblue"
border.width: 5
visible: true
TableView {
id: tableView
width: 870
height: 480
anchors.centerIn: parent
sortIndicatorVisible: true
sortIndicatorOrder: 1
sortIndicatorColumn: 1
//frameVisible: true
model: UserEventLog
style: TableViewStyle {
headerDelegate: Rectangle {
height: textItem.implicitHeight * 1.2
width: textItem.implicitWidth
color: "lightsteelblue"
Text {
id: textItem
anchors.centerIn: parent
text: styleData.value
}
Rectangle {
anchors.right: parent.right
anchors.top: parent.top
anchors.bottom: parent.bottom
width: 3
color: "yellow"
}
}
}
TableViewColumn {
role: "id"
title: "id"
width: 100
}
TableViewColumn {
role: "userName"
title: "User Name"
width: 200
}
TableViewColumn {
role: "eventMessage"
title: "Event Message"
width: 372
}
TableViewColumn {
role: "dateTime"
title: "Date Time"
width: 201
}
}
}

[jefftee]

You may have better success starting new threads for different subjects. :)

[jfinn88]

I realized this an already did :) thanks