Chapter 5. Inserting and fetching blobs

Index

Inserting blobs
Retrieving blobs

Most databases implement a custom blob datatype for defining a table field that contains a large text or a binary chunk, or a blob. Inserting a blob is done by defining an beginning and an ending iterator for a char sequence that defines a text blob, or an unsigned char sequence that defines a binary blob. Similarly, retrieving a blob involves defining an output iterator, which gets iterated over the contents of the blob field.

It's possible that some database drivers may require input iterators to be, at least, forward iterators, and will have poor performance unless they are random access iterators.

Inserting blobs

#include <x/sql/insertblob.H>

int memo_id;

auto stmt=conn->prepare("INSERT INTO memos(memo_id, memo_text) VALUES(?, ?)");

stmt->execute(0, x::sql::insertblob
    ::create(x::fdinputiter(x::fd::open("memo.txt", O_RDONLY)),
             x::fdinputiter());

x::sql::insertblob passes a blob parameter to execute(). It's a reference-counted object whose create() takes a pair of iterators for a sequence that defines the contents of a blob. The resulting x::sql::insertblob can be used in place of any parameter to execute().

The iterators must iterate over chars or unsigned chars. This is an indicator to the database driver whether the blob contains character or binary data. The semantics of character and binary blobs are driver-dependent.

auto fd=x::fd::open("blob.txt", O_RDONLY);

int memo_id;

std::pair<x::sql::insertblob, x::sql::bitflag> memo_blob=std::make_pair(
    x::sql::insertblob::create(x::fdinputiter(fd), x::fdinputiter()), 0);

std::pair<x::sql::insertblob, x::sql::bitflag> memo_null_blob=std::make_pair(
    x::sql::insertblob::create(), 1);

conn->execute("INSERT INTO memos(memo_id, memo_txt) VALUES (?, ?)",
    memo_id,
    memo_blob);

Similar to regular execute() parameters, a std::pair of a x::sql:insertblob and an x::sql::bitflag specifies a possibly NULL value for the parameter. Use create() with no parameters to create an x::sql:insertblob for an empty character sequence, and set the x::sql::bitflag in order to specify a NULL value.

x::sql:insertblob also works when it's passed indirectly via a std::vector to execute(), like any other parameter. Database drivers may also support multiple blob parameters in a single execute(). Database drivers may also support blobs in execute_vector(), which uses x::sql:insertblob the same way as any other parameter vector:

int upload_files(int first_id, const std::vector<std::string> &filenames)
{
    std::vector<x::sql::bitflag> status;

    std::vector<int> ids;
    std::pair<std::vector<x::sql::insertblob>
              std::vector<x::sql::bitflag> memos;

    status.resize(filenames.size());
    ids.reserve(filenames.size());
    flags.first.reserve(filenames.size());
    flags.second.reserve(filenames.size());

    for (const auto &filename:filenames)
    {
        ids.push_back(first_id++);

        int isnull=filename.size() == 0;

        memos.second.push_back(isnull);
        memos.first.push_back(isnull ? x::insertblob::create():
                              x::insertblob::create(x::fd::inputiter(x::fd::open(filename, O_RDONLY)),
                                                    x::fd::inputiter()));
    }

    conn->execute_vector("INSERT INTO memos(memo_id, memo_txt) VALUES (?, ?)",
        status, ids, memos);

    return first_id;
}