use strict;
use warnings;

use DBI;

my $dbh = DBI->connect( "dbi:SQLite:dbname=test.sqlite", "", "", { Rai
+seError => 1 } );
$dbh->do("CREATE TABLE aaa (num INTEGER, name TEXT)");
$dbh->do("CREATE TABLE bbb (num INTEGER, name TEXT)");
my $i = 1;
for (qw(alice bob carol)) {
    $dbh->do( "INSERT INTO aaa VALUES (?, ?)", {}, $i++, $_ );
}

my $sth = $dbh->prepare("SELECT * FROM aaa");
$sth->execute;
while ( my @row = $sth->fetchrow_array ) {
    $dbh->do( "INSERT INTO bbb VALUES (?, ?)", {}, @row );
}
[download]

and although it may work and is only an example it is also a prime example of doing something in Perl that could be better done in the database - insert into bbb (num, name) select num, name from aaa. I sincerely hope the OP is not doing something like this.

When you get a Segmentation Fault in something like SQLite that typically means that either you have a broken build or you discovered a bug. However, you having discovered a bug by accident is quite unlikely. So the first thing you should do is reinstall/update DBI and DBD::SQLite.

In my experience, SQLite is rock solid. It has it's drawbacks compared to other RDBMS, but stability is certainly not one of them.

Obviously you've got a problem if your getting a segfault and it sounds from your description that it is happening when you have multiple active statements. I have yet to see a good example of doing updates/deletes/inserts in a loop on a result-set that is not better written another way. If it wasn't SQLite then even if you cannot easily do them in one go in plain SQL you probably could in a procedure.

You should perhaps consider producing a small self contained example producing the segfault so it can be reported for SQLite but in any case I'd try very hard to do your updates/deletes/inserts in one piece of SQL if you can and avoid multiple active statements.

Maybe your segfaults are coming from an improper installation or upgrade.... if you can, compile the latest sqlite libs and perl module and see if you get install errors.

... you can swap between database engines fairly easily, without changing much in your code ...

That made me chuckle. It's true in theory but a real huge pain in practice -- the database vendors have done their fair share of ensuring lock-in with proprietary extensions to SQL (and from what I remember, the DDL portions of SQL were never standardized - but it's been a while since I looked into it).

-derby

...which is why some of us stick to the SQL standard wherever possible, only gritting our teeth and cursing the database vendors, lo, unto the seventh generation, in those rare cases in which we have no choice but to blight ourselves and our code with their proprietary extensions.

And then there are those who use ORMs to abstract it all away.

Perhaps I just don't get heavy enough into the database side, but I find it pretty easy to write only cross-platform SQL.