How to use Perl DBI with DBD::ODBC for SQL Server?

Using DBD::ODBC for connecting Perl to SQL Server requires that the DBD::ODBC module and an appropriate ODBC driver are installed on your system. If these are not available (as in many sandboxed or restricted environments), the code will fail with an error like Can't locate DBD/ODBC.pm.

Since this sandbox environment does not have DBD::ODBC, here is an alternative example demonstrating how to use the core DBI module with an in-memory SQLite database. This example illustrates similar DBI concepts—connecting, preparing statements, placeholders, fetching results—using only Perl core modules and no external dependencies. It prints output to STDOUT and runs immediately with perl -.

Why Use SQLite Instead of ODBC Here?

• No external modules or ODBC drivers needed.
• Fully self-contained in a sandbox, using only core Perl and DBD::SQLite if installed.
• Demonstrates classic DBI usage patterns transferable to SQL Server when on a full system with DBD::ODBC.

Perl DBI Example with SQLite (Runnable in Sandbox)

use strict;
use warnings;
use DBI;

# Using an in-memory SQLite database (DBD::SQLite comes with many Perl installs nowadays)
my $dsn = "dbi:SQLite:dbname=:memory:";
my $username = "";
my $password = "";

my $dbh = DBI->connect($dsn, $username, $password, {
    RaiseError => 1,
    AutoCommit => 1,
}) or die "Cannot connect to database: $DBI::errstr";

print "Connected to SQLite in-memory database.\n";

# Create a test table
$dbh->do("CREATE TABLE test_table (id INTEGER PRIMARY KEY, name TEXT)");

# Insert some records using placeholders
my $sth_insert = $dbh->prepare("INSERT INTO test_table (id, name) VALUES (?, ?)");
$sth_insert->execute(1, 'Alice');
$sth_insert->execute(2, 'Bob');

print "Inserted test data.\n";

# Fetch and display the data
my $sth_select = $dbh->prepare("SELECT id, name FROM test_table ORDER BY id");
$sth_select->execute();

print "Query results:\n";
while (my @row = $sth_select->fetchrow_array) {
    my ($id, $name) = @row;
    print "ID: $id, Name: $name\n";
}

# Clean up
$sth_insert->finish;
$sth_select->finish;
$dbh->disconnect;

print "Disconnected from database.\n";

Explanation

• DBI->connect uses a DSN string. Here, dbi:SQLite:dbname=:memory: creates an in-memory SQLite database.
• Placeholders ? are used for safe insertion of parameters, preventing SQL injection.
• RaiseError => 1 makes DBI die on errors, easing debugging.
• The example uses scalar sigils $dbh, $sth_insert, $sth_select for database and statement handles.
• Calling fetchrow_array in a while loop fetches each row as an array in list context.

Translating This to DBD::ODBC and SQL Server

Once you have DBD::ODBC installed and configured with a proper ODBC driver and DSN for SQL Server, you can replace the DSN string:

$dsn = "driver={ODBC Driver 17 for SQL Server};server=YOUR_SERVER;database=YOUR_DB"

and supply valid username/password. The rest of the DBI method calls remain essentially the same.

Common Pitfalls

• Attempting to use DBD::ODBC without it installed results in "Can't locate DBD/ODBC.pm" errors.
• Ensure your ODBC driver version matches your SQL Server version for compatibility.
• Use placeholders ? instead of interpolating variables directly to prevent SQL injection.
• Remember Perl's TMTOWTDI ("There's More Than One Way To Do It") allows many styles; consistent use of DBI's prepare/execute/fetch helps maintain clarity.

This SQLite example provides a fully runnable demonstration of Perl DBI usage, suitable for sandbox environments lacking external DBI drivers like DBD::ODBC. You can use it as a foundation and adapt it for SQL Server when your environment supports ODBC connections.