SQLite, Real & Float

[johnaaronrose]

I'm confused about whether to use the Type Real or Float for columns when creating a SQLite database on a PC to be later used by B4A as read only. I have a column which needs to be held with a sign, up to 3 digits before the decimal point and up to 6 digits after the decimal point. I have the requisite validation in the PC app maintaining this database.

Currently I'm using the Float Type when creating the table (in SQL) containing this column. However, when I use the B4A code lblColumn.Text = Cursor.GetString... to retrieve the column's value & display it in a Label view , it seems to be sometimes taking stored values with 6 used decimal places (after the decimal point) and displaying them rounded to 4 decimal places (after the decimal point). Should I change the Type in the SQL Create verb to Real? I looked at the SQL Table Creation example on page 11 of the User's Guide and it has type FLOAT in the Create verb & REAL in the 'spec' underneath.

Or is the problem due to using GetString rather than GetDouble or even some other reason?

 

[Erel]

See the SQLite data types docs: Datatypes In SQLite Version 3

There is no FLOAT data type. It is actually translated into REAL. You should use GetDouble to get the numeric value.

Note that double / REAL types cannot accurately represent decimal numbers. You might want to use a TEXT field instead.

 

[Informatix]

It's always a pleasure to see a user ignoring the answer you gave him.

 

[johnaaronrose]

Apologies

It's always a pleasure to see a user ignoring the answer you gave him.

Apologies. I forgot to reply because I had some other problems to sort out with my app. I do try to thank people for their help. However, I think that you could phrase your response more diplomatically e.g "Please look at my reply on thread...Did you see it?". Sarcasm tends to lead to flaming. There's an old maxim: Try not to offend someone as you'll never know when you'll need your help.

 

[johnaaronrose]

Calculation using a number in a Text column

See the SQLite data types docs: Datatypes In SQLite Version 3

There is no FLOAT data type. It is actually translated into REAL. You should use GetDouble to get the numeric value.

Note that double / REAL types cannot accurately represent decimal numbers. You might want to use a TEXT field instead.

If I use GetDouble, I presume that the resultant variable will accurately hold the column value. Will it display accurately in the lblLatitude view (i.e. with all, and no extra, decimal places used being displayed) using B4A code similar to:
Public Latitude As Double
Latitude = CursorAspect.GetDouble("latitude")
lblLatitude.Text=Latitude

Or is there a better method or view to use?

PS I'd prefer not to define the database's table's Latitude column type as Text because I also want to use it for calculations. Also, It would need modifying the existing PC database maintenance app significantly.

 

[Informatix]

Apologies. I forgot to reply because I had some other problems to sort out with my app. I do try to thank people for their help. However, I think that you could phrase your response more diplomatically e.g "Please look at my reply on thread...Did you see it?". Sarcasm tends to lead to flaming. There's an old maxim: Try not to offend someone as you'll never know when you'll need your help.

That was not the first time, so I lost a bit my diplomatic mood. But all is right now and I agree with your wise maxim.

 

[Erel]

Getting the value with GetDouble will return the number exactly as it is stored. There is also no problem with showing it in a label.

The problem is that a 8 byte standard floating number cannot accurately represent decimal numbers. For example it is not appropriate for currency calculations.

 

[klaus]

The error in the User's Guide (FLOAT should be REAL) is corrected for the next release.

Best regards.

 

[johnaaronrose]

Thank you

Getting the value with GetDouble will return the number exactly as it is stored. There is also no problem with showing it in a label.

The problem is that a 8 byte standard floating number cannot accurately represent decimal numbers. For example it is not appropriate for currency calculations.

Thanks, Erel. I will try that out when I'm able to amend the PC app to include database structure changes.