Hi Richard, 'SAR' looks like a great utility!
However, on production *nix machines, the path to the SQLite 'sar' will probably have to be absolute, or else the native 'sar' (System Activity Reporter) will run instead. Perhaps add a caveat to the docs for SQLite's 'sar?' Keith On Sun, Apr 6, 2014 at 7:43 PM, Richard Hipp <d...@sqlite.org> wrote: > On Sun, Apr 6, 2014 at 4:01 PM, <to...@acm.org> wrote: > >> I haven't figured out how to load a blob (e.g., image) from the shell. I >> would think there should be something like this but can't find anything: >> >> insert into table values(file('path_to_binary_file')); >> >> Are blobs only loadable by using SQLite from C? >> >> Any ideas? >> > > At http://www.sqlite.org/sar there is a utility program that generates an > "SQLite Archive", similar to a ZIP archive but using SQLite as the file > format instead of the ZIP format. You can use that utility (with the > just-added "-n" option to disable compression) to load one or more images > into a database. Then in the shell, transfer those images out of the "SAR" > table where the "sar" utility puts them and into the field and table of > your choice. Use the reverse procedures to extract the BLOBs. This is > more work (more commands) but has the advantage of being able to load many > thousands of BLOBs all at once, instead of one at a time. The "sar" > utility works on unix. I have made no effort to make it work on Windows, > but I will accept patches if that is important to you. > > Larray Brasfield's extention to shell.c to support ".blobextract" and > ".blobreplace" commands apparently uses the incremental BLOB I/O interface > to avoid the need to load entire images into memory. ("sar" does not do > this, btw. It loads each image into memory.) That is nice, but on a > modern workstation with many GB of RAM, is it really necessary? Maybe in > some obscure cases. But in the common case of a smaller BLOB (a few > megabytes) I think custom functions would work better: > > INSERT INTO sometable(x) VALUES(fromfile('data/myblob.gif')); > UPDATE sometable SET x=fromfile('data/myblob.gif') WHERE rowid=123; > SELECT tofile('data/blob-out.gif', x) FROM sometable WHERE rowid=123; > > -- Extract all blobs: > SELECT tofile('data/blob-out-'||rowid||'.gif', x) FROM sometable; > > I'm open to the possibility of adding fromfile() and tofile() as extension > functions in shell.c. Maybe tomorrow sometime. > > Another idea is to create a virtual table that wraps the filesystem: > > CREATE VIRTUAL TABLE temp.fs AS fs; > INSERT INTO sometable(x) SELECT content FROM fs WHERE > name='data/myblob.gif'; > UPDATE sometable SET x=(SELECT content FROM fs WHERE > name='data/myblob.gif) > WHERE rowid=123; > REPLACE INTO fs(name, content) SELECT 'data/blob-out.gif', x FROM > sometable > WHERE rowid=123; > > REPLACE INTO fs(name, content SELECT 'data/blob-out'||rowid||'.gif, x > FROM sometable; > > The virtual table is potentially a much more powerful abstraction, but as > you can see from the examples above, it requires a little more work to > actually use. > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users