Is there a max character length of a SQLite field (TEXT/VARCHAR)?

Hi. Quick question. Is there a limit to the number of characters a field in SQLite can accept? I’m currently working on a study guide with various modules/chapters. I’ve been sub-sectioning each topic, but some are still quite lengthy. I don’t want to run into an issue where some of the text is not saving because I’ve gone past the limit. At least if I know, I can be sure to stay below the limit.

I found this on the SQLite website but wasn’t sure if the numbers are referring to characters

[quote]Maximum length of a string or BLOB

The maximum number of bytes in a string or BLOB in SQLite is defined by the preprocessor macro SQLITE_MAX_LENGTH. The default value of this macro is 1 billion (1 thousand million or 1,000,000,000). You can raise or lower this value at compile-time using a command-line option like this:

The current implementation will only support a string or BLOB length up to 231-1 or 2147483647. And some built-in functions such as hex() might fail well before that point. In security-sensitive applications it is best not to try to increase the maximum string and blob length. In fact, you might do well to lower the maximum string and blob length to something more in the range of a few million if that is possible.[/quote]

Effectively, no. Even if you specify VARCHAR(12) for example, length will not be enforced. When I was with Xojo (Real Software at the time) making the transition from SQLite to PostgreSQL highlighted a number of instances of this. The most humorous were logs of activation code redemption, typically 6 characters. I found the entire first chapter of the user’s guide… in russian.

I appreciate it Thom. So you’re basically saying, I need not worry much about the number of characters. In the one subsection I’m working on now, I’m up to 8,000 characters (including spaces), and I have more to add to this one. Just seeing if I need to think about splitting this one up more

IMO, this is a drawback of SQLite.

yes I agree most of the time. some of the time its a bonus (being extra flexible).

What is? Unable to limit the size? I haven’t set any limitations and don’t want to with this project. I want to go big :slight_smile:

In other SQL engines, like Postgres, size is enforced for good reason. If you define a column as VARCHAR(9) to hold, say, a social security number, you don’t want to accidentally store notes or a book in there. On the other hand, if you need virtually unlimited space, you would define the column as TEXT. SQLite doesn’t give you that choice as it doesn’t enforce size or type. Want to store some text in an integer column? Go right ahead.

It’s almost like defining all of your variables in Xojo as Variant. It will lead to issues later on.

Thumbs up! Thank you all for responding! I did read that where SQLite really doesn’t care what’s in the field or what it is “defined” as. Seems silly

to a point… If I were you I would read the documentation about SQLite AFFINITY, while not strict datatypes, it does define the “rules” the SQLite DOES use

Thanks Dave. I’ll take a read at the documentation. For the most part, I’m choosing either TEXT or INTEGER when defining my fields. Most are TEXT

A good example would be the results of an order by clause. Having an Integer or Text column will result in different sort orders.