[odb-users] Storing files in an oracle database

[Boris Kolpackov]

Hi Marcel,

Marcel Nehring <mne at qosmotec.com> writes:

> as always your thoughts were very helpful and brought us closer to 
> a working solution. Thanks for that!

Glad I could help. If, at the end, you could post your functions that
implement the mapping for BFILE, that would be much appreciated.


> Would be cool to see that in a future version of ODB.

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.

If you would like to investigate what it would take to add this support
to ODB, I would be happy to help/guide.


> I wasn't aware of the fact that it is possible to use custom functions with
> ODB. I tried that and got it working in principle. An initial insert of a new
> row and reading that row back again works. However, the problem are
> updates. Once the row is loaded and the corresponding class member contains
> binary data ODB uses these binary data in the update clause. That is why I
> tried to separate filename and binary data into two separate members. I would
> then need to use the filename member in INSERT and UPDATE queries and the
> binary member for SELECT. Since my tries with the get-pragma did not work you
> mentioned one could achieve this with sections. Unfortunately I don't see
> how, could you elaborate on this?

Here is how you could do it (an outline; you will also need the db map
pragma):

struct object
{
  string file_name;
  vector<char> file_data;

  #pragma db member(file_name) transient
  #pragma db member(file_data) transient

  #pragma db member(file) virtual(vector<char>) type("BFILE") \
    get(to_bfile) set(file_data)

  vector<char>
  to_bfile () const
  {
    return vector<char> (file_name.c_str (), 
                         file_name.c_str () + file_name.size ());
  }
};

Once this is working, you could create an encapsulated BFILE mapping:

struct bfile
{
  string file_name;
  vector<char> file_data;
};

#pragma db value(bfile) type("BFILE")

To complete this, you would either need to provide the value_traits
specialization that maps bfile to BLOB or, 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).

Boris