I’m trying to create a SQLite table but no matter how I try(creating it via ide or via SQLite studio) xojo will switch the type from integer to text. Is it a bug or something I’m missing within the IDE?
TY,
Matteo
What is the name of the column?
Filesize and Pathlength are my columns(set to integer), the ide will revert them to text no matter what I do
Then it is something different than I thought. SQLite changes column types when the name contains INT or TEXT for example.
changes type?
SQLite is a database of variants.
You can specify a column type, but that is only used for sorting.
Each value in the database can be one of the 5 supported types (int64, double, text, null or blob).
@ChristianSchmitz one of the reasons I’m using a db is to sort the data so I need the types to check out. I tried int, integer and int64, the ide will change the column type to text on save(if I use the ide to create the db tables) or on import(if I use SQLite studio to crate all the tables I need).
Apparently xojo IDE will revert the type if the column length is set, by omitting the length the type stays as integer. Did I found a bug or is it the S.O.P. for the IDE?
This is not an error. You should read about SQLite data types and especially about affinity onhttp://www.sqlite.org/datatype3.html.
I’m aware of the datatypes, I’m just puzzled by the ide switching stuff at it pleases without user confirmation/notification. No biggie, I’ll have xojo export my data to CSV and doing the finished touches by sed/php.
I managed to set up the db using SQLite studio, the ide seems to ignore the mandatory checkbox. I don’t like the ide doing A while being asked to do B so I’ll do this app using a different IDE.
SQLite doesn’t really have data types. it is not the same as with other SQL databases.
@Eli Ott The affinity is a datatype, just more flexible. Unfortunately I need the db to do some basic validation(making sure it doesn’t put numbers where text should go and viceversa and making sure certain cells are always populated) and xojo ide seems unable to create my db with my specs(even if I create the columns as text or integer, mandatory is not applied when done on the ide) without external app help. I just wanted to be sure that my problem was a known one and if there were any workarounds(I’m not very fond of doing stuff using multiple tools when dealing with dataflows, I prefer to do everything with a single ide to avoid formatting mishaps). There is little to no point into having a gui based SQLite table maker if the tables properties are decided using random() don’t you think?
SQLite doesn’t make a difference between storing text and numbers. You can put text into a column typed as INT and vice versa. You either have to control that in your application or you need to create triggers in the SQLite database.
It does not stores everything as text. Numbers have binary representation. The affinity rules defines how they will be stored. Makes difference on how they are stored due to space and speed in math.
And Yes, you can send TEXT to an INTEGER column and it will be stored as TEXT IF that text cant be translated to an integer.
An INTEGER value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
A REAL value is a floating point value, stored as an 8-byte IEEE floating point number.
All the rest uses as many bytes as needed.
I would recommend against using the IDE to maintain your database. It’s never worked well. Do not import a database either, as that creates its own set of problems (not portable). Open your database in code. Create/maintain the database either in code or in a separate db editor.