René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Execute SQL in a (MS-Access or MDB) database

use warnings;
use strict;

use WIN32::OLE;
use WIN32::OLE::Const 'Microsoft.DAO'; # for dbFailOnError

if (@ARGV < 2) {
  print "$0 MDB-file-name SQL-file-name\n";
  exit 0;
}

if (@ARGV < 1) {
  print "Name of sql file missing\n";
  exit 0;
}

my $mdb_file_name = $ARGV[0];
my $sql_file_name = $ARGV[1];

my $sql_text = "";

open SQL, $sql_file_name or die "could not open $sql_file_name";
while (my $sql_line = <SQL>) {
  $sql_line =~ s!//.*!!;
  $sql_text .= $sql_line;
}
close SQL;

$sql_text =~ s/\/\/.*$//mg;

my $db_engine = new Win32::OLE 'DAO.DBEngine.36'       or die 'DAO.DBEngine';
my $db = $db_engine -> OpenDatabase($mdb_file_name);

my @sql_statements = split /; *\n/m, $sql_text;

for my $sql_statement (@sql_statements) {
  execute_sql_statement($sql_statement);
# print ">$sql_statement<\n";
}

$db->Close();

exit 1;

sub execute_sql_statement {
  my $sql_statement = shift;

# print $sql_statement;

  # dbFailOnError: 128
  $db->execute($sql_statement, dbFailOnError);
# $db->execute($sql_statement, 128);

  my $err = WIN32::OLE->LastError();
  if ($err) {
  print "

$sql_statement

  caused

$err

";
  }
}