On 19/10/2011 07:58, Stefan Rohlfing wrote:
Thanks for this information.
I am working on a program to store csv data into a database. As
several csv files can be handled at the same time, I want to achieve
that in the case of an error there will be a complete rollback of all
data already inserted (either all of the data form all files is
inserted or none).
Using transactions would be the best choice to achieve this behavior,
but as the program is supposed to work with different databases,
including MySQL, and as I have no influence on the user's database
setup, I am afraid I have to come up with a different solution.
Therefore I now consider to first run a test input (followed by a
delete) on all data and only proceed with a final insert if no
error occurred with any of the data during the "simulation". However,
as this doubles the workload I would be glad if anyone could point me
to a more elegant solution.
Stefan
Even MySQL has transactions, newer versions have it enabled by default
(Innodb).
I'm not sure how you would otherwise guarantee the all or nothing
atomicity you require. What if your program crashes or you lose the
database/network connection for some reason? In the case of transactions
even those failures will see the transaction rolled back.
If you are the sole inserter into the database you may be able to use an
autoincrement primary key, make a note of the highest ID before you
start and in the event of an error, delete anything above that ID. You
would have to make a note of the highest ID and store it outside your
program (disk file?) so that a recovery can still be made if your
program or the PC crashes.
Otherwise you may have to have minimum requirement that your target DB
supports transactions. Most do.
Cheers,
Gary.
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
