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

N

nickysuwandi

Member
Licensed User
Longtime User
Total value = 1628375

1. when using this code return = 1628380, table trhinvfa content only 1 row
B4X: 
Dim total As Long
total =Sql1.ExecQuerySingleResult("select total from trhinvfa")

2. when using this code return = 1628375
B4X: 
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
 
Sort by date Sort by votes
N

nickysuwandi

Member
Licensed User
Longtime User
Erel said:
Tip: don't use Cursor. Use ResultSet instead.

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

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
);
 
Upvote 0

Erel

B4X founder
Staff member
Licensed User
Longtime User
I made a small test and the output I get is:

1.62838e+06

B4X: 
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.
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Erel said:
t is better to switch to ExecQuery2 and get the value with ResultSet.GetInt.
Click to expand...
LucaMs said:
Total is a REAL
Click to expand...

You can keep the data type as REAL, but use any one of these queries, it will display the correct value: 1628375
B4X: 
total =Sql1.ExecQuerySingleResult("select printf('%f',total) from trhinvfa") 'will display:  1628375
or this:
B4X: 
total =Sql1.ExecQuerySingleResult("select printf('%u',total) from trhinvfa") 'will display:  1628375
 
Upvote 0
N

nickysuwandi

Member
Licensed User
Longtime User
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.
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
nickysuwandi said:
i hope in next update of Sql1 function, it can return real/numeric value (sqlite), not only string couse rounding value.
Click to expand...
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:
B4X: 
Dim total As Int
Dim total As Double
Dim total As Long
Dim total As String
 
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Question
Android Question Doubt on Number Format?
2
Replies
36
Views
5K
TILogistic
  • Question
Android Question Rounding decimal point number
Replies
14
Views
6K
emexes
E
C
  • Question
Android Question Im having a problem when a number goes negative
Replies
6
Views
1K
Claudio Oliveira
A
  • Question
Android Question crc computing error
Replies
4
Views
1K
ajk
A
  • Question
Android Question SQLiteException in GetBlob
Replies
2
Views
2K
welu1805
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…