Android Question SQLite Cursor GetString versus GetDouble

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

Not familiar with Regex, seems not works on my case.

Have a real value 12345.89, with
became 12345.9

Seem Regex needs more pattern.

incendio said:

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.

Click to expand...

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


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.

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

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.

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.

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:

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

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

@incendio, You are right !
Try the latest version in the Flexible Table thread ?
I check now for *.* instead of "e" !

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

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.

I already do that, but I'd rather minimize the merging task when new versions are released.

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

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

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!

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:


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:


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

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.

incendio said:

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.

Click to expand...

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.

I think codes should change to this

Asumming that max fractions no more than 9 digits.

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.

incendio said:

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.

Click to expand...

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.