[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.