DBD::ODBC Tutorial Part 3 - Connecting Perl on UNIX to Microsoft SQL Server
Last Changed $Date: 2005-03-22 13:31:24 +0000 (Tue, 22 Mar 2005) $
Publisher: Easysoft Limited

Copyright © 1993-2005 by Easysoft Limited.
All rights reserved.

You may not reverse engineer, decompile or disassemble this
document. Information in this document is subject to change without
notice. Companies, names, and data used in examples are fictitious
unless otherwise noted.

The names of companies referred to herein, their corporate logos, the
names of their hardware and software may be trade names, trademarks or
registered trademarks of their respective owners.

Easysoft and the Easysoft logo are registered trademarks of Easysoft
Limited.

Contents

Introduction

This is part 3 of a series of Easysoft tutorials on using Perl DBI with DBD::ODBC. This tutorial will provide basic details for accessing MS SQL Server databases from Perl on UNIX but concentrates on MS SQL Server specifics, where DBD::ODBC running under DBI to MS SQL Server differs from accessing other databases.

Pre-requisites

Before you start part 3 of this tutorial you need to ensure you have satisfied all the pre-requisites:

  1. Perl

    We used Perl 5.8 but you only need the minimum required by the DBI and DBD::ODBC modules which is currently 5.6. Use perl --version to see what version of Perl you have installed.

  2. DBI module

    We used DBI 1.45 but this tutorial should work with anything after 1.40. To see if you have a recent enough version of DBI installed run:

      perl -e 'use DBI 1.40;'
      

    If you get an error like "DBI version 1.40 required--this is only version 1.30 at -e line 1." you need to upgrade DBI.

    If you get an error saying DBI cannot be found in @INC you've probably not got DBI installed.

    Go to CPAN to get an up to date version of the DBI module.

  3. DBD::ODBC

    We used DBD::ODBC 1.11. You can use similar methods as above to determine if DBD::ODBC is installed and to see what version you have:

    To check you have the DBD::ODBC module installed:

      perl -e 'use DBD::ODBC;'
      

    If you have not got DBD::ODBC installed you should see Applications and Interfaces: Perl DBI and DBD::ODBC for instructions.

    To show the DBD::ODBC version:

      perl -MDBD::ODBC -e 'print $DBD::ODBC::VERSION;'
      

    To show all drivers DBI knows about and their versions:

      perl -MDBI -e 'DBI->installed_versions;'
      

    Go to CPAN to get an up to date version of the DBD::ODBC module.

  4. ODBC driver and driver manager

    Unsurprisingly you will need an ODBC driver for most of this tutorial and we recommend you use an ODBC driver manager under DBD::ODBC (see Applications and Interfaces: Perl DBI and DBD::ODBC). Easysoft can supply ODBC drivers for many databases and operating systems and all our ODBC drivers come with the unixODBC driver manager.

    You probably have the unixODBC driver manager installed if you have the odbcinst command (for ODBC drivers from Easysoft, the unixODBC driver manager is located in /usr/local/easysoft/unixODBC and the odbcinst command in the bin sub directory of that path.

    We used unixODBC 2.2.9 in this tutorial. You can find out the unixODBC version you are using with:

      odbcinst --version
      

    You will also need an ODBC driver for UNIX which can communicate with MS SQL Server. We used the Easysoft ODBC-ODBC Bridge as the ODBC driver to access a remote MS SQL Server database from UNIX.

Assumptions

Previous tutorials in this series

This tutorial assumes you have read or understand all the concepts in the previous tutorials DBD::ODBC Tutorial Part 1 - Drivers, Data Sources and Connection and DBD::ODBC Tutorial Part 2 - Introduction to retrieving data from your database.

Operating System

This tutorial was designed on UNIX and we have assumed you are using UNIX too. However, all the Perl examples should work equally well on Windows so long as minor alterations for the command line are made.

ODBC Driver Manager

We have assumed you are using the unixODBC driver manager. All discussion in this document relating to the location and definition of ODBC data sources is for unixODBC.

Accessing Microsoft SQL Server from Perl in UNIX

What you need to install

The Easysoft ODBC-ODBC Bridge (OOB) allows ODBC applications on one machine to access ODBC drivers on a remote machine.

OOB consists of a client ODBC driver (which you install on the machine where your ODBC application is running) and a server (which you install on a machine where you have an ODBC driver for the database you want to access). OOB distributions contain both the client and the server but you generally only install one or the other.

The following scenario is an example which helps to illustrate what you need.

Once all the components are installed it should look like this:

linuxclient
===========

perl_script.pl
     |
     v
    DBI
     |
     v
  DBD::ODBC
     |
     v
unixODBC Driver Manager
     |
     v
  OOB Client
     |
     v     
  network
     |
     v

windows_server
==============

 OOB Server
     |
     v
MS Driver Manager
     |
     v
MS SQL Server ODBC Driver
     |
     v
  Database

Data Sources

You will find a lot of very useful information on ODBC connection attributes and data sources in DBD::ODBC Tutorial Part 1 - Drivers, Data Sources and Connection.

An ODBC data source is a named resource the application passes to the ODBC driver manager which tells it which ODBC driver to use and any connection attributes it should use. You do not have to create data sources, there are what is called DSN-less connections but you will find detail on this in the first tutorial.

With OOB there are two data sources to consider. The first data source is an OOB client one on your client machine. This data source tells the driver manager to use the OOB Client ODBC data source and contains a number of attributes for the OOB Client which tell it which server to connect to and which data source on the remote machine to use.

A typical OOB Client data source looks like this:

[mydatasource]
# Driver tells the driver manager which ODBC driver to use
Driver = OOB
# Description is a description of this data source
Description = connect to SQL Server on windows
# Server is the name of the machine where the OOB Server is running
Server = windows_server
# Port is the port on which the OOB Server is listening
Port = 8888
# Transport is always tcpip for now
Transport = tcpip

# LogonUser is a valid user account on the windows_server machine
# LogonAuth is LognUser's password
LogonUser = my_windows_username
LogonAuth = LogonUsers_password

# TargetDSN is the name of the data source on windows_server to connect to
TargetDSN = windows_server_dsn
# TargetUser and TargetAuth are synonomous with the ODBC connection
# attributes UID and PWD and specify the user and password to use for
# the database login - if required.
#
TargetUser = db_username
TargetAuth TargetUsers_password

We will describe these attributes in the following sections. When using Perl DBI you do not need to specify TargetUser and TargetAuth in the data source as these can be passed in to the the DBI->connect method.

Authentication

There are two levels of authentication you need to be aware of:

  1. OOB Server authentication

    By default the OOB Server needs to authenticate the client and become the specified user on the windows_server machine. The OOB Client needs to have specified the data source attributes LogonUser and LogonAuth to do this. LogonUser is the name of a user on the windows_server machine who has permission to logon locally to that machine and LogonAuth is that user's password. Think of a user walking up to the windows_server machine and actually logging on to it. When the OOB Server has checked the authentication with the Windows operating system it will become the LogonUser.

    NOTE You can turn off OOB Server authentication but it has various implications (see the OOB manual).

  2. MS SQL Server authentication

    When you create a MS SQL Server data source in the ODBC Administrator on Windows you have the choice of:

At this stage an example might help illustrate. Suppose you have an account on the windows_server machine of Fred Bloggs with a password of mypassword and your database administrator has used SQL Server authentication and assigned you a database login of dbuser/dbpassword. You need to set the OOB data source attributes LogonUser/LogonAuth to "Fred Bloggs" and "mypassword" and call DBI->connect with dbuser/dbpassword.

Server

There are three attributes in an OOB client DSN which tell the OOB Client ODBC driver which server to connect to and how. Server is the name or IP address of the server machine where the OOB Server Service is installed and running. Port is the port the OOB Server is listening on. This defaults to 8888 in the OOB Server configuration. This is not the port your database engine is listening on. Transport is tcpip as currently this is the only transport supported.

The OOB Server is configurable through a HTTP interface. Supposing your OOB Server is installed on windows.mycompany.local you can access the OOB Server administration interface using the URL http://windows.mycompany.local:8890.

Target Data Source

The Target data source (TargetDSN) tells the OOB client which data source on the remote server you want to access. This must be the name of a SYSTEM data source as the OOB Server can only access System data sources (i.e. when you create the data source in the MS ODBC Administrator select the SYSTEM tab before clicking on Add).

Testing your data source

The following script selects and fetches some test data. Use it to check that you can successfully access your data source from a Perl script. Remember to replace the data source name and database username and password placeholders with appropriate values for your database.

#!/usr/bin/perl -w
use strict;

use DBI;

   # Replace datasource_name with the name of your data source.
   # Replace database_username and database_password
   # with the SQL Server database username and password.
   my $data_source = q/dbi:ODBC:datasource_name/;
   my $user = q/database_username/;
   my $password = q/database_password/;

   # Connect to the data source and get a handle for that connection.
   my $dbh = DBI->connect($data_source, $user, $password)
       or die "Can't connect to $data_source: $DBI::errstr";

   # This query generates a result set with one record in it.
   my $sql = "SELECT 1 AS test_col";

   # Prepare the statement.
   my $sth = $dbh->prepare($sql)
       or die "Can't prepare statement: $DBI::errstr";

   # Execute the statement.
   $sth->execute();
  
   # Print the column name.
   print "$sth->{NAME}->[0]\n";

   # Fetch and display the result set value.
   while ( my @row = $sth->fetchrow_array ) {
      print "@row\n";
   }

   # Disconnect the database from the database handle.
   $dbh->disconnect;

Where to go now

Once you've tested your data source (see Testing your data source) you are ready to start fetching and manipulating the data in your database from Perl. We suggest you take a look at DBD::ODBC Tutorial Part 2 - Introduction to retrieving data from your database or the excellent book "Programming the Perl DBI" which you can find at Perl DBI.

The rest of this tutorial concerns itself with MS SQL Server specific issues or common questions from people accessing MS SQL Server from Perl.

Calling Procedures

The following section shows you how to create and call SQL Server procedures that accept input and return output.

To use the sample scripts in this section you need a SQL Server database in which you can create and delete tables and procedures. The data source that you specify in the scripts needs to connect to this database.

Input Parameters

Input parameters are used to supply values to a procedure. For example, a procedure that executes a SELECT statement might use an input parameter to supply a value in a WHERE clause. You provide input parameter values when the procedure is called.

The following example script shows how to create a procedure that accepts one input parameter. The procedure adds 1 to the input parameter value and returns the new value. The script calls the procedure and displays the return value.

#!/usr/bin/perl -w
use strict;

use DBI;

   # Replace datasource_name with the name of your data source.
   # Replace database_username and database_password
   # with the SQL Server database username and password.
   my $data_source = q/dbi:ODBC:datasource_name/;
   my $user = q/database_username/;
   my $password = q/database_password/;
   my $dbh = DBI->connect($data_source, $user, $password)
       or die "Can't connect to $data_source: $DBI::errstr";

   # This procedure takes one input parameter. It adds 1 to the input
   # parameter value and returns the new value.
   $dbh->do(q/CREATE PROCEDURE PROC_RETURN_INPUT_PARAM (@inputval int) AS 
	      SET @inputval = @inputval+1;
              RETURN @inputval;/);

   # The first placeholder (?) in this prepared statement is used to
   # capture the return value of the called procedure. The second
   # placeholder represents the value that's supplied as the procedure's
   # input parameter.
   my $sth1 = $dbh->prepare ("{? = call PROC_RETURN_INPUT_PARAM(?) }");

   my $output;

   my $i = 1;

   # Bind value for the first placeholder. Because the prepared statement
   # updates the bind value, bind_param_inout rather than bind_param needs
   # to be used.
   $sth1->bind_param_inout(1, \$output, 1);

   # Bind value for second placeholder. This is value for the procedure's
   # input parameter.
   $sth1->bind_param(2, $i);

   # Execute the prepared statement.
   $sth1->execute();

   # Output the procedure's return value.
   print "$output\n";

   $dbh->do(q/DROP PROCEDURE PROC_RETURN_INPUT_PARAM/);

   $dbh->disconnect;

This example script inserts rows into a table with an identity column and uses @@IDENTITY to display the identity value used in each new row. The identity value is a unique, incremental value that SQL Server automatically generates when a new row is added to the table.

The procedure in the script accepts one input parameter: a value to insert into the new row. The procedure returns the row's identity value.

#!/usr/bin/perl -w
use strict;

use DBI;

   # Replace datasource_name with the name of your data source.
   # Replace database_username and database_password
   # with the SQL Server database username and password.
   my $data_source = q/dbi:ODBC:datasource_name/;
   my $user = q/database_username/;
   my $password = q/database_password/;
   my $dbh = DBI->connect($data_source, $user, $password)
       or die "Can't connect to $data_source: $DBI::errstr";

   # Create sample table in which to insert test data. The first column is an
   # identity column. When a new row is added to the table, SQL Server
   # provides a unique, incremental value for the column so long as that
   # column value is not specified.
   $dbh->do(q/CREATE TABLE PERL_SAMPLE_TABLE (a INTEGER identity, b CHAR(1))/);

   $dbh->do(q/CREATE PROCEDURE PROC_LAST_INSERTED_VALUE (@inputval char) AS 
	      INSERT INTO PERL_SAMPLE_TABLE VALUES (@inputval);
              RETURN @@IDENTITY;/);

   # The first placeholder (?) in this prepared statement is used to
   # capture the return value of the called procedure. The second
   # placeholder represents the value that's supplied as the procedure's
   # input parameter.
   my $sth1 = $dbh->prepare ("{? = call PROC_LAST_INSERTED_VALUE(?) }");

   my $output = 0;

   my $char = q/a/;

   # Insert two records into the table.
   while ($char ne q/c/) {
      # Bind value for the first placeholder. Because the prepared statement
      # updates the bind value, bind_param_inout rather than bind_param needs
      # to be used.
      $sth1->bind_param_inout(1, \$output, 100);

      # Bind value for second placeholder. This is value for the procedure's
      # input parameter.
      $sth1->bind_param(2, $char);

      # Execute the prepared statement.
      $sth1->execute();

      # Display the last generated identity value.
      print "Identity value for last record = $output\n" if ($output != 0);
      
      $char++;
    }

   $dbh->do(q/DROP PROCEDURE PROC_LAST_INSERTED_VALUE/);
   $dbh->do(q/DROP TABLE PERL_SAMPLE_TABLE/);

   $dbh->disconnect;

Output Parameters

A procedure can return information by using either RETURN or output parameters. RETURN lets you return an integer value. Output parameters let you return other types of values from a procedure. For example, character strings and cursors.

Output parameters also let you return more than one value from a procedure. For example, in a procedure that contains multiple INSERT statements, you can use multiple output parameters to capture and return the numbers of rows affected by each statement.

The following sample script illustrates this technique. It uses output parameters to generate a cumulative total of the number of rows affected by statements in a procedure. The script contrasts this total with the one returned by the DBI rows method. The rows method returns the number of rows affected by the last row affecting statement. It's unaware that the example procedure contains more than one INSERT statement. The totals are different therefore. Note that this comment also applies to UPDATE and DELETE statements.

#!/usr/bin/perl -w
use strict;

use DBI;


# Replace datasource_name with the name of your data source.
# Replace database_username and database_password
# with the SQL Server database username and password.

my $data_source = q/dbi:ODBC:datasource_name/;
my $user = q/database_username/;
my $password = q/database_password/;
my $dbh = DBI->connect($data_source, $user, $password)
       or die "Can't connect to $data_source: $DBI::errstr";


# Create sample table in which to insert test data.

$dbh->do(q/CREATE TABLE PERL_SAMPLE_TABLE (i INTEGER)/);


# This procedure takes one input parameter and two output parameters.
# The procedure inserts the input parameter value into a test table.
# The output parameters are used to return the number of rows affected
# by each INSERT statement.
$dbh->do(q/CREATE PROCEDURE PROC_INSERT_TABLES (@inputval int,
                                                @rowcount1 int OUTPUT,
                                                @rowcount2 int OUTPUT) AS 
    BEGIN 
    
    -- SET NOCOUNT ON here will prevent DBI rows from returning
    -- the row count.
    
    INSERT INTO PERL_SAMPLE_TABLE VALUES (@inputval);
    
    -- @@ROWCOUNT returns the number of rows affected by the
    -- last statement. Store this number in the first output
    -- parameter.
    
	SET @rowcount1 = @@ROWCOUNT;
        INSERT INTO PERL_SAMPLE_TABLE VALUES (@inputval + 1);
	SET @rowcount2 = @@ROWCOUNT;
    END/);


# The first placeholder (?) in this prepared statement represents
# the value that's supplied as the procedure's input parameter.
# The second and third placeholders are used to capture the
# procedure's output parameter values.

my $sth1 = $dbh->prepare ("{call PROC_INSERT_TABLES(?, ?, ?)}");

my $i = 1;
my $sqlserverrowcount;
my $sqlserverrowcount2;


# Bind value for the first placeholder (the procedure's input parameter).
$sth1->bind_param(1, $i);


# $sqlserverrowcount and $sqlserverrowcount2 store the row count
# values returned by the procedure's output parameters.
$sth1->bind_param_inout(2, \$sqlserverrowcount, 1);
$sth1->bind_param_inout(3, \$sqlserverrowcount2, 1);


# Execute the prepared statement.
$sth1->execute();


# Produce a cumulative total for the number of rows affected
# by both INSERT statements.
$sqlserverrowcount = $sqlserverrowcount + $sqlserverrowcount2;

print "Rows affected (Cumulative \@\@ROWCOUNT) = $sqlserverrowcount\n";


# The rows method returns the number of rows affected by the last
# row affecting statement. It's unaware that the procedure contained
# more than one INSERT statement.
print "Rows affected (DBI rows method) = ", $sth1->rows, "\n";

$dbh->do(q/DROP PROCEDURE PROC_INSERT_TABLES/);
$dbh->do(q/DROP TABLE PERL_SAMPLE_TABLE/);

$dbh->disconnect;
Typical output from the above script is:
Rows affected (Cumulative @@ROWCOUNT) = 2
Rows affected (DBI rows method) = 1

Note it is common practise to put "SET NOCOUNT ON" at the front of procedures as this prevents the done_in_proc TDS messages and speeds up procedures. If you do this the DBI rows method will return -1 because the MS SQL Server driver does not get the count. The above procedure gets around this problem as @@ROWCOUNT is still set.

Procedures generating multiple result-sets

Procedures can contain multiple SELECT statements. They are therefore capable of generating an unknown number of result sets. The following script shows how to handle multiple result set generating statements by using the boolean DBD::ODBC method odbc_more_results.

odbc_more_results lets your script check whether there is another result-set to be fetched. It returns false when no more results are available.

Note also that output parameters are not returned until odbc_more_results returns false.

To determine whether to return true or false, odbc_more_results calls SQLMoreResults. There are some situations where DBD::ODBC will automatically call SQLMoreResults without the need for odbc_more_results. If the previous SQL statement that executed was a non result-set generating statement, such as an INSERT statement, DBD::ODBC calls SQLMoreResults for you. This triggers the execution of the next statement in the procedure. If the previous statement generated a result set, your script needs to call SQLMoreResults explicitly by using odbc_more_results.

For example in the sample procedure, the first SELECT statement gets executed as a result of DBD::ODBC calling SQLMoreResults:

INSERT INTO PERL_SAMPLE_TABLE VALUES (@inputval);
SELECT i FROM PERL_SAMPLE_TABLE;
SELECT i FROM PERL_SAMPLE_TABLE WHERE i = @inputval;
SELECT @result = i FROM PERL_SAMPLE_TABLE WHERE i = @inputval;

The second and third SELECT statements get executed as a result of the script calling SQLMoreResults by using odbc_more_results.

#!/usr/bin/perl -w
use strict;

use DBI;


# Replace datasource_name with the name of your data source.
# Replace database_username and database_password
# with the SQL Server database username and password.

my $data_source = q/dbi:ODBC:datasource_name/;
my $user = q/database_username/;
my $password = q/database_password/;
my $dbh = DBI->connect($data_source, $user, $password)
    or die "Can't connect to $data_source: $DBI::errstr";

$dbh->do(q/CREATE TABLE PERL_SAMPLE_TABLE (i INTEGER)/);

$dbh->do(q/CREATE PROCEDURE PROC_MULTIPLE_RESULT_SETS (@inputval int,
						       @result int OUTPUT) AS 
	 BEGIN
	 -- Insert the input parameter value into the test table. 
	 INSERT INTO PERL_SAMPLE_TABLE VALUES (@inputval);
	 SELECT i FROM PERL_SAMPLE_TABLE;
	 SELECT i FROM PERL_SAMPLE_TABLE WHERE i = @inputval;
	 -- This SELECT statement returns one value. Capture
	 -- the value with the procedure's output parameter.
         SELECT @result = i FROM PERL_SAMPLE_TABLE
	 WHERE i = @inputval;
	 END /);

my $sth1 = $dbh->prepare ("{call PROC_MULTIPLE_RESULT_SETS(?, ?)}");

my $i = 1;
my $output = 0;

while ($i < 4) {
    print "Interation $i\n";
    print "============\n";

    $sth1->bind_param(1, $i);
    $sth1->bind_param_inout(2, \$output, 100);

    $sth1->execute();
    print "  Rows affected: ", $sth1->rows, "\n";
    
    # The execute will cause the insert in the procedure to insert a
    # $i into the table. DBD::ODBC will spot this is not a result-set
    # generating statement and call the ODBC API SQLMoreResults for you
    # thus causing the first select to run.
    #
    
    my $rs=1;
    do {
        
	# Fetch and display the result sets generated by the first two
	# SELECT statements in the procedure.
        
	print "  result-set ", $rs++, ":\n";
	while (my (@row) = $sth1->fetchrow_array) {
	    print "\t", join (", ", @row) , "\n";
	}
	print "  (calling SQLMoreResults)\n";
    } while ($sth1->{odbc_more_results}); # Check to see if there's more
    
    # All result sets have been retrieved. The procedure will now
    # have returned the output parameter value.
    
    print "  result-set ", $rs++, "\n\t$output\n" if ($output != 0);

    $i++;
    print "\n";
}

$dbh->do(q/DROP PROCEDURE PROC_MULTIPLE_RESULT_SETS/);
$dbh->do(q/DROP TABLE PERL_SAMPLE_TABLE/);

$dbh->disconnect;
Typical output from the above script is:
Interation 1
============
  Rows affected: 1
  result-set 1:
        1
  (calling SQLMoreResults)
  result-set 2:
        1
  (calling SQLMoreResults)
  result-set 3
        1

Interation 2
============
  Rows affected: 1
  result-set 1:
        1
        2
  (calling SQLMoreResults)
  result-set 2:
        2
  (calling SQLMoreResults)
  result-set 3
        2

Interation 3
============
  Rows affected: 1
  result-set 1:
        1
        2
        3
  (calling SQLMoreResults)
  result-set 2:
        3
  (calling SQLMoreResults)
  result-set 3
        3

PRINT Statement and Status Messages

PRINT statements let you return a user-defined message from a procedure. They help you troubleshoot a procedure. For example, PRINT statements let you check data values or embed trace messages to isolate problem areas in a procedure.

To capture PRINT statement output in a Perl script, you need to create a custom error handler. The standard ODBC diagnostic mechanism doesn't retrieve PRINT statement output.

SQL Server commands such as BACKUP and DBCC also generate status messages to report their progress. You also need to create an error handler if you want to intercept these messages.

To replace the default error handler, set the odbc_err_handler database handle attribute to a reference to a subroutine that will act as the replacement error handler. The following script shows you how to do this.

#!/usr/bin/perl -w
use strict;

use DBI;
   # Replace datasource_name with the name of your data source.
   # Replace database_username and database_password
   # with the SQL Server database username and password.
   my $data_source = q/dbi:ODBC:datasource_name/;
   my $user = q/database_username/;
   my $password = q/database_password/;
   my $dbh = DBI->connect($data_source, $user, $password)
       or die "Can't connect to $data_source: $DBI::errstr";

# Catch and display status messages with this error handler.
sub err_handler {
   my ($sqlstate, $msg, $nativeerr) = @_;
   # Strip out all of the driver ID stuff
   $msg =~ s/^(\[[\w\s:]*\])+//;
   print $msg;
   print "===> state: $sqlstate msg: $msg nativeerr: $nativeerr\n";
   return 0;
}

   $dbh->{odbc_err_handler} = \&err_handler;

   $dbh->{odbc_exec_direct} = 1;

   my $sql = q/CREATE PROCEDURE PROC_PRINT_MESSAGES AS
              PRINT 'START';
              SELECT 1;
              PRINT 'END';/;
   
   $dbh->do($sql);
   my $sth = $dbh->prepare("{ call PROC_PRINT_MESSAGES  }");
   $sth->execute;
   do {
       while (my @row = $sth->fetchrow_array) {
          if ($row[0] eq 1) {
             print "Valid select results with print statements\n";
          }
       }
   } while ($sth->{odbc_more_results});

   $dbh->do(q/drop procedure PROC_PRINT_MESSAGES/);

   $dbh->disconnect;

The following line in the script forces DBD::ODBC to use SQLExecDirect instead of SQLPrepare then SQLExecute.

$dbh->{odbc_exec_direct} = 1;

This prevents certain versions of the SQL Server ODBC driver from generating errors when the procedure the script contains is executed. The errors are similar to the following:

(SQL-42S02)(DBD: Execute immediate failed err=-1) at 
myscript.pl line 6. DBD::ODBC::st execute failed: 
[Microsoft][ODBC SQL Server Driver]Invalid cursor state 
(SQL-24000)(DBD: dbd_describe/SQLNumResultCols err=-1) at 
myscript.pl line 12. DBD::ODBC::st fetchrow_array failed: 
(DBD: no select statement currently executing err=-1) at 
myscript.pl line 14.

Multiple Active Statements

SQL Server doesn't support Multiple Active Statements with the standard cursor type (Forward-only). The following script provides a workaround if you have no alternative other than to use Multiple Active Statements.

#!/usr/bin/perl -w
use strict;

use DBI;

#
#
# MS SQL Server does not by default allow Multiple Active Statements
# i.e. it does not allow you to create a second result set on another
# statement whilst a result-set is active on another statement.
# MS SQL Server can support MASs if you use a server side cursor.
#
# A workaround was found by setting SQL_ROWSET_SIZE to a value > 1.
# However,
#
# a) although this persuades MS SQL Server to create a server side cursor
#    server-side cursors are slower than static cursors.
# b) it is slightly dangerous as if you do not consume all the result-set
#    or call finish you can hang the MS SQL Server ODBC driver.
#    This is easily demonstrated via the PHP interface
#    (http://www.easysoft.com/products/2002/apache.phtml#appb2) but
#    we have never reproduced it in DBD::ODBC - perhaps because finish
#    is called for you.
# c) this workaround only works because DBD::ODBC does not use
#    SQLExtendedFetch - if that changed it would undoubtably break.
#  
# A more reliable way of doing this is by setting the cursor type
# to SQL_CURSOR_DYNAMIC. You can do this in the connect method
# as below.


my $data_source = "dbi:ODBC:datasource_name";
my $user = "database_username";
my $password = "database_password";
my $dbh = DBI->connect($data_source, $user, $password)
    or die "Can't connect to $data_source: $DBI::errstr";

#
# Use this form of connect instead to get multiple active statements.
#
#my $dbh = DBI->connect($data_source, $user, $password, {odbc_cursortype => 2})
#    or die "Can't connect to $data_source: $DBI::errstr";



#
# Uncomment the next line to use the workaround to get MAS.
# better method is to use odbc_cursor_type.
#
#$dbh->{odbc_SQL_ROWSET_SIZE} = 2;


$dbh->do(q/create table "mas" (a integer)/);
$dbh->do(q/create table "mas2" (a integer)/);
$dbh->do(q/insert into "mas" values (1)/);
$dbh->do(q/insert into "mas2" values (2)/);

my $sth = $dbh->prepare(q/select * from "mas"/);
$sth->execute;

my $sth2 = $dbh->prepare(q/select * from "mas2"/);

# If you have not set a dynamic cursor (see commented out connect call
# above) or you have not uncommented the SQL_ROWSET_SIZE workaround
# (see commented out code above) then you will get:
#
# DBD::ODBC::st execute failed: [unixODBC][Microsoft][ODBC SQL Server Driver]
# Connection is busy with results for another hstmt (SQL-HY000)
# (DBD: st_execute/SQLExecute err=-1) at ./mas.pl line 35.
# from this next prepare.
#

if ($sth2->execute) {
  my $col = $sth->fetchrow_array;
  print "Column from first result-set, val = $col\n";
  my $othercol = $sth2->fetchrow_array;
  print "Column from second result-set, val = $othercol\n";
}

$sth->finish;
$sth2->finish;

$dbh->do(q/drop table "mas"/);
$dbh->do(q/drop table "mas2"/);

$dbh->disconnect;

Faster Inserting

These examples use a table containing 5 columns, a integer, a char(30), a varchar(255), a timestamp and a float.

The simplest code to populate this table is:

my $sql;
for (my $i = 0; $i <50000; $i++) {
    sql = "insert into perf values (" . $i . ", 'this is a char thirty'" .
           ",'this is a varchar 255', {ts '2002-08-01 11:12:13.123'}, "
	   . $i * 1.1 . ")";
    my $sth = $dbh->prepare($sql);
    $sth->execute;
}

Here we construct the SQL insert statement once for each row and call SQLExecDirect once for each row. When we run this code it takes 5 minutes and 10 seconds. Why? The first problem with this code is the database has to parse and prepare the insert statement once per row; this can be time consuming.

A much more efficient method is to used a parameterized insert. Here parameters are using in place of the column data. Instead of the above SQL we would use:

insert into perf values (?,?,?,?,?)

We prepare this SQL once (using the prepare method), bind the parameters with the bind_param method and then just keep calling the execute method, changing the data we bound each time. e.g.

my $idata;
my $cdata1;
my $cdata2;
my $ts;
my $fdata;

my $sth = $dbh->prepare(q/insert into perf values (?,?,?,?,?)/);

$sth->bind_param(1, $idata);
$sth->bind_param(2, $cdata1);
$sth->bind_param(3, cdata2);
$sth->bind_param(4, ts)
$sth->bind_param(5, fdata)

for (i = 0; i <= 50000; i++)
{
    #set idata, cdata1, cdata2, ts, fdata to whatever values you
    #want to insert
    $sth->execute;
}

However, if you run this through OOB you'll find it does not make any real difference. The reason for this is that in the first case with straight forward SQL insert you are making one network call per insert whereas with the parameterised inserts there are two network calls; one for execute and one to send the parameters (OOB also has some extra work to pack up the parameters).

With many databases a very easy speed up if you are doing bulk inserts, say copying large quantities of data from one place to another is to turn autocommit off and commit the inserts at the end. This can half the insert time.

e.g.

Make the following changes:

# Add this immediately after obtaining the $dbh or in the call
# to connect (see connect method in the DBI docs).
$dbh->{AutoCommit} = 0;

# Add this after the loop
$sth->commit;    #commit all the inserts

When we apply this change and run to MS SQL Server the time comes down to around 2 minutes 30 seconds; this is nearly half of what we started with. There are two things to remember about this:

  1. If you only commit at the end of all your inserts then if you abort when any execute fails none of your inserts are committed.
  2. Individual inserts will not be visible to other users until you have finished all your inserts and committed them. If this bothers you then perhaps you can still get improved speed by committing less often.

One final change we could make to this is to use arrays of parameters but DBI/DBD::ODBC cannot do this yet.

Common Problems

Why do I get corrupted text columns back from MS SQL Server?

We have had reported to us a problem accessing text fields in MS SQL Server. The reporter was attempting to retrieve multiple columns defined as SQL Server "text" in PHP and always retrieved garbage in the second text column. This problem only appears to happen when retrieving multiple text fields from a table with SQLGetData and occurs for the second text column retrieved. PHP sees that text fields in SQL Server can be very long so instead of binding the column (as usual) it uses SQLGetData. This problem could occur in any other ODBC interface if SQLGetData is used for multiple text fields.

The specific report involved a table created as follows:

    create table BENCH_TEXT (f1 integer, f2 text, f3 text)
    insert into BENCH_TEXT values(1, 'some text', 'some text')
    select f1, f2, f3 from BENCH_TEXT

The f1 and f2 columns are retrieved OK with SQLGetData but when the f2 column is requested with SQLGetData the returned StrLen_or_IndPtr value is usually too short and the data is garbage.

After a search of the net we discovered a similar report by Chad Slater-Scott which read:

Subject: BUG: Service Pack 1 on SQL7.0 (SQLExtendedFetch Returns metadata on SQLGetData)
Date: 09/27/1999
Author: Chad Slater-Scott <avacado@usa.net>

Just wanted to let you all know that SP1 For SQL Server has a new bug. After using SQLExtendedFetch or SQLFetchScroll to retrieve records using SQL_ABSOLUTE or SQL_RELATIVE positioning, a SQLGetData call to a text field will return garbage. This is not reproducible if the text field is the only field selected or if you the text field is the first field called. In order to reproduce it, another field value must be retrieved using SQLGetData. I reported this to Microsoft and a Bug has been filed. If any of you are using this sequence of calls and have installed SP1, check your stuff for garbage on the text fields. The Bug ID is 56509 is want to track its status.

We have been told that the problem is in the SQL Server driver included in SP1 for SQL Server and SP6 for NT. The version with the problem appears to be C:\winnt\system32\sqlsvr32.dll (3.70.06.90) and the working version is 3.70.0623.

Our machines running SQL Server 7 and NT 4 sp4 appear OK.

Why do I get a transaction already started error calling a procedure?

This often happens if the procedure mixes insert/update and select operations and auto-commit has been turned off. This is quite a common question from people using Perl where the script connects with autocommit turned off (as in one of the OOB examples):

my $dbh = DBI->>connect($ENV{DBI_DSN},
                        $ENV{DBI_USER},
                        $ENV{DBI_PASS},
                        {
                            RaiseError => 1,
                            AutoCommit => 0
                            }
                        ) || die "Database connection failed: $DBI::errstr";

Why can't I insert timestamps with sub-millisecond fractions in to MS SQL Server datetime field?

Firstly, consult http://support.microsoft.com.

MS SQL Server only does millisecond accuracy (precision of 3) on datetime columns so anything sub-millisecond tends to generate an error such as 22008 [Microsoft][ODBC SQL Server Driver]Datetime field overflow. As the fraction part of a timestamp can range from 0 to 999,999,999 you need to specify the fraction in thousands of second.

If you are using column-wise bound parameters to insert a timestamp in to a datetime field make sure the ColumnSize is set to 23.

Why are my output bound parameters from a MS SQL Server procedure not retrieved?

Assuming your application was written correctly then the likelihood is you are not calling SQLMoreResults() after each SQLExecute. A quote from Microsoft's web site says:

"For SQL insert statements, ODBC 3.5 changed the behavior with respect to SQLMoreResults such that, output parameters aren't stored in the application's buffer until after the app calls SQLMoreResults and it returns SQL_NO_DATA_FOUND. The ODBC 2.65 driver would read-ahead and sometimes lump result sets together or skip over them. The ODBC 3.5 driver was changed to provide result sets in a consistent fashion w/o the various problems that used to occur."

So if you have procedures that return values and they contain insert statements you must call SQLMoreResults() to fill your output bound parameters.

You may also get a function sequence error if you attempt another SQLExecute call before SQLMoreResults() has returned SQL_NO_DATA_FOUND.

According to Microsoft, setting "SET NOCOUNT ON" in your SQL will also work. However, if you do this you need to insert it into the first line of your procedure (it is no good putting it in to the SQL that calls your procedure in the SQLPrepare).

If you are using unixODBC there was a bug in an early 2.2.4 release (dated around Jan 20 2003). If SQLMoreResults returned SQL_ERROR then all further calls to SQLMoreResults were not passed through to the driver and returned SQL_NO_DATA. This prevents the OOB client from obtaining the procedure return output parameter.

Why do I get "Incorrect Syntax near the Keyword 'by'" error message when calling SQLDescribeParam in the MS SQL Server ODBC driver?

When calling SQLDescribeParam in the MS SQL Server ODBC driver you get an error return with the diagnostic text:

"Incorrect Syntax near the Keyword 'by'"

This was a bug in MDAC 2.6 and is fixed in MDAC 2.6 sp1.

It happens if you have a column named "c", "ca" or "cas" and you call SQLDescribeParam because the driver is checking for a CASE statement and does the check incorrectly.

See http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q273813

Why do I get "String data, right truncation" retrieving result-sets from MS SQL Server?

Please see the question "Why do I keep getting data truncation errors in my Perl" first as that is more likely to be the answer you are looking for.

Before going in to the specifics of an issue in the MS SQL Server ODBC driver it should be noted that the most common reason for this error is calling SQLGetData or binding a column and supplying too small a buffer for the column data.

e.g.

SQLExecDirect(select var_char_column from table)
SQLDescribeCol(column=1)
returns a column size of 100
or SQLColAttributes(column=1, SQL_DESC_DISPLAY_SIZE)
     which also returns 100
SQLFetch
SQLGetData(column=1, type=SQL_C_CHAR, buffer, buffer_size = 10)

or

SQLExecDirect(select var_char_column from table)
SQLDescribeCol(column=1)
returns a column size of 100
or SQLColAttributes(column=1, SQL_DESC_DISPLAY_SIZE)
     which also returns 100
SQLBindCol(column=1, type=SQL_C_CHAR, buffer, buffer_size = 10)
SQLFetch

In both cases the buffer supplied for the column data was too small and you will get a SQL_SUCCESS_WITH_INFO return from SQLGetData/SQLFetch and a state of 01004.

However, there is one specific issue with the MS SQL Server ODBC driver which can seriously confuse ODBC applications that bind columns as SQL_C_CHAR. When retrieving column data from a result-set as strings the average ODBC application:

[1] issues the query

[2] calls SQLNumResultCols to find out how many columns in the result-set

[3] calls SQLDescribeCol for each column to find out the type of the column and sometimes SQLColAttribute to get the display size.

[4] for each column calls SQLGetData (or SQLBindCol) passing a buffer based on the reported display size.

Ordinarily this works fine with the MS SQL Server driver but when "Use Regional Settings" is checked in the MS SQL Server ODBC driver DSN setup dialog everything changes. Once "Use Regional Settings" is checked the ODBC driver returns integer fields using the current regional settings and this generally increases the size of the returned data. For UK machines the simple integer 1234, is returned as "1,234.00". The problem is that when "Use Regional Settings" is checked SQLDescribeCol and SQLColAttribute return the same column-size as they do when this setting is unchecked and so for larger numbers the column-size of 10 will be insufficient to hold the number and you will get "string data right truncated". A simple example illustrates the problem:

SQLGetTypeInfo(SQL_LONGVARCHAR)

this returns a result-set describing the SQL_LONGVARCHAR type of which one of the columns (column 3) is the maximum column size that the server supports for this data type. In MS SQL Server the maximum size of a SQL_LONGVARCHAR is 2147483647 bytes.

SQLDescribeCol(column=3)

this returns (Name : COLUMN_SIZE, Type : 4, Size : 10, Decimal Digits : 0, Nullable : 1)

Note the Size=10. Most applications will use this number to allocate a buffer to receive the column data if the column data is being retrieved as SQL_C_CHAR (they add 1 for the terminating NUL as well).

SQLColAttribute(column=3, SQL_DESC_DISPLAY_SIZE)

returns 11

SQLGetData(column=3, buffer, buffer_size=100)

returns "2,147,483,647.00" in the UK which is too big to fit in the previously returned size of 10 or 11.

We have found a number of applications that suffer from this problem but perhaps the most common is Perl's DBD::ODBC which binds integer columns as SQL_C_CHAR (quite naturally really given Perl's text processing features). If "Use Regional Settings" is checked in your MS SQL Server data source and you run make test for DBD::ODBC you are likely to see something like:

# make test
PERL_DL_NONLAZY=1 /usr/local/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/01base.........ok
t/02simple.......ok 2/17
DBD::ODBC::st fetchrow failed:
[unixODBC][][Microsoft][ODBC SQL Server Driver]
String data, right truncation (SQL-01004)
(DBD: st_fetch/SQLFetch (long truncated) err=-1) at t/ODBCTEST.pm line 56.
DBD::ODBC::st fetchrow failed:
[unixODBC][][Microsoft][ODBC SQL Server Driver]
String data, right truncation (SQL-01004)
(DBD: st_fetch/SQLFetch (long truncated) err=-1) at t/ODBCTEST.pm line 56.
Unable to find a suitable test type for field COL_C at t/ODBCTEST.pm line 63.
t/02simple.......dubious
        Test returned status 255 (wstat 65280, 0xff00)

There are two possible solutions to this problem:

[1] uncheck "Use Regional Settings" in the MS SQL Server data source.

[2] add Regional=No to your connection string passed to DBI->Connect. e.g. "dbi:ODBC:DSN=mydsn;UID=dbuser;PWD=dbpassword;Regional=No;"

The MS help for the "Use Regional Settings" specifically says "Select this option forapplications that only display data, not for applications that process data".

For DBD::ODBC and perhaps for some other ODBC applications you will find that disabling "Use Regional Settings" is the only way of making them work.

Why are my numeric and currency values returned too long with an additional random character from MS SQL Server?

If you check "Use regional settings when outputting currency, numbers, dates and times" in a MS SQL Server data source and then retrieve numeric or currency fields as chars (SQL_CHAR) you will encounter two problems:

1.

For numeric data MS SQL Server will convert the number using your regional settings. e.g. the number 1000 in the UK is converted to 1,000.00. Unfortunately length returned by the SQLGetData or SQLFetch (with SQLBindCol) is one more than the actual data returned. For the returned string "1,000.00" MS SQL Server will actually say this string is 9 characters long when in fact it is 8.

You can reproduce this with a simple bit of perl:

my $sth = $dbh->prepare("select count(*) from table");
$sth->execute;
my @row = $sth->fetchrow_array);
print "\"$row[0]\" length of ". length($row[0]). "\n";
print "unpacked - |". unpack("H*", $row[0]), "|\n";

which when there are 1000 rows prints:

"1,000.00" length of 9
unpacked - |312c3030302e303000|

Notice the length of 9 (one too many) and the last character is hex 00 here but in actual fact seems pretty random. If you run this code under the perl debugger and use the 'x' command on $row[0] you'll see something like this:

DB<1> x $row[0]
0 "1,000.00\c@"

To reproduce in the ODBC API simply:

connect etc
SQLExecDirect("select count(*) from table");
SQLFetch
SQLGetData(SQL_CHAR, buffer, buffer_size=20, StrLen_or_IndPtr)

and you'll get 9 back in StrLen_or_IndPtr when the returned data is "1,000.00" (one too many).

2.

The second problem is that when regional settings are turned on numeric and currency data are longer than with regional settings off but SQLDescribeCol does not return larger column sizes. This is best documented in "Why do I get "String data, right truncation" retrieving result-sets from MS SQL Server?"

If you cannot change the type retrieved from SQL_CHAR to SQL_INTEGER then you'll need to uncheck the regional settings in the data source.

Why do tests 1, 2 and 6 of 20Sqlserver test in DBD::ODBC fail to MS SQL Server?

Symptom.

You are doing a make test in DBD::ODBC and tests 1, 2 and 6 of the 20Sqlserver test fail like this:

t/20SqlServer....NOK 1Please upgrade your ODBC drivers to the latest
SQL Server drivers available.
t/20SqlServer....FAILED tests 1-2, 6
        Failed 3/25 tests, 88.00% okay

If you run the test again with TEST_VERBOSE=1 the relevant failues are:

t/20SqlServer....1..25
Inserting: 0, string length 13
Inserting: 1, 2001-01-01 01:01:01.110 string length 12
Inserting: 2, 2002-02-02 02:02:02.123 string length 114
Inserting: 3, 2003-03-03 03:03:03.333 string length 251
Inserting: 4, 2004-04-04 04:04:04.443 string length 282
Inserting: 5, 2005-05-05 05:05:05.557 string length 131
Retrieving: 0, string length 13
Retrieving: 1, 2001-01-01 01:01:00.000 string length 12 !time
Retrieving: 2, 2002-02-02 02:02:02.123 string length 114
Retrieving: 3, 2003-03-03 03:03:03.333 string length 251
Retrieving: 4, 2004-04-04 04:04:04.443 string length 282
Retrieving: 5, 2005-05-05 05:05:05.557 string length 131
not ok 1
f ne foo
Please upgrade your ODBC drivers to the latest SQL Server drivers available.
not ok 2
1
2
3
ok 3
CREATE PROCEDURE PERL_DBD_PROC1 (@i int, @result int OUTPUT)
AS BEGIN     SET @result = @i+1;END
ok 4
ok 5
2002-07-12 05:09:00.000
not ok 6

Here you will note that test 1 and 6 fails because the datetime inserted is not retrieved correctly (the seconds and milliseconds are zeroed) and test 2 fails because the string "foo" that was inserted is retrieved as "f". We have identified this problem as a bug in the MS SQL Server driver. If you go to the ODBC Administrator on your server machine and click on drivers the version of your SQL Server driver is likely to be 2000.80.194.00 (if it is not please let us know).

The solution is to upgrade your SQL Server ODBC driver. The version in MDAC 2.7 appears to work OK. Version 2000.81.9030.04 of the SQL Server ODBC driver is also OK.

Why do I get "[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile())"

It is a resource issue when running a lot of processes (e.g. MultiProcess in the OOB Server) to the MS SQL Server ODBC driver issue. Switch to TCP/IP instead of named pipes in the MS SQL Server ODBC DSN and the problem will go away.

Why do I get an Access Violation in MS SQL Server ODBC Driver?

We reported the following issue on microsoft.public.sqlserver.odbc:

Using MS SQL Server ODBC driver version 3000.81.9030.04. Create a table and deny select permission to a specific user From odbctest do a full connect with ODBC 2.0 checked Do an SQLPrepare(select * from table) Do an SQLNumResultCols - this fails with permission denied Do an SQLExecute and you get an exception in odbctest

"The instruction at "0x119c8fcc" referenced memory at
"0x0170e000". The memory could not be written"

Better exception data from my own application instead of odbctest says:

Exception Address: 0X1F9C8F22
Number of Parameters: 2
Exception Record: 0X00000000
Exception Information (RW Flag) : Write
Exception Information (Virtual Address) : 0X0162E000

Module: C:\WINNT\System32\SQLSRV32.dll, Section: 01,
Offset:00007F22
Call Stack:
Address Frame
1F9C8F22 0120F854 SQLNumResultCols+3EB
1F9EF7E4 0120F8B8 BCP_init+5D2
1F9F1737 0120F8CC SQLNativeSqlW+637
1F9F47DA 0120F900 SQLExecute+1E1
1F7CD793 0120F91C SQLExecute+D1
004227CC 0120F988 0001:000217CC
C:\WINNT\System32\esoobserver.exe
00419A85 0120FF84 0001:00018A85
C:\WINNT\System32\esoobserver.exe
0042F218 0120FFB8 0001:0002E218
C:\WINNT\System32\esoobserver.exe
77F04EDE 0120FFEC lstrcmpiW+BE
No deeper stack frame

Brannon Jones, MDAC Developer said he had reproduced it and submitted a bug.

We have also seen it in version 2000.85.1025.00 of the MS ODBC driver.

You might be able to identify this problem from the entry the OOB Server puts into the application event log or the esoob.exception file in LogDir. An example follows:

Current OOB Flags: 0X112
OS: Major 5, Minor 0, Build 2195, Service Pack 4
PageSize: 4096, AllocationG: 65536, MinAppA: 00010000, MaxAppA: 7FFEFFFF
Active Processor Mask: 1, Number of Processors: 1
Username: XXXXX
Computer Name: YYYYY
cwd: C:\WINNT\system32
System Dir: C:\WINNT\system32
Windows Dir: C:\WINNT
Exception Code: 0XC0000005 (ACCESS_VIOLATION)
Exception Flags: 0
Exception Address: 0X0176136B
Number of Parameters: 2
Exception Record: 0X00000000
Exception Information (RW Flag) : Write
Exception Information (Virtual Address) : 0X01C0D000
exception_filter() at 0041C7B0
RPCAllocHandle() at 0042EE98
Last RPC: Name=sql_execute, Ordinal=91
Current Thread : 2060
Module: C:\WINNT\System32\SQLSRV32.dll, Section: 01, Offset:0000036B
Thread List:
in_use, threadid, threadh, socket, client_ip, dsn,start_time
0 340 0x00000000 1744 10.10.10.5 - Tue Sep 14 13:25:11 2004

Appendix A: Resources

Copyright © 1993-2005 by Easysoft Limited.