Eric:
thank you for your reply.
I ended up creating a %dbh hash with $table as the index along with one
Operating System file for each table.
I was hoping there was a way not to create so many Operating System files
because of the extra Administration they require.
can you think of any way around this?
I read all my data from a log and I am creating multiple Tables based on
the content of the data in the log.
Regards,
[EMAIL PROTECTED]
NCCI
Boca Raton, Florida
561.893.2415
greetings / avec mes meilleures salutations / Cordialmente
mit freundlichen Grüßen / Med vänlig hälsning
Eric Bohlman
<[EMAIL PROTECTED] To:
sqlite-users@sqlite.org
lobal.net> cc:
Subject: Re: [sqlite] Multiple
Tables on one Flat File
04/13/2005 06:59
PM
Please respond to
sqlite-users
[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.
The information contained in this e-mail message is intended only for
the personal and confidential use of the recipient(s) named above. This
message may be an attorney-client communication and/or work product and
as such is privileged and confidential. If the reader of this message
is not the intended recipient or an agent responsible for delivering it
to the intended recipient, you are hereby notified that you have
received this document in error and that any review, dissemination,
distribution, or copying of this message is strictly prohibited. If you
have received this communication in error, please notify us immediately
by e-mail, and delete the original message.
