1. Common ways which use SQlite
#!/usr/ bin/perl -w
use warnings;
use constant LIB_DIR =>'/home/xialeizhou/perl5/lib/perl5';
use lib LIB_DIR;
use DBI;
#my $dbargs = { AutoCommit => 0, PrintError => 1 };
#my $dbh = DBI->connect( "dbi:SQLite:dbname=./record.db", "", "", $dbargs );
my $dbh = DBI->connect( "dbi:SQLite:dbname=./record.db" );
my $creat_sth = $dbh->do( "create table idClassString( site_id, class)" );
#my $index_sth = $dbh->do( "create index idx_site_id ON idClassString( site_id )" );
# $index_sth = $dbh->do( "create index idx_class ON idClassString( class )" );
# insert data
my %hash;
for (0..1000){
$hash{"site_$_"} = "test_strings_is_$_";
}
while (my ($key, $valule) = each %hash) {
#print $key, $hash{$key}, "\n";
$dbh->do( qq[insert into idClassString values( "$key","$hash{$key}" )] );
}
#my $insert_sth = $dbh->do( "insert into idClassString values( 12, 'hello world' )" );
## debug messages
#DBI->trace( 1, 'dbtrace.log');
#
#$dbh->{ AutoCommit } = 0;
#
## update data
#my $update_sth = $dbh->do( "update idClassString set class='foo neel' where site_id='12'");
#
## delete data
#my $delete_sth = $dbh->do( "delete from idClassString where site_id='12'" );
#
my $sql = "SELECT * FROM idClassString";
my $dataCenter = $dbh->prepare($sql);
$dataCenter->execute();
my (@row_array, $out_id, $out_str);
while (@row_array = $dataCenter->fetchrow_array){
# ( $out_id, $out_str ) = @row_array;
print @row_array, "\n";
}
#$dbh->do( "delete from idClassString" );
$dbh->disconnect();
2. Read data from a db file
#!/usr/ bin/perl -w
use warnings;
use constant LIB_DIR =>'/home/xialeizhou/perl5/lib/perl5';
use lib LIB_DIR;
use DBI;
#my $dbargs = { AutoCommit => 0, PrintError => 1 };
my $dbh = DBI->connect( "dbi:SQLite:dbname=./record.db" );
#my $creat_sth = $dbh->do( "create table idClassString( site_id, class)" );
#my $index_sth = $dbh->do( "create index idx_site_id ON idClassString( site_id )" );
# $index_sth = $dbh->do( "create index idx_class ON idClassString( class )" );
# insert data
#my %hash;
#for (0..100){
# $hash{"site_$_"} = "test_strings_is_$_";
#}
##
#while (my ($key, $value) = each %hash) {
###print $key, $hash{$key}, "\n";
# $dbh->do( qq[insert into idClassString values("$key", "$hash{$key}")] );
#}
#my $insert_sth = $dbh->do( "insert into idClassString values( 12, 'hello world' )" );
## debug messages
#DBI->trace( 1, 'dbtrace.log');
#
#$dbh->{ AutoCommit } = 0;
#
## update data
#my $update_sth = $dbh->do( "update idClassString set class='foo neel' where site_id='12'");
#
## delete data
#my $delete_sth = $dbh->do( "delete from idClassString where site_id='12'" );
#
my $sql = "SELECT * FROM idClassString";
my $dataCenter = $dbh->prepare($sql);
$dataCenter->execute();
my (@row_array, $out_id, $out_str);
while (@row_array = $dataCenter->fetchrow_array){
print @row_array;
print "\n";
}
##$dbh->do( "delete from idClassString" );
$dbh->disconnect();
#
#