How to get decimal value from table

blackout69

Hello,
I encounter the following problem by taking the values from a table with a decimal(9,5) field where there are written values with 5 decimals.
Example 88.33500.
My goal is to take the value and round it to the second decimal place using the round () function
https://www.programiz.com/cpp-programming/library-function/cmath/round

// Value on the field decimal(9,5) is 88.33500
QString query_2 = QString("SELECT value FROM table WHERE id = id_doc");

QSqlQuery query_mov;
query_mov.prepare(query_2);
query_mov.exec();
while (query_mov.next())
   {
	qDebug() << query_mov.value(0).toString();			// print "88.33"
        qDebug() << query_mov.value(0).toDouble();			// print 88.33
	qDebug() << round(query_mov.value(0).toDouble() * 100) / 100;	// print 88.33
	qDebug() << round(88.33500 * 100) / 100;			// print 88.34. Correct value
   }

As you can see the problem lies in taking the entire value from the decimal field of the table without losing the decimals. But strangely I also lose them with query_mov.value (0) .toString (). While if I write directly the decimal value including decimals, I do not lose anything.
Could someone give me some advice on how to fix it?
Thank you in advance.

blackout69

koahnig

@blackout69

I guess the problem is already in theSql table. Most likely there is only 88.33 stored or there is some other problem causing clipping of the field.

This should give you already what is stored there. Trailing digits shall be visible.

	qDebug() << query_mov.value(0).toString();			// print "88.33"

The other two cases are simply outputting what is there and that is consitent with the string content. Therefore, I would not expect the problem in the section you are showing here.

VRonin

It might also be qDebug trying to be clever try const double checkVal = query_mov.value(0).toDouble(); and then inspect that variable in the debugger to see if it's correct

JonB

@blackout69
Be aware: Your problem is that SQL has a Decimal() type while Qt/C++ does not. This means that the low-level Qt code which reads the value from the SQL has already converted it from the database native decimal representation to a C++ double. This is approximate, and the C++ value is not guaranteed/will not be exactly the same as the decimal in the database. There is nothing you can do about this limitation. You'll have to hope that the approximation/rounding is good enough for your purposes.

In this case I'm guessing that decimal exact 88.33500 rounds to double representation equivalent to, say, 88.3349999999999, hence your rounding issue (unless @VRonin is correct and it's to do with qDebug(). EDIT I don't think it can be: your qDebug() << query_mov.value(0).toString(); // print "88.33" eliminates it from being a qDebug() issue).

P.S.
IMPORTANT
Have a read of https://forum.qt.io/topic/87280/how-to-correctly-read-decimal-values-from-mysql-with-qsqlquery/4. There I see @SGaist suggests you can modify behaviour via https://dev.mysql.com/doc/refman/5.5/en/c-api-prepared-statement-type-conversions.html, I haven't looked into how easy that is, it might be good....

blackout69

Thank you for your answers,
surely the problem is what he described JonB.
I solved by taking the value as int

QString query_2 = QString("SELECT (value * 100000) FROM table WHERE id = id_doc");
int value = query_mov.value(0).toInt();
I perform my calculations ...
return value / 100000;

blackout69