select writefile(name, contents) from files where name='filename.ext';
--- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-----Original Message----- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Jonathan Leslie >Sent: Wednesday, 3 December, 2014 18:20 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] trying to store a file as a blob. caught on >syntax... > > > > > >>________________________________ >> 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',' >89504e470d0a1a0a0000000d4948445200000063000000530803000000bc9f490a0000001 >5744558744372656174696f6e2054696d650007de0a060d282ed76943c40000000774494d >4507de0a0 >60d290759c10999000000097048597300000e7400000e74016b24b3d6000002b8504c5445 >ffffffe7e7e7cececec0c0c0bdbdbdc6cecedededeeff7f7d6d6d6b5b5b59494948484848 >48c8c949c >9cadb5b5e7efefa5a5a56b6b6b565e5e5252526b73738c9494a5b5b5c6d6d6a5adad3c3f3 >f2e36362931314a5a5a6b7b7b94a5a5b5bdbdced6d6adadad292929181818212929546d6d >7b8c8c94a >dadb5c6c6efefef4a5252080808637b7b8494949cb5b5bdcecef7f7f7d6dede2121211010 >100000006b84848ca5a5adc6c6808080c6c6c6182121081010394a4a84a5a5a5bdbd7b848 >45f6f6f04 >3643124c533b75775a8c8c8c8c8c737f7f424a4a4ab5b5188ca429b5d61a9ebb1896b27ed >8dbadbdbd7b94949cadadbdc6c618849418b5d618adce18b5ce18add64ce9f999d0d07b9c >9cdee7e71 >56c7410a6c531d6f79cdede189fc07bbdbd09586b1096af21bdd6399ca91829292b474a06 >1d2208849c21b5d6102121173538165d6c22737a8cdede8cbdbd187b940008080c738c21b >5ce5abdc6 >00101812788c81f4fcb5f7f7186b7f18849c18738490ffffc6ffff77f3fbbdffff737373a >5efef31b5b5adffff39c1deceffff8cadadd6ffff849c9c9cbdbd52efffb5ffff0e85948c >9c9c087b9 >c5afbff6bfbff21bdde6b94943cdcfaf7ffff0c94a0efffff29b5ce3fcede4ad6e742c6de >4ab5bd5ac6ce6dcfd65ab5b552d4d667f3ff29bddea5ffff84e6eb31b5d652ffffdeffff8 >4c6c68cb5 >b5188c9490cecea5d6d6b5cecea9dadab5d6d66badad41525273949494b9b9adcecebdd6d >6189494738c8c1bd0f421d6ff29e2ff29efff21deff18c6e742b5ce21d6d618c6ef31ffff >39ffff42f >fff39cece31f7f731efef29e2f763b5c642b5b521ffff42d6d64abdbd4aefff29cece2bd8 >d84affff29ffff6b8c8c42e7e7b5e2e2739c9c7ba5a584b5b5c6f7f7bddedea5c6c69c9c9 >cc6e7e79c >a5a5bdefefd6efefa0a0a4bde7e7cef7f7e7f7f7d6e7e7cededee7ffffdef7f7deefef4e9 >a2f670000000174524e530040e6d8660000080b4944415478daed98fb5f5be51dc7c325e1 >12ca25949 >613109824084d68b904360296f4145b4f13b02427c7b6a18d74854e49eba9cc2049b37455 >a485ba5e44b46a756bb573b656aca5f6e6664bebd44d37759b9b733a2f6cffc69ecb39272 >7c97952c2 >fada0f7bf179f12270cef97edfcff7f97e9feff3e4a8540b5ad0ff8bea525252f26ea29a8 >c0905abf8ab8ace81325b5ad41589f5ddb779b393131372336855ffcdec645d4204782c0d >3857a7e77 >f5e5c5e9d40dddd2bbefbf2e4ececec64464d1ef6ae06565fe52efdd7c97fe7652442a466 >e567179797e77ea5dd93f3c5faf5c5442d59b2f2dcd723a3a327bfd9542f8ca90b5a2d3a3 >df2f5b7f5 >9b6a12213ed7eed196ad2fcb2928b455d5ee7b82a8e6e6e5673e0b854646be5cda2d8ca90 >c5aad3913fae7b915a91ad274d565024441a1aebdb6768d4e4fd9ce9fbf87a88d1bcf7e1a >0e8743a1b >f9f5e52f6051a537bd51a1d65fcc7235bce2eef4e4d2185d1920f10fa0663d379f332bdc1 >78e44f7f26ea2faf6c088700e4b3bffe6d5f7b55151a5393b9943286761a9b1e2f4fd510c >3c8de0310 >16dff4f4b487a6df9bf9e013a2ae5dbd160a85c743e14ff737e9cc4d784c1feff09d0aefa >2cccd5d24060a636d83c5b2e3c30f3f9ab9fb77d76fdc782781aefee1eafb7f1c0f8f9f6a >9a321ff9d >83645ef9d99f9e893e346c0b893c8d0a4e3302cef41e7d7dfbdfcd61536569c5bf8e3c2c5 >cb9720e8fd778e4fef00de3f78018ce9faa5df6f34e9a712c4a1c9ca4661f4fffaca6fd8b >9e8ad0b00 >73f5da8b3330e21bef5eb8c20efed66ad11bcfdf514dcab9c870ce0920622e5eba78f9c29 >5c3f8dfb74b77e9db6cfb56aa49b52b32a6b92420517a69a721711812e357ae79227a9e19 >afa4a640c >68961c0d2450cffbc194f8e5652e67dfb6fb202511cee793354d636f39dfbab533309fd4a >2cddd6add8622ea498cc3da92a6d9b7ae3ec9be7ea53943baf980eba07bb77cf1d2531264 >65e7ffdbe >0da7ce2c2540a4d2edb9a9dfe1e161866519fc21d3e19755e39d9daf6e790d4014db7ba4a >cd00c701cf874bbe11f71c801cff000cf06e958063b18568d9fd8f9ea8653a795dbbbc838 >c04106e78 >a5497db1d43196059bf8f0d7ae2231c3c38191eeafce583af28b77769ae5c2eecd36b7720 >d9ed7171b0ac2f88e270c4510e9e7cf4d811a3f971c59625323a5e1a04febd0e860900399 >d8100e388 >a1f4f5f5f95802837d39d4d98a5b6f1d99b1fbc45e04703a791efc805f01c61e550703ac6 >3c0ab385750bb475b492b51935584189ea113db3080f743410acf785d510cd637accce058 >d798cad05 >6aabc178a0c9a61d0e8fdfea020000ad86318f6012263f3c41113612f1419ab3c70f4827b >b004d0a7d33ed726c671bf38fe5312a33eb7c80619db3d01278fbc0f7b80e0e7709061936 >8f83f09b7 >92185db5b6b525568be585a117ed0e8607841d5080130c78e74e60d99f87488c4dd54fdca >66b830b64ddfda088ec8ec0b6e79e3f76ecf9e7b6051c4921d843e156c2b921a322b7164d >d6011758e >470993ffdd833478f1e7df669b0d293da1a0f11e70a260425fd213179621693ddb30e2660 >880b646bbc993b99163f166ac48c3aa538d05cad7a2a99228a1f0e3b3659d94658e852611 >d800cb76b >be5b2ed8aa880b1d171660f81163be048edb1c222e74a9b02063de08c0383c445ce8b2c21 >2f63e09944cca413d1e1a9f536101afeef905c3252e5eb1b0e4bbf47c3863c4851e29ac9f >719131c91 >8f28ae638527d73091952c73ac0c51faf702d733208984f0ed3b898318c91f311e958e0e0 >83934e4ab6c20db7c4702760c80b4bf2e1f5da81f00125d27e41dd89c71607bceff5ca5ab >36b6c9cd8 >4c2285e5887866a002588cd4e439d61bb98a2503de3b4a6c26d276dbcf0b7601514e289e0 >f38a40971a04bce805c8215f3d808b199a834e5d97b96e91fb66cf707a28500e87cc2e246 >efee61d0a >9289682517b1fed400bbd4589217c3de8f347b917fcc37390c870b10e1e9f8a1028162234 >93960447459a17a7470e006715695fe7382fe30f8aa72f1e5264205e383524620479d17fe >4a4050f41 >bc5d4c0768685e463c7d09932626c819e0874237650ce363220f7c7b7c344dafc2a23d8c4 >b8a03ecc0419f7087eeeded1542c243db1d420b3df15cf5fa7a916b00a007765990763d40 >3f242d333 >7c70dfe78752bbafe701f8d844880e5f70f3d928021d4ee6aa07e93254edb7f2431d8fbed >fdf25ba607067c58e04cb6f3412203365eb811c6a8b2d18465b1f46e9526eb87dbac166ba >3703d82ea >eb5fddd1d1516a2232ba6a8d54a529c675a3d5d0500965b05a3c91c2da7c5fa59ea20ce0b >a3402b9f43665c6a6ae1c1dd5608a71dda05f4b959494e829d8657aa4c9dabca5516f34eb >a892dbd15 >322494019da6ccacdbda61be4433041aef5c8f7329daeb050a7435fe4d6b958f05d0e7e05 >b877432b65dbd8bcb8103d259204546589aea9f9fb8aef1b34dfd3eab009725d007c03ef0 >5ed7769b5 >6b50d7a7ef66987540a05f761ae19cafecba0b3f259809280365bb677db7f26b93cc7c2d3 >601ae17b717e5688172b2f3b3d21755e16a5845c39402d156139cf3952bd4d9f021d10ca3 >f454e96d6 >5d51584d7269959d804ba2e4e6b494b4f4f4f6bc94c6929aed251d6d6e8b496b48138d49a >7af8906826a0748b8b962c25bd9951a5a44213e85a5d1f7955509f0baba131aa760c7a636 >4ce053380 >fa0190366751ae9a8820a826ada860d9ed52ede0b4969a6b97af889e737184696a4d4d920 >8580d2010b17684b41aabeea8ae48de155999c552c99548692dafc8bb8588a8928ba4f5d6 >22e42587e >b0ea4f55623642527a635e3bff7b9a0052de87fa4ff008005e0ee675f1061000000004945 >4e44ae426082'); > > >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| >89504e470d0a1a0a0000000d4948445200000063000000530803000000bc9f490a0000001 >5744558744372656174696f6e2054696d650007de0a060d282ed76943c40000000774494d >4507de0a0 >60d290759c10999000000097048597300000e7400000e74016b24b3d6000002b8504c5445 >ffffffe7e7e7cececec0c0c0bdbdbdc6cecedededeeff7f7d6d6d6b5b5b59494948484848 >48c8c949c >9cadb5b5e7efefa5a5a56b6b6b565e5e5252526b73738c9494a5b5b5c6d6d6a5adad3c3f3 >f2e36362931314a5a5a6b7b7b94a5a5b5bdbdced6d6adadad292929181818212929546d6d >7b8c8c94a >dadb5c6c6efefef4a5252080808637b7b8494949cb5b5bdcecef7f7f7d6dede2121211010 >100000006b84848ca5a5adc6c6808080c6c6c6182121081010394a4a84a5a5a5bdbd7b848 >45f6f6f04 >3643124c533b75775a8c8c8c8c8c737f7f424a4a4ab5b5188ca429b5d61a9ebb1896b27ed >8dbadbdbd7b94949cadadbdc6c618849418b5d618adce18b5ce18add64ce9f999d0d07b9c >9cdee7e71 >56c7410a6c531d6f79cdede189fc07bbdbd09586b1096af21bdd6399ca91829292b474a06 >1d2208849c21b5d6102121173538165d6c22737a8cdede8cbdbd187b940008080c738c21b >5ce5abdc6 >00101812788c81f4fcb5f7f7186b7f18849c18738490ffffc6ffff77f3fbbdffff737373a >5efef31b5b5adffff39c1deceffff8cadadd6ffff849c9c9cbdbd52efffb5ffff0e85948c >9c9c087b9 >c5afbff6bfbff21bdde6b94943cdcfaf7ffff0c94a0efffff29b5ce3fcede4ad6e742c6de >4ab5bd5ac6ce6dcfd65ab5b552d4d667f3ff29bddea5ffff84e6eb31b5d652ffffdeffff8 >4c6c68cb5 >b5188c9490cecea5d6d6b5cecea9dadab5d6d66badad41525273949494b9b9adcecebdd6d >6189494738c8c1bd0f421d6ff29e2ff29efff21deff18c6e742b5ce21d6d618c6ef31ffff >39ffff42f >fff39cece31f7f731efef29e2f763b5c642b5b521ffff42d6d64abdbd4aefff29cece2bd8 >d84affff29ffff6b8c8c42e7e7b5e2e2739c9c7ba5a584b5b5c6f7f7bddedea5c6c69c9c9 >cc6e7e79c >a5a5bdefefd6efefa0a0a4bde7e7cef7f7e7f7f7d6e7e7cededee7ffffdef7f7deefef4e9 >a2f670000000174524e530040e6d8660000080b4944415478daed98fb5f5be51dc7c325e1 >12ca25949 >613109824084d68b904360296f4145b4f13b02427c7b6a18d74854e49eba9cc2049b37455 >a485ba5e44b46a756bb573b656aca5f6e6664bebd44d37759b9b733a2f6cffc69ecb39272 >7c97952c2 >fada0f7bf179f12270cef97edfcff7f97e9feff3e4a8540b5ad0ff8bea525252f26ea29a8 >c0905abf8ab8ace81325b5ad41589f5ddb779b393131372336855ffcdec645d4204782c0d >3857a7e77 >f5e5c5e9d40dddd2bbefbf2e4ececec64464d1ef6ae06565fe52efdd7c97fe7652442a466 >e567179797e77ea5dd93f3c5faf5c5442d59b2f2dcd723a3a327bfd9542f8ca90b5a2d3a3 >df2f5b7f5 >9b6a12213ed7eed196ad2fcb2928b455d5ee7b82a8e6e6e5673e0b854646be5cda2d8ca90 >c5aad3913fae7b915a91ad274d565024441a1aebdb6768d4e4fd9ce9fbf87a88d1bcf7e1a >0e8743a1b >f9f5e52f6051a537bd51a1d65fcc7235bce2eef4e4d2185d1920f10fa0663d379f332bdc1 >78e44f7f26ea2faf6c088700e4b3bffe6d5f7b55151a5393b9943286761a9b1e2f4fd510c >3c8de0310 >16dff4f4b487a6df9bf9e013a2ae5dbd160a85c743e14ff737e9cc4d784c1feff09d0aefa >2cccd5d24060a636d83c5b2e3c30f3f9ab9fb77d76fdc782781aefee1eafb7f1c0f8f9f6a >9a321ff9d >83645ef9d99f9e893e346c0b893c8d0a4e3302cef41e7d7dfbdfcd61536569c5bf8e3c2c5 >cb9720e8fd778e4fef00de3f78018ce9faa5df6f34e9a712c4a1c9ca4661f4fffaca6fd8b >9e8ad0b00 >73f5da8b3330e21bef5eb8c20efed66ad11bcfdf514dcab9c870ce0920622e5eba78f9c29 >5c3f8dfb74b77e9db6cfb56aa49b52b32a6b92420517a69a721711812e357ae79227a9e19 >afa4a640c >68961c0d2450cffbc194f8e5652e67dfb6fb202511cee793354d636f39dfbab533309fd4a >2cddd6add8622ea498cc3da92a6d9b7ae3ec9be7ea53943baf980eba07bb77cf1d2531264 >65e7ffdbe >0da7ce2c2540a4d2edb9a9dfe1e161866519fc21d3e19755e39d9daf6e790d4014db7ba4a >cd00c701cf874bbe11f71c801cff000cf06e958063b18568d9fd8f9ea8653a795dbbbc838 >c04106e78 >a5497db1d43196059bf8f0d7ae2231c3c38191eeafce583af28b77769ae5c2eecd36b7720 >d9ed7171b0ac2f88e270c4510e9e7cf4d811a3f971c59625323a5e1a04febd0e860900399 >d8100e388 >a1f4f5f5f95802837d39d4d98a5b6f1d99b1fbc45e04703a791efc805f01c61e550703ac6 >3c0ab385750bb475b492b51935584189ea113db3080f743410acf785d510cd637accce058 >d798cad05 >6aabc178a0c9a61d0e8fdfea020000ad86318f6012263f3c41113612f1419ab3c70f4827b >b004d0a7d33ed726c671bf38fe5312a33eb7c80619db3d01278fbc0f7b80e0e7709061936 >8f83f09b7 >92185db5b6b525568be585a117ed0e8607841d5080130c78e74e60d99f87488c4dd54fdca >66b830b64ddfda088ec8ec0b6e79e3f76ecf9e7b6051c4921d843e156c2b921a322b7164d >d6011758e >470993ffdd833478f1e7df669b0d293da1a0f11e70a260425fd213179621693ddb30e2660 >880b646bbc993b99163f166ac48c3aa538d05cad7a2a99228a1f0e3b3659d94658e852611 >d800cb76b >be5b2ed8aa880b1d171660f81163be048edb1c222e74a9b02063de08c0383c445ce8b2c21 >2f63e09944cca413d1e1a9f536101afeef905c3252e5eb1b0e4bbf47c3863c4851e29ac9f >719131c91 >8f28ae638527d73091952c73ac0c51faf702d733208984f0ed3b898318c91f311e958e0e0 >83934e4ab6c20db7c4702760c80b4bf2e1f5da81f00125d27e41dd89c71607bceff5ca5ab >36b6c9cd8 >4c2285e5887866a002588cd4e439d61bb98a2503de3b4a6c26d276dbcf0b7601514e289e0 >f38a40971a04bce805c8215f3d808b199a834e5d97b96e91fb66cf707a28500e87cc2e246 >efee61d0a >9289682517b1fed400bbd4589217c3de8f347b917fcc37390c870b10e1e9f8a1028162234 >93960447459a17a7470e006715695fe7382fe30f8aa72f1e5264205e383524620479d17fe >4a4050f41 >bc5d4c0768685e463c7d09932626c819e0874237650ce363220f7c7b7c344dafc2a23d8c4 >b8a03ecc0419f7087eeeded1542c243db1d420b3df15cf5fa7a916b00a007765990763d40 >3f242d333 >7c70dfe78752bbafe701f8d844880e5f70f3d928021d4ee6aa07e93254edb7f2431d8fbed >fdf25ba607067c58e04cb6f3412203365eb811c6a8b2d18465b1f46e9526eb87dbac166ba >3703d82ea >eb5fddd1d1516a2232ba6a8d54a529c675a3d5d0500965b05a3c91c2da7c5fa59ea20ce0b >a3402b9f43665c6a6ae1c1dd5608a71dda05f4b959494e829d8657aa4c9dabca5516f34eb >a892dbd15 >322494019da6ccacdbda61be4433041aef5c8f7329daeb050a7435fe4d6b958f05d0e7e05 >b877432b65dbd8bcb8103d259204546589aea9f9fb8aef1b34dfd3eab009725d007c03ef0 >5ed7769b5 >6b50d7a7ef66987540a05f761ae19cafecba0b3f259809280365bb677db7f26b93cc7c2d3 >601ae17b717e5688172b2f3b3d21755e16a5845c39402d156139cf3952bd4d9f021d10ca3 >f454e96d6 >5d51584d7269959d804ba2e4e6b494b4f4f4f6bc94c6929aed251d6d6e8b496b48138d49a >7af8906826a0748b8b962c25bd9951a5a44213e85a5d1f7955509f0baba131aa760c7a636 >4ce053380 >fa0190366751ae9a8820a826ada860d9ed52ede0b4969a6b97af889e737184696a4d4d920 >8580d2010b17684b41aabeea8ae48de155999c552c99548692dafc8bb8588a8928ba4f5d6 >22e42587e >b0ea4f55623642527a635e3bff7b9a0052de87fa4ff008005e0ee675f1061000000004945 >4e44ae426082 >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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
