>________________________________
> From: Clemens Ladisch <clem...@ladisch.de>
>To: sqlite-users@sqlite.org
>Sent: Wednesday, December 3, 2014 1:00 PM
>Subject: Re: [sqlite] trying to store a file as a blob. caught on syntax...
>
>
>Jonathan Leslie wrote:
>> I'm at a cmd.exe prompt.
>
>
>
>
>>
>> sqlite> INSERT INTO Files (name,contents) VALUES ('tsql.lis',X"$(od -A n -t
>> x1 tsql.lis|tr -d '\r\n\t ')");
>> Error: near ""$(od -A n -t x1 tsql.lis|tr -d '\r\n\t ')"": syntax error
>>
>> I'm trying to store the file tsql.lis as a blob, and od.exe and tr.exe are
>> in my path
>
>The entire command would have to be executed from a 'real' shell that
>supports $() commands. Neither sqlite3.exe nor cmd.exe do.
>
>With a sufficiently recent SQLite version (yours is the last one that
>isn't), you can use the readfile() function:
>
> sqlite> INSERT INTO Files (name, contents) VALUES ('tsql.lis',
> readfile('tsql.lis'));
>
>
>Regards,
>Clemens
>______________________
>
Ahhh!!! Thank you Clemens! you gave me just enough info to get it to work. I
can do it with a bat file using sqlite3 and the cmd.exe prompt. I'll show the
cmd.exe session below. My next question is now that I have the blob in the
database how do I retrieve it and remake the original file?
cmd.exe session::::::::::::::::::::::::
C:\jon>od -A n -t x1 image2.png |tr -d '\r\n\t '>tempfile.txt
C:\jon>cat insertbegin.txt
INSERT INTO Files (name,contents) VALUES ('image2.png','
C:\jon>cat insertend.txt
');
C:\jon>cat insertbegin.txt tempfile.txt insertend.txt >insertblob.sql
C:\jon>cat insertblob.sql
INSERT INTO Files (name,contents) VALUES ('image2.png','
89504e470d0a1a0a0000000d4948445200000063000000530803000000bc9f490a00000015744558744372656174696f6e2054696d650007de0a060d282ed76943c40000000774494d4507de0a0
60d290759c10999000000097048597300000e7400000e74016b24b3d6000002b8504c5445ffffffe7e7e7cececec0c0c0bdbdbdc6cecedededeeff7f7d6d6d6b5b5b5949494848484848c8c949c
9cadb5b5e7efefa5a5a56b6b6b565e5e5252526b73738c9494a5b5b5c6d6d6a5adad3c3f3f2e36362931314a5a5a6b7b7b94a5a5b5bdbdced6d6adadad292929181818212929546d6d7b8c8c94a
dadb5c6c6efefef4a5252080808637b7b8494949cb5b5bdcecef7f7f7d6dede2121211010100000006b84848ca5a5adc6c6808080c6c6c6182121081010394a4a84a5a5a5bdbd7b84845f6f6f04
3643124c533b75775a8c8c8c8c8c737f7f424a4a4ab5b5188ca429b5d61a9ebb1896b27ed8dbadbdbd7b94949cadadbdc6c618849418b5d618adce18b5ce18add64ce9f999d0d07b9c9cdee7e71
56c7410a6c531d6f79cdede189fc07bbdbd09586b1096af21bdd6399ca91829292b474a061d2208849c21b5d6102121173538165d6c22737a8cdede8cbdbd187b940008080c738c21b5ce5abdc6
00101812788c81f4fcb5f7f7186b7f18849c18738490ffffc6ffff77f3fbbdffff737373a5efef31b5b5adffff39c1deceffff8cadadd6ffff849c9c9cbdbd52efffb5ffff0e85948c9c9c087b9
c5afbff6bfbff21bdde6b94943cdcfaf7ffff0c94a0efffff29b5ce3fcede4ad6e742c6de4ab5bd5ac6ce6dcfd65ab5b552d4d667f3ff29bddea5ffff84e6eb31b5d652ffffdeffff84c6c68cb5
b5188c9490cecea5d6d6b5cecea9dadab5d6d66badad41525273949494b9b9adcecebdd6d6189494738c8c1bd0f421d6ff29e2ff29efff21deff18c6e742b5ce21d6d618c6ef31ffff39ffff42f
fff39cece31f7f731efef29e2f763b5c642b5b521ffff42d6d64abdbd4aefff29cece2bd8d84affff29ffff6b8c8c42e7e7b5e2e2739c9c7ba5a584b5b5c6f7f7bddedea5c6c69c9c9cc6e7e79c
a5a5bdefefd6efefa0a0a4bde7e7cef7f7e7f7f7d6e7e7cededee7ffffdef7f7deefef4e9a2f670000000174524e530040e6d8660000080b4944415478daed98fb5f5be51dc7c325e112ca25949
613109824084d68b904360296f4145b4f13b02427c7b6a18d74854e49eba9cc2049b37455a485ba5e44b46a756bb573b656aca5f6e6664bebd44d37759b9b733a2f6cffc69ecb392727c97952c2
fada0f7bf179f12270cef97edfcff7f97e9feff3e4a8540b5ad0ff8bea525252f26ea29a8c0905abf8ab8ace81325b5ad41589f5ddb779b393131372336855ffcdec645d4204782c0d3857a7e77
f5e5c5e9d40dddd2bbefbf2e4ececec64464d1ef6ae06565fe52efdd7c97fe7652442a466e567179797e77ea5dd93f3c5faf5c5442d59b2f2dcd723a3a327bfd9542f8ca90b5a2d3a3df2f5b7f5
9b6a12213ed7eed196ad2fcb2928b455d5ee7b82a8e6e6e5673e0b854646be5cda2d8ca90c5aad3913fae7b915a91ad274d565024441a1aebdb6768d4e4fd9ce9fbf87a88d1bcf7e1a0e8743a1b
f9f5e52f6051a537bd51a1d65fcc7235bce2eef4e4d2185d1920f10fa0663d379f332bdc178e44f7f26ea2faf6c088700e4b3bffe6d5f7b55151a5393b9943286761a9b1e2f4fd510c3c8de0310
16dff4f4b487a6df9bf9e013a2ae5dbd160a85c743e14ff737e9cc4d784c1feff09d0aefa2cccd5d24060a636d83c5b2e3c30f3f9ab9fb77d76fdc782781aefee1eafb7f1c0f8f9f6a9a321ff9d
83645ef9d99f9e893e346c0b893c8d0a4e3302cef41e7d7dfbdfcd61536569c5bf8e3c2c5cb9720e8fd778e4fef00de3f78018ce9faa5df6f34e9a712c4a1c9ca4661f4fffaca6fd8b9e8ad0b00
73f5da8b3330e21bef5eb8c20efed66ad11bcfdf514dcab9c870ce0920622e5eba78f9c295c3f8dfb74b77e9db6cfb56aa49b52b32a6b92420517a69a721711812e357ae79227a9e19afa4a640c
68961c0d2450cffbc194f8e5652e67dfb6fb202511cee793354d636f39dfbab533309fd4a2cddd6add8622ea498cc3da92a6d9b7ae3ec9be7ea53943baf980eba07bb77cf1d253126465e7ffdbe
0da7ce2c2540a4d2edb9a9dfe1e161866519fc21d3e19755e39d9daf6e790d4014db7ba4acd00c701cf874bbe11f71c801cff000cf06e958063b18568d9fd8f9ea8653a795dbbbc838c04106e78
a5497db1d43196059bf8f0d7ae2231c3c38191eeafce583af28b77769ae5c2eecd36b7720d9ed7171b0ac2f88e270c4510e9e7cf4d811a3f971c59625323a5e1a04febd0e860900399d8100e388
a1f4f5f5f95802837d39d4d98a5b6f1d99b1fbc45e04703a791efc805f01c61e550703ac63c0ab385750bb475b492b51935584189ea113db3080f743410acf785d510cd637accce058d798cad05
6aabc178a0c9a61d0e8fdfea020000ad86318f6012263f3c41113612f1419ab3c70f4827bb004d0a7d33ed726c671bf38fe5312a33eb7c80619db3d01278fbc0f7b80e0e77090619368f83f09b7
92185db5b6b525568be585a117ed0e8607841d5080130c78e74e60d99f87488c4dd54fdca66b830b64ddfda088ec8ec0b6e79e3f76ecf9e7b6051c4921d843e156c2b921a322b7164dd6011758e
470993ffdd833478f1e7df669b0d293da1a0f11e70a260425fd213179621693ddb30e2660880b646bbc993b99163f166ac48c3aa538d05cad7a2a99228a1f0e3b3659d94658e852611d800cb76b
be5b2ed8aa880b1d171660f81163be048edb1c222e74a9b02063de08c0383c445ce8b2c212f63e09944cca413d1e1a9f536101afeef905c3252e5eb1b0e4bbf47c3863c4851e29ac9f719131c91
8f28ae638527d73091952c73ac0c51faf702d733208984f0ed3b898318c91f311e958e0e083934e4ab6c20db7c4702760c80b4bf2e1f5da81f00125d27e41dd89c71607bceff5ca5ab36b6c9cd8
4c2285e5887866a002588cd4e439d61bb98a2503de3b4a6c26d276dbcf0b7601514e289e0f38a40971a04bce805c8215f3d808b199a834e5d97b96e91fb66cf707a28500e87cc2e246efee61d0a
9289682517b1fed400bbd4589217c3de8f347b917fcc37390c870b10e1e9f8a102816223493960447459a17a7470e006715695fe7382fe30f8aa72f1e5264205e383524620479d17fe4a4050f41
bc5d4c0768685e463c7d09932626c819e0874237650ce363220f7c7b7c344dafc2a23d8c4b8a03ecc0419f7087eeeded1542c243db1d420b3df15cf5fa7a916b00a007765990763d403f242d333
7c70dfe78752bbafe701f8d844880e5f70f3d928021d4ee6aa07e93254edb7f2431d8fbedfdf25ba607067c58e04cb6f3412203365eb811c6a8b2d18465b1f46e9526eb87dbac166ba3703d82ea
eb5fddd1d1516a2232ba6a8d54a529c675a3d5d0500965b05a3c91c2da7c5fa59ea20ce0ba3402b9f43665c6a6ae1c1dd5608a71dda05f4b959494e829d8657aa4c9dabca5516f34eba892dbd15
322494019da6ccacdbda61be4433041aef5c8f7329daeb050a7435fe4d6b958f05d0e7e05b877432b65dbd8bcb8103d259204546589aea9f9fb8aef1b34dfd3eab009725d007c03ef05ed7769b5
6b50d7a7ef66987540a05f761ae19cafecba0b3f259809280365bb677db7f26b93cc7c2d3601ae17b717e5688172b2f3b3d21755e16a5845c39402d156139cf3952bd4d9f021d10ca3f454e96d6
5d51584d7269959d804ba2e4e6b494b4f4f4f6bc94c6929aed251d6d6e8b496b48138d49a7af8906826a0748b8b962c25bd9951a5a44213e85a5d1f7955509f0baba131aa760c7a6364ce053380
fa0190366751ae9a8820a826ada860d9ed52ede0b4969a6b97af889e737184696a4d4d9208580d2010b17684b41aabeea8ae48de155999c552c99548692dafc8bb8588a8928ba4f5d622e42587e
b0ea4f55623642527a635e3bff7b9a0052de87fa4ff008005e0ee675f10610000000049454e44ae426082');
C:\jon>dir *.db3
Volume in drive C has no label.
Volume Serial Number is F0C1-CFFE
Directory of C:\jon
141203 11:01 AM 3,072 test2.db3
3 File(s) 78,848 bytes
0 Dir(s) 47,816,364,032 bytes free
C:\jon>sqlite3 test2.db3
SQLite version 3.8.5 2014-06-04 14:06:34
Enter ".help" for usage hints.
sqlite> .tables
Files
sqlite> .schema files
CREATE TABLE Files (name TEXT PRIMARY KEY,contents BLOB);
sqlite> .read insertblob.sql
sqlite> select * from files;
image2.png|
89504e470d0a1a0a0000000d4948445200000063000000530803000000bc9f490a00000015744558744372656174696f6e2054696d650007de0a060d282ed76943c40000000774494d4507de0a0
60d290759c10999000000097048597300000e7400000e74016b24b3d6000002b8504c5445ffffffe7e7e7cececec0c0c0bdbdbdc6cecedededeeff7f7d6d6d6b5b5b5949494848484848c8c949c
9cadb5b5e7efefa5a5a56b6b6b565e5e5252526b73738c9494a5b5b5c6d6d6a5adad3c3f3f2e36362931314a5a5a6b7b7b94a5a5b5bdbdced6d6adadad292929181818212929546d6d7b8c8c94a
dadb5c6c6efefef4a5252080808637b7b8494949cb5b5bdcecef7f7f7d6dede2121211010100000006b84848ca5a5adc6c6808080c6c6c6182121081010394a4a84a5a5a5bdbd7b84845f6f6f04
3643124c533b75775a8c8c8c8c8c737f7f424a4a4ab5b5188ca429b5d61a9ebb1896b27ed8dbadbdbd7b94949cadadbdc6c618849418b5d618adce18b5ce18add64ce9f999d0d07b9c9cdee7e71
56c7410a6c531d6f79cdede189fc07bbdbd09586b1096af21bdd6399ca91829292b474a061d2208849c21b5d6102121173538165d6c22737a8cdede8cbdbd187b940008080c738c21b5ce5abdc6
00101812788c81f4fcb5f7f7186b7f18849c18738490ffffc6ffff77f3fbbdffff737373a5efef31b5b5adffff39c1deceffff8cadadd6ffff849c9c9cbdbd52efffb5ffff0e85948c9c9c087b9
c5afbff6bfbff21bdde6b94943cdcfaf7ffff0c94a0efffff29b5ce3fcede4ad6e742c6de4ab5bd5ac6ce6dcfd65ab5b552d4d667f3ff29bddea5ffff84e6eb31b5d652ffffdeffff84c6c68cb5
b5188c9490cecea5d6d6b5cecea9dadab5d6d66badad41525273949494b9b9adcecebdd6d6189494738c8c1bd0f421d6ff29e2ff29efff21deff18c6e742b5ce21d6d618c6ef31ffff39ffff42f
fff39cece31f7f731efef29e2f763b5c642b5b521ffff42d6d64abdbd4aefff29cece2bd8d84affff29ffff6b8c8c42e7e7b5e2e2739c9c7ba5a584b5b5c6f7f7bddedea5c6c69c9c9cc6e7e79c
a5a5bdefefd6efefa0a0a4bde7e7cef7f7e7f7f7d6e7e7cededee7ffffdef7f7deefef4e9a2f670000000174524e530040e6d8660000080b4944415478daed98fb5f5be51dc7c325e112ca25949
613109824084d68b904360296f4145b4f13b02427c7b6a18d74854e49eba9cc2049b37455a485ba5e44b46a756bb573b656aca5f6e6664bebd44d37759b9b733a2f6cffc69ecb392727c97952c2
fada0f7bf179f12270cef97edfcff7f97e9feff3e4a8540b5ad0ff8bea525252f26ea29a8c0905abf8ab8ace81325b5ad41589f5ddb779b393131372336855ffcdec645d4204782c0d3857a7e77
f5e5c5e9d40dddd2bbefbf2e4ececec64464d1ef6ae06565fe52efdd7c97fe7652442a466e567179797e77ea5dd93f3c5faf5c5442d59b2f2dcd723a3a327bfd9542f8ca90b5a2d3a3df2f5b7f5
9b6a12213ed7eed196ad2fcb2928b455d5ee7b82a8e6e6e5673e0b854646be5cda2d8ca90c5aad3913fae7b915a91ad274d565024441a1aebdb6768d4e4fd9ce9fbf87a88d1bcf7e1a0e8743a1b
f9f5e52f6051a537bd51a1d65fcc7235bce2eef4e4d2185d1920f10fa0663d379f332bdc178e44f7f26ea2faf6c088700e4b3bffe6d5f7b55151a5393b9943286761a9b1e2f4fd510c3c8de0310
16dff4f4b487a6df9bf9e013a2ae5dbd160a85c743e14ff737e9cc4d784c1feff09d0aefa2cccd5d24060a636d83c5b2e3c30f3f9ab9fb77d76fdc782781aefee1eafb7f1c0f8f9f6a9a321ff9d
83645ef9d99f9e893e346c0b893c8d0a4e3302cef41e7d7dfbdfcd61536569c5bf8e3c2c5cb9720e8fd778e4fef00de3f78018ce9faa5df6f34e9a712c4a1c9ca4661f4fffaca6fd8b9e8ad0b00
73f5da8b3330e21bef5eb8c20efed66ad11bcfdf514dcab9c870ce0920622e5eba78f9c295c3f8dfb74b77e9db6cfb56aa49b52b32a6b92420517a69a721711812e357ae79227a9e19afa4a640c
68961c0d2450cffbc194f8e5652e67dfb6fb202511cee793354d636f39dfbab533309fd4a2cddd6add8622ea498cc3da92a6d9b7ae3ec9be7ea53943baf980eba07bb77cf1d253126465e7ffdbe
0da7ce2c2540a4d2edb9a9dfe1e161866519fc21d3e19755e39d9daf6e790d4014db7ba4acd00c701cf874bbe11f71c801cff000cf06e958063b18568d9fd8f9ea8653a795dbbbc838c04106e78
a5497db1d43196059bf8f0d7ae2231c3c38191eeafce583af28b77769ae5c2eecd36b7720d9ed7171b0ac2f88e270c4510e9e7cf4d811a3f971c59625323a5e1a04febd0e860900399d8100e388
a1f4f5f5f95802837d39d4d98a5b6f1d99b1fbc45e04703a791efc805f01c61e550703ac63c0ab385750bb475b492b51935584189ea113db3080f743410acf785d510cd637accce058d798cad05
6aabc178a0c9a61d0e8fdfea020000ad86318f6012263f3c41113612f1419ab3c70f4827bb004d0a7d33ed726c671bf38fe5312a33eb7c80619db3d01278fbc0f7b80e0e77090619368f83f09b7
92185db5b6b525568be585a117ed0e8607841d5080130c78e74e60d99f87488c4dd54fdca66b830b64ddfda088ec8ec0b6e79e3f76ecf9e7b6051c4921d843e156c2b921a322b7164dd6011758e
470993ffdd833478f1e7df669b0d293da1a0f11e70a260425fd213179621693ddb30e2660880b646bbc993b99163f166ac48c3aa538d05cad7a2a99228a1f0e3b3659d94658e852611d800cb76b
be5b2ed8aa880b1d171660f81163be048edb1c222e74a9b02063de08c0383c445ce8b2c212f63e09944cca413d1e1a9f536101afeef905c3252e5eb1b0e4bbf47c3863c4851e29ac9f719131c91
8f28ae638527d73091952c73ac0c51faf702d733208984f0ed3b898318c91f311e958e0e083934e4ab6c20db7c4702760c80b4bf2e1f5da81f00125d27e41dd89c71607bceff5ca5ab36b6c9cd8
4c2285e5887866a002588cd4e439d61bb98a2503de3b4a6c26d276dbcf0b7601514e289e0f38a40971a04bce805c8215f3d808b199a834e5d97b96e91fb66cf707a28500e87cc2e246efee61d0a
9289682517b1fed400bbd4589217c3de8f347b917fcc37390c870b10e1e9f8a102816223493960447459a17a7470e006715695fe7382fe30f8aa72f1e5264205e383524620479d17fe4a4050f41
bc5d4c0768685e463c7d09932626c819e0874237650ce363220f7c7b7c344dafc2a23d8c4b8a03ecc0419f7087eeeded1542c243db1d420b3df15cf5fa7a916b00a007765990763d403f242d333
7c70dfe78752bbafe701f8d844880e5f70f3d928021d4ee6aa07e93254edb7f2431d8fbedfdf25ba607067c58e04cb6f3412203365eb811c6a8b2d18465b1f46e9526eb87dbac166ba3703d82ea
eb5fddd1d1516a2232ba6a8d54a529c675a3d5d0500965b05a3c91c2da7c5fa59ea20ce0ba3402b9f43665c6a6ae1c1dd5608a71dda05f4b959494e829d8657aa4c9dabca5516f34eba892dbd15
322494019da6ccacdbda61be4433041aef5c8f7329daeb050a7435fe4d6b958f05d0e7e05b877432b65dbd8bcb8103d259204546589aea9f9fb8aef1b34dfd3eab009725d007c03ef05ed7769b5
6b50d7a7ef66987540a05f761ae19cafecba0b3f259809280365bb677db7f26b93cc7c2d3601ae17b717e5688172b2f3b3d21755e16a5845c39402d156139cf3952bd4d9f021d10ca3f454e96d6
5d51584d7269959d804ba2e4e6b494b4f4f4f6bc94c6929aed251d6d6e8b496b48138d49a7af8906826a0748b8b962c25bd9951a5a44213e85a5d1f7955509f0baba131aa760c7a6364ce053380
fa0190366751ae9a8820a826ada860d9ed52ede0b4969a6b97af889e737184696a4d4d9208580d2010b17684b41aabeea8ae48de155999c552c99548692dafc8bb8588a8928ba4f5d622e42587e
b0ea4f55623642527a635e3bff7b9a0052de87fa4ff008005e0ee675f10610000000049454e44ae426082
sqlite>
sqlite> .quit
C:\jon>dir *.db3
Volume in drive C has no label.
Volume Serial Number is F0C1-CFFE
Directory of C:\jon
141203 08:03 PM 8,192 test2.db3
3 File(s) 83,968 bytes
0 Dir(s) 47,816,429,568 bytes free
C:\jon>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
