Android Question [SOLVED] ExecQuerySingleResult not return correct value

[incendio]

Hi guys,

I have this code

Sub Test as String
    Private Val As Object
   
    Val = SqlMem.ExecQuerySingleResult("SELECT SUM(VAL) from MemTable")
   
    If Val <> Null Then
        LogColor(NumberFormat2(1949371083,0,0,0,True),Colors.Green)
        LogColor(NumberFormat2(Val,0,0,0,True),Colors.Magenta)
        Return NumberFormat2(Val,0,0,0,True)

    End If
    Return "0"
End Sub

The correct return value from that sub should be 1,949,371,083, but it return 1,949,370,000.

Any hints how to fix this problem?

 

[Erel]

What is the output of:

Log(SqlMem.ExecQuerySingleResult("SELECT SUM(VAL) from MemTable"))

You will probably need to use ExecQuery with ResultSet.GetLong.

 

[Mahares]

Any hints how to fix this problem?

This would work for you:

Private Val As Double
    Val = Starter.SQL1.ExecQuerySingleResult("SELECT SUM(ifnull(Val,0)) from MemTable")    
    If IsNumber(Val) Then
        LogColor(NumberFormat2(Val,0,0,0,True),Colors.Magenta)
    End If

 

[incendio]

You will probably need to use ExecQuery with ResultSet.GetLong.

This method works!

Is there a bug in ExecQuerySingleResult or there is some Java limitation?
What is the max number ExecQuerySingleResult can handle?

This would work for you:

Private Val As Double
    Val = Starter.SQL1.ExecQuerySingleResult("SELECT SUM(ifnull(Val,0)) from MemTable")   
    If IsNumber(Val) Then
        LogColor(NumberFormat2(Val,0,0,0,True),Colors.Magenta)
    End If

This methods didn't work.

Attached is the sample project for this problem.

 

[Mahares]

This methods didn't work.

It worked for me: Instead of this:

SQLMem.ExecNonQuery("CREATE TABLE MEMTABLE(MN INT, QTY INT, VAL REAL)")

use this where Val is TEXT:

SQLMem.ExecNonQuery("CREATE TABLE MEMTABLE(MN INT, QTY INT, VAL TEXT)")

In all cases I got:
** Service (starter) Destroy (ignored)**
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
Total : 1.949371083E9
Formated : 1,949,371,083
Val : 102816133
Val : 7520827
Val : 779800
Val : 1398405025
Val : 58862578
Val : 380986720
Formated2 : 1,949,371,083

 

[incendio]

It worked for me: Instead of this:

SQLMem.ExecNonQuery("CREATE TABLE MEMTABLE(MN INT, QTY INT, VAL REAL)")

use this where Val is TEXT:

SQLMem.ExecNonQuery("CREATE TABLE MEMTABLE(MN INT, QTY INT, VAL TEXT)")

In all cases I got:
** Service (starter) Destroy (ignored)**
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
Total : 1.949371083E9
Formated : 1,949,371,083
Val : 102816133
Val : 7520827
Val : 779800
Val : 1398405025
Val : 58862578
Val : 380986720
Formated2 : 1,949,371,083

Using a TEXT type for numeric field? In this case, when using sqlite, not need to use others data types, just convert all to TEXT type?