SQLite Forum

Data Types
Login

Data Types

(1) By anonymous on 2021-10-12 20:11:25 [link] [source]

I am looking for a table showing

[Type Name] [Numeric Code] [Storage Class Code] [Affinity Type Code]
Null
Integer
Real
Text
BLOB

(2) By Simon Slavin (slavin) on 2021-10-12 21:55:34 in reply to 1 [source]

SQLite doesn't work in a way that would need that table. For instance, it doesn't use numeric codes, and its internal storage doesn't use classes you might see elsewhere (e.g. C++).

One table which resembles the table you asked for appears here:

https://www.sqlite.org/datatype3.html#affinity_name_examples

but actually I'd recommend you read the whole page since it explains why you won't see the table you asked about.

For technical details about how values of all types are stored in the database, see this page:

https://sqlite.org/fileformat.html

If I misunderstood your question, or can help, please post a reply to this thread.

(3) By anonymous on 2021-10-13 06:45:40 in reply to 2 [link] [source]

The reason for seeking that table is for me to (have a quick way to) understand (rather than use it to coerce a desired behaviour in SQLite3) the results that SQLite produces.

Also, In one place I saw NULL associated with numeric code 1 and in another INTEGER was associated with numeric code 1; granted that such associations must be interpreted in context. I tried to use sqlite3_column_type against a query (not a table) to see what values were returned but the only value I get is 5 which is BLOB.

(4) By anonymous on 2021-10-13 10:40:14 in reply to 3 [link] [source]

Return codes for sqlite3_column_type are here

https://sqlite.org/c3ref/c_blob.html

Not sure what your other columns are about.

(5) By Simon Slavin (slavin) on 2021-10-13 12:19:33 in reply to 3 [link] [source]

Ah, got it.

With regard to all values returned being 5, did you define the column types when you defined the table ? Or was the table definition like

CREATE TABLE MyTable (first, second, third);

? Also note that SQLite does not enforce values to be the same type as a column definition. For instance, you can define a column as INTEGER but put a text value into one row for it. If that row happens to the first one returned by a query, sqlite3_column_type() will return SQLITE_TEXT for that column.

From the page you quoted …

The sqlite3_column_type() routine returns the datatype code for the initial data type of the result column. The returned value is one of SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB, or SQLITE_NULL. The return value of sqlite3_column_type() can be used to decide which of the first six interface should be used to extract the column value. The value returned by sqlite3_column_type() is only meaningful if no automatic type conversions have occurred for the value in question. After a type conversion, the result of calling sqlite3_column_type() is undefined, though harmless. Future versions of SQLite may change the behavior of sqlite3_column_type() following a type conversion.

(6) By anonymous on 2021-10-13 13:46:09 in reply to 5 [link] [source]

did you define the column types when you defined the table?

The tables are defined with types for each column; I queried sqlite3_column_type() inside a user-defined function. I could equally query the column type inside a callback function ... but is is not reliable to assume the column type from the current row ... as you say, note that SQLite does not enforce values to be the same type as a column definition.

(7) By Gunter Hick (gunter_hick) on 2021-10-13 15:10:39 in reply to 3 [link] [source]

Maybe you are confusing values returned from interfaces with serial type codes used in the internal record format.