Android Question ExecQuerySingleResult no decimals for numbers>100k

[mokrokuce]

Hello, I have a strange problem.
I am multiplying two numbers (eg. 100.9 and 1000.11) within a query, and the result is a decimal number, (eg. 100911.099), but ExecQuerySingleResult is returning number without decimals (100911).
I have made a small project to illustrate the problem (make sure to include SQL library):

#Region  Project Attributes
    #ApplicationLabel: B4A Example
    #VersionCode: 1
    #VersionName:
    'SupportedOrientations possible values: unspecified, landscape or portrait.
    #SupportedOrientations: unspecified
    #CanInstallToExternalStorage: False
#End Region

#Region  Activity Attributes
    #FullScreen: False
    #IncludeTitle: True
#End Region

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.

End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.
    Dim sql1 As SQL
    Dim lbl1, lbl2 As Label
    Dim lbl3, lbl4 As Label
   
End Sub

Sub Activity_Create(FirstTime As Boolean)
    Dim n1, n2 As Double
    'Do not forget to load the layout file created with the visual designer. For example:
    'Activity.LoadLayout("Layout1")
    sql1.Initialize(File.DirDefaultExternal, "test.db",True)
    sql1.ExecNonQuery("drop table if exists tmp")
    sql1.ExecNonQuery("create table tmp (num1 real, num2 real)")
    'result<100000
    lbl1.Initialize("")
    lbl2.Initialize("")
    n1=100.01
    n2=99.001
    sql1.ExecNonQuery("delete from tmp ")
    sql1.ExecNonQuery("insert into tmp (num1, num2) select " & n1 & "," & n2)
    lbl1.Text=n1*n2
    lbl2.Text=sql1.ExecQuerySingleResult("Select num1*num2 from tmp")
    Activity.AddView(lbl1,0,0,50%x,60dip)
    Activity.AddView(lbl2,0,60dip,50%x,60dip)

    'result>100000
    lbl3.Initialize("")
    lbl4.Initialize("")
    n1=100.9
    n2=1000.11
    sql1.ExecNonQuery("delete from tmp ")
    sql1.ExecNonQuery("insert into tmp (num1, num2) select " & n1 & "," & n2)
    lbl3.Text=n1*n2
    lbl4.Text=sql1.ExecQuerySingleResult("Select num1*num2 from tmp")
    Activity.AddView(lbl3,50%x,0,50%x,60dip)
    Activity.AddView(lbl4,50%x,60dip,50%x,60dip)
   
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

even this

sql1.ExecQuerySingleResult("Select 100911.099 from tmp")

gets me nowhere.

For results below 100000 I see decimals, but only one decimal place for results between 10000 and 99999, and two decimals for results below 10000.

If I try to retrieve results via cursor, I get them fine, but this is an issue that has come up in an existing project where ExecQuerySingleResult is heavily utilized, so that would mean a lot of code changing.

Is there a solution to this?

 

[Mahares]

sql1.ExecQuerySingleResult("Select 100911.099 from tmp")

If you check the syntax, singleresult yields a string or Int. Therefore the below code works:

Log(SQL1.ExecQuerySingleResult("Select '100911.099' from temp") )  'returns 100911.099

You might have to use cursor or post you complete project as 'File, 'Export as Zip' from IDE instead of putting the text on the screen. Someone may have a solution.

 

[sorex]

you mean this?

Dim v As Double
v=sql1.ExecQuerySingleResult("Select num1*num2 from tmp")
lbl4.Text=NumberFormat(v,0,4)

 

[sorex]

ignore the above.. I mis interpret your post.