Re: [sqlite] Dumping and importing database via Perl DBD::SQLite

[Puneet Kishor]

Corrected one obvious error...
Puneet Kishor wrote:
On May 24, 2004, at 8:34 AM, H. Wade Minter wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hello,
I'm investigating migrating my Perl/Tk application from MySQL to 
SQLite. I've got most of the conversion to DBD::SQLite done, but there 
are a couple of areas I'm stuck on.

My first issue is that I need to be able to export and import database 
dumps.  Given that the end user won't have the sqlite command-line 
utility available, how would I do a database export and database 
import strictly within the DBD driver?

The export should write out to a file the schema and contents of the 
database, whereas the import should take that export and rebuild the 
database schema and exported contents.  The equivalent in MySQL would 
be doing a mysqldump > file.sql for a database, then doing a mysql < 
file.sql.


# All of the following is untested, but should give you a general idea
# Assuming a table t
open EXP, "t_exp.tab";
$aref = $dbh->selectall_arrayref(q{SELECT c1, c2... FROM t});
for (@$aref) {
  for my $c (@$_) { print EXP $c . "\t"; }
    chop;  # remove the trailing tab
  print EXP "\n";
}
close EXP;
# Alternatively you could also try
#$rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
$sth = $dbh->prepare(q{DROP TABLE t}) or die $dbh->errstr;
$sth->execute or die $dbh->errstr;
$sth = $dbh->prepare(q{CREATE TABLE t (c1, c2)}) or die $dbh->errstr;
$sth->execute or die $dbh->errstr;
$sth = $dbh->prepare(q{COPY t FROM 't_exp.tab'}) or die $dbh->errstr;
$sth->execute or die $dbh->errstr;
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]