SQLite, Real & Float

johnaaronrose · Aug 30, 2012

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 · Aug 30, 2012

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 · Aug 30, 2012

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

johnaaronrose · Aug 30, 2012

Apologies

Informatix said:
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 · Aug 30, 2012

Calculation using a number in a Text column

Erel said:
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 · Aug 30, 2012

johnaaronrose said:
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 · Aug 30, 2012

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 · Aug 30, 2012

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

Best regards.

johnaaronrose · Aug 30, 2012

Thank you

Erel said:
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.

SQLite, Real & Float

johnaaronrose

Active Member

Erel

B4X founder

Informatix

Expert

johnaaronrose

Active Member

johnaaronrose

Active Member

Informatix

Expert

Erel

B4X founder

klaus

Expert

johnaaronrose

Active Member

SQLite, Real & Float

Active Member

B4X founder

Expert

Active Member

Active Member

Expert

B4X founder

Expert

Active Member

Privacy & Transparency

Privacy & Transparency