Using DBI to connect to a Microsoft SQL Server

 Using DBI to connect to a Microsoft SQL server is a relatively simple task. Rather than go with a simple example, I have chosen to demonstrate not just connectivity, but also error checking.As always, when you want to use a module, you must declare its use at some point. For our purposes, we will do this at the top of the script. We also want to enable useful warnings, so for now we will specify the -w switch.    #!/usr/bin/perl -w    use DBI;We will need to define some global parameters that will be necessary later in the example. We will need a variable with our username, password, and DSN name to start with:    #    # Database/ODBC declarations and options    #    $db_user     = "your_dbusername";    $db_pass     = "dbuser_password";    $dsn_name    = 'dbi:ODBC:YourDSN';It is necessary to specify dbi:driver in your DSN variable. In this example, we put that information in our $dsn_name variable, but you could as easily add it to the DSN name when you initiate the database connection. Make sure that "driver" corresponds to the DBI driver you want to use to connect -- in this case, ODBC since we are using a Windows ODBC connection.Next, let's set up a function to connect to the database using our DSN:    #    # Function:     dbh_connect    # Description:  Creates a connection to a database    # Arguments:    none    # Returns:      $db_conn - database connection handle    #    sub dbh_connect    {        my ($dbh);        $dbh = DBI->connect($dsn_name, $db_user, $db_pass, {                    PrintError => 0,                    AutoCommit => 1                });        if (! defined($dbh) )        {            print "Error connecting to DSN '$dsn_name'\n";            print "Error was:\n";            print "$DBI::errstr\n";         # $DBI::errstr is the error                                            # received from the SQL server            return 0;        }        return $dbh;    }Now we will set up a function to handle passing SQL statements to the server and checking that they do not return an error.    #    # Function:     db_sql    # Description:  Passes a SQL statement to the server and checks for    #               an error response from the server    # Arguments:    $sql - SQL Statement to process    # Returns:      $sth - SQL Statement Handle    #    sub db_sql    {        my ($sql, $sth, $rc);        $sql = shift;        if (! ($sql) )        {            print "Must pass SQL statement to db_sql!\n");            return 0;        }        #        # Verify that we are connected to the database        #        if (! ($dbh) || ! ($sth = $dbh->prepare("GO") ) )        {            #            # Attempt to reconnect to the database            #            if (! dbh_connect() )            {                print "Unable to connect to database.\n";                exit;   # Unable to reconnect, exit the script gracefully            }        }        else        {            $sth->execute;      # Execute the "GO" statement            $sth->finish;       # Tell the SQL server we are done        }        $sth = $dbh->prepare($sql);     # Prepare the SQL statement passed to db_sql        #        # Check that the statement prepared successfully        #        if(! defined($sth) || ! ($sth))        {            print "Failed to prepare SQL statement:\n";            print "$DBI::errstr\n";            #            # Check for a connection error -- should not occur            #            if ($DBI::errstr =~ /Connection failure/i)            {                if (! dbh_connect() )                {                    print "Unable to connect to database.\n";                    exit;       # Exit gracefully                }                else                {                    print "Database connection re-established, attempting to prepare again.\n";                    $sth = $dbh->prepare($sql);                }            }            #            # Check to see if we recovered            #            if ( ! defined( $sth ) || ! ($sth) )            {                print "Unable to prepare SQL statement:\n";                print "$sql\n";                return 0;            }        }        #        # Attempt to execute our prepared statement        #        $rc = $sth->execute;        if (! defined( $rc ) )        {            #            # We failed, print the error message for troubleshooting            #            print "Unable to execute prepared SQL statement:\n";            print "$DBI::errstr\n";            print "$sql\n";            return 0;        }        #        # All is successful, return the statement handle        #        return $sth;    }The function above may not be super-efficient, but I have definitely gotten my use out of all of the error checking, especially with scripts that run continuously. If you have a script that runs continuously, sometimes the SQL server can fail between loops in the script. When you next pass a SQL statement to the server, it will result in error after error. Double-checking the connection when you get ready to pass a SQL statement makes sure that you still have a connection, and improves the likelyhood that your script will recover on its own.Now that we have a connection, and a method to pass SQL statements to the server, it is time to use them. The DBI module provides several methods for retrieving the rows, but I will only cover two, fetchrow_arrayref, and fetchrow_hashref.fetchrow_arrayrefMost of the time I use fetchrow_hashref for the convenience. Because it returns the rows in a hash reference, you do not have to remember what order you selected the fields in, only the field names. Field names are case-sensitive based on how they were typed in the SQL SELECT statement.    sub retrieve_rows    {        my ($sql, $sth, $href);        $sql = "SELECT ID, FOO, BAR FROM TMPTABLE";        $sth = db_sql( $sql );  # Pass the SQL statement to the server        #        # Check that we received a statement handle        #        if (! ($sth) )        {            return 0;        }        #        # Retrieve the rows from the SQL server        #        while( $href = $sth->fetchrow_hashref )        {            print "ID: " . $$href{"ID"} . "\n";            print "FOO: " . $$href{"FOO"} . "\n";            print "BAR: " . $$href{"BAR"} . "\n";            print "\n";        }        return 1;    }Note that each of the field names is in double-quotes ("). Failure to put the field names in quotes may result in an inability to retrieve the values of the rows.I have found DBI method fetchrow_arrayref most useful when retrieving values in a specific order (as specified in the SELECT statement) in conjunction with modules like Text::CSV_XS for putting the data into a CSV file, for example.    sub rows_to_csv    {        my ($sql, $sth, $aref, $csv);        use Text::CSV_XS;        $csv = Text::CSV_XS->new( { always_quote => 1 } );  # Open a new Text::CSV_XS object                                                            # and turn always_quote on        if (! open(OUTPUT, ">FOO.CSV") )        {            print "Could not open FOO.CSV: $!\n";           # Print the file open error            return 0;        }        $sql = "SELECT ID, FOO, BAR FROM TMPTABLE";        $sth = db_sql( $sql );  # Pass the SQL statement to the server        #        # Check that we received a statement handle        #        if (! ($sth) )        {            return 0;        }        $aref = $sth->{NAME};                   # Retrieve column names        $csv->combine( @$aref );                # Combine column names for CSV output        print OUTPUT $csv->string() . "\n";     # Print column headers to FOO.CSV        #        # Retrieve the rows from the SQL server        #        while( $aref = $sth->fetchrow_arrayref )        {            $csv->combine( @$aref );                        # Combine the fields for ouput            print OUTPUT $csv->string() . "\n";             # Print the record to FOO.CSV        }        $sth->finish;       # Tell the SQL server we are done        close(OUTPUT);      # Close FOO.CSV        return 1;    }Add all the parts above together, and the beginnings of a script appear. You can download the script here.