Android Question SQL-DB and NUMERIC

[strupp01]

I have declared a SQL DB with decimal values as text.
In order to quickly get a total value, I would like to change the DB to NUMERIC.
Is NUMERIC a good choice or are there problems with it?
Is there an alternative ?

Greetings strupp01

 

[klaus]

I have declared a SQL DB with decimal values as text.

What exactly have you declared in the SQL DB?
In the DB you should declare the column as REAL for decimal numbers or INTEGER for integers.
Then you can get the sum of the column with the sum or total method like :
SELECT sum(Col1) FROM TableName
SELECT total(Col1) FROM TableName
To see the difference between the two methods look HERE.

Maybe you could have a look at the B4X SQLite Database booklet.

 

[strupp01]

Hello Klaus,
Thank you for your answer. I had entered NUMERIC in SQL. Changed this to REAl. Also changed the sum from sum () to total ().
Still, I have strange behavior.
if i query the DB with sum or totel i get a wrong result. However, if I work every time I read from the DB and add them up, I get the right amount.
Can't explain that to me. Have attached a small test program. Maybe you can look over there.

strupp01

 

[Erel]

1. You should use File - Export as zip to build the zip.
2. I don't see where you are requesting the external storage runtime permission.
3. Some of the files are missing.

It will be easier to help you if you create a small example that includes everything needed to run.

 

[strupp01]

Hello Erel,
in advance, I am not as fit in B4A as many others. So I have a lot of fuss about your answer.
For 1. I chose the function "Hinzufügen zu 'Test_SQL.zip' with 7-Zip.
2. The database "Daten_Mein_Haushaltsbuch.db" is in the zip file and must be stored on the Android device under 'Mein Haushaltsbuch'.
3. Which files are missing? I unpacked the program and copied the DB onto my Android device. Everything runs.

 

[klaus]

The problem is SQL.
When SQL returns numeric values as Strings, they are converted to Float.
Which means 6 significant digits, that's why the result of the total() query returns 11510.80 and not 11510.75.

To get correct results you need to get the individual values and add those in the loop as you already do.
But, instead of
Gesamtsumme_1 = Gesamtsumme_1 + Cursor.GetString("Guthaben")
use
Gesamtsumme_1 = Gesamtsumme_1 + Cursor.GetDouble("Guthaben")
In your case GesamtSumme_1 returns the correct value because all individual numbers are converted to Double without 'Float rounding'.
If you had in your database any individual number with more than 6 significant digits you would also get in trouble.

Another workaround in your case with two decimal digit numbers, could be to multiply all values by 100 and use the INTEGER data type in the database and divide then by 100 after reading. I would use the calculation in the loop.

Question for Erel:
Could it be possible to have a query like ExecQuerySingleResultDouble, which returns a Double instead of a String, or is the problem in the Android library?

 

[Erel]

Could it be possible to have a query like ExecQuerySingleResultDouble, which returns a Double instead of a String, or is the problem in the Android library?

It is technically possible. However it is very simple to create such method and add it to your program or to DBUtils. This way it will be cross platform and will not add "clutter" to the library (we can also add ExecQuerySingleResultBlob and ExecQuerySingleResultLong).

Untested code:

Sub ExecQuerySingleResultDouble2(SQL1 As SQL, Query As String, Args() As String) As Double
 Dim rs As ResultSet = SQL1.ExecQuery2(Query, Args)
Dim Result As Double = 0
If rs.NextRow Then
 Result = rs.GetDouble2(0)
End If
rs.Close
 Return Result
End Sub

 

[klaus]

@strupp01
You can add the DB file in the Files folder of the program and then, in the program, test if the file doesn't exist in your folder and if no copy it from File.DirAssets to your folder.
Image files are missing like Plus.png and Aufklappen.png, I had to comment these lines to test your project.

Some other advices:
1. Declare the SQL object in the Starter Service instead of Main.
2. Don't initialize and close the database several times. Open it once at the beginning and close it when leaving the program.
3. You should read about RunTimePermissions.

@Erel
I was also thinking about something like this when answering in this thread, but haven't tested it yet.

 

[Erel]

BTW, it is also a good idea to replace ExecQuerySingleResult (String) as there is a historic mistake in it where it returns Null instead of "". This causes all kinds of annoying issues.

 

[strupp01]

I still have big deficits. But without knowledge of English, translating large data is a problem.
Can you post the call to the sub-Rotine, because I already have problems understanding everything at this point. Afterwards I will understand and learn from it.
Thank you for your effort.

 

[klaus]

Attached you find your program with my tests.

If you want, we can continue in the German Forum.
Wenn Du willst können wir im Deutsche Forum weitermachen.

 

[strupp01]

With pleasure. How is that done ?

 

[klaus]

Click on Forums and scroll down until German Forum, click on it and open a thread.
Klicke auf Forums und scrolle runter bis German Forum, öffne es und sende deine Frage.