How to insert Qxlsx data into sqlite database table?

Joshika_Namani

#include "XlsxDAO.h"
#include "mainwindow.h"
#include "DatabaseDAO.h"
#include <vector>
using namespace QXlsx;
void XlsxDAO::read()
{

QXlsx::Document xlsx;

qInfo()<<"hai";
QSqlDatabase sqlite;
if(QSqlDatabase::contains("qt_sql_default_connection"))
  sqlite = QSqlDatabase::database("qt_sql_default_connection");
else
  sqlite = QSqlDatabase::addDatabase("QSQLITE");

sqlite.setDatabaseName("/home/acuvate/database/users");
Cell* cell;
int col=1;

std::vector<QVariant> vec;

if(xlsx.load())
{
qInfo()<<"hai-1";
for(int row=1;row<=1000;row++)
{
qInfo()<<"hai-2";

    cell=xlsx.cellAt(row,col);
  // Storing cell value into vector
    QVariant var=cell->readValue();
    vec.push_back(var);
   }
for(int i=0;i<=1000;i++)
  {
    //displaying vector values
        qInfo()<<vec[i];
   }
if(sqlite.open())
    {
    QSqlQuery query;
    for(int i=0;i<vec.size();i++)
    {
        query.prepare("insert into Dataset(AT) values(?)");
        query.addBindValue(vec[1]);
        if(!query.exec())
        {
            qInfo()<<"inserted";
        }
    }


    }

}
}

jsulm

@Joshika_Namani Please format your code properly.
What exactly does not work?
You do not have any error handling (hint: QSqlQuery::exec returns a bool and there is https://doc.qt.io/qt-5.15/qsqlquery.html#lastError).

Joshika_Namani

Thanks for your reply, It's executing with out any errors but table is empty.

jsulm

@Joshika_Namani said in How to insert Qxlsx data into sqlite database table?:

It's executing with out any errors

So, you see "inserted" printed (from qInfo()<<"inserted")?

Joshika_Namani

@jsulm
yes

jsulm

@Joshika_Namani How did you check that the table is empty?
I also don't see where you're closing your database.

Joshika_Namani

I have used sqlite browser its gui database.
Yes, i didn't close the database.

JonB

@Joshika_Namani said in How to insert Qxlsx data into sqlite database table?:

    if(!query.exec())
    {
        qInfo()<<"inserted";
    }

Somebody here is crazy. You said

Thanks for your reply, It's executing with out any errors but table is empty.

So, you see "inserted" printed (from qInfo()<<"inserted")?

yes

You are printing inserted if query.exec() fails. Why??

insert into Dataset(AT) values(?)

I know nothing about SQLite. Dataset(AT) is a (syntactically valid) table to insert into, is it? In any case you have QSqlQuery::lastError(). Also QSqlQuery::prepare() returns a bool for success/failure, which you should be checking.

Separate issue when you have that working:

query.addBindValue(vec[1]);

You always want to re-insert vec[1], not vec[i], right?

jsulm

@JonB said in How to insert Qxlsx data into sqlite database table?:

Somebody here is crazy

Oh, I guess I'm blind or crazy or both :-D

JonB

@jsulm I didn't mean you... :)

jsulm

@JonB I know :-) But I really should have seen the wrong if condition.

Joshika_Namani

@JonB
Sorry for that i modified everything here I'm getting an error like QSqlError("", "", "") by using lastError() method.

JonB

@Joshika_Namani said in How to insert Qxlsx data into sqlite database table?:

i modified everything here

In that case show new code! :)

Joshika_Namani

@JonB
QXlsx::Document xlsx("Test.xlsx");
QSqlDatabase sqlite;
sqlite = QSqlDatabase::addDatabase("QSQLITE");

sqlite.setDatabaseName("/home/Qt Program/DB/Database");
Cell* cell;
int col=1;

std::vector<QVariant> vec;

if(xlsx.load())
{
for(int row=1;row<=20;row++)
{

    cell=xlsx.cellAt(row,col);
    QVariant var=cell->readValue();
    vec.push_back(var);
   }
for(int i=0;i<=20;i++)
  {
        qInfo()<<vec[i];
   }
if(sqlite.open())
    {
 
    QSqlQuery query;
    for(int i=0;i<vec.size();i++)
    {
        
        query.prepare("insert into Data(AT) values(?)");
        query.addBindValue(vec[i]);
        if(query.exec())
        {
            qInfo()<<"inserted";
        }
        else
        {
            qInfo()<<sqlite.lastError();
        }
    }


    }
else
{
    qInfo()<<"not opened";
}

}
}

JonB

@Joshika_Namani
You have not answered or acted on what I already wrote. Please don't ignore suggestions and make responders type the same thing again. I already wrote:

insert into Dataset(AT) values(?): I know nothing about SQLite. Dataset(AT) is a (syntactically valid) table to insert into, is it?
Also QSqlQuery::prepare() returns a bool for success/failure, which you should be checking.
Plus, you check QSqlDatabase::lastError() after QSqlQuery::exec() fails, but that says

Failures that occur in conjunction with an individual query are reported by QSqlQuery::lastError().

which you should be checking instead/as well.

Please read the documentation for the functions you call, and check all errors before asking us, it saves you & us time.

Joshika_Namani

@JonB
sorry for that,

insert into Dataset(AT) values(?): I know nothing about SQLite. Dataset(AT) is a (syntactically valid) table to insert into, is it?
Yes, it is valid statement in sqlite.
Also QSqlQuery::prepare() returns a bool for success/failure, which you should be checking.
Returns true if the query is prepared successfully; otherwise returns false.
Plus, you check QSqlDatabase::lastError() after QSqlQuery::exec() fails, but that says
yes, I got error. like QSqlError("","","");

I checked again there was a problem in database but it solved thanks alot.
Now, I'm able to insert data into database from QXlsx file.