Re: [sqlite] How-to change column limit?
On Sun, Jul 12, 2009 at 08:23:35AM -0400, Stephan Lindner scratched on the wall: > Dear all, > > Sorry ahead for a really trivial question, but I'm not very familiar > with sqlite and just can't figure it out ... > > I'm importing large survey files into sqlite, and I run into the > problem of creating a table with too many columns, i.e. > > bash$ sqlite3 < tables.sql > > produces > > bash$ SQL error near line 3: too many columns on t3 You need to re-compile SQLite with a larger limit. The limit is defined by the compile-time define SQLITE_MAX_COLUMNs. The default is 2000. You need to seriously consider your database design if you're going over this. http://sqlite.org/limits.html#max_column http://sqlite.org/compile.html > Now I figured that I have to change the limit on columns -- see > > http://www.sqlite.org/c3ref/c_limit_attached.html > > but I don't know how! I tred all kinds of variations of > > int sqlite3_limit(sqlite3*, SQLITE_LIMIT_COLUMN, 5000) That's a C programming call. And you can only use it to lower the limit, not raise it. By default the soft-limit is set to the hard-limit. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How-to change column limit?
On 12/07/2009 10:23 PM, Stephan Lindner wrote: > > I'm importing large survey files into sqlite, and I run into the > problem of creating a table with too many columns, i.e. How many columns do you have? > bash$ sqlite3 < tables.sql > > produces > > bash$ SQL error near line 3: too many columns on t3 > (where tables.sql looks like this: create table t1(var1,var2,...); create > t2(var1, var2, ...); ) > > Now I figured that I have to change the limit on columns -- see > > http://www.sqlite.org/c3ref/c_limit_attached.html > > but I don't know how! I tred all kinds of variations of > > int sqlite3_limit(sqlite3*, SQLITE_LIMIT_COLUMN, 5000) > > > as for instance > > sqlite> int sqlite3_limit(sqlite3*, SQLITE_LIMIT_COLUMN, 5000); Huh? "sqlite>" indicates the command-line executable program ... but int sqlite3_limit() is C code > > all without success. http://www.sqlite.org/limits.html Summary: Default is 2000. You can change the default max at COMPILE time, up to 32767. You can REDUCE the maximum at RUN time using sqlite3_limit(). If, as it appears, you have more than 2000 columns, you might like to consider a bit of normalisation of your schema. Please note carefully the remarks about O(N**2) in the docs. HTH, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How-to change column limit?
Dear all, Sorry ahead for a really trivial question, but I'm not very familiar with sqlite and just can't figure it out ... I'm importing large survey files into sqlite, and I run into the problem of creating a table with too many columns, i.e. bash$ sqlite3 < tables.sql produces bash$ SQL error near line 3: too many columns on t3 (where tables.sql looks like this: create table t1(var1,var2,...); create t2(var1, var2, ...); ) Now I figured that I have to change the limit on columns -- see http://www.sqlite.org/c3ref/c_limit_attached.html but I don't know how! I tred all kinds of variations of int sqlite3_limit(sqlite3*, SQLITE_LIMIT_COLUMN, 5000) as for instance sqlite> int sqlite3_limit(sqlite3*, SQLITE_LIMIT_COLUMN, 5000); all without success. Thanks in advance fr the help -- you'll do me a big favor. Stephan -- --- Stephan Lindner University of Michigan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users