Hi there !

I'm working on a program in Perl which uses the DBI/SQLite driver to record some information into a database file, with ActivePerl, on Windows, with module DBD::SQLite in version 1.54.

I have one simple operation I want to perform with this database : CREATE a table. I'm doing everything that's described in the tutorials and I have no error returned from the DBI Library BUT the data is not recorded into the database. The file stays empty.

See below :

my $driver   = "SQLite";
my $database = 'creditdb.db';
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { AutoCommit => 1, RaiseError => 1 }) 
   or die $DBI::errstr;
print "Opened database successfully\n";

Then (the below SQL request has been tested)

my $request = "DROP TABLE IF EXISTS `credit`; CREATE TABLE IF NOT EXISTS `credit` (";
[...]

Then running the SQL Statement

my $rv = $dbh->do($request);
if($rv < 0) {
  print $DBI::errstr;
} else {
  print "Table created successfully\n";
}
$dbh->disconnect();

When running the program, I get the following messages :

Opened database successfully
Table created successfully

I can see the "creditdb.db" file created in the current directory. However, the file is empty. Nothing is recorded into the file. I have added the "AutoCommit => 1" attribut to the connection, but it does not work either. Please help if you have any idea who to solve this. Thanks

You can set that if you wish. From the DBD::SQLite documentation:

   Processing Multiple Statements At A Time
       DBI's statement handle is not supposed to process multiple statements
       at a time. So if you pass a string that contains multiple statements (a
       "dump") to a statement handle (via "prepare" or "do"), DBD::SQLite only
       processes the first statement, and discards the rest.

       If you need to process multiple statements at a time, set a
       "sqlite_allow_multiple_statements" attribute of a database handle to
       true when you connect to a database, and "do" method takes care of the
       rest (since 1.30_01, and without creating DBI's statement handles
       internally since 1.47_01). If you do need to use "prepare" or
       "prepare_cached" (which I don't recommend in this case, because
       typically there's no placeholder nor reusable part in a dump), you can
       look at << $sth->{sqlite_unprepared_statements} >> to retrieve what's
       left, though it usually contains nothing but white spaces.