ORM: What is the correct way to bulk add a lot of rows?

[asmwarrior]

Hi, I would like to build operation on a table by adding many rows to a table, here is a simple code which looks OK

            // try to add some "person" to the table
            // Assume that you have a vector of rows to insert
            std::vector<person> rows = {
                {0, "John", 30},
                {0, "Jane", 25},
                {0, "Bob", 40}
            };

            // Create a statement to insert a row into the table
            person p;
            soci::statement stmt = (sql.prepare << "INSERT INTO testtable (id, name, age) VALUES (:id, :name, :age)", soci::use(p));

            for (int i=0; i<rows.size(); i++)
            {
                // Bind the placeholders to the values of each row
                p = rows[i];

                // Execute the statement to insert the rows into the table
                stmt.execute(true);
            }

The person class has such definition:

class person
{
public:
    person()
    {
        std::cout << "person constructor" << std::endl;
    }

    person(int id_, std::string name_, int age_)
    {
        id = id_;
        name = name_;
        age = age_;
    }

    person(const person & old)
    {
        id = old.id;
        name = old.name;
        age = old.age;
        std::cout << "copy constructor" << std::endl;
    }

    ~person()
    {
        std::cout << "destroy id = " << id << std::endl;
    }
public:
    int id;
    std::string name;
    int age;
};

namespace soci
{
    template<>
    struct type_conversion<person>
    {

        typedef values base_type;

        static void from_base(const values& v, indicator ind, person& row)
        {
            row.id =   v.get<int>("id", -1);
            row.name = v.get<std::string>("name", "");
            row.age =  v.get<int>("age", 0);

        }

        static void to_base(const person& row, values& v, indicator& ind)
        {
            v.set("id",   row.id);
            v.set("name", row.name);
            v.set("age",  row.age);

            ind = i_ok;
        }
    };
}

My question is: is my code the best way to bulk operation of adding several rows from a vector?

Is it possible to avoid the for loop, so that in the statement, I can directly add(use) the whole rows, such as:

soci::statement stmt = (sql.prepare << "INSERT INTO testtable (id, name, age) VALUES (:id, :name, :age)", soci::use(rows));

Any ideas?

Thanks.

[zann1x]

Is it possible to avoid the for loop, so that in the statement, I can directly add(use) the whole rows, such as:

soci::statement stmt = (sql.prepare << "INSERT INTO testtable (id, name, age) VALUES (:id, :name, :age)", soci::use(rows));

No, that's not possible because bulk operations with custom types are currently not supported.

[asmwarrior]

No, that's not possible because bulk operations with custom types are currently not supported.

OK, thanks for the help. So, my current implementation is the correct way(using the for loop) to add the custom types.

[asmwarrior]

From the document, I see some example has extra functions:

https://github.com/SOCI/soci/blob/924d990f8f4b253e9f7897c92dccfd4c814f569e/docs/statements.md?plain=1#L224-L268

such as

        // first insert
        int a0 = 0;

        // update reference
        stmt.exchange(soci::use(a0));

        stmt.define_and_bind();
        stmt.execute(true);
        stmt.bind_clean_up();

So, do I need to adding the define_and_bind and bind_clean_up in my for loop in my first post in this ticket?

[zann1x]

No, that's not possible because bulk operations with custom types are currently not supported.

OK, thanks for the help. So, my current implementation is the correct way(using the for loop) to add the custom types.

Yes, it is.

From the document, I see some example has extra functions:

https://github.com/SOCI/soci/blob/924d990f8f4b253e9f7897c92dccfd4c814f569e/docs/statements.md?plain=1#L224-L268

such as

        // first insert
        int a0 = 0;

        // update reference
        stmt.exchange(soci::use(a0));

        stmt.define_and_bind();
        stmt.execute(true);
        stmt.bind_clean_up();

So, do I need to adding the define_and_bind and bind_clean_up in my for loop in my first post in this ticket?

As far as I can see, your initial code looks fine already. The example you're referring to is used in cases where the variable to be bound isn't necessarily available during statement creation. This isn't the case in your example, so there's no need to explicitly call define_and_bind and bind_clean_up.

[asmwarrior]

No, that's not possible because bulk operations with custom types are currently not supported.

OK, thanks for the help. So, my current implementation is the correct way(using the for loop) to add the custom types.

Yes, it is.

Thanks, and sorry a bit late response.

As far as I can see, your initial code looks fine already. The example you're referring to is used in cases where the variable to be bound isn't necessarily available during statement creation. This isn't the case in your example, so there's no need to explicitly call define_and_bind and bind_clean_up.

OK, thanks.

About the bulk operations for custom types, I see a pull request here: add support of bulk operations for ORM in ORACLE and SQLite backends #1053

Will this feature be discussed and merged in the future?