Overview
Features
Download
Documentation
Community
Add-Ons & Services

SQL insert and update - how to?

Please post support and help requests here.

SQL insert and update - how to?

Postby muellerto » 17 Jan 2013, 10:51

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
muellerto
 
Posts: 23
Joined: 13 May 2009, 18:06

Return to Support

Who is online

Users browsing this forum: No registered users and 1 guest

cron