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();
}

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();
}

To copy to clipboard, switch view to plain text mode 

------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();
}

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();
}

To copy to clipboard, switch view to plain text mode 

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

private:
    QSqlDatabase m_insertDataBase;
};

private:
    QSqlDatabase m_insertDataBase;
};

To copy to clipboard, switch view to plain text mode 

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

m_insertDataBase = QSqlDatabase::database("conn1");

To copy to clipboard, switch view to plain text mode 

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

m_insertDataBase = QSqlDatabase::database("conn1");

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode 

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(...);

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

To copy to clipboard, switch view to plain text mode 

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
}

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

To copy to clipboard, switch view to plain text mode 

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