Android Question SQLite Cursor GetString versus GetDouble

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:

klaus

Expert
Licensed User
I like cimperias' simple solution.
I have modified the LoadSQLiteDB routine and updated the first post in the Flexible Table thread.
 
Upvote 0

incendio

Well-Known Member
Licensed User
Not familiar with Regex, seems not works on my case.

Have a real value 12345.89, with
B4X:
Regex.IsMatch("-?(?:0|[1-9]\d*)(?:\.\d*)?(?:(e|E)[+\-]?\d+)", str)
became 12345.9

Seem Regex needs more pattern.
 
Upvote 0

cimperia

Active Member
Licensed User
Not familiar with Regex, seems not works on my case.

Have a real value 12345.89, with
B4X:
Regex.IsMatch("-?(?:0|[1-9]\d*)(?:\.\d*)?(?:(e|E)[+\-]?\d+)", str)
became 12345.9

Seem Regex needs more pattern.

Incendio, the Regex does not transform the value, just checks (true/false) for Scientific Notation Numbers. So When I run this code

B4X:
Dim str As String = "12345.89"
If Regex.IsMatch("-?(?:0|[1-9]\d*)(?:\.\d*)?(?:(e|E)[+\-]?\d+)", str) Then
     Log(str & " is a scientific notation number")
Else
     Log(str & " is NOT a scientific notation number")
End If

It'll log, (on my machine anyway): 12345.89 is NOT a scientific notation number

In any case, I did not thoroughly tested the code accuracy and Klaus implemented the solution differently, though doing fundamentally the same check.
 
Upvote 0

Yafuhenk

Active Member
Licensed User
I've tested the updated LoadSQLiteDB routine.
It works perfect for me.
This topic can be marked as solved as far as I can judge.
Thanks to you all.
Henk
 
Upvote 0

incendio

Well-Known Member
Licensed User
Yes I knew, RegEx only checks for pattern, and checking only for scientific pattern is not enough to solve the issue.

GetString will return number 12345.89 as 12345.9, which is not correct, that's why I said, should be more pattern for Regex, since the issue is how to convert Real number in sqlite into string correctly.
 
Upvote 0

incendio

Well-Known Member
Licensed User
Just found, every real value that have length more than 7 digit (included dot sign), will always converted to 7 digits value (included dot sign) with GetString.

Real value -> value with GetString
12345.678 -> 12345.7
1234.567 -> 1234.57
12345.67 -> 12345.7

The solution to check for scientific format or if string contains "e" won't works for real value.

I think the simplest & best solution is included field types in LoadSQLiteDB functions.
 
Upvote 0

klaus

Expert
Licensed User
Did you look at the updated version 1.42 in the Flexible Table thread ?
When you detect a string with scientific notation you must read the value with GetDouble2 not with GetString2 !
This is the code I use:
B4X:
For col = 0 To NumberOfColumns - 1
    Curs.Position = row
    str = Curs.GetString2(col)
    If str <> Null Then
        If IsNumber(str) AND str.Contains(".") Then
            dd = Curs.GetDouble2(col)
            R(col) = dd
        Else
            R(col) = str
        End If
    Else
        R(col) = ""
    End If
Next

EDIT:
changed the original line
If IsNumber(str) AND str.ToLowerCase.Contains("e") Then
to
If IsNumber(str) AND str.Contains(".") Then
 
Last edited:
Upvote 0

cimperia

Active Member
Licensed User
@incendio, you're correct, there's still an issue, and it has nothing to do with scientific notation numbers (E number for short), as this is working fine and you said so.

However I tested your value: 12345.89

As it is not an E number, the method calls getString2 and the values gets rounded to 12345.9
Calling getDouble2 will return 12345.89.

Well spotted.
 
Last edited:
Upvote 0

cimperia

Active Member
Licensed User
I ran it, and it's working fine! Hopefully no more cobblers.

I wonder whether it would be a good idea to implement an optional SetColumnFormat (or something) method. It would define the formats for one or more columns, ie, not all columns need to be present. The method would be called before calling the Table.LoadSQLiteDB method. The number formats could be compatible with BA's NumberFormat method for example.

This method would define and initialize a Data Structure and Table.LoadSQLiteDB would check for it. If the data structure is not initialized then LoadSQLiteDB would behave as it is now.

The beauty is that it is backwardly compatible with current versions.

Just an idea.

Edit: I don't mind giving it a try :)
 
Last edited:
Upvote 0

klaus

Expert
Licensed User
The idea is interesting but I'm afraid that the Table Class will be getting more and more complex.
The problem is always having a 'universal' class with a lot of specific features.
But feel free to adapt the class to your needs.
 
Upvote 0

cimperia

Active Member
Licensed User
I already do that, but I'd rather minimize the merging task when new versions are released.
 
Upvote 0

incendio

Well-Known Member
Licensed User
Hi Klaus,

I have looked the latest version of flexible table class. It will works for most case, but could be a problem if users apply different format for text and for real/int, like I do.

For example, consider this sql statements
B4X:
Create Table Master_Item (Code Text,Name Text, Price int)

insert into Master_Item values('1000.01','Pencil',1500)

So I used solution similar to LoadSqlDB2, the one with extra parameter to hold field types, to apply different format for text and number.
 
Last edited:
Upvote 0

Yafuhenk

Active Member
Licensed User
I switched back again to the right aligment solution (if rightaligned then getdouble). This one works fine for me.
I tried several other things this morning but I can't get around this 'getstring rouding thing'. I give up.
It's Kingsday in the Netherlands today. It's time to switch off all devices and celebrate!
 
Upvote 0

cimperia

Active Member
Licensed User
Have a nice and restful day Yafuhenk!

I have worked out a solution as I outlined in this post here

I have attached Klaus’s zip file with the modifications.

I have tried to minimize the impact to LoadSQLiteDB, i.e. it’ll behave normally and is backwardly compatible with V1.42 and your old code will work without changes. No new Type has been introduced and very few Global variables.

If you want to take advantage of the formatting features, you only need to call a new class method: SetSQLColumnFormat before calling LoadSQLiteDB.
LoadSQLiteDB will detect whether there are columns (0, 1 or more) that require formatting and will apply it. The formatting is simply of these types:

TypeInt
TypeText
TypeReal
TypeImage

TypeImage is not implemented, but could be in the future?

The formatting is forgiving, i.e. if you type a column as Real and the real value is text, you’ll end up with “0.0” but no exception will be raised. And if use TypeText for a Real column, the result will be as erroneous as before :)

Initiating the formatting is done this way:

B4X:
Table1.SetSQLColumnFormat(Array As Object ( Array As Int(0, Table1.TypeInt) _
                                           ,Array As Int(2, Table1.TypeReal)))

Each entry is column number (as per the query order) and its data type. The entries in the array need not be in order.

In the demo, 4 columns are displayed from the test table, but only two are formatted. I have introduced some null in the data for testing purpose.

To disable Column Formatting, if you had enabled it, call the method with null:

B4X:
Table1.SetSQLColumnFormat(Null)

I have done quite a bit of testing, but it would be nice to get more, so guys if you are interested :)

Now, you may prefer to keep the original LoadSQLiteDB as is and call this one LoadSQLiteDBxxxx.

BTW, I have set Table1.SetAutomaticWidths in the demo, so if you want to use a large sample data, be aware of this.

Claudio
 

Attachments

  • TableV1_42.zip
    49.8 KB · Views: 132
Last edited:
Upvote 0

incendio

Well-Known Member
Licensed User
I believe there is still an issue. Don't know if this is the lack of Java or B4A.

Try this number 99999999.99, GetDouble returns it in scientific format.
 
Last edited:
Upvote 0

cimperia

Active Member
Licensed User
I believe there is still an issue, a big one I think.
Don't know if this is the lack of Java or B4A.

Try this number 99999999.99, GetDouble is not capable to handle it properly.

This is a Java "issue". It can handle the number, just uses scientific notation and returns 9.999999999E7 which is correct.

A double has 8 bytes and can store from -1.79769313486231570E+308 to 1.79769313486231570E+308

Now it's possible to further format the value to a decimal number using B4A NumberFormat I believe.
 
Last edited:
Upvote 0

incendio

Well-Known Member
Licensed User
I think codes should change to this
B4X:
If(IsNumber(str) AND (str.ToLowerCase.Contains(".") OR str.ToLowerCase.Contains("e"))) Then
   str = NumberFormat2(Curs.GetDouble("COL2"),0,9,0,False)
'or str = NumberFormat2(Curs.GetDouble2(col),0,9,0,False)
End If

Asumming that max fractions no more than 9 digits.
 
Upvote 0

cimperia

Active Member
Licensed User
That would work and is a simple fix.

I have uploaded a modified version of the class as described above. It could be expanded to accept formatting (a la NumberFormat), therefore applying further "nice" formatting to numbers, as I suggested here. I did not do it as I wanted to keep as simple as possible, but there might be a need.
 
Last edited:
Upvote 0

klaus

Expert
Licensed User
I have looked the latest version of flexible table class. It will works for most case, but could be a problem if users apply different format for text and for real/int, like I do.
I uploaded version 1.43 in the first post of Flexible Table where I went back to a previous solution.
For LoadSQLiteDB the original (like in version 1.40).
Added LoadSQLiteDB2 which needs a String array with the column data types.
 
Upvote 0
Top