Saturday, June 09, 2012

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:
# ----- My SQL Server wants a couple of settings set before executing a query, set them here.
$dbh->do("SET ANSI_NULLS 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
# ----- 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";

No comments: