SQLITE column types and PB

[collectordave]

I am storing some numbers in an SQLite database such as 46.496 in a column wih the SQLite designation 'FLOAT'

I am later reading them back using the following to determine which getdatabase statement to use:-

Code: Select all

      Select DatabaseColumnType(CurrentDB\DBID, ColumnNumber)
          
        Case #PB_Database_Blob 
          
          Debug "Blob"

        Case #PB_Database_String
        
          Debug "String"
 
        Case #PB_Database_Long
          
          Debug "Long"
      
        Case #PB_Database_Float
          
          Debug "Float"

        Case #PB_Database_Double
          
          Debug "Double"
   
        Case #PB_Database_Quad
          
          Debug "Quad"
 
      EndSelect 

I have just left the debug statements in.

The SQLite 'FLOAT' type is returned in the above as #PB_Database_Double so I use getdatabase long to retrieve the value which is returned with nothing after the decimal place.

Should I retrieve #PB_Database_Double with getdatabaseFloat?

CD

[skywalk]

Sqlite has only Text, real, blob, integer.
viewtopic.php?p=534701#p534701

[collectordave]

Hi

PB has

GetDatabaseBlob()
GetDatabaseDouble()
GetDatabaseFloat()
GetDatabaseLong()
GetDatabaseQuad()
GetDatabaseString()

Sqlite REAL is returned as #PB_Database_Double as well.

I have some documentation that sqlite has quite a few field definitions for numbers such as:-

REAL
DOUBLE
DOUBLE PRECISION
FLOAT

SQLite will store numbers such as 22.567 in each of these so should I use GetDatabaseFloat() for all the list above? They all seem to return #PB_Database_Double for field type.

I know there are SQLite field types Of

INT
INTEGER
TINYINT
SMALLINT
MEDIUMINT
BIGINT
UNSIGNED BIG INT
INT2
INT8
NUMERIC
DECIMAL(10,5)
BOOLEAN
DATE
DATETIME

All of which seem to store decimal numbers as well but all are integers.

[skywalk]

What is your question?

[collectordave]

SQLite will store numbers such as 22.567 in each of these so should I use GetDatabaseFloat() for all the list above?

May have answered my own question here. Found something similar in help

Code: Select all

value1.d = 0.21457

Debug value1   ; will give 0.21470000000000001

Debug StrD(Value1) ;will give 0.21457

So the answer is retrieve doubles as double and floats as float then use the correct StrF or StrD to get a human readable answer the same as the value stored in the database.

My confusion was seeing the value in the database and getting a different answer when reading through PB. Just trust PB to get the field types right. More confusion comes along when I found that SQLite allows me to store 0.21457 as an integer (well in an integer field).

We live and learn,

Thanks

CD

[skywalk]

Yes, reading through my link and elsewhere, my understanding is SQLite's data storage is 2-toned. Non-text data is stored as both text and numeric. Integers are created from perceived whole numbers; 0.0, 1.0, -3e9, etc. But they are also retrievable as text. So, PB's getdatabasestring() or getdatabasedouble() will have a response, regardless of the field's defined type.