I had the impression additionally that BLOBS aren't usually journalled (for many rdbms), so storing them in the database is nothing more than a convenience, and just makes the database too darned big.
With DRH's advice, remember to store locations you will *always* be able to get to (like not "C:\WIN32", or "/home/keith/private"). Use UNC or web addresses or something you can remap easily, that doesn't break if you just copy the database and forget to copy the files. I know that sounds like an obvious thing, but it will bite you in areas most prefer unbitten. Ask me how I learned that lesson (painfully, I might add). --Keith > -----Original Message----- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Thursday, July 15, 2004 7:54 AM > To: [EMAIL PROTECTED] > Subject: Re: [sqlite] Storing large files > > > Paul Malcher wrote: > > Dennis Volodomanov wrote: > > > >> Hello all, > >> > >> Can someone please tell me if there's any limit on the > size of data > >> stored in one field (BLOB) in SQLite3? > >> > >> > > Hi, I spent all day yesterday picking through SQLite3 > docs, trying to > > solved a problem that turned out was my own fault. I never saw any > > mention of 16 MB Blob limit. Either way I intend to find > out , I'm gonna > > lay some serious abuse on SQLite today and see what can and cannot > > handle. I'll check the docs again maybe I missed it either > way I'll let > > you know what I find out. > > The 16MB limit was in SQLite 2.8 file format. In most > installations, a separate limitation of 1MB is imposed by the > schema layer. To relax that limit, you have to change > MAX_BYTES_PER_ROW in the sqliteInt.h file and recompile. > > In SQLite version 3.0, there is no theoretical limit on the > size of BLOBs. (The limit is really about 4.6e+18 bytes, but > file size limits will come into play first so you will never > get to that size.) However, the same MAX_BYTES_PER_ROW > constraint is still in place. So with a default build, the > maximum BLOB size is still about 1MB. You can, I suppose, > increase MAX_BYTES_PER_ROW to whatever value you want and > recompile. But, if you make MAX_BYTES_PER_ROW really big, I > think you will find that performance gets very bad for very > large rows. > > For large BLOBs, your best bet is to store the BLOB data in a > separate file and store the name of the separate file in the > SQLite database. > > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > >
