SQL insert and update - how to?

[muellerto]

I want to update a database table record or insert a new one. This is not as easy as it sounds. My first approach was as follows:

Code: Select all

try
{
   *pSession << "UPDATE ...", now;
}
catch (...)
{
   *pSession << "INSERT INTO ...", now;
}


But UPDATE doesn't throw an exception when the record is not found. It just updates 0 records. OK.

Then I tried to obtain the number of affected records by creating a Statement object and calling the execute()-method. But in my case on MySQL the result of Statement::execute() was always 0 when the statement was UPDATE, even if the UPDATE was indeed executed and affected a row. This seems to be a bug. On SQLite this worked always as expected.

So I changed the order of the statements - first INSERT then UPDATE:

Code: Select all

try
{
   *pSession << "INSERT INTO ...", now;
}
catch (...)
{
   *pSession << "UPDATE ...", now;
}


On SQLite this works always fine. On MySQL it is as follows: when the record already exists the INSERT throws the expected exception. But then in many cases also the UPDATE will not be executed successfully. It does just nothing, I think because the session is somehow in an error state or something like that. I found two work arounds for that:

• make a (superfluous) transaction around all that, the commit will then either INSERT or UPDATE safely
• avoid "now", use a Statement object instead and execute() this, this will also be safe

But what's the difference between "now" and Statement::execute()?

And finally, what would be a recommended, portable algorithm to implement this UPDATE/INSERT thing?

POCO 1.5.1 on Arch Linux x86_64