Re: [sqlite] Multiple Tables on one Flat File
Got it. Thank you. Eric did point it out as well. In summary, do all my creates, then do all my prepares, then do all my executes with the dummy @bind (? ?) fields. Finally, I proceed to do my true executes and commits. 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 John LeSueur <[EMAIL PROTECTED]To: sqlite-users@sqlite.org om> cc: Sent by: Subject: Re: [sqlite] Multiple Tables on one Flat File [EMAIL PROTECTED] du 04/14/2005 01:43 PM Please respond to sqlite-users [EMAIL PROTECTED] wrote: > > >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 > > If you look at Dr Hipp's mail, the problem is actually the order of your operations. You need to drop all your prepared statements and reprepare them after you do any CREATE, ALTER, DROP or VACUUM statements. John 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.
Re: [sqlite] Multiple Tables on one Flat File
The sqlite_master table keeps the sql used to create the table automatically. check out select * from sqlite_master; On 4/14/05, Eric Bohlman <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > 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. > > Each time you create a table, squirrel away the text of the INSERT > statement (and any other table-related queries) by storing it in a hash > keyed by the table name or the like. Then, when you're done creating > the tables, loop over the stored queries creating prepared sth's. > -- --- You a Gamer? If you're near Kansas City: Conquest 36 https://events.reddawn.net The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264
Re: [sqlite] Multiple Tables on one Flat File
[EMAIL PROTECTED] wrote: 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. Each time you create a table, squirrel away the text of the INSERT statement (and any other table-related queries) by storing it in a hash keyed by the table name or the like. Then, when you're done creating the tables, loop over the stored queries creating prepared sth's.
Re: [sqlite] Multiple Tables on one Flat File
[EMAIL PROTECTED] wrote: 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 If you look at Dr Hipp's mail, the problem is actually the order of your operations. You need to drop all your prepared statements and reprepare them after you do any CREATE, ALTER, DROP or VACUUM statements. John
Re: [sqlite] Multiple Tables on one Flat File
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 recipi
Re: [sqlite] Multiple Tables on one Flat File
D. Richard Hipp wrote: Not only CREATE statements, but also DROP and VACUUM statements will also invalidate all previously prepared statements. Once a prepared statement is invalidated, it must be prepared again. And also ALTER TABLE.
Re: [sqlite] Multiple Tables on one Flat File
On Wed, 2005-04-13 at 17:59 -0500, Eric Bohlman wrote:
> [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.
>
Not only CREATE statements, but also DROP and VACUUM statements
will also invalidate all previously prepared statements. Once a
prepared statement is invalidated, it must be prepared again.
--
D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Multiple Tables on one Flat File
[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.
Re: [sqlite] Multiple Tables on one Flat File
Follow up:
If I create multiple $dbh as in $dbh{$tables}, and point each to a
different Operating System (O.S.) flat file, then I am OK.
LOOP-for-tables:
$dbh{$tables} = DBI->connect('dbi:SQLite:' . $dbms_file . $table , "",
"",
{ RaiseError => 1,AutoCommit => 0 });
It is just a bit disappointing that now I have to manage one O.S. file for
each table.
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
Uriel_Carrasquill
[EMAIL PROTECTED] To:
sqlite-users@sqlite.org
cc:
04/13/2005 04:06 Subject: [sqlite] Multiple
Tables on one Flat File
PM
Please respond to
sqlite-users
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);
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
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.
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?
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
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.
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.
