[sqlite] Working with blob

[Keith Medcalf]

> I don't know a way to write binary data to a file using the
> command-line tool.  Maybe there's one out there someone else knows of.

The SQL functions readfile and writefile mayhaps?

They have to be loaded as an extension (or compiled into) to lib/dll if you 
want them there.

[sqlite] Working with blob

[Simon Slavin]

On 29 Apr 2016, at 3:00am, James K. Lowden  wrote:

> I don't know a way to write binary data to a file using the
> command-line tool.  Maybe there's one out there someone else knows of.  

Nobody else knew of a way either, so one was specially introduced into a recent 
version.  On this page



look for 'blob'.

Simon.

[sqlite] Working with blob

[R Smith]

On 2016/04/28 8:27 PM, deltagamma1 at gmx.net wrote:
> What is a convenient way to store the path from a external blob (jpg,
> pdf) ?
> How can I retrieve this blob ?
> Is there a Frontend which opens the jpg directly from the listed
> table-content ?
>
> If I store the blob directly in the sqlite, is there a way to open the
> blob directly with the respective programm (e.g. irfanview or a pdf with
> acroread) ? I just discovered recently sqlitespeed, but do not know how
> to handle the problems described above.

A blob is just a set of bytes to the DB engine, it usually knows nothing 
about what those bytes represent. Some DB admin tools or GUIs may 
include ways to display the bytes (if they can detect that they are some 
standard format).  Some other programs, such as Irfanview, might happily 
display images from a DB, but it would likely need a very specific 
schema to go on.

Typically, you would be writing a program that uses the SQLite API to 
store blobs and retrieve them from some byte or stream you choose, into 
and from the formats you choose.

What I do is save both the blob and the file name (and perhaps the 
original path if you want to treat it as a backup) in separate columns 
(fields) and then, once I would like to open it, I just recreate the 
file (in whatever destination I would like, the TEMP folder is a good 
idea) with the correct file name, and then execute/open it - which 
should have your system open it with whatever program is registered to 
open such a file.

SQLite is not really concerned with what is in your blobs - just how to 
best store and retrieve them. Your program can do all kinds of magic 
with the blobs and SQLite will ensure you can save and load them fast 
and easy.

Best of luck,
Ryan

[sqlite] Working with blob

[James K. Lowden]

On Thu, 28 Apr 2016 20:27:17 +0200
"deltagamma1 at gmx.net"  wrote:

> If I store the blob directly in the sqlite, is there a way to open the
> blob directly with the respective programm (e.g. irfanview or a pdf
> with acroread) ? 

I have heard of a FUSE filesystem implemented with SQLite.  I don't
know if it exposes tables as files, or anything like that.  

If you're going to use an external program like acroread to view the
blob, you're pretty much forced to provide it with a filename, hence
you'll have to write your blob to a file, and give the application that
filename.  A few such programs might be willing to read from standard
input, in which case you could provide a pipe.  Or you might be able to
use a named pipe (mkfifo) instead of a temporary file.  

I don't know a way to write binary data to a file using the
command-line tool.  Maybe there's one out there someone else knows of.  

--jkl

[sqlite] Working with blob

[deltagam...@gmx.net]

What is a convenient way to store the path from a external blob (jpg,
pdf) ?
How can I retrieve this blob ?
Is there a Frontend which opens the jpg directly from the listed
table-content ?

If I store the blob directly in the sqlite, is there a way to open the
blob directly with the respective programm (e.g. irfanview or a pdf with
acroread) ? I just discovered recently sqlitespeed, but do not know how
to handle the problems described above.

[sqlite] Working with blob

[Darren Duncan]

I strongly recommend that you store a SHA-256 hash (or pick a better hash 
algorithm if there is one) of the file content as a blob in the database as the 
canonical way to identify the file.  This is much more reliable than doing it 
with the file's name, and any filename info can then be metadata without having 
to be a unique identifier.  This way you don't confuse 2 files with the same 
name but different content or vice-versa, and you gain other benefits. -- 
Darren 
Duncan

On 2016-04-28 3:43 PM, R Smith wrote:
> On 2016/04/28 8:27 PM, deltagamma1 at gmx.net wrote:
>> What is a convenient way to store the path from a external blob (jpg,
>> pdf) ?
>> How can I retrieve this blob ?
>> Is there a Frontend which opens the jpg directly from the listed
>> table-content ?
>>
>> If I store the blob directly in the sqlite, is there a way to open the
>> blob directly with the respective programm (e.g. irfanview or a pdf with
>> acroread) ? I just discovered recently sqlitespeed, but do not know how
>> to handle the problems described above.
>
> A blob is just a set of bytes to the DB engine, it usually knows nothing about
> what those bytes represent. Some DB admin tools or GUIs may include ways to
> display the bytes (if they can detect that they are some standard format).  
> Some
> other programs, such as Irfanview, might happily display images from a DB, but
> it would likely need a very specific schema to go on.
>
> Typically, you would be writing a program that uses the SQLite API to store
> blobs and retrieve them from some byte or stream you choose, into and from the
> formats you choose.
>
> What I do is save both the blob and the file name (and perhaps the original 
> path
> if you want to treat it as a backup) in separate columns (fields) and then, 
> once
> I would like to open it, I just recreate the file (in whatever destination I
> would like, the TEMP folder is a good idea) with the correct file name, and 
> then
> execute/open it - which should have your system open it with whatever program 
> is
> registered to open such a file.
>
> SQLite is not really concerned with what is in your blobs - just how to best
> store and retrieve them. Your program can do all kinds of magic with the blobs
> and SQLite will ensure you can save and load them fast and easy.
>
> Best of luck,
> Ryan
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Working with blob

[Stephen Chrzanowski]

Semantics and methodology of use aside, SQLite is the same as MySQL, MSSQL,
Access, etc, in that it is a tool to store and retrieve bytes in a
retrievable way via some kind of structured language.  None of mentioned
tools actually do any sort of presentation of said data.  "From the
factory", SQLite at best contains a command line tool to pull text out of
the database using a query language.  MySQL is pretty much the same as
well.  MS being MS, its administration tools work via a GUI in which what
is read from the database presents the data in whatever format it thinks it
should be done, however, the back end engine stores the data.

There are, of course, 3rd party tools across the board that willl do the
image processing for you, so long you give whatever is presenting the
information the proper information.  With MySQLAdmin, for instance, you can
store information that a particular field should be treated as an image,
WAV, etc.  I use SQLite Expert which optionally figures out what kind of
blob data it is on its own and presents it within the UI.

As for storing blob information that should be placed in a certain
location, IMO, store all blobs in a table with a 1:1 PK:FK relationship
outside of what data you want.  So for instance, something of the sort

tImageList
  ImageID:integer as PK
  ImagePath:char
  ImageDetails:char

tImageBlob
  ImageID:integer as FK to tImageList.ImageID
  ImageData:Blob



On Thu, Apr 28, 2016 at 2:27 PM, deltagamma1 at gmx.net 
wrote:

> What is a convenient way to store the path from a external blob (jpg,
> pdf) ?
> How can I retrieve this blob ?
> Is there a Frontend which opens the jpg directly from the listed
> table-content ?
>
> If I store the blob directly in the sqlite, is there a way to open the
> blob directly with the respective programm (e.g. irfanview or a pdf with
> acroread) ? I just discovered recently sqlitespeed, but do not know how
> to handle the problems described above.
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>