Create postgreSQL database with Qt

ivanicy

Hello!!

I am trying to create a postgre database with Qt and then I want to check it with pgAdmin.

Which query and which connections I have to do before to do it? I have tried this, but it doesn't work:

m_pDatabase = new QSqlDatabase(QSqlDatabase::addDatabase(sDriverName));        

    if (m_pDatabase != nullptr) {
        m_pDatabase->setConnectOptions();
        m_pDatabase->setHostName(sServerName);
//        m_pDatabase->setDatabaseName(sDatabaseName);
        m_pDatabase->setUserName(sUserName);
        m_pDatabase->setPassword(sPassword);
    }

    QSqlQuery query(*m_pDatabase);
    bool bCorrect = query.exec("CREATE DATABASE " + sDatabaseName);
    if (!bCorrect) {
        qDebug() << "[DATABASE ERROR]: " << m_pDatabase->lastError().text();
        IFDBG (1) { CFileLog::write(QString("[DATABASE ERROR]: " + QString(m_pDatabase->lastError().text())).toStdString().c_str()); }
        return false;
    } else {
        bCorrect = query.exec("USE " + sDatabaseName);
    }

Thank you very much!!

jsulm

@ivanicy said in Create postgreSQL database with Qt:

This code is what has been working for me so far

So, create the database and tables now after you open the database

JonB

@ivanicy
Where do you open the database?

I have tried this, but it doesn't work:

And what does your m_pDatabase->lastError().text() say?

Christian Ehrlicher

And why do you hold QSqlDatabase as a pointer (and dereference a nullptr). Please read the docs on how to use QSqlDatabase.

ivanicy

@JonB I open the database a few lines later, but it can't open it. lastError() is empty:

[DATABASE ERROR]: ""

Christian Ehrlicher

@ivanicy said in Create postgreSQL database with Qt:

I open the database a few lines later,

Please post your code, not only some single lines.

ivanicy

@Christian-Ehrlicher Ok sorry, this is the function:

bool CDatabaseManager::connectToDatabase(const char *sDriverName, QString sServerName, QString sDatabaseName, QString sUserName, QString sPassword)
{
    m_pDatabase = new QSqlDatabase(QSqlDatabase::addDatabase(sDriverName));        

    if (m_pDatabase != nullptr) {
        m_pDatabase->setConnectOptions();
        m_pDatabase->setHostName(sServerName);
//        m_pDatabase->setDatabaseName(sDatabaseName);
        m_pDatabase->setUserName(sUserName);
        m_pDatabase->setPassword(sPassword);
    }

    QSqlQuery query(*m_pDatabase);
    bool bCorrect = query.exec("CREATE DATABASE " + sDatabaseName);
    if (!bCorrect) {
        qDebug() << "[DATABASE ERROR]: " << m_pDatabase->lastError().text();
        IFDBG (1) { CFileLog::write(QString("[DATABASE ERROR]: " + QString(m_pDatabase->lastError().text())).toStdString().c_str()); }
        return false;
    } else {
        bCorrect = query.exec("USE " + sDatabaseName);
    }

    bCorrect = query.exec("CREATE TABLE IF NOT EXISTS Alarms ("
                          "alarmCode VARCHAR(10) NOT NULL,"
                          "cameraSN VARCHAR(10),"
                          "cameraID VARCHAR(2) NOT NULL,"
                          "presetID VARCHAR(2) NOT NULL,"
                          "roiID VARCHAR(2) NOT NULL,"
                          "alarmDate DATE NOT NULL,"
                          "alarmTime TIME NOT NULL,"
                          "description VARCHAR(100),"
                          "data VARCHAR(300),"
                          "image BYTEA,"
                          "pathVideo VARCHAR(300),"
                          "CONSTRAINT pk_alarms PRIMARY KEY (alarmCode, cameraID, presetID, roiID, alarmDate, alarmTime));");

    if (!bCorrect) {
        qDebug() << "[DATABASE ERROR]: " << m_pDatabase->lastError().text();
        IFDBG (1) { CFileLog::write(QString("[DATABASE ERROR]: " + QString(m_pDatabase->lastError().text())).toStdString().c_str()); }
    }

    bCorrect = query.exec("CREATE TABLE IF NOT EXISTS Data ("
                          "dataCode VARCHAR(10) NOT NULL,"
                          "cameraSN VARCHAR(10),"
                          "cameraID VARCHAR(2) NOT NULL,"
                          "presetID VARCHAR(2) NOT NULL,"
                          "roiID VARCHAR(2) NOT NULL,"
                          "dataDate DATE NOT NULL,"
                          "dataTime TIME NOT NULL,"
                          "description VARCHAR(100),"
                          "data VARCHAR(300),"
                          "CONSTRAINT pk_data PRIMARY KEY (dataCode, cameraID, presetID, roiID, dataDate, dataTime));");

    if (!bCorrect) {
        qDebug() << "[DATABASE ERROR]: " << m_pDatabase->lastError().text();
        IFDBG (1) { CFileLog::write(QString("[DATABASE ERROR]: " + QString(m_pDatabase->lastError().text())).toStdString().c_str()); }
    }

    if (m_pDatabase->open()) {
        return true;
    } else {
        qDebug() << "[DATABASE ERROR]: Database not open." << m_pDatabase->lastError().text();
        return false;
    }
}

jsulm

@ivanicy You open the database AFTER you use it, how should that work?!

JonB

@ivanicy said in Create postgreSQL database with Qt:

I open the database a few lines later

No, I meant before the CREATE DATABASE!
Unless I am mistaken, you still must do this to establish a connection with your credentials to do any query.exec() even though I know I know you haven't got a database initially. Unless @Christian-Ehrlicher says otherwise....

ivanicy

I tell more details: right now, I create the database with pgAdmin, create the tables with pgAdmin, connect to them with Qt and fill them from Qt. My goal is to create the database and tables from Qt as well, to further automate the process. This code is what has been working for me so far:

bool CDatabaseManager::connectToDatabase(const char *sDriverName, QString sServerName, QString sDatabaseName, QString sUserName, QString sPassword)
{
    m_pDatabase = new QSqlDatabase(QSqlDatabase::addDatabase(sDriverName));        

    if (m_pDatabase != nullptr) {
        m_pDatabase->setConnectOptions();
        m_pDatabase->setHostName(sServerName);
        m_pDatabase->setDatabaseName(sDatabaseName);
        m_pDatabase->setUserName(sUserName);
        m_pDatabase->setPassword(sPassword);
    }

    if (m_pDatabase->open()) {
        return true;
    } else {
        qDebug() << "[DATABASE ERROR]: Database not open." << m_pDatabase->lastError().text();
        return false;
    }
}

jsulm

@ivanicy said in Create postgreSQL database with Qt:

This code is what has been working for me so far

So, create the database and tables now after you open the database

JonB

@ivanicy said in Create postgreSQL database with Qt:

m_pDatabase->setDatabaseName(sDatabaseName);

If your code works, fine. As @jsulm says you are now supposed to add your "create database" code.

But it is possible your code will now only work if sDatabaseName already exists --- have you tested when it does not yet exist? At that point it may be that you must not specify a non-existent database name here: you might to leave it blank, or for something like MS SQL Server (which I happen to know about, I don't know about PostgreSQL ) you would specify master if you wanted to connect to create a database.

ivanicy

@jsulm Thank you very much! It works perfect now.