Issue with multi-row insert types

[retsimx]

Hi there,

(Using v0.60) I'm having trouble trying to figure out why I'm having issues using insert_into().columns().values.add(). I'm trying to change an insert in a loop in to a multi-row insert query as described in https://github.com/rbock/sqlpp11/blob/develop/docs/Insert.md

This old code block, compiles and runs totally fine.

db(
    insert_into(fileDownloadTable)
        .set(
            fileDownloadTable.user = (uint32_t) authResult->payload()["userId"],
            fileDownloadTable.jobId = (uint32_t) jobId,
            fileDownloadTable.uuid = uuid,
            fileDownloadTable.path = std::string(filePath),
            fileDownloadTable.timestamp = std::chrono::system_clock::now()
        )
    )
)

However, this new approach, does not work as expected. It simply doesn't compile.

auto insert_query = insert_into(fileDownloadTable).columns(
    fileDownloadTable.user,
    fileDownloadTable.jobId,
    fileDownloadTable.uuid,
    fileDownloadTable.path,
    fileDownloadTable.timestamp
);

...

insert_query.values.add(
    fileDownloadTable.user = (uint32_t) authResult->payload()["userId"],
    fileDownloadTable.jobId = (uint32_t) jobId,
    fileDownloadTable.uuid = uuid,
    fileDownloadTable.path = std::string(path),
    fileDownloadTable.timestamp = std::chrono::system_clock::now()
);

Initially, user and jobId don't compile using the (uint32_t) type, but converting them to (int) fixes them. The real problem I'm unable to solve is the timestamp field - I cannot for the life of me figure out how to cast from std::chrono::system_clock::now() to the (mysql datetime) type expected by fileDownloadTable.timestamp. It seems strange to me that insert_into().set() behaves differently to insert_into().columns().values.add().

If there is some kind of workaround for now for the timestamp field, I could live with that.

Cheers

[rbock]

I don't have an answer yet, but here's how you can dig into it:

insert_query.values.add tries to emplace into
insert_query.values._data._values (this is a vector of tuples)

By looking at the type of that vector, you will see the type of the tuple field. That should help with the analysis.

Hope that helps.

I'll try to find time on the weekend.

Cheers,

Roland

[rbock]

Hi,

Found the reason. The multi-insert is more picky with respect to the data types. It is expecting a time_point in microseconds resolution and your clock is likely to yield it in nanoseconds.

You'll have to do something like

fileDownloadTable.timestamp = 
      std::chrono::time_point_cast<std::chrono::microseconds>(std::chrono::system_clock::now())

[rbock]

Added an example and hinted to that in the docs.

[retsimx]

Awesome! Thank you @rbock!