How To Get COUNT(*) out in the Query using Values(0)?

[Mr.QT]

Hi

I have problem with this code..
it does not give me any results.
even if i try to use .size() it want work as will.

QSqlQuery query;
	query.prepare("SELECT COUNT(*) AS 'numberOfUsers'\n"
	              " FROM users AS u );
	query.exec();
 
	if (!query.isActive())
		QMessageBox::warning(this, tr("Database Error"),query.lastError().text());
 
	numberOfPages = query.value(0).toInt();

QSqlQuery query;
	query.prepare("SELECT COUNT(*) AS 'numberOfUsers'\n"
	              " FROM users AS u );
	query.exec();
		
	if (!query.isActive())
		QMessageBox::warning(this, tr("Database Error"),query.lastError().text());

	numberOfPages = query.value(0).toInt();

To copy to clipboard, switch view to plain text mode 

I do not want to use the QSqlTableModel as it slew up all the program.
I need fast way to get this COUNT number.

Using Values want work an using Results() did not work as the Handel and .asInt() function in not working as will.

Regards

[spirit]

remove \n from query text.

[Mr.QT]

Thank you spirit for the vary fast replay..
I Have toke the \n out of the code..

its now look like this

query.prepare("SELECT COUNT(*) AS 'numberOfUsers' FROM users AS u);

query.prepare("SELECT COUNT(*) AS 'numberOfUsers' FROM users AS u);

To copy to clipboard, switch view to plain text mode 

but id did get any count(*)..

There is no problem with the SQL statement.

It just that the line.

numberOfPages = query.value(0).toInt();

numberOfPages = query.value(0).toInt();

To copy to clipboard, switch view to plain text mode 

Wont gave me the number at all..
if the SQL Statement has got more than one field back it will work.
But that will make the program slow and to get something like 500

Regards

[spirit]

did you call QSqlQuery::next before calling QSqlQuery::value?
do you try to execute this query in Qt's Sqlbrowser which is located in QTDIR/demos/sqlbrowser?

[Mr.QT]

Hi spirit,
WOW it worked. thanks a lot.
That was fast.

I have changed the code to this.

query.next();
	numberOfPages = query.value(0).toInt();

query.next();
	numberOfPages = query.value(0).toInt();

To copy to clipboard, switch view to plain text mode 

I think that will not effect the speed..

But i normally get the query and it work without .next().
but the problem happened when i have COUNT(*) in the query.

I did not look at the next function before in the QT Assistant.
they say :

The following rules apply:

If the result is currently located before the first record, e.g. immediately after a query is executed, an attempt is made to retrieve the first record.
If the result is currently located after the last record, there is no change and false is returned.
If the result is located somewhere in the middle, an attempt is made to retrieve the next record.
If the record could not be retrieved, the result is positioned after the last record and false is returned. If the record is successfully retrieved, true is returned.

Thank you again.


Best Regards

[lni]

You just need to count the first column in the table, it is faster than counting all columns since they will all have the same value anyway. I think the two "AS" is not necessary.

I think the following should also work?

query.prepare("SELECT COUNT( 1 )  FROM users);  
if ( query.exec() && query.seek( 0 ) ) {
     numberOfPages = query.value(0).toString().toInt();
  }

query.prepare("SELECT COUNT( 1 )  FROM users);  
if ( query.exec() && query.seek( 0 ) ) {
     numberOfPages = query.value(0).toString().toInt();
  }

To copy to clipboard, switch view to plain text mode 

[Mr.QT]

Hi lni,

I think it will be faster.
But why query.seek( 0 ).

Regards

[lni]

Hi lni,

I think it will be faster.
But why query.seek( 0 ).

Regards

I think seek( 0 ) is not necessary in this case, and toString() is not necessary either...