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


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

[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;
}
}

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



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.
}

[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



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.
}


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