Android Question SQLite Cursor GetString versus GetDouble

[klaus]

I noticed when getting values from a cursor there is a difference with GetString and GetDouble.
I used GetString even for REAL numbers.
But when the numbers are bigger than 1000000 GetString returns the numbers in scientific notation with only 6 digits truncating the rest.
Example:
GetDouble > 1497823.0
GetString > 1.49782e+06
It seems that GetString returns a Float number and not a Double.
123.45678 returns 123.4568.

Is this conversion done in B4A or in Android ?
If it's B4A it should be modified.
If it's Android could it be modified in the SQLite library or do we need to live with it and check the data type to get the precise value ?

The problem comes from this thread.

 

[Erel]

It is not modified in B4A. It happens in the native API and as far as I see it cannot be changed.

 

[klaus]

Thank you.
So we need to check the data type in our code.

 

[Yafuhenk]

If we have to do it by code my suggestion in the origional thread creates another problem.

If IsNumber(Curs.GetString2(col)) = True Then
                str = Curs.GetDouble2(col)
            Else
                str = Curs.GetString2(col)
            End If

In a database I stored a salesmanager code as string. This salesmanager code happens to be a number.
With the code above this string is converted into a double and shown in the table as a number with fractions.
I this case however I want to see the salesmanager code as a string
Have a look at the jpg file in the first column and you will see what I mean.

 

[klaus]

To do it correctly, you need to get the data type for each column and use the right GetXXX method.
The Flexible Table Class has been updated.

 

[Yafuhenk]

Hi Klaus,

I guess

Case "REAL"
                    dd = Curs.GetLong2(col)
                    str = dd
                    R(col) = dd

should be

Case "REAL"
                    dd = Curs.GetDouble2(col)
                    str = dd
                    R(col) = dd

 

[klaus]

Oups !
You are right, first post in Flexible Table amendet.

 

[incendio]

I have a number 1250000, it was return OK ( not in scientific format) with
Lbl.Text = Cursor.GetString("FIELD_NAME")

But FIELD_NAME type in sqlite is declare as Numeric(10,2).

I think GetString, when retrieve a field value from Sqlite, has a bug.
It won't returns a value if field name in sqlite, is in lower case, it must be declare in upper case.

 

[klaus]

There are 4 data types in SQLite:
INTEGER
REAL
TEXT
BLOB
All data types declared differently will be converted in one if the 'standard' types.
As Numeric(10,2) is not a standard SQLite data type it will be converted.
If it is converted to TEXT, no problem.

 

[incendio]

Strange, just changed type Numeric (10,2) into type REAL, still using GetString, result seem OK.

Could it be because of OS? I am still using JB & using this : <uses-sdk android:minSdkVersion="4" /> in manifest file.

 

[klaus]

What do you get with the attached test program ?
I get the following on both my devices
Sony xperia z1 Android 4.4.4
Asus TF 700T Android 4.2.1

GetString:
1234567 GetString
1.23457e+06 GetString
1234570.0 GetString converted to a Double, here is the problem !
1234567 GetString

GetDouble:
1234567 GetInt
1234567.0 GetDouble
1234567.0 GetDouble
1234567 GetString

 

[Yafuhenk]

Hi Klaus,

I come back to your solution for the sub LoadSQLDB of the flexible table class
I tried your solution but in most cases it will not work.
Let me explain why.

In the first step you store all the column name types in the ColumnType array
Lets imagine that the table has 3 Columns, the first 2 columns TEXT , the last column REAL.
ColumnType(0) = 'TEXT'
ColumnType(1) = 'TEXT'
ColumnType(2) = 'REAL'

Now lets imagine that the query has less columns then the total number of columns in the table (which is mostly the case)

In this example the query has two columns 1 TEXT and 1 REAL

With the current code both columns are seen as TEXT type.

In my opinion we have to check the column names of the tables as well as the column types
This can be done with the PRAGMA statement as well.
Please have a look at this code

Public Sub LoadSQLiteDB(SQLite As SQL, QueryString As String, AutomaticWidths As Boolean)
  
    Dim Curs As Cursor
    Dim TableName As String
    Dim i1, i2 As Int
    TableName = QueryString.ToUpperCase
    i1 = TableName.IndexOf("FROM") + 5
    i2 = TableName.IndexOf2(" ", i1)
    If i2 = -1 Then
        i2 = QueryString.Length
    End If
    Curs.
    TableName = QueryString.SubString2(i1, i2)
    Curs = SQLite.ExecQuery("PRAGMA table_info (" & TableName & ")")
    Dim NumberOfColumnsinTable As Int
    NumberOfColumnsinTable = Curs.RowCount
    Dim ColumnName(NumberOfColumnsInTable) As String
    Dim ColumnType(NumberOfColumnsinTable) As String
    Dim row As Int
    For row = 0 To NumberOfColumnsinTable - 1
        Curs.Position = row
        ColumnName(row) = Curs.GetString2(1)
        ColumnType(row) = Curs.GetString2(2).ToUpperCase
    Next
  
    Curs = SQLite.ExecQuery(QueryString)
  
    cAutomaticWidths = AutomaticWidths
    NumberOfColumns = Curs.ColumnCount
    innerClearAll(NumberOfColumns)

    Dim Headers(NumberOfColumns) As String
    Dim ColumnWidths(NumberOfColumns) As Int
    Dim HeaderWidths(NumberOfColumns) As Int
    Dim DataWidths(NumberOfColumns) As Int
    Dim col, row As Int
    Dim str As String
    For col = 0 To NumberOfColumns - 1
        Headers(col) = Curs.GetColumnName(col)
        If AutomaticWidths = False Then
            ColumnWidths(col) = 130dip
            HeaderWidths(col) = 130dip
            DataWidths(col) = 130dip
        Else
            HeaderWidths(col) = cvs.MeasureStringWidth(Headers(col), Typeface.DEFAULT, cTextSize) + ExtraWidth
            DataWidths(col) = 0
            For row = 0 To Curs.RowCount - 1
                Curs.Position = row
                str = Curs.GetString2(col)
                If str <> Null Then
                    DataWidths(col) = Max(DataWidths(col), cvs.MeasureStringWidth(str, Typeface.DEFAULT, cTextSize) + ExtraWidth)
                End If
            Next
            ColumnWidths(col) = Max(HeaderWidths(col), DataWidths(col))
        End If
    Next
    SetHeader(Headers)
    SetColumnsWidths(ColumnWidths)
  
    Dim ii As Long
    Dim dd As Double
    For row = 0 To Curs.RowCount - 1
        Curs.Position = row
        Dim R(NumberOfColumns), str As String
        Dim MyColumn As Int
        For col = 0 To NumberOfColumns - 1
            For I = 0 To NumberOfColumnsinTable -1
                If Curs.GetColumnName(col) = ColumnName(I) Then
                    MyColumn = I
                    Exit
                End If
            Next
            str = Curs.GetString2(col)
            If str = Null Then
                R(col) = ""
            Else
                Select ColumnType(MyColumn)
                Case "INTEGER"
                    ii = Curs.GetLong2(col)
                    str = ii
                    R(col) = ii
                Case "REAL"
                    dd = Curs.GetDouble2(col)
                    str = dd
                    R(col) = dd
                Case "TEXT"
                    R(col) = Curs.GetString2(col)
                Case Else '"BLOB"
                    R(col) = ""
                End Select
            End If
        Next
        AddRow(R)
    Next
    Curs.Close

The problem with this code is that the array ColumName contains the original column names and that Curs.GetColumnName(col) contains the aliases used in the query.
Therefore Curs.GetColumnName(col) = ColumnName(I) will never be true.
The issue is that I don't know how to get the original column names from query in case aliases have been used.

Henk

 

[incendio]

I think function LoadSqliteDB must have additional parameter to hold column types in the SQL.

 

[Yafuhenk]

@incendio .
that will work indeed. The only thing is that you not always know the column names upfront.
I search the Internet but i couldn't find a function to get the original columns names in a query when aliases are used. I will solve this with string functions.
That will work anyhow. Will place my solution later today or tomorrow.

 

[incendio]

Maybe no need to know columns names.

It can also done without additional parameter in LoadSQLiteDB function.
Modifying QueryString As String to add keyword should be enough.

What I have in mind, QueryString should have format something like this :

select nm, col1*col2 as qty, prc from table >> t,r,i

>> sign is a keyword to tells column type, t=text,r=real,i=int

or QueryString could also have format something like this :

select nm, col1*col2 as qty, prc from table >> auto

>> auto means that function will try to get value from all columns with GetString2, and if column's value contains + sign, it will retrieve again its value, but this time with GetDouble2.

Function without keyword sign in the QueryString will get value for each column using standard GetString2.

 

[Yafuhenk]

The string solution I was talking about in my previous post is much to complicated when the query contains functions or a sub query.
The idea from incendio might be a solution but I will not change the more than 2400 rows of queries I have.
His remarks however brought me on another idea which unfortunately will only work for me.
My queries functions are build up like this

Result(0) = "SELECT SalesManagerCode AS 'Sales Manager Number', SalesManagerName AS 'Sales Manager Name', MonthYear AS 'Month', IFNULL(SUM(CASE WHEN FiscalYear = 'V9/" & fy0 & "' THEN PlanningEuro END),0) AS 'Planning', SUM(CASE WHEN (FiscalYear = 'V9/" & fy0 & "' AND MonthYear <= '" & lm & "') THEN " & epf & " * TurnoverEuro ELSE '0' END) AS 'Extrapolation', SUM(CASE WHEN FiscalYear = 'V9/" & fy0 & "' THEN TurnoverEuro ELSE '0' END) AS 'Turnover " & fy0 & "', SUM(CASE WHEN FiscalYear = 'V9/" & fy1 & "' THEN TurnoverEuro ELSE '0' END) AS 'Turnover " & fy1 & "', SUM(CASE WHEN FiscalYear = 'V9/" & fy2 & "' THEN TurnoverEuro ELSE '0' END) AS 'Turnover " & fy2 & "' FROM " & Main.TABLENAME0 & " WHERE (" & Main.memsb & ") GROUP BY SalesManagerCode, SalesManagerName, MonthYear ORDER BY SUM(CASE WHEN FiscalYear = 'V9/" & fy0 & "' THEN TurnoverEuro ELSE '0' END) DESC"
Result(1) = "SELECT IFNULL(SUM(CASE WHEN FiscalYear = 'V9/" & fy0 & "' THEN PlanningEuro END),0) AS 'Planning', SUM(CASE WHEN (FiscalYear = 'V9/" & fy0 & "' AND MonthYear <= '" & lm & "') THEN " & epf & " * TurnoverEuro ELSE '0' END) AS 'Extrapolation', SUM(CASE WHEN FiscalYear = 'V9/" & fy0 & "' THEN TurnoverEuro ELSE '0' END) AS 'Turnover " & fy0 & "', SUM(CASE WHEN FiscalYear = 'V9/" & fy1 & "' THEN TurnoverEuro ELSE '0' END) AS 'Turnover " & fy1 & "', SUM(CASE WHEN FiscalYear = 'V9/" & fy2 & "' THEN TurnoverEuro ELSE '0' END) AS 'Turnover " & fy2 & "' FROM " & Main.TABLENAME0 & " WHERE (" & Main.memsb & ")"
Result(2) = "SELECT Format(MonthYear, 'yyyy-MM') AS 'Month', IFNULL(SUM(CASE WHEN FiscalYear = 'V9/" & fy0 & "' THEN PlanningEuro END),0) AS 'Planning', SUM(CASE WHEN (FiscalYear = 'V9/" & fy0 & "' AND MonthYear <= '" & lm & "') THEN " & epf & " * TurnoverEuro ELSE '0' END) AS 'Extrapolation', SUM(CASE WHEN FiscalYear = 'V9/" & fy0 & "' THEN TurnoverEuro ELSE '0' END) AS 'Turnover " & fy0 & "', SUM(CASE WHEN FiscalYear = 'V9/" & fy1 & "' THEN TurnoverEuro ELSE '0' END) AS 'Turnover " & fy1 & "', SUM(CASE WHEN FiscalYear = 'V9/" & fy2 & "' THEN TurnoverEuro ELSE '0' END) AS 'Turnover " & fy2 & "' FROM " & Main.TABLENAME0 & " WHERE (" & Main.memsb & ") GROUP BY MonthYear ORDER BY  MonthYear DESC"
Result(3) = "3;4;5;6;7"
Result(4) = "0;2"

Result(0) is the query for the table data
Result(1) is the query for the totals shown in the header
Result(2) is the query for the chart
Result(3) gives the column(s) for right alignment
Result(4) gives the column(s) to look for in the next drilldown level.

I now use Result(3) in the LoadSQLiteDB sub of the flexible table class.
If the alignment is right then, in my case the column contains numbers and getdouble2 is used otherwise I get the data with getstring2.

Dim dd As Double
    For row = 0 To Curs.RowCount - 1
        Curs.Position = row
        Dim R(NumberOfColumns), str As String
        For col = 0 To NumberOfColumns - 1
            str = Curs.GetString2(col)
            If str = Null Then
                R(col) = ""
            Else
                Dim ColumnsToAlignRight() As String
                Dim RightAlignment As Boolean
                RightAlignment = False
                ColumnsToAlignRight = Regex.Split(";", Query.Result(3))
                For J = 0 To ColumnsToAlignRight.Length - 1
                    If ColumnsToAlignRight(J) = col Then
                        RightAlignment = True
                        Exit
                    End If
                Next
                If RightAlignment = True Then
                    dd = Curs.GetDouble2(col)
                    R(col) = dd
                Else
                    R(col) = Curs.GetString2(col)
                End If
            End If
        Next
        AddRow(R)
    Next
    Curs.Close

I am fully aware that this solution will not work for most people and we therefore have to look for another more general solution.
Maybe Klaus has some ideas. I found this function (sqlite3_column_origin_name) on the internet but I don't know if it can be used in B4A
For the time being I will use the solution described above.

Henk

 

[klaus]

Sorry for answering only today, but I was on travel the last three days without my computer.

Can you please try the attached version.
I modified the LoadSQLiteDB routine back like before.
Added a new LoadSQLiteDB2 routine with an additional parameter, a String array containing the DataTypes for each column in the query.
In the example project there are three tables.
The first one shows the 'old' style with GetString.
The second one with the new routine showing the whole database.
The third one showing only twp columns.

EDIT: 2014.04.26
Removed the source code, no more valid.
A simpler solution is suggested by cimperia in post #19.
The new version is in the Flexible Table thread.

 

[Yafuhenk]

Hi Klaus,

I had a look at your LoadSQLiteDB2 routine and I am sure it will work but for me it's not an option.
I have so many queries and I don't want to check them all to see on which positions the REAL and the TEXT are.

Do you agree that in 99% of the cases figures are aligned right. I you do, what do you think of the following idea:
- write an GetColumnAlignment routine (values for the alignment are set in the setheader routine)
- in the LoadSqliteDB check if the column is right aligned, if yes then check if the content is a number, if yes then use GetDouble2 otherwise use GetString2

Henk

 

[cimperia]

I think there is no solution that will suit everybody as Sqlite's lack of metadata is the real issue.

I have come up with a few ideas.

The simplest (simplistic?) is to modify LoadSQLiteDB to check whether getString2 returns a scientific number using for example the current bit of code, and call GetDouble2 or GetInt2 after further checking.

If Regex.IsMatch("-?(?:0|[1-9]\d*)(?:\.\d*)?(?:(e|E)[+\-]?\d+)", str) Then
  'Log(str & " is a valid scientific notation number")
  str = Curs.GetDouble2(col)
End If

The second solution is to pass an extra parameter ( a list ) that may either be null, and therefore LoadSQLiteDB behaves as in the original, or the list contains only the col positions and datatypes for the full set of columns or only a subset, i.e. one or more.

The third solution builds on the second. The idea is that if you have to add an extra argument, we may as well make it useful in other ways. As well as col position and datatype, it'll optionally contain the name of a sub that can be called to further transform the data before it gets displayed in the table.

I have coded this 3rd solution as it is the most useful to me. It's working OK but it would need further testing (and I have implemented the callback function only for text for demo):

Here's the class additions:

Sub Class_Globals
...

   Public IntegerType As Int = 0
   Public TextType As Int = 1
   Public RealType As Int = 2
   Type tExtraParams (colPos As Int, colType As Int, colFunc As String)
End Sub

'load data from a SQLite database
'SQLite = SQL object
'Query = SQLite query
'AutomaticWidths  True > set the column widths automaticaly
'ExtraParamsList = List with the col number, data type and optional sub to call for each column
'  The parameter may be left null
'Example:
Public Sub LoadSQLiteDB3(SQLite As SQL, Query As String, AutomaticWidths As Boolean, ExtraParamsList As List)
   Dim Curs As Cursor

   Curs = SQLite.ExecQuery(Query)

   NumberOfColumns = Curs.ColumnCount
   cAutomaticWidths = AutomaticWidths
   innerClearAll(NumberOfColumns)

   Dim Headers(NumberOfColumns) As String
   Dim ColumnWidths(NumberOfColumns) As Int
   Dim HeaderWidths(NumberOfColumns) As Int
   Dim DataWidths(NumberOfColumns) As Int
   Dim col, row As Int
   Dim str As String
   Dim ii As Long
   Dim dd As Double
   Dim colType As Int
   Dim m As tExtraParams

   For col = 0 To NumberOfColumns - 1
     Headers(col) = Curs.GetColumnName(col)
     If AutomaticWidths = False Then
       ColumnWidths(col) = 130dip
       HeaderWidths(col) = 130dip
       DataWidths(col) = 130dip
     Else
       HeaderWidths(col) = cvs.MeasureStringWidth(Headers(col), Typeface.DEFAULT, cTextSize) + ExtraWidth
       DataWidths(col) = 0

       colType = -1
       For row = 0 To Curs.RowCount - 1
         Curs.Position = row
         If ExtraParamsList.IsInitialized Then
           For i = 0 To ExtraParamsList.Size - 1
             m = ExtraParamsList.Get(i)
             If m.colPos = col Then
               colType = m.colType
               Exit
             End If
           Next
         End If

         Select colType
           Case IntegerType
             ii = Curs.GetInt2(col)
             str = ii
           Case RealType
             dd = Curs.GetDouble2(col)
             str = dd
           Case TextType
             str = Curs.GetString2(col)
           Case Else
             str = Curs.GetString2(col)  
         End Select

         If str <> Null Then
           DataWidths(col) = Max(DataWidths(col), cvs.MeasureStringWidth(str, Typeface.DEFAULT, cTextSize) + ExtraWidth)
         End If
       Next
       ColumnWidths(col) = Max(HeaderWidths(col), DataWidths(col))
     End If
   Next
   SetHeader(Headers)
   SetColumnsWidths(ColumnWidths)

   For row = 0 To Curs.RowCount - 1
     Curs.Position = row
     Dim R(NumberOfColumns), str As String
     For col = 0 To NumberOfColumns - 1
       str = Curs.GetString2(col)
       If str = Null Then
         R(col) = ""
       Else
         colType = -1
         If ExtraParamsList.IsInitialized Then
           For i = 0 To ExtraParamsList.Size - 1
             m = ExtraParamsList.Get(i)
             If m.colPos = col Then
               colType = m.colType
               Exit
             End If
           Next
         End If

         Select colType
           Case IntegerType
             ii = Curs.GetLong2(col)
             str = ii
             R(col) = ii
           Case RealType
             dd = Curs.GetDouble2(col)
             str = dd
             R(col) = dd
           Case TextType
             If SubExists(Callback, m.colFunc) Then
               R(col) = CallSub2(Callback, m.colFunc, Curs.GetString2(col))
             Else
               R(col) = Curs.GetString2(col)
             End If
           Case Else
             R(col) = Curs.GetString2(col)  
         End Select
       End If
     Next
     AddRow(R)
   Next

   Curs.Close
End Sub

Here's the MAIN module additions. For the callback function to work, I had to move the code from Activity_Create to Activity_Resume. The function MyUpperCase is stupid and is used as proof of concept. It does display the names in uppercase.

To obtain the results equivalent to LoadSQLiteDB2, only one entry in the list would be necessary: ExtraParams(0). ExtraParams(1) has been added to demonstrate the Callback idea. So, though the data structure looks heavy, there would usually be only a few entries and sometimes none at all and a NULL parameter would be passed, and the sub would behave exactly as LoadSQLiteDB.

Sub Activity_Resume
   Table1.Initialize(Me, "Table1", 4, Gravity.CENTER_HORIZONTAL, False)
   Dim ExtraParams(2) As tExtraParams

   ExtraParams(0).Initialize
   ExtraParams(0).colPos = 2
   ExtraParams(0).colType = Table1.RealType
   ExtraParams(0).colFunc = Null

   ExtraParams(1).Initialize
   ExtraParams(1).colPos = 1
   ExtraParams(1).colType = Table1.TextType
   ExtraParams(1).colFunc = "MyUpperCase"

   ExtraParamsList.Initialize2(ExtraParams)
   Table1.AddToActivity(Activity, 0, 0, 100%x, 33%y)

'   Table1.LoadSQLiteDB3(SQL1, "SELECT * FROM Test", True, Null)
   Table1.LoadSQLiteDB3(SQL1, "SELECT * FROM Test", True, ExtraParamsList)
End Sub

Public Sub MyUpperCase(s As String) As String
   Return s.ToUpperCase
End Sub

 

[Yafuhenk]

@cimperia thanks for your post. I will probably go for what you call the simplest solution.