Select data from database SQLite error - Help please!

[sousadaniel7]

Good afternoon.

How do I do a SELECT to an SQLite database?

I have the following:

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
 
    db.setDatabaseName("bd_agt");
 
    if(db.open()){
        qDebug() << "Opening database, status: " << db.open();
    }
 
    QSqlQuery query("SELECT * FROM tag");
         while (query.next()) {
             QString nome = query.value(1).toString();
             ui->label->setText(nome);
         }
 
         qDebug() << query.size();

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");

    db.setDatabaseName("bd_agt");

    if(db.open()){
        qDebug() << "Opening database, status: " << db.open();
    }

    QSqlQuery query("SELECT * FROM tag");
         while (query.next()) {
             QString nome = query.value(1).toString();
             ui->label->setText(nome);
         }

         qDebug() << query.size();

To copy to clipboard, switch view to plain text mode 

But in query.size () always returns -1, but I have values ​​in the database
How can I fix this?

Regards,
Daniel Sousa

[kito]

check if the connection is opening.
Is your code getting into the if statement?

[sousadaniel7]

He says that the database opens. Where do I have to file for the database? Within the project folder or in the debug folder?


Regards,
Daniel Sousa

[kito]

To see whether connection opens the statement at line 6 in your code should be executed.

[sousadaniel7]

It appears on the console: Opening database, status: true

[kito]

if that is the case then everything seems ok,try show output of nome,and declare it out side while statement.

[sousadaniel7]

I've seen the problem! I put subestitui db.setDatabaseName by line 3 ("/ Users / danielsousa / Qt / BaseDados / bd_agt");

However I want to use only the name of the database that is to take on multiple operating systems. How do I do that?


regards,
Daniel Sousa

[Jonny174]

Try this code:

QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE", "bd_agt" );
db.setDatabaseName( "full_path_to_db" );
db.setUserName( "UserName" );
db.setPassword( "UserPass" );
 
if (db.open())
{
  QSqlQuery query( QSqlDatabase::database( "bd_agt" ) );
 
  if (query.exec( "SELECT * FROM tag" ))
  {
    while (query.next()) 
    {
      QString nome = query.value(1).toString();
      ui->label->setText( nome );
    }
 
    query.clear();
  }
  else
    qDebug() << "Error: " << query.lastError().text();
}
else
  qDebug() << "Error: " << db.lastError().databaseText();

QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE", "bd_agt" );
db.setDatabaseName( "full_path_to_db" );
db.setUserName( "UserName" );
db.setPassword( "UserPass" );

if (db.open())
{
  QSqlQuery query( QSqlDatabase::database( "bd_agt" ) );

  if (query.exec( "SELECT * FROM tag" ))
  {
    while (query.next()) 
    {
      QString nome = query.value(1).toString();
      ui->label->setText( nome );
    }

    query.clear();
  }
  else
    qDebug() << "Error: " << query.lastError().text();
}
else
  qDebug() << "Error: " << db.lastError().databaseText();

To copy to clipboard, switch view to plain text mode 

[nish]

ideally, the db file should be stored relative to your executable file. see QCoreApplication::applicationDirPath

[Jonny174]

qDebug() << query.size() == -1 ? query.numRowsAffected() : query.size();

[Spitfire]

From documentation: QSqlQuery::size():
Returns the size of the result (number of rows returned), or -1 if the size cannot be determined or if the database does not support reporting information about query sizes.

From documentation: QSqlQuery::numRowsAffected():
Returns the number of rows affected by the result's SQL statement, or -1 if it cannot be determined.

As per quotes above, if everything works but size is -1, then it can't be determined and that's all.

When working with SQLite, numRowsAffected() nor size() never worked for me.

As long as next() works, you have nothing to worry about.

[sousadaniel7]

Hello
I'm trying to find a record in a database through the name. I have the following code:

//Metodo que procura uma tag através do seu nome
bool BaseDados::procurarTagPeloNome(QString nome){
    QSqlQuery query;
    query.prepare("SELECT * FROM tag WHERE nome=:nome ");
    query.bindValue(":nome", nome);
    query.exec();
 
    qDebug() << query.size();
 
    if(!query.isSelect()){
        return true;
    }else{
        return false;
    }
}

//Metodo que procura uma tag através do seu nome
bool BaseDados::procurarTagPeloNome(QString nome){
    QSqlQuery query;
    query.prepare("SELECT * FROM tag WHERE nome=:nome ");
    query.bindValue(":nome", nome);
    query.exec();

    qDebug() << query.size();

    if(!query.isSelect()){
        return true;
    }else{
        return false;
    }
}

To copy to clipboard, switch view to plain text mode 

However, the size of me and I have always -1 there a record with the same name.
Am I doing something wrong?

Regards,
Daniel Sousa

[ChrisW67]

Please read the post immediately above yours. Only some database engines support reporting the number of rows returned in a query. For Sqlite:

QSqlDatabase db;
    qDebug() << db.driver()->hasFeature(QSqlDriver::QuerySize);
// Output
false

QSqlDatabase db;
    qDebug() << db.driver()->hasFeature(QSqlDriver::QuerySize);
// Output
false

To copy to clipboard, switch view to plain text mode 

If you only want to know if a record exists then:

//Metodo que procura uma tag através do seu nome
bool BaseDados::procurarTagPeloNome(const QString &nome){
    QSqlQuery query;
    query.prepare("SELECT 1 FROM tag WHERE nome=:nome ");
    query.bindValue(":nome", nome);
    return query.exec() && qry.next();
// If the query succeeds and returns a row (or more) then returns true.
}

//Metodo que procura uma tag através do seu nome
bool BaseDados::procurarTagPeloNome(const QString &nome){
    QSqlQuery query;
    query.prepare("SELECT 1 FROM tag WHERE nome=:nome ");
    query.bindValue(":nome", nome);
    return query.exec() && qry.next();
// If the query succeeds and returns a row (or more) then returns true.
}

To copy to clipboard, switch view to plain text mode 

[sousadaniel7]

Please read the post immediately above yours. Only some database engines support reporting the number of rows returned in a query. For Sqlite:

QSqlDatabase db;
    qDebug() << db.driver()->hasFeature(QSqlDriver::QuerySize);
// Output
false

QSqlDatabase db;
    qDebug() << db.driver()->hasFeature(QSqlDriver::QuerySize);
// Output
false

To copy to clipboard, switch view to plain text mode 

If you only want to know if a record exists then:

//Metodo que procura uma tag através do seu nome
bool BaseDados::procurarTagPeloNome(const QString &nome){
    QSqlQuery query;
    query.prepare("SELECT 1 FROM tag WHERE nome=:nome ");
    query.bindValue(":nome", nome);
    return query.exec() && qry.next();
// If the query succeeds and returns a row (or more) then returns true.
}

//Metodo que procura uma tag através do seu nome
bool BaseDados::procurarTagPeloNome(const QString &nome){
    QSqlQuery query;
    query.prepare("SELECT 1 FROM tag WHERE nome=:nome ");
    query.bindValue(":nome", nome);
    return query.exec() && qry.next();
// If the query succeeds and returns a row (or more) then returns true.
}

To copy to clipboard, switch view to plain text mode 

Thank longer works. I thought only query.size () is that eventually could not be supported.