Opening Blob Data stored in Sqlite as a File with Tcl/Tk

Question

I am using the following tcl code to store a file from my deskstop into a Sqlite database as blob data ($fileText is a path to a text file):

sqlite3 db Docs.db
set fileID [open $fileText RDONLY]
fconfigure $fileID -translation binary
set content [read $fileID]
close $fileID
db eval {insert into Document (Doc) VALUES ($content)}
db close

I have found many resources on how to open the blob data to read and write to it, but I cannot find any resources on opening the blob data as a file. For example, if $fileText was a pdf, how would I open it, from Sqlite, as a pdf?

Donal Fellows · Accepted Answer · 2010-05-17 20:47:03Z

2

When you say “open as a PDF”, I assume that you mean that you want some external program to see the data as a file? The only ways to do that are either:

Do some clever shenanigans with user-mode filesystems on Linux (or the equivalent on your OS) so that the database can be actually mounted, or
Copy the data from the database into a temporary file with the right name (hint: keep that as a separate column in that table).

There's also presenting it all as a webserver, but that's really the second with a browser in the mix; the data is still copied.

On the other hand, if all you want to do is have the data as a stream that you can read or write from Tcl, the sqlite3 package has what you need:

dbcmd incrblob ?-readonly? ?db? table column rowid

Which returns a standard channel handle (though not one backed up by an OS handle, so be careful if using as a redirection with exec).

[EDIT]: Here's how to getthe data out (replace ... with a clause to get the right row, of course):

# Open the DB
sqlite3 db Docs.db

# Open the file to write to
set fileID [open $fileText w]
fconfigure $fileID -translation binary

# Write the BLOB
db eval {SELECT Doc FROM Document WHERE ... LIMIT 1} row {
    puts -nonewline $fileID $row(Doc)
}

# We're done!
close $fileID
db close

Don't worry about the size of the BLOB; the Tcl sqlite3 package passes it around efficiently. If you're still concerned, here's the other way (again, you'll need to replace ... appropriately):

# Open the DB
sqlite3 db Docs.db

# Open the file to write to
set fileOut [open $fileText w]
fconfigure $fileOut -translation binary

# Get the BLOB as a (read-only) channel
set fdBlob [db incrblob -readonly Document Doc ...]
fconfigure $fdBlob -translation binary

# Do the copy 
fcopy $fileOut $fdBlob

# We're done!
close $fdBlob
close $fileOut
db close

edited May 17, 2010 at 20:47

answered May 17, 2010 at 19:28

Donal Fellows

139k19 gold badges161 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DFM Over a year ago

Hi Donald - thanks for your feedback. I was hoping to develop a tcl tk frontend that would allow an end user to upload a pdf file (to sqlite), and then be able to download the pdf for usage, like a standard file database. The upload part seems simple enough, using binary/blob data. I might be doing something wrong. Is opening a i/o channel my only option with blob data or can the data be opened as a file, similar to opening a file from a web driven Db?

Donal Fellows Over a year ago

@DFM: SQLite keeps the BLOB in the database, so opening it as a file directly isn't going to work (well, not without egregious hacks as in point 1 of my answer). Copy it out. NB: copying out is only a few lines of code.

user175328 Over a year ago

Hi Donald - Copying the data out works well. As a test, instead of puts..., I copied it out to a text box widget, since I am not using a shell. I found that copying out pdf or Word doc blob data gives me unrecognized characters, most likely because of the format. How would I compile this data back into a pdf or Word doc?

Collectives™ on Stack Overflow

Opening Blob Data stored in Sqlite as a File with Tcl/Tk

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related