AW: [odb-users] Storing files in an oracle database
Marcel Nehring
mne at qosmotec.com
Fri Jan 22 10:17:30 EST 2016
Hi Boris,
> It is not clear how widely used this type is (you are the first person interested in using BFILE with ODB) as well as how difficult it will be to support this dual mapping. In fact, we did something similar for SQLite not long ago (Incremental BLOB/TEXT I/O support) and it turned out to be tricky, to put it mildly.
The hard problems are the interesting ones, aren't they? ;-)
> Here is how you could do it (an outline; you will also need the db map pragma)
I had to modify your example code in a few ways, but it seems to be working now. INSERT, SELECT, UPDATE all succeed and the results look good.
* In the virtual member declaration I had to replace vector<char> with a corresponding typedef
* Accessing the filename member via a special get method didn't work since ODB requires a method returning by const-ref. I ended up having the member of type vector<char> with get/set methods converting it to string. Not the prettiest solution but at least this implementation detail is hidden.
So my implementation looks similar to this:
struct object
{
using Binary = std::vector<char>;
#pragma db map type("BFILE") as("BLOB") to("blob_to_bfile((?))") from("bfile_to_blob((?))")
#pragma db member(filename) virtual(std::string)
#pragma db transient
vector<char> m_filename;
#pragma db member(file) virtual(Binary) type("BFILE") get(m_filename) set(m_file)
#pragma db transient
vector<char> m_file;
};
The corresponding functions in my Oracle database are as follows
-- Found via Google at http://technologydribble.info/2009/08/18/loading-a-file-into-a-blob-object-in-oracle/
CREATE OR REPLACE FUNCTION ODB.bfile_to_blob(file BFILE) RETURN BLOB AS
dest_loc BLOB := empty_blob();
src_loc BFILE := file;
BEGIN
-- Open source binary file from OS
DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);
-- Create temporary LOB object
DBMS_LOB.CREATETEMPORARY(
lob_loc => dest_loc
, cache => true
, dur => dbms_lob.session
);
-- Open temporary lob
DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
-- Load binary file into temporary LOB
DBMS_LOB.LOADFROMFILE(
dest_lob => dest_loc
, src_lob => src_loc
, amount => DBMS_LOB.getLength(src_loc));
-- Close lob objects
DBMS_LOB.CLOSE(dest_loc);
DBMS_LOB.CLOSE(src_loc);
-- Return temporary LOB object
RETURN dest_loc;
END bfile_to_blob;
CREATE OR REPLACE FUNCTION ODB.blob_to_bfile(filename BLOB) RETURN BFILE AS
src_loc BFILE := BFILENAME('ODB_DATA_DIR', UTL_RAW.CAST_TO_VARCHAR2(filename));
BEGIN
RETURN src_loc;
END blob_to_bfile;
> with the upcoming version of ODB (or a pre-release ;-)), you will be able to simply map bfile to, say, vector<char> by providing a pair of conversion functions (very similar to db map except for C++ types rather than database types)
This feature sounds interesting, looking forward to the release :-)
Thanks again for your kind support.
Regards,
Marcel
More information about the odb-users
mailing list