Creating a SQL Server view - part 2

We'll need to present several sql commands to the server in order to create/update a view.  So let's talk about what's needed to execute a query from perl on a SQL Server.

First, let's initialize the connection:

use DBI();
my $SQL_Server   = 'changeme';

my $SQL_User     = 'changeme';

my $SQL_password = 'changeme';

my $inputfile = @ARGV[0];

my $dbh = DBI->connect("DBI:ODBC:$SQL_Server", "$SQL_User", "$SQL_password", {PrintError => 0, RaiseError => 1}) or die "Can't connect to dev database: $DBI::errstr\n";

# ----- you can enable tracing if you'd like:

#$dbh->trace(1);;
 

# ----- My SQL Server wants a couple of settings set before executing a query, set them here.

 

$dbh->do("SET ANSI_NULLS ON");
$dbh->do("SET ANSI_WARNINGS ON");
 

# ----- set up the query

my $drop_query = "USE $Database; IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[$inputfile]')) DROP VIEW [dbo].[$inputfile]";
 

# ----- execute it

$dbh->do($drop_query);
 

# ----- report any errors

warn "view check terminated early by error: $DBI::errstr\n" if $DBI::err;
 

# ----- disconnect

$dbh->disconnect or warn "Error disconnecting: $DBI::errstr\n";