[EMAIL PROTECTED] wrote:
I am running into a situation that does not make sense.
I have allocated a flat file under the Operating System as follows (notice
that autocommit is off):
$dbh = DBI->connect('dbi:SQLite:' . $dbms_file , "", "",
{ RaiseError => 1,AutoCommit => 0 });
Then, I create multiple tables in a loop with with the sequence:
1) CREATE TABLE ....
2) $sql = SQL::Abstract->new;
3) ($sql,@bind) = $sql->insert($table,\%rec);
4) $sth{$table} = $dbh->prepare($sql);
Your problem is that each new CREATE invalidates all the statement handles you previously prepared.
Finally, right after the loop, i do the following for one table:
1) $#bind = -1;
2) foreach $item (sort keys %rec)
{
push(@bind, $rec{$item});
}
3) $sth{$table} -> execute(@bind); # this is line 697 in the error message
It's not your problem, but that's rather bizarre Perl; use the slice, Luke:
@bind = @rec{sort keys %rec);4) Sometime later, I do the $dbh->commit().
When I run my loop with only one table, it works. When I run the loop with multiple tables, it fails on the following message: DBD::SQLite::st execute failed: database schema has changed(1) at dbdimp.c line 389 at ./sarparsed.pl line 697. DBD::SQLite::st execute failed: database schema has changed(1) at dbdimp.c line 389 at ./sarparsed.pl line 697.
It works fine with only one table because your only active statement handle was prepared *after* you stopped changing the schema.
I suspect that I will need to have multiple $dbh (as in $dbh{$table}) but I don't want to connect to multiple Operating System files, just one. am I on the right track?
Nope. I confirmed the problem by creating the following test case:
#!perl use strict; use warnings; use DBI;
unlink 'dummy.db';
my $dbh=DBI->connect(
'dbi:SQLite:dummy.db',"","",{RaiseError=>1,AutoCommit=>0});
$dbh->do('create table t1 (a integer,b integer)');
my $sth1=$dbh->prepare('insert into t1 values (?,?)');
$dbh->do('create table t2 (c integer,d integer)');
my $sth2=$dbh->prepare('insert into t2 values (?,?)');
$sth1->execute(1,2); # this fails because $sth1 is now stale
$sth2->execute(3,4); # this succeeds because $sth2 is still fresh
$dbh->commit();
$dbh->disconnect();This will fail, but moving the preparation of $sth1 to after the second CREATE will make it succeed.
