Android Question Is this bug from sql ? ExecQuerySingleResult return rounding value from number

[nickysuwandi]

Total value = 1628375

1. when using this code return = 1628380, table trhinvfa content only 1 row

Dim total As Long
total =Sql1.ExecQuerySingleResult("select total from trhinvfa")

2. when using this code return = 1628375

Dim curs1 As Cursor
        curs1 = Sql1.ExecQuery("select total from trhinvfa")
        curs1.Position = 0
        For i = 0 To curs1.RowCount - 1
            total=curs1.GetLong("total")
            Exit
        Next
        curs1.Close

why using the method one return wrong value (rounding )?

Thanks

 

[Erel]

Tip: don't use Cursor. Use ResultSet instead.

ExecQuerySingleResult calls ResultSet.GetString to get the value. How are you defining the column type?

 

[nickysuwandi]

Tip: don't use Cursor. Use ResultSet instead.

ExecQuerySingleResult calls ResultSet.GetString to get the value. How are you defining the column type?

this the table

CREATE TABLE "trhinvfa" (
"kd_cus" TEXT,
"nama_cus" TEXT,
"kd_sales" TEXT,
"nama_sales" TEXT,
"total" REAL,
"cust" TEXT,
"sls" TEXT,
"jns_pay" TEXT,
"pay" REAL,
"ket" TEXT
);

 

[Mahares]

this the table

Change the data type from: "total" REAL to "total" TEXT
Then, run your code, it will work for you:

Dim total As Long
total =Sql1.ExecQuerySingleResult("select total from trhinvfa") 'will display:  1628375

 

[Hamied Abou Hulaikah]

total=curs1.GetLong("total")

Are you try this:

total=curs1.GetString("total")

 

[Mahares]

Are you try this:

The OP is telling you that the problem is with the first., not the second option. This is what he says: why using the method one return wrong value (rounding )?

 

[Erel]

I made a small test and the output I get is:

1.62838e+06

Dim sql As SQL
File.Delete(File.DirInternal, "1.db")
sql.Initialize(File.DirInternal, "1.db", True)
sql.ExecNonQuery($"
CREATE TABLE table1 (
    total REAL
);
"$)
sql.ExecNonQuery2("INSERT INTO table1 VALUES (?)", Array(1628375))
Log(sql.ExecQuerySingleResult("SELECT total FROM table1"))

If you convert this string to an Int then it will be 1628380.

Changing to TEXT as @Mahares wrote will work, though it is better to switch to ExecQuery2 and get the value with ResultSet.GetInt.

 

[LucaMs]

Total is a REAL, in DB and... reality; so, why do you store it in a Long variable instead of in a Double? Also, I would use ResultSet.GetDouble.

total =Sql1.ExecQuerySingleResult("select total from trhinvfa")

Without a Where clause?

 

[Mahares]

t is better to switch to ExecQuery2 and get the value with ResultSet.GetInt.

Total is a REAL

You can keep the data type as REAL, but use any one of these queries, it will display the correct value: 1628375

total =Sql1.ExecQuerySingleResult("select printf('%f',total) from trhinvfa") 'will display:  1628375

or this:

total =Sql1.ExecQuerySingleResult("select printf('%u',total) from trhinvfa") 'will display:  1628375

 

[nickysuwandi]

Thanks Erel,Mahares, LucaMS,Hah for your suggestion.

i hope in next update of Sql1 function, it can return real/numeric value (sqlite), not only string couse rounding value.
i am searching 4 hour to trace this error.

 

[Mahares]

i hope in next update of Sql1 function, it can return real/numeric value (sqlite), not only string couse rounding value.

There is already a function in SQLite. I gave you the syntax in post #9. It is Printf Apparently, you are not reading the posts carefully. You can leave your data type as REAL and any one of these declarations will work and will not round 1628375 to 1628380 , if you use the Printf mentioned above:

Dim total As Int
Dim total As Double
Dim total As Long
Dim total As String