AW: [odb-users] Storing files in an oracle database

[Marcel Nehring]

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