Re: [sqlite] Question on Blobs
Dennis Cote wrote: > If you are using the command line sqlite3 program rather than the > library, then all your input must be text that can appear on the command > line or be redirected from stdin. Handling binary data this way will be > difficult. Thanks Dennis, You cleared up allot for me. I think for my (simple) purposes, it's more trouble than it's worth. Plus, I wouldn't be able to search for a word or phrase without lots of trouble decoding first. Mostly, I would just like to save the formating of text when I save a record, the view it again in an editor (as plain text). Some data I am encrypting with gpg, but I am saving that as files outside the database. Fred ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Blobs
Fred J. Stephens wrote: > Thanks John & Dennis; > Looks like I am getting ahead of myself here. I'm just doing a simple > PIM app as a BASH script that uses SQLite to store data. Probably I > can't do this in a script as you could in C. > > I find the formating of the text from a file is not saved if I read it > and insert it into a table, so I was hoping there was a way to save the > binary file and thus preserve all the formatting. Also it would let me > save images etc. in the database. > > When I get back to learning Python, maybe one of the SQLite wrappers > will help me do this more easily. My present program is really just a > way to get started until I move to Python. Also, it is good practice in > BASH scripting, SQL and basic usage of SQLite. > Fred, If you are using the command line sqlite3 program rather than the library, then all your input must be text that can appear on the command line or be redirected from stdin. Handling binary data this way will be difficult. They only idea I have is to use another command to encode the binary file as pure text, say by converting each byte into a two character hex encoding of that byte, and then using .import to load the encoded text file into a table. From there other SQL commands can move the data to other tables etc. To recover the file your bash scripts would have to export the encoded file using .output and a select statement, then run another command to decode the file back to its binary representation. The encode/decode programs would be quite easy to write in C or Python, or they may already exist. One disadvantage of this scheme is that the encoded file will be twice the size of the original. This could be mitigated for text files by compressing them before encoding, and decompressing them after decoding, since text files typically compress very well. This wouldn't work (i.e. won't stop the file doubling) for files that hold compressed data such as jpeg image data. Any of the command line file compressors, like gzip and bzip, should work fine. Note the encoding scheme must not split a file into multiple lines (i.e the encoded format can't have any embedded linefeeds), since the .import command would split the file into multiple records. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Blobs
Thanks John & Dennis; Looks like I am getting ahead of myself here. I'm just doing a simple PIM app as a BASH script that uses SQLite to store data. Probably I can't do this in a script as you could in C. I find the formating of the text from a file is not saved if I read it and insert it into a table, so I was hoping there was a way to save the binary file and thus preserve all the formatting. Also it would let me save images etc. in the database. When I get back to learning Python, maybe one of the SQLite wrappers will help me do this more easily. My present program is really just a way to get started until I move to Python. Also, it is good practice in BASH scripting, SQL and basic usage of SQLite. Thanks for your information. Fred ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Blobs
Just get a pointer to the data in the file and the number of bytes and use the sqlite API call to transfer it into the DB. You can get the pointer by either reading the file into local memory or by mmap'ing it. Also look at the API calls which let you process a blob in chunks. A BLOB is called a Binary Large OBject because it accepts anything. Fred J. Stephens wrote: > Mike McGonagle wrote: >> Hello all, >> I was hoping that someone might share some tips on working with Blobs? > I am also curious about this. > For instance, how can I store a file in a table? > Not read the file and store the text, but the binary file itself? > Thanks. > ___ > 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
Re: [sqlite] Question on Blobs
On Feb 27, 2008, at 7:35 PM, Mike McGonagle wrote: > Wow, Peter, didn't expect that anyone would go to the trouble of > writing a > program on the spot I didn't. :-) That was just a snippet of something I wrote for myself when I first started playing with SQLite. > Just curious, but from those few things that I have seen, it > appears that > you can only put a Blob into the DB if it is already on disc, > right? All > three examples I have seen passed the filename to the database, and > one of > them was working within a server context, so I wasn't sure how the > local > filename would be of any use to a machine that is in another part > of the > room (or anywhere else...). The insert statement has two parameters. One of them happens to be the filename and is bound as a text parameter. That's just there as a means to identify the images and allow for wildcard searches. The actual file data is loaded into memory by the code, not SQLite itself. You just bind the binary blob to the appropriate parameter with the address of the loaded data. Whether the image data was loaded from a file or fetched over a TCP connection doesn't matter, the method of storing the data is the same. Peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Blobs
Rather than doing malloc you can mmap the file and then copy it into the
blob.
Peter A. Friend wrote:
> On Feb 27, 2008, at 4:48 PM, Mike McGonagle wrote:
>
>> Hello all,
>> I was hoping that someone might share some tips on working with
>> Blobs? I
>> would like to be able to store some images and sound files in a
>> database,
>> but never having dealt with them, I am kind of at a loss for some
>> examples.
>> I have looked on the web, and there are few examples that were of use.
>
> Well, I wrote a quick and dirty program for stuffing image files into
> a database. You just provide a directory and it stats() each file,
> allocates enough space for the image data, then loads it from disk.
> Sql statement is something like:
>
> char* sql = "insert into i (name, data) values (?, ?);";
>
> Of course if your images are huge this method coud be problematic. I
> believe SQLite supports an incremental way to do this but I haven't
> looked at those calls yet.
>
> while ( (dentry = readdir(dir)) != '\0') {
>if (dentry->d_name[0] == '.')
> continue;
>
>if (fd != -1) {
> close(fd);
> fd = -1;
>}
>
>if (data != '\0') {
> free(data);
> data = '\0';
>}
>
>snprintf(fname, sizeof(fname), "%s/%s", newdir, dentry->d_name);
>stat(fname, );
>
>if ( (data = malloc(sb.st_size)) == '\0') {
> fprintf(stderr, "malloc() failed\n");
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
>}
>
>if ( (fd = open(fname, O_RDONLY, )) == -1) {
> fprintf(stderr, "open() failed\n");
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
>}
>
>if ( (retval = read(fd, data, sb.st_size)) == -1) {
> fprintf(stderr, "read() failed\n");
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
>}
>
>if (retval != sb.st_size) {
> fprintf(stderr, "read() failed\n");
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
>}
>
>rc = sqlite3_bind_text(stmt, 1, dentry->d_name, dentry->d_namlen,
> SQLITE_STATIC);
>
>if (rc != SQLITE_OK) {
> fprintf(stderr, "sqlite3_bind_text() %s\n", sqlite3_errmsg
> (db));
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
>}
>
>rc = sqlite3_bind_blob(stmt, 2, data, sb.st_size, SQLITE_STATIC);
>
>if (rc != SQLITE_OK) {
> fprintf(stderr, "sqlite3_bind_blob() %s\n", sqlite3_errmsg
> (db));
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
>}
>
>rc = sqlite3_step(stmt);
>
>if (rc != SQLITE_DONE) {
> fprintf(stderr, "sqlite3_step() %s\n", sqlite3_errmsg(db));
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
>}
>
>sqlite3_reset(stmt);
> }
>
> ___
> 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
Re: [sqlite] Question on Blobs
Fred J. Stephens wrote: > For instance, how can I store a file in a table? > Not read the file and store the text, but the binary file itself? Fred, You can't do anything with the contents of a file until you read in into memory. To store a 1MB file in a database you need to decide if you will use the older complete blob API (i.e. sqlite3_bind_blob and sqlite3_column_blob) or the newer incremental I/O API routines sqlite3_blob_open, sqlite3_blob_write, and sqlite3_blob_close). The main difference is the size of the memory buffer needed to hold the data, and the number of times you call the routines. For a 1MB file you need a 1MB memory buffer to use the old API. You will read the entire file into the buffer and call the bind_blob function once to insert the data into the database. If you use the new API's you would first insert a 1MB zeroblob (i.e. 1M of zero data) using the sqlite3_bind_zeroblob function. Next you open the blob using sqlite3_open_blob. Then you could use a 10K buffer, and call the file read and write_blob functions 100 times in a loop to transfer the file into the database. Finally you close the blob. The newer APIs are more complicated to use in that you need to make more function calls to insert the blob, but they allow the application to use less memory, and they can handle data that is larger than what will fit in memory (i.e. it is the only way to insert a 100G file on a machine with only 2G of memory). HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Blobs
Hi Mike,
The data in this example happens to come from file, but that isn't
relevant. The line:
rc = sqlite3_bind_blob(stmt, 2, data, sb.st_size, SQLITE_STATIC);
is binding a chunk of data on the heap to the blob column and
inserting that into the database. Where this chunk of data comes from
isn't relevant. HTH.
Thursday, February 28, 2008, 2:35:27 PM, you wrote:
MM> Wow, Peter, didn't expect that anyone would go to the trouble of writing a
MM> program on the spot
MM> Just curious, but from those few things that I have seen, it appears that
MM> you can only put a Blob into the DB if it is already on disc, right? All
MM> three examples I have seen passed the filename to the database, and one of
MM> them was working within a server context, so I wasn't sure how the local
MM> filename would be of any use to a machine that is in another part of the
MM> room (or anywhere else...).
MM> Just so you understand what it is I am trying to do, I am working in a
MM> Multimedia programming environment (Pure Data), and I would like to be able
MM> to read and write some chunks of audio or video as needed. While Pure Data
MM> is a realtime environment, I am not expecting this to be responsive to work
MM> in realtime.
MM> Thanks again, I will study this to see if it tells me anything more...
MM> Mike
MM> On Wed, Feb 27, 2008 at 8:02 PM, Peter A. Friend <[EMAIL PROTECTED]>
MM> wrote:
>>
>> On Feb 27, 2008, at 4:48 PM, Mike McGonagle wrote:
>>
>> > Hello all,
>> > I was hoping that someone might share some tips on working with
>> > Blobs? I
>> > would like to be able to store some images and sound files in a
>> > database,
>> > but never having dealt with them, I am kind of at a loss for some
>> > examples.
>> > I have looked on the web, and there are few examples that were of use.
>>
>> Well, I wrote a quick and dirty program for stuffing image files into
>> a database. You just provide a directory and it stats() each file,
>> allocates enough space for the image data, then loads it from disk.
>> Sql statement is something like:
>>
>> char* sql = "insert into i (name, data) values (?, ?);";
>>
>> Of course if your images are huge this method coud be problematic. I
>> believe SQLite supports an incremental way to do this but I haven't
>> looked at those calls yet.
>>
>>while ( (dentry = readdir(dir)) != '\0') {
>> if (dentry->d_name[0] == '.')
>> continue;
>>
>> if (fd != -1) {
>> close(fd);
>> fd = -1;
>> }
>>
>> if (data != '\0') {
>> free(data);
>> data = '\0';
>> }
>>
>> snprintf(fname, sizeof(fname), "%s/%s", newdir, dentry->d_name);
>> stat(fname, );
>>
>> if ( (data = malloc(sb.st_size)) == '\0') {
>> fprintf(stderr, "malloc() failed\n");
>> sqlite3_finalize(stmt);
>> sqlite3_close(db);
>> exit(1);
>> }
>>
>> if ( (fd = open(fname, O_RDONLY, )) == -1) {
>> fprintf(stderr, "open() failed\n");
>> sqlite3_finalize(stmt);
>> sqlite3_close(db);
>> exit(1);
>> }
>>
>> if ( (retval = read(fd, data, sb.st_size)) == -1) {
>> fprintf(stderr, "read() failed\n");
>> sqlite3_finalize(stmt);
>> sqlite3_close(db);
>> exit(1);
>> }
>>
>> if (retval != sb.st_size) {
>> fprintf(stderr, "read() failed\n");
>> sqlite3_finalize(stmt);
>> sqlite3_close(db);
>> exit(1);
>> }
>>
>> rc = sqlite3_bind_text(stmt, 1, dentry->d_name, dentry->d_namlen,
>> SQLITE_STATIC);
>>
>> if (rc != SQLITE_OK) {
>> fprintf(stderr, "sqlite3_bind_text() %s\n", sqlite3_errmsg
>> (db));
>> sqlite3_finalize(stmt);
>> sqlite3_close(db);
>> exit(1);
>> }
>>
>> rc = sqlite3_bind_blob(stmt, 2, data, sb.st_size, SQLITE_STATIC);
>>
>> if (rc != SQLITE_OK) {
>> fprintf(stderr, "sqlite3_bind_blob() %s\n", sqlite3_errmsg
>> (db));
>> sqlite3_finalize(stmt);
>> sqlite3_close(db);
>> exit(1);
>> }
>>
>> rc = sqlite3_step(stmt);
>>
>> if (rc != SQLITE_DONE) {
>> fprintf(stderr, "sqlite3_step() %s\n", sqlite3_errmsg(db));
>> sqlite3_finalize(stmt);
>> sqlite3_close(db);
>> exit(1);
>> }
>>
>> sqlite3_reset(stmt);
>>}
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
--
Best regards,
Neville Franks, http://www.surfulater.com http://blog.surfulater.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Blobs
Mike McGonagle wrote: > Hello all, > I was hoping that someone might share some tips on working with Blobs? I am also curious about this. For instance, how can I store a file in a table? Not read the file and store the text, but the binary file itself? Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Blobs
Wow, Peter, didn't expect that anyone would go to the trouble of writing a
program on the spot
Just curious, but from those few things that I have seen, it appears that
you can only put a Blob into the DB if it is already on disc, right? All
three examples I have seen passed the filename to the database, and one of
them was working within a server context, so I wasn't sure how the local
filename would be of any use to a machine that is in another part of the
room (or anywhere else...).
Just so you understand what it is I am trying to do, I am working in a
Multimedia programming environment (Pure Data), and I would like to be able
to read and write some chunks of audio or video as needed. While Pure Data
is a realtime environment, I am not expecting this to be responsive to work
in realtime.
Thanks again, I will study this to see if it tells me anything more...
Mike
On Wed, Feb 27, 2008 at 8:02 PM, Peter A. Friend <[EMAIL PROTECTED]>
wrote:
>
> On Feb 27, 2008, at 4:48 PM, Mike McGonagle wrote:
>
> > Hello all,
> > I was hoping that someone might share some tips on working with
> > Blobs? I
> > would like to be able to store some images and sound files in a
> > database,
> > but never having dealt with them, I am kind of at a loss for some
> > examples.
> > I have looked on the web, and there are few examples that were of use.
>
> Well, I wrote a quick and dirty program for stuffing image files into
> a database. You just provide a directory and it stats() each file,
> allocates enough space for the image data, then loads it from disk.
> Sql statement is something like:
>
> char* sql = "insert into i (name, data) values (?, ?);";
>
> Of course if your images are huge this method coud be problematic. I
> believe SQLite supports an incremental way to do this but I haven't
> looked at those calls yet.
>
>while ( (dentry = readdir(dir)) != '\0') {
> if (dentry->d_name[0] == '.')
> continue;
>
> if (fd != -1) {
> close(fd);
> fd = -1;
> }
>
> if (data != '\0') {
> free(data);
> data = '\0';
> }
>
> snprintf(fname, sizeof(fname), "%s/%s", newdir, dentry->d_name);
> stat(fname, );
>
> if ( (data = malloc(sb.st_size)) == '\0') {
> fprintf(stderr, "malloc() failed\n");
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
> }
>
> if ( (fd = open(fname, O_RDONLY, )) == -1) {
> fprintf(stderr, "open() failed\n");
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
> }
>
> if ( (retval = read(fd, data, sb.st_size)) == -1) {
> fprintf(stderr, "read() failed\n");
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
> }
>
> if (retval != sb.st_size) {
> fprintf(stderr, "read() failed\n");
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
> }
>
> rc = sqlite3_bind_text(stmt, 1, dentry->d_name, dentry->d_namlen,
> SQLITE_STATIC);
>
> if (rc != SQLITE_OK) {
> fprintf(stderr, "sqlite3_bind_text() %s\n", sqlite3_errmsg
> (db));
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
> }
>
> rc = sqlite3_bind_blob(stmt, 2, data, sb.st_size, SQLITE_STATIC);
>
> if (rc != SQLITE_OK) {
> fprintf(stderr, "sqlite3_bind_blob() %s\n", sqlite3_errmsg
> (db));
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
> }
>
> rc = sqlite3_step(stmt);
>
> if (rc != SQLITE_DONE) {
> fprintf(stderr, "sqlite3_step() %s\n", sqlite3_errmsg(db));
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
> }
>
> sqlite3_reset(stmt);
>}
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Peace may sound simple—one beautiful word— but it requires everything we
have, every quality, every strength, every dream, every high ideal.
—Yehudi Menuhin (1916–1999), musician
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Blobs
"Mike McGonagle" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > My impression from what I have seen is that Blobs are dealt with in a > different way than other data types. Am I correct in assuming that > Blobs need to be created explicitly, and then you repeatedly call > 'sqlite3_blob_write()' to store them, and 'sqlite3_blob_read()' to > read them? That's a fairly recent API. The old way, that still happily works, is to use prepared parameterized statements (see sqlite3_prepare et al) together with sqlite3_bind_blob. You bind a BLOB just as you would any other parameter. > Also, can you have a select statement that retrieves regular data as > well as blob data in the same query? Yes. To extract BLOB field, use sqlite3_column_blob. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Blobs
On Feb 27, 2008, at 4:48 PM, Mike McGonagle wrote:
> Hello all,
> I was hoping that someone might share some tips on working with
> Blobs? I
> would like to be able to store some images and sound files in a
> database,
> but never having dealt with them, I am kind of at a loss for some
> examples.
> I have looked on the web, and there are few examples that were of use.
Well, I wrote a quick and dirty program for stuffing image files into
a database. You just provide a directory and it stats() each file,
allocates enough space for the image data, then loads it from disk.
Sql statement is something like:
char* sql = "insert into i (name, data) values (?, ?);";
Of course if your images are huge this method coud be problematic. I
believe SQLite supports an incremental way to do this but I haven't
looked at those calls yet.
while ( (dentry = readdir(dir)) != '\0') {
if (dentry->d_name[0] == '.')
continue;
if (fd != -1) {
close(fd);
fd = -1;
}
if (data != '\0') {
free(data);
data = '\0';
}
snprintf(fname, sizeof(fname), "%s/%s", newdir, dentry->d_name);
stat(fname, );
if ( (data = malloc(sb.st_size)) == '\0') {
fprintf(stderr, "malloc() failed\n");
sqlite3_finalize(stmt);
sqlite3_close(db);
exit(1);
}
if ( (fd = open(fname, O_RDONLY, )) == -1) {
fprintf(stderr, "open() failed\n");
sqlite3_finalize(stmt);
sqlite3_close(db);
exit(1);
}
if ( (retval = read(fd, data, sb.st_size)) == -1) {
fprintf(stderr, "read() failed\n");
sqlite3_finalize(stmt);
sqlite3_close(db);
exit(1);
}
if (retval != sb.st_size) {
fprintf(stderr, "read() failed\n");
sqlite3_finalize(stmt);
sqlite3_close(db);
exit(1);
}
rc = sqlite3_bind_text(stmt, 1, dentry->d_name, dentry->d_namlen,
SQLITE_STATIC);
if (rc != SQLITE_OK) {
fprintf(stderr, "sqlite3_bind_text() %s\n", sqlite3_errmsg
(db));
sqlite3_finalize(stmt);
sqlite3_close(db);
exit(1);
}
rc = sqlite3_bind_blob(stmt, 2, data, sb.st_size, SQLITE_STATIC);
if (rc != SQLITE_OK) {
fprintf(stderr, "sqlite3_bind_blob() %s\n", sqlite3_errmsg
(db));
sqlite3_finalize(stmt);
sqlite3_close(db);
exit(1);
}
rc = sqlite3_step(stmt);
if (rc != SQLITE_DONE) {
fprintf(stderr, "sqlite3_step() %s\n", sqlite3_errmsg(db));
sqlite3_finalize(stmt);
sqlite3_close(db);
exit(1);
}
sqlite3_reset(stmt);
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
