SQLite Variant Column to Hold Database Constants

Databases
1

Hi,
I’m looking to have a single table with two columns (name, value) that holds application defaults. Is it possible to have the value column be variant?

There’s probably a better way to do this but on a fresh DB, I store all default values of popup menus to -1, which is the popup menu’s rowtag, so the popup menu displays, “Choose”. When the user sets a new default value, this value column of the constant table will hold the integer primary key of the record the user selects.

For other situations, I need the value column to hold text defaults.

Or, Is it safe to store integer primary keys from the source table as text in the constant table?

Thank you

2

SQLite is not like other databases. You can store any value in a column regardless of its “type”.

3

I would convert everything to a string. You have to know what the type is to use it anyway, so you don’t gain much by using a “variant”.

Edit: To ease coding, I use a group of overloaded methods.
Sub Prefs(name as string, assigns value as string)
Sub Prefs(name as string, assigns value as double)
Sub Prefs(name as string, assigns value as date)

Function Prefs(name as string) as string
Function Prefs(name as string) as double
Function Prefs(name as string) as date

4

Paul says it best. In SQLite, everything in many ways really is treated like a Variant wether it is or not. So even if you have a string column in a database that holds numeric values you can get an integer value for a record set by doing:

Dim i as integer = r.field(“SomeColumn”).IntegerValue

Even if SomeColumn is of type text.

You can be quite clever with it. I don’t think there’s any need to convert everything to string first.

5

Thanks guys. I didn’t know column types didn’t matter in SQLite. It makes me wonder why I need to even give a type at all when I use CREATE TABLE?

So if I’m storing the integer primary key IDs in a TEXT column of this table, is it necessary to surround the IDs in single quotes when storing them in the database?

Thank you.

6

Well, technically (and surprisingly) you do not need to. Generally, I don’t recommend mixing types in a single column, but I don’t see the harm with your example.

No, not if it is a number.

7

If you use the built in editor you do
But if you were to so

create table test(column1, column2, column3) in code or running the sqlite cmd line editor you would get a table with 3 columns
See CREATE TABLE
Note the section on “column affinity” which is not the same as most other DB’s declared type for a column