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:
-DSQLITE_MAX_LENGTH=123456789
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 users 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
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.
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
