How to get decimal value from table
-
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
-
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.
-
@blackout69
Be aware: Your problem is that SQL has aDecimal()
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 nativedecimal
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
exact88.33500
rounds todouble
representation equivalent to, say,88.3349999999999
, hence your rounding issue (unless @VRonin is correct and it's to do withqDebug()
. EDIT I don't think it can be: yourqDebug() << query_mov.value(0).toString(); // print "88.33"
eliminates it from being aqDebug()
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.... -
Thank you for your answers,
surely the problem is what he described JonB.
I solved by taking the value as intQString 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