Android Question SQLite Cursor GetString versus GetDouble

Discussion in 'Android Questions' started by klaus, Apr 15, 2015.

  1. klaus

    klaus Expert Licensed User

    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.
     
    Last edited: Apr 30, 2015
    Peter Simpson and YAFUHENK like this.
  2. Erel

    Erel Administrator Staff Member Licensed User

    It is not modified in B4A. It happens in the native API and as far as I see it cannot be changed.
     
    Peter Simpson likes this.
  3. klaus

    klaus Expert Licensed User

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

    YAFUHENK Active Member Licensed User

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

    Code:
    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.
     

    Attached Files:

  5. klaus

    klaus Expert Licensed User

    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.
     
    Peter Simpson likes this.
  6. YAFUHENK

    YAFUHENK Active Member Licensed User

    Hi Klaus,

    I guess

    Code:
    Case "REAL"
                        dd = Curs.GetLong2(col)
                        str = dd
                        R(col) = dd
    should be

    Code:
    Case "REAL"
                        dd = Curs.GetDouble2(col)
                        str = dd
                        R(col) = dd
     
  7. klaus

    klaus Expert Licensed User

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

    incendio Well-Known Member Licensed User

    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.
     
    Last edited: Apr 17, 2015
  9. klaus

    klaus Expert Licensed User

    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.
     
  10. incendio

    incendio Well-Known Member Licensed User

    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.
     
  11. klaus

    klaus Expert Licensed User

    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
     

    Attached Files:

    Last edited: Apr 24, 2015
  12. YAFUHENK

    YAFUHENK Active Member Licensed User

    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

    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
     
    Last edited: Apr 23, 2015
    incendio likes this.
  13. incendio

    incendio Well-Known Member Licensed User

    I think function LoadSqliteDB must have additional parameter to hold column types in the SQL.
     
    Last edited: Apr 22, 2015
  14. YAFUHENK

    YAFUHENK Active Member Licensed User

    @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.
     
  15. incendio

    incendio Well-Known Member Licensed User

    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 :

    Code:
    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 :

    Code:
    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.
     
  16. YAFUHENK

    YAFUHENK Active Member Licensed User

    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
    Code:
    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.

    Code:
    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
     
    Last edited: Apr 23, 2015
  17. klaus

    klaus Expert Licensed User

    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.
     

    Attached Files:

    Last edited: Apr 26, 2015
  18. YAFUHENK

    YAFUHENK Active Member Licensed User

    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
     
  19. cimperia

    cimperia Active Member Licensed User

    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.

    Code:
    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:

    Code:
    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.

    Code:
    Sub Activity_Resume
       Table1.Initialize(Me, 
    "Table1"4Gravity.CENTER_HORIZONTAL, False)
       
    Dim ExtraParams(2As 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(
    Activity00100%x33%y)

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

    Public Sub MyUpperCase(s As StringAs String
       
    Return s.ToUpperCase
    End Sub
     
    Last edited: Apr 26, 2015
    klaus and YAFUHENK like this.
  20. YAFUHENK

    YAFUHENK Active Member Licensed User

    @cimperia thanks for your post. I will probably go for what you call the simplest solution.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice