Name

Win32::SqlServer – Access Microsoft SQL Server from Perl through OLE DB.

use Win32::SqlServer;
use Win32::SqlServer qw([explicit_name ...] [:DEFAULT] [:cmdstates] [:consts]
                        [:datetime] [:directions] [:providers] [:resultstyles]
                        [:returns] [:routines] [:rowstyles]);

explicit_name is any importable routine or enumeration constant. For a complete list of what is importable, including description of export tags, please see the section Exported Names and Export Tags.

Contents

Overview and Introduction

Win32::SqlServer is a module that permits you to access Microsoft SQL Server from Perl with full access to all (well, almost) features of MS SQL Server using OLE DB as the underlying API. Win32::SqlServer has a high-level interface that permits you to submit an SQL statement or call a stored procedure and retrieve the result in one single function call. There are several options that you can use to specify the structure of the result set(s) – arrays of rows that are hashes, arrays or scalars; a hash keyed by the primary key of the result set; multiple result sets can be flattened out or you can get an array of result sets. There are also options to retrieve information about the columns in the result set(s).

Win32::SqlServer is intended to be best choice for Perl programmers who need to access MS SQL Server from Windows, but who have no requirements for portability. With no generic layer in between, you can expect good performance. Maybe more important is that the interface throughout is tailored to the data types and features of SQL Server. A prime example is error diagnostics: with Win32::SqlServer you get full access to the error information from SQL Server.

A terminology note: method is a popular word in object-oriented terminology. But being of the old school, having learnt the object-oriented paradigm though Simula, I prefer to use functions or simply routines, and those are the words you will mainly see in this document.

Note on Documented vs. Undocumented

While this a long and extensive manual, you will find even more routines and properties if you read the source code. Given the nature of Perl, these will be accessible to your script if you like. However, any use of undocumented routines is entirely unsupported, and particularly I feel no obligation to maintain backwards compatibility. Such routines may be dropped, renamed or altered between different versions of Win32::SqlServer. Thus stay away, and if you don't: you have been warned!

Structure of this Document

This section gives a high-level overview of Win32::SqlServer for the impatient. I first detail what platforms you can expect this module to run on. You get a list of the functions with their parameters without any details and a quick list of object properties. I introduce the options for error handling with Win32::SqlServer. The introductory section closes with a couple of quick examples demonstrating how to access data with Win32::SqlServer's high-level functions.

Next chapter discusses how Win32::SqlServer represents the SQL Server data types in Perl. This is followed by descriptions of the functions in Win32::SqlServer. These are split up over no less than six chapters. Connection functions, High-level query functions. Mid-level query functions, OpenSqlFilestream(), Character-set conversion routines and Utility routines. After the function descriptions, there is a section that describes all properties of the Win32::SqlServer object. Next chapter is on the important topic of Error Handling, an area where Win32::SqlServer offers quite a few knobs.

Then there is a chapter of Miscellaneous minor topics: exported identifiers and export tags, how to use Query Notification with Win32::SqlServer, some information about threaded scripts and the inevitable tale of sorrow about known bugs and restrictions. Finally, you find acknowledgements and author information.

Prerequisites and Supported Platforms

Hardware
x86 and AMD64 are supported.
 
Perl
The module includes the statement require 5.012;. Thus, you need at least Perl 5.12. However, beware that the lowest Perl version I have tested with (and provide binaries for) is Perl 5.24.
 
Operating Systems
Minimum level is Windows 7 SP1 (client O/S) and Windows 2008 R2 SP1 (server O/S). For versions before Windows 10, you need to have the update in KB2999226 installed.
 
OLE DB Provider
To connect to SQL Server you need a OLE DB Provider for SQL Server. There are several of them, or at least there are several names (they can been seen as different versions of the same provider). They are here listed in reverse order of release:
 
SQL Server
All versions from SQL Server 2000 and on are supported. Azure SQL Database and Azure Managed Instance should work; however, I have conducted minimal or no testing against these platforms.
 
Compiler
If you want to compile Win32::SqlServer on your own, you need Visual Studio 2017 or later. You will also need the SDK file for Microsoft OLE DB Driver for SQL Server. (Note that the SDK is not installed by default when you install the provider.) If you use any other compiler than Visual C++, feel free to try. I have not tested that, nor do I support it. If you have downloaded a binary, you do not need any compiler.
 
Binary Support

To run Win32::SqlServer on platforms not included above, you can use older versions as follows:

Finally, a note about using UTF-8 as you ANSI code page. (This is a setting in Windows 10, which is still labeled as "beta", although it has been there for a few years now). If you have setting enabled, you need to observe these prerequisites:

These limitations are not set by Win32::SqlServer itself, but are due to the capabilities of the OLE DB provider.

Summary of Functions

The functions of Win32::SqlServer fall into five groups: 1) Connection routines. 2) High-level query functions. 3) Mid-level query functions 4) Conversion routines. 5) Utility routines. Note that many of the parameters are optional. For the sake of simplicity, I have not indicated this in the list below. Please see the full function descriptions for this.

Connection Routines

$sqlsrv = Win32::SqlServer::sql_init($server, $user, $pw, $database, $provider);
Creates a Win32::SqlServer object and connects to the specified server and database.
 
$sqlsrv = Win32::SqlServer->new();
Creates an unconnected Win32::SqlServer object.
 
$sqlsrv->setloginproperty($property, $value);
Sets a login property such as server, user, network packet size etc.
 
$stats = $sqlsrv->connect();
Connects to SQL Server with the login information set through setloginproperty().
 
$sqlsrv->disconnect();
Disconnects from SQL Server.
 
$ret = $sqlsrv->isconnected();
Reports whether you are connected to SQL Server.

High-level Query Functions

$result = $sqlsrv->sql($batch, \@unnamed, \%named,
                       $colinfostyle, $rowstyle, $resultstyle, \@keys);
Passes a single query batch to SQL Server and returns a (reference to) one or more result sets. The batch may have parameters marked with ? or T-SQL style @variables with values passed in \@unnamed and/or \%named. The parameters $colinfostyle, $resultstyle, $rowstyle and \@keys control the structure of the returned data.
 
$result = $sqlsrv->sql_one($batch, \@unnamed, \%named, $rowstyle);
Like sql(), but has a built-in assertion that $batch should return a single row, and croaks if it does not.
 
$result = $sqlsrv->sql_sp($SP, \$retval, \@unnamed, \%named,
                          $colinfostyle, $rowstyle, $resultstyle, \@keys);
Calls the stored procedure $SP, else similar to sql(). The return value from $SP is returned in $retval.
 
$sqlsrv->sql_insert($table, \%data);
Inserts the data in \%data into the table $table, using the keys in \%data as column names.

Mid-level Query Access

The high-level routines are implemented in Perl, and they use the mid-level functions implemented in C++ to access OLE DB. Occasionally, you may find that they give you more degrees of freedom.

$sqlsrv->initbatch($cmdtext);
Initiates a command batch by setting the command text. When you use the mid-level routines, you must always start with this function.
 
$status = $sqlsrv->enterparameter($typename, $maxlen, $paramname,
                                  $isinput, $isoutput, $value,
                                  $prec, $scale, $typeinfo);
Describes a parameter in the command batch and passes a value for the parameter. You must call enterparameter() once for each parameter in the command batch.
 
$status = $sqlsrv->definetablecolumn($tblname, $colname, $nameoftype,
                                     $maxlen, $prec, $scale,
                                     $usedefault, $typeinfo);
Describes a column for a table-valued parameter. When you pass a table as a parameter, you need to call definetablecolumn() once for each column in the table.
 
$status = $sqlsrv->inserttableparam($tblname, $inputref);
Inserts a single row into a table-valued parameter. Call it once for every row you want to pass.
 
$status = $sqlsrv->executebatch(\$rows_affected);
Executes the command batch you defined with initbatch(), but does not make any result set active.
 
$more = $sqlsrv->nextresultset(\$rows_affected);
Make the next result set active. Returns a false value if there are no more result sets. You must call this function at least once for the batch.
 
$more = $sqlsrv->nextrow($hashref, $arrayref);
Retrieves the next row and places the data in %$hashref and @$arrayref. Returns false, if there are no more rows in the result set.
 
$sqlsrv->getcolumninfo($hashref, $arrayref);
Returns information in %$hashref and @$arrayref about the columns in the current result set.
 
$result = $sqlsrv->get_result_sets($colinfostyle, $rowstyle, $resultstyle, \@keys);
Rather than iterating over nextresultset() and nextrow(), you can call get_result_sets() to get all results in one go.
 
$sqlsrv->getoutputparams($hashref, $arrayref);
Retrieves the value of all output parameters (including the return value from a stored procedure) and puts the data into %$hashref and @$arrayref. You cannot call getoutputparams() until you have retrieved all result sets and all rows.
 
$sqlsrv->cancelbatch();
Cancels the current batch, and makes Win32::SqlServer ready to accept a call to initbatch().
 
$sqlsrv->cancelresultset();
Moves to the end of the current result set. You still need to call nextresultset() to move to the next result set.

OpenSqlFilestream()

$fh = $sqlsrv->OpenSqlFilestream($path, $access, $context [, $options [, $alloclen]])
Makes a FILESTREAM column available for access through Win32::API.

Character-set Conversion Routines

Csveat: These routines are mainly intended for ANSI/OEM conversion if you really have data in the OEM code page. Be careful to read more closely about these functions before you start using them.

$sqlsrv->sql_set_conversion($client_cs, $server_cs, $direction);
Sets up a conversion from one code page to another.
 
$sqlsrv->sql_unset_conversion($direction);
Removes an active code-page conversion.
 
$sqlsrv->codepage_convert($string, $from_cp, $to_cp);
Converts $string from one code page to another.

Utility Routines

$ret = $sqlsrv->sql_has_errors($keepinfo);
Returns whether the command batch or stored procedure generated an error. Requires that you have set the ErrInfo property SaveMessages.
 
$cmdtext = $sqlsrv->sql_get_command_text();
Returns the text for the current command; to be used from a message handler.
 
$quotedstring = $sqlsrv->sql_string($string);
Quotes $string, doubling any embedded quote characters in $string and returns the quoted string.
 
$sqlsrv->sql_begin_trans();
$sqlsrv->sql_commit();
$sqlsrv->sql_rollback();
Issues BEGIN/COMMIT/ROLLBACK TRANSACTION.

Summary of Properties

Property names are case-sensitive. Win32::SqlServer is a tied hash, and if you refer to an undefined property Win32::SqlServer croaks.

AutoConnect
When it has a true value, Win32::SqlServer will connect and disconnect automatically, if you have not connected explicitly.
 
BinaryAsStr
Controls how binary data in SQL Server is represented in Perl, as hex strings (the default) or binary values.
 
CommandTimeout
How long Win32::SqlServer should wait before timing out on a command. The default is 0, which means wait forever.
codepages
A cache with the code page for each database hitherto visited. Used when passing and receiving char and varchar data.
CurrentDB
Holds the name of the current database (which may change if a batch issues a USE command). This is a read-only property.
 
DateFormat
Controls how datetime values are formatted when DatetimeOption has the value DATETIME_STRFMT.
 
DatetimeOption
Controls how date and time values returned from SQL Server are represented in Perl. Default is ISO format, YYYY-MM-DD hh:mm:ss.fffffff ±hh:mm.
 
DecimalAsStr
Controls how decimal and money values are returned from SQL Server are represented in Perl. Floating point (the default) or as strings.
 
ErrInfo
A complex structure that controls how Win32::SqlServer reacts on (error) messages from SQL error. See next section for an introduction.
 
LogHandle
A file handle to which Win32::SqlServer writes the commands it passes to SQL Server.
 
MsecFormat
Controls how the milliseconds portion of a datetime value is formatted when DatetimeOption has the value DATETIME_STRFMT.
 
MsgHandler
A reference to a Perl sub that is invoked when SQL Server generates a message (error or informational). Also invoked for some errors from the OLE DB provider and Win32::SqlServer itself. Win32::SqlServer comes with a default message handler, Win32::SqlServer::sql_message_handler.
 
NoExec
Prevents Win32::SqlServer from sending any command batches to SQL Server.
 
procs
A hash with parameter profiles for stored procedures, used by sql_sp() as a cache.
 
PropsDebug
When set, Win32::SqlServer will dump login properties and their status, in case of an unhandled error at login.
 
Provider
Selects which OLE DB provider to use, MSOLEDBSQL, SQLNCLI11, SQLNCLI10, SQLNCLI or SQLOLEDB. By default, Win32::SqlServer uses the most recent provider that is installed. That is, if MSOLEDBSQL is available, this is the default.
 
QueryNotification
A hash that specifies parameters for query notification.
 
RowsAtATime
Controls how many rows at a time nextrow() requests from the OLE DB provider. The default is 100.
 
SQL_version
Returns the version number for the SQL Server you are connected to. This is a read-only property.
 
tables
A hash with table definitions, used as a cache by sql_insert().
 
tabletypes
A hash with definition of table types, used as a cache by the high-level routines for table-valued parameters.
 
TZOption
Sets a default time-zone offset for the datetimeoffset data type.
 
UserData
An entry where you can save your own data in a Win32::SqlServer object.
 
usertypes
A hash with the definition of user-defined types, used as a cache by sql() and sql_one().

Error Handling

When you call Win32::SqlServer, (error) messages can come from three different sources:

The prime source for messages is SQL Server, but Win32::SqlServer fits provider errors and its own errors and warnings into the same mould. (Errors that are plain abuse of the Perl interface or internal errors result in the traditional croak.)

Messages can be informational, warnings or errors, as determined by the severity level. If the level is 11 or higher, the message is an error condition, else not.

The default behaviour of Win32::SqlServer is to print all messages. And if the message is an error condition, Win32::SqlServer aborts execution with croak. You can trap this through the traditional exception handling in Perl by calling Win32::SqlServer routines with eval and then check $@.

However, the full story is far more sophisticated. When there is a message from SQL Server, Win32::SqlServer invokes the message handler defined by the MsgHandler property. That is, you can establish your own callback routine. Now, you don't have to do this very often, because Win32::SqlServer comes with a built-in default handler, sql_message_handler(), and this handler is highly configurable through the ErrInfo property. Thus, by setting ErrInfo elements, you can suppress printing, prevent Win32::SqlServer to abort on normal users errors (severity ≤ 16) but still abort on fatal errors such as running out of disk space. You can control printing and abortion by severity level or by individual message numbers.

By setting the SaveMessages element, you can specify that you want messages passed back to you in the ErrInfo.Messages array. This is necessary, if you turn off the default abortion behaviour and you want to check for errors with the function sql_has_errors().

Say you want to do all error handling yourself, and you do not want sql_message_handler() to print any messages and never abort execution. Here is how you would do this:

$sqlsrv->{ErrInfo}{MaxSeverity}  = 19;
$sqlsrv->{ErrInfo}{PrintLines}   = 19;
$sqlsrv->{ErrInfo}{PrintMsg}     = 19;
$sqlsrv->{ErrInfo}{PrintText}    = 19;
$sqlsrv->{ErrInfo}{SaveMessages} = 1;

Then to check for errors when you submit a command batch, you would do something like:

delete $sqlsrv->{ErrInfo}{Messages};
$sqlsrv->sql('blafs');
if ($sqlsrv->sql_has_errors()) {
   die "there was an error\n";
}

There is a full chapter devoted to error handling with the full details on how you can configure sql_message_handler(), or set up your own message handler.. This chapter also includes two example scripts on how to load SQL files with Win32::SqlServer demonstrating the use of ErrInfo.

Quick Examples

The intention of these examples is to give you a taste of how you work with the connection routines and the high-level query functions in Win32::SqlServer. (The chapter on mid-level query functions includes some examples on those functions.) I am not giving a full explanation of all details, but I'm leaving that for the description of the functions.

The most of these examples runs in the Northwind database which you can download here.

Example 1

Here we run a parameterised query and get back a result set in the default format. The result set is an array. Each row is then a hash, keyed by column names.

use strict;
use Win32::SqlServer;

# Create object and login in one step to local server with
# integrated security.
my $sqlsrv = sql_init(undef, undef, undef, 'Northwind');

# Our SQL statement. The ? are placeholders for the parameters.
my $stmnt = <<SQLEND;
SELECT OrderID, OrderDate, Freight
FROM   Orders
WHERE  CustomerID = ?
  AND  OrderDate > ?
 ORDER BY OrderID
SQLEND

# Run query. The return value is a reference to an array. The
# second parameter is an arrayref of parameter descriptions.
my $result = $sqlsrv->sql($stmnt, [['nchar(5)', 'BERGS'],
                                   ['datetime', '1997-06-01']]);

# Print results. Each array entry is a hash reference.
foreach my $row (@$result) {
   print "OrderID: $$row{OrderID}   ";
   print "OrderDate: " . substr($$row{OrderDate}, 0, 10), "   ";
   print "Freight: $$row{Freight}\n";
}

Example 2

Same query, but this time the parameters are named, and we get the columns into an array.

use strict;
use Win32::SqlServer;

# Create object, set database and then connect with integrated security.
my $sqlsrv = Win32::SqlServer->new;
$sqlsrv->setloginproperty('Database', 'Northwind');
$sqlsrv->connect();

# Our SQL statement. @ needs to be escaped, because this is an SQL @.
my $stmnt = <<SQLEND;
SELECT OrderID, OrderDate, Freight
FROM   Orders
WHERE  CustomerID = \@custid
  AND  OrderDate  > \@fromdate
ORDER BY OrderID
SQLEND

# This time we receive the result set directly into an array. Since
# we use named parameters, the second parameter is now a hash reference.
my @result = $sqlsrv->sql($stmnt, {'@custid'   => ['nchar(5)', 'BERGS'],
                                   '@fromdate' => ['datetime', '1997-06-01']},
                          Win32::SqlServer::LIST);

# Print results. Each array entry is an array reference.
foreach my $row (@result) {
   print "OrderID: $$row[0]   ";
   print "OrderDate: " . substr($$row[1], 0, 10), "   ";
   print "Freight: $$row[2]\n";
} 

Example 3

Yet a variation of the above. Here we use a colinfo style to retrieve the column names in the query. A typical case when you want to do this is when you write data for a file, for a query passed to you, and you don't want to make any assumptions about the columns in the query.

use strict;
use Win32::SqlServer;

# Create object, set database and then connect with integrated security.
my $sqlsrv = Win32::SqlServer->new;
$sqlsrv->setloginproperty('Database', 'Northwind');
$sqlsrv->connect();

# Our SQL statement. Rather than escaping @ as above, we can put SQLEND in
# single qoutes.
my $stmnt = <<'SQLEND';
SELECT OrderID, OrderDate, Freight
FROM   Orders
WHERE  CustomerID = @custid
  AND  OrderDate  > @fromdate
ORDER BY OrderID
SQLEND

# Again we receive the result set directly into an array. We pretend
# that we don't know the name of the columns from the query, and use a
# colinfo style to get the column names.
my @result = $sqlsrv->sql($stmnt, {'@custid'   => ['nchar(5)', 'BERGS'],
                                   '@fromdate' => ['datetime', '1997-06-01']},
                          Win32::SqlServer::LIST,
                          Win32::SqlServer::COLINFO_NAMES);

# First print a header with the column names.
print join ("\t", @{$result[0]}), "\n";

# The print the values.
foreach my $row (@result[1..$#result]) {
   print join("\t", @$row), "\n";
}

Example 4

Here is a query that returns a single row, and we can then receive this row directly into a hash.

use strict;
use Win32::SqlServer qw (SINGLEROW);

# Log in to the remote server with SQL authentication.
my $sqlsrv = new Win32::SqlServer;
$sqlsrv->setloginproperty('Server',   'SVR1');
$sqlsrv->setloginproperty('Username', 'frits');
$sqlsrv->setloginproperty('Password', 'PaSsvvord');
$sqlsrv->setloginproperty('Database', 'Northwind');
$sqlsrv->connect();

# Our SQL statement. This time the order id is the parameter.
my $stmnt = <<SQLEND;
SELECT CustomerID, OrderDate, Freight
FROM   Orders
WHERE  OrderID = \@orderid
SQLEND

# Run query. Since the result is a single row, we opt to receive this row
# directly in a hash.
my %order = $sqlsrv->sql($stmnt, {orderid => ['int', 10987]}, SINGLEROW);

# Print results.
print "CustomerID: $order{CustomerID}   ";
print "OrderDate: " . substr($order{OrderDate}, 0, 10), "   ";
print "Freight: $order{Freight}\n";

Example 5

Many times when you have a single-row query, you prefer to get the results back in output parameters. Here is the query in Example 4 again, now with output parameters instead of a result set.

use strict;
use Win32::SqlServer;

# Log in to the remote server with SQL authentication.
my $sqlsrv = new Win32::SqlServer;
$sqlsrv->setloginproperty('Server',   'SVR1');
$sqlsrv->setloginproperty('Username', 'frits');
$sqlsrv->setloginproperty('Password', 'PaSsvvord');
$sqlsrv->setloginproperty('Database', 'Northwind');
$sqlsrv->connect();

# Our SQL statement. The order id is the input parameter and then there
# are three output parameters. Again we put SQLEND in single quotes to
# be relieved from having to escape all @.
my $stmnt = <<'SQLEND';
SELECT @custid = CustomerID, @orderdate = OrderDate, @freight = Freight
FROM   Orders
WHERE  OrderID = @orderid
SQLEND

# Run query. The output parameters must be passed by reference. (And that
# is how Win32::SqlServer knows that they are output parameters.)
my($custid, $orderdate, $freight);
$sqlsrv->sql($stmnt, {orderid   => ['int',      10987],
                      custid    => ['nchar(5)', \$custid],
                      orderdate => ['datetime', \$orderdate],
                      freight   => ['money',    \$freight]});

# Print results.
print "CustomerID: $custid   ";
print "OrderDate: " . substr($orderdate, 0, 10), "   ";
print "Freight: $freight\n";

 Example 6

Here we list all customer names in a one-column result set. Then we can receive this column as a scalar value.

use strict;
use Win32::SqlServer qw(SCALAR);

# Log in to local server with Windows authentication.
my $sqlsrv = new Win32::SqlServer;
$sqlsrv->setloginproperty('Database', 'Northwind');
$sqlsrv->connect();

# Our SQL statement. This time there is no parameter.
my $stmnt = <<SQLEND;
SELECT CompanyName
FROM   Customers
ORDER  BY CompanyName
SQLEND

# Run query. We get a reference to an array with scalar values.
my $result = $sqlsrv->sql($stmnt, SCALAR);

# Print results.
foreach my $name (@$result) {
   print "$name\n";
}

Example 7

In this example, we list the number of orders and total freight per customer. This time we receive a result set which is keyed by the customer id.

use strict;
use Win32::SqlServer qw (:rowstyles :resultstyles); # To get HASH and KEYED.

# Log in to remote server with SQL authentication.
my $sqlsrv = Win32::SqlServer::sql_init(
                    'SVR1', 'frits', 'PaSsvvord', 'Northwind');

# Our SQL statement.
my $stmnt = <<SQLEND;
SELECT CustomerID, cnt = COUNT(*), totfreight = SUM(Freight)
FROM   Orders
GROUP  BY CustomerID
SQLEND

# KEYED mandates the result set to be keyed by the data, in this case
# the CustomerID column.
my %customers = $sqlsrv->sql($stmnt, HASH, KEYED, ['CustomerID']);

# Print results. Each hash entry is a hash reference keyed by column name.
foreach my $cust (sort keys %customers) {
   print "CustomerID: $cust   ";
   print "Order count: $customers{$cust}{'cnt'}   ";
   print "Total freight: $customers{$cust}{'totfreight'}\n";
}

Example 8

Here is an example of running a stored procedure with a single parameter. This is also an example on how you can handle a query batch that returns more than one result set.

use strict;
use Win32::SqlServer;

# Log in to local server with SQL authentication. Use tempdb.
my $sqlsrv = Win32::SqlServer::sql_init();

# Run sp_helpdb for tempdb. Note that here we don't specify the data type
# for the parameter; sql_sp looks it up. sp_helpdb returns two result sets,
# with different structures. We therefore get the results as an array of
# result sets. Each result set is an array of rows, and rows are hashes,
# keyed by column name.
my $result = $sqlsrv->sql_sp('sp_helpdb', ['tempdb'],
                              Win32::SqlServer::MULTISET);

# Print results for first result set. For sp_helpdb this is a single row.
foreach my $col (keys %{$$result[0][0]}) {
   print "$col: $$result[0][0]{$col}\n";
}

# And print results for the second result set. This is always two rows,
# more if you have secondary data files.
foreach my $file (@{$$result[1]}) {
   foreach my $col (keys %$file) {
      my $value = $$file{$col};
      $value = '<NULL>' if not defined $value;
      print "$col: $value\n";
   }
   print '-' x 50 . "\n";
}

Example 9

In this last example, we call a procedure with an output parameter that we retrieve. The example also shows how you can use named and unnamed parameters.

use strict;
use Win32::SqlServer;

# Log in to local server with SQL authentication. Stay in tempdb.
my $sqlsrv = new Win32::SqlServer;
$sqlsrv->connect();

# First, create a temporary procedure, to use in the example.
$sqlsrv->sql(<<'SQLEND');
CREATE PROCEDURE #ordercnt @custid nchar(5),
                           @fromdate datetime,
                           @no_of_orders int OUTPUT AS
SELECT @no_of_orders = COUNT(*)
FROM   Northwind..Orders
WHERE  CustomerID = @custid
   AND OrderDate > @fromdate
SQLEND

# Run this procedure. We can pass positional parameters in an array. For the
# output parameter we pass a reference to a scalar that will receive the value.
my $no_of_orders;
$sqlsrv->sql_sp('#ordercnt', ['BERGS', '1997-06-01', \$no_of_orders]);

print "There are $no_of_orders orders for BERGS later than 1997-06-01.\n";

# We can also use named parameters, by using a hash. The @ is implicit.
$sqlsrv->sql_sp('#ordercnt', {custid => 'ALFKI',
                              fromdate => '1997-06-01',
                              no_of_orders => \$no_of_orders});
print "There are $no_of_orders orders for ALFKI later than 1997-06-01.\n";

# We can even mix named and unnamed. And specify @ if we feel like.
$sqlsrv->sql_sp('#ordercnt', ['VINET', '1997-06-01'],
                             {'@no_of_orders' => \$no_of_orders});
print "There are $no_of_orders orders for VINET later than 1997-06-01.\n";

More Examples

There are more examples this document. Under the description of sql_sp(), there is one more example of using this function. The section Working with table parameters, has a whole slew of examples to show how to use table-valued parameters. The chapter of the mid-level query functions closes with three examples of using these functions. And the chapter on error handling has two examples of loading stored procedures with Win32::SqlServer.

Data-Type Representation

Introduction

Note: this chapter appears here, because, well I had to place it somewhere. You may prefer to first read the function descriptions, and then come back here when you need to know how Win32::SqlServer handles a certain data type.

At a glance, Perl may appear to be an untyped language, but in fact it is dynamically typed, and a variable may change data types several time during its existence. These are the four the main scalar data types in Perl:

(Well, depending on you look at the glass, you may be able to find more data types. But in the context of Win32::SqlServer, these are the ones we have to consider.)

SQL on the other hand has a multitude of data types, and this chapter is about how Win32::SqlServer maps the SQL data types to Perl values. In some cases, there is only a single mapping. In other cases, there are Win32::SqlServer properties you can use to control the mapping.

Mapping occurs on both input and output. Input to SQL Server happens when you pass a Perl value as a parameter to a stored procedure or parameterised statement with any of the routines sql(), sql_sp(), sql_one(), sql_insert() or enterparameter(). (There is also the case when you pass a complete SQL string where you have interpolated Perl values, but in this case the mapping happens outside Win32::SqlServer.)

Output from SQL Server can happen in three different ways: 1) A column in a result set. 2) An output parameter from a stored procedure. 3) The return value from a stored procedure or a scalar user-defined function. The mapping is the same in all three cases.

When converting input data from Perl to SQL Server, this conversion may fail because the Perl value cannot be mapped to a value of the SQL data type. In this case, Win32::SqlServer issues a warning message through the current message handler. If you attempt to execute the command batch, this results in an error. (You do not get an error directly for the conversion failure, to permit you get information of conversion failures for all input values.)

NULL

SQL's special value NULL always maps to Perl's special value undef, even though they don't have exactly the same semantics.

bit, tinyint, smallint, int

Output

You get an integer number in Perl from an SQL value of any these types.

Input

The Perl value is auto-converted to an integer number, according to the standard Perl rules. Thus, a string like '9F' will be interpreted as 9 and a string like 'ABC' as 0 (and both these strings will yield Perl's standard warning Argument "%s" isn't number in subroutine entry if you are running with -w.)

bigint

bigint works like the other integer data types, with one exception: if you have n 32-bit Perl with 32-bit integers, bigint is handled in the same manner as decimal and money, see below.

real, float

Output

You get a floating-point number in Perl.

Input

Input values are auto-converted to floating-point according to the standard rules in Perl.

decimal, numeric, (small)money

What is said here, also applies to the bigint data type if you have 32-bit Perl with 32-bit integers.

Output

You get back the value as a floating-point value or a string, depending on the boolean property DecimalAsStr. As the name indicates, when this property has a false value, you get a floating-point value, else a string. The default for DecimalAsStr is 0.

With a floating-point value, you may lose precision, whereas with a string value the exact number is retained. Note however, that if you then go on to use the number in a numeric expression in Perl, the string will be auto-converted to floating-point anyway. Receiving the number as a string, is mainly useful when:

Input

On input, Win32::SqlServer looks at the Perl data type only and does not consider DecimalAsStr.

If the value is not a string, Win32::SqlServer handles it as a floating-point number, and converts the value to the target type with the OLE DB interface IDataConvert. If the target type cannot fit the Perl value, the conversion fails and Win32::SqlServer issues a warning message through the current message handler. .

If the value is a string, the string is converted to the target data type, using IDataConvert. This interface is different from Perl, and the conversion fails if the string cannot be converted to the target type. (Curiously though, while a string like '9E' gives an error for decimal and money, for bigint IDataConvert converts this value without any error to – 0.)

As for the format of the string, well, the short story is: use decimal point as delimiter and no thousands separators. The longer story is that for bigint and decimal/numeric nothing else works. Whereas for money and smallmoney, IDataConvert appears to look at the regional settings, but if a string has only one point and no other separator this point is taken as a decimal point, even if point is a thousands separator for the current regional setting. Thus with regional settings set to Spanish (Spain), "111.999" is the decimal number 111.999 (one hundred and eleven point 999), where as "1.111.999" is 1111999 (one million one hundred eleven thousand) and "€ 111.999" is 111999 (one hundred eleven thousand). You are now warned.

Date and time data types

Output

The property DatetimeOption determines how Win32::SqlServer converts the value returned by SQL Server. The property TZOffset can be used to shift the time-zone of a datetimeoffset value.

Note: if you any of the providers SQLOLEDB or SQLNCLI, SQL Server will return values of the data types introduced in SQL 2008 (date, time, datetime2 and datetimefoffset) as strings in ISO format, and the properties DatetimeOption and TZOffset will never apply. They only apply to the new data types if you use SQLNCLI10 or later. DatetimeOption always applies to datetime and smalldatetime values.

The property DatetimeOption can have five different values:

DATETIME_ISO
You get a string on the form YYYY-MM-DD hh:mm:ss.fffffff ±hh.mm. You only get the parts and decimals that are applicable to the data type in question. So for date you get YYYY-MM-DD. For time(0) you get hh:mm:ss, and for datetime2(2) you get YYYY-MM-DD hh:mm:ss.ff. Only for datetimeoffset(7) you get the full YYYY-MM-DD hh:mm:ss.fffffff ±hh.mm and only if the property TZOffset has not been set, see below.
 
DATETIME_REGIONAL
You get a string that is formatted according to the user's regional settings. For date values, the string will include the date portion only. Likewise for time values, the string will only include the time portion. For datetime, smalldatetime, datetime2 and datetimeoffset, the time portion is not included if it's 00:00:00, or so it seems. (Win32::SqlServer uses the Automation routine VarBstrFromDate for the conversion and really has no control over this.) Note that with regional settings, fractions are never included in the output. For datetimeoffset values, the time-zone offset is included in the string, unless TZOffset is set, see below. The time-zone offset is added by Win32::SqlServer itself, and is always formatted in the one and same way.
 
DATETIME_HASH
You get a reference to hash with up to nine keys: Year, Month, Day, Hour, Minute, Second, Fraction, TZHour and TZMinute, each containing that part of the datetime value. For date, only Year, Month and Day are present in the hash. For time, the only keys to appear are Hour, Minute, Second and Fraction. For datetime, datetime2 and smalldatetime, the first seven appear. Only for datetimeoffset, all nine are present, and only if the property TZOffset has not been set.

Note that the value for the Fraction key is in milliseconds. That is, a fractional value of 123456700 nanoseconds appears as 123.4567.

The sign of TZMinute follows the sign of TZHour. Thus, if the value returned from SQL Server is 2007-11-11 20:11:21 -03:30, TZHour will be -3 and TZMinute is -30.

DATETIME_FLOAT
You get a floating-point number that represents the number of fractional days since 1899-12-30 00:00:00.000. This format can be useful if you need to pass the datetime value to COM methods that use this form to represent datetime values. (Note that down in SQL Server a float value of 0 converts to 1900-01-01 00:00:00.000.) Obviously, the time-zone offset for a datetimeoffset value will disappear with this representation. Beware that for dates before 1899-12-30, the values are discontiguous, since the sign applies only to the integer part. For instance, -2.25 is 1899-12-28 06:00 and not 1899-12-27 18:00 as a strict mathematical definition would give.
 
DATETIME_STRFMT
You get a string formatted according to the properties DateFormat and MsecFormat. The value of DateFormat is passed to the C run-time function strftime and controls how all portions of the datetime value is formatted, save the milliseconds (not supported by strftime). The value of MsecFormat is passed to sprintf and controls how the milliseconds are formatted. The default for DateFormat is %Y%m%d %H:%M:%S and for MsecFormat the default is .%3.3d. While you can use these properties to get about any format you like, beware that Win32::SqlServer will never consider this format on input.

For a complete listing of the available format codes, see a reference for strftime. Here is a sample of the codes that are likely to be the most useful:

%b, %BAbbreviated/full month name.
%cDate and time representation appropriate for locale.
%dDay of month as decimal number (01 – 31)
%H, %IHour in 24/12-hour format (00 – 23)/(01 - 12)
%mMonth as decimal number (01 – 12)
%MMinute as decimal number (00 – 59)
%pCurrent locale's A.M./P.M. indicator for 12-hour clock.
%SSecond as decimal number (00 – 59)
%y, %YYear without/with century, as decimal number.
%%Percent sign

Note: do not use codes that involve weekdays, or day number within the year, as Win32::SqlServer does not supply these values to strftime.

The main purpose of DATETIME_STRFMT is to provide compatibility with MSSQL::Sqllib and this format has not been enhanced for the new data types. Specifically, you will not get any time-zone offset with this format for datetimeoffset values.

These constants are not imported by default. To refer to them, you need to say e.g. Win32::SqlServer::DATETIME_ISO or import them when you use Win32::SqlServer, explicitly or with an export tag.

The TZOffset property

You can set this property to a time-zone offset on the format ±hh:mm. Win32::SqlServer will then shift the value returned from SQL Server to the time zone you have specified, and not include the time-zone offset in the value. That is, if the value returned from SQL Server is 2007-11-11 18:00:00 +01:00 and you have set TZOffset to -08:00, Win32::SqlServer will return 2007-11-11 09:00:00 for ISO format. {Year => 2007, Month => 11, Day => 11, Hour => 9, Minute => 0, Second => 0, Fraction => 0) for DATETIME_HASH and 39397.375 for DATETIME_FLOAT. The property is also in force for DATETIME_REGIONAL and DATETIME_STRFMT.

A second choice for TZOffset is the string "local". With this value, Win32::SqlServer will look up the current time-zone offset (on the client, not on the server) and use this offset. This lookup is performed every time, to permit for DST changes while the script is running.

Input

On input, Win32::SqlServer looks at the Perl data type only, and does not consider DatetimeOption. It does however consider the TZOffset property for datetimeoffset values. Please see note at the end if you are using the SQLOLEDB or SQLNCLI provider with the new date/time data types in SQL 2008.

string value
If the Perl value is a string, Win32::SqlServer performs these steps:
  1. Reads the string from the back, and if there is a time-zone offset on the form ±hh:mm, Win32::SqlServer saves this value and strips it from the string.
  2. Win32::SqlServer tries to interpret the string as an ISO-formatted date, according to the rules detailed below.
  3. If the ISO interpretation fails, Win32::SqlServer hands the string over to the Automation routine VarDateFromStr that will interpret the string according to regional settings. Note that this step is likely to fail if the string includes fractional seconds.
  4. If the string neither can be interpreted according to regional settings, the conversion fails and the current message handler is invoked. Thus, Win32::SqlServer never considers the format defined by DateFormat and MsecFormat.

When examining whether a string may be an ISO string, Win32::SqlServer considers these variations:

If you supply portions that do not apply to the data type in question, they are simply thrown away. That is, hours are truncated from date values, seconds are truncated from smalldatetime values and superfluous decimals are truncated from fractions. Note that this behaviour is different from SQL Server which rounds in the latter two cases.

If there was a time-zone offset in the string, Win32::SqlServer will supply that when it passes a datetimeoffset value, and ignore it for other data types. If there never was any time-zone offset in the string, Win32::SqlServer will use the time-zone specified in TZOffset. If neither TZOffset is set, Win32::SqlServer uses a default of +00:00, that is UTC.

Note: if you experiment, you may find more variations that are accepted. However, the only formats that are officially supported are those listed above, and those interpreted as regional settings by Windows. Anything else is occasional, and may yield a different result with a later version of Win32::SqlServer. This does not the least apply to two-digit years, I strongly recommend that you always use four-digit years.

hash reference
As a hash produced by Win32::SqlServer when DatetimeOption is DATETIME_HASH, see above. For all types but time, Year, Month and Day must be present in the hash, or else conversion will fail. For time, you must supply Hour and Minute. As on output, Fraction is in milliseconds, and to supply a fraction of 123456700 nanoseconds, you need to specify 123.4567. As on string input, parts or decimals not relevant to the type are always discarded and truncated.

You can supply TZHour and TZMinute for any type, but they will only be considered for datetimeoffset. It's legal to supply TZHour only, in which case TZMinute is assumed to be 0. The reverse is not permitted; if TZMinute is defined and TZHour is missing, this is an error. Note that the sign of TZMinute follows TZHour; to specify the time zone offset -03:30, set TZHour to -3 and TZMinute to -30.

If you do not supply a time-zone offset for datetimeoffset values, Win32::SqlServer will use the time zone defined by TZOffset, or +00:00 if TZOffset has not been set.

numeric value (integer or floating-point)
Number of (fractional) days since 1899-12-30, thus the reverse of DATETIME_FLOAT. For datetimeoffset values, Win32::SqlServer will use the time-zone offset defined by the TZOffset property, and if property is not defined, the time-zone offset will be +00:00.

Note: If you use the SQLOLEDB or SQLNCLI provider, the rules above apply only to datetime and smalldatetime. For the types date, time, datetime2 and datetimeoffset that are not supported by these providers, Win32::SqlServer will pass the value you send in as nvarchar, and this string will be interpreted by SQL Server. This means that strings formatted according to regional settings will not work, nor will hash references, nor will numeric values. Only ISO strings will work. And even with ISO strings, you may get different results with a legacy provider than with SQLNCLI10, since Win32::SqlServer has somewhat different conversion rules than SQL Server. (Truncation instead of rounding, using 1899-12-30 as the default date etc.)

nchar, nvarchar and ntext

For nvarchar(MAX) and ntext, see also large data types below.

Output

The SQL value is a placed in a Perl string which will always have the UTF-8 bit set, even if the string contains ASCII characters only. (But this will not matter to you, unless you use the value in some other XS extension.)

Note: if you are using the SQLOLEDB provider, be aware of that output parameters of the type nvachar(MAX) will be truncated to nvarchar(4000) by SQL Server.. You cannot call user-defined functions with a return value with this type from SQLOLEDB.

char, varchar and text

For varchar(MAX) and text, see also large data types below.

Output

A value in a result set is handled just like n(var)char data. That is, even if your script that is running on an computer with an ANSI code page or Latin-1, the value αβγ from a varchar column with a Greek collation, will come back to you as αβγ.

The same is almost true for output parameters. However they will always be coerced into the code page of the current database by SQL Server. Example: Your current database has the collation Greek_CI_AS (codepage 1253) and you call a stored procedure that returns the value αβγ; you will receive that value correctly. However, if you make a cross-database call to the same procedure from a database with the collation Latin1_General_CI_AS (codepage 1252), you will receive this value as aß?, because as the data passes through the Latin1 database, SQL Server will replace the Greek characters with fallback characters.

Notes on UTF-8 collations: SQL 2019 introduced UTF-8 collations. If you are connected with version 18.2.1 or later of the MSOLEDBSQL provider, SQL Server will return (var)char data as-is. For older providers without UTF-8 support, SQL Server will return the data as n(var)char as a fallback. This makes it largely transperant for Win32::SqlServer. However, I found that for output parameters, it is best to pass them as n(var)char to get everything to work smoothlessly and the high-level routines take care of this, with one caveat: I only do this if the provider is SQLNCLI11 or earlier, so if you are using the first release(s) of MSOLEDBSQL which I don't think has the UTF-8 support, you may experience some rough edges. (I have not tested with these older versions of that provider.)

If you are calling enterparameter() yourself, you are adviced to specify n(var)char as the data type for if you are using a low-level provider and your database has a UTF-8 collations.

If you are using SQLOLEDB, see Issue 7 in the list of known issues for a restriction with data from UTF-8 data.

Input

When you pass data to (var)char parameters, Win32::SqlServer will convert the Perl data to the code page of the current database. This permits you to insert a value such as αβγ in a database with a Greek collation, even if you are running from a computer with an ANSI code page of Latin-1. However, observe the following:

When you pass data to varchar columns in table-valued parameters, the situation is different. If you are defining the columns of the table parameter with definetablecolumn() directly, you are adviced to use n(var)char for (var)char columns that have a collation that is different from the code page of the database collation and/or the ANSI code page of the client. This is because while Win32::SqlServer turns off the AutoTranslate option in the OLE DB provider, the OLE DB does not respect this for data to table parameters. If you are using the the high-level routines, they take care of this for you, and net effect is that result is the same for input parameters minus the two observations above.

binary, varbinary, image

For varbinary(MAX) and image, see also large data types below.

Output

You always receive a binary value from SQL Server in a Perl string. However, the contents of this Perl string depend on the property BinaryAsStr. This property can have three different settings:

false
The string is the binary proper, which you can interpret with unpack, write to a binary file or do whatever you want to do with it.
 
Any true value but 'x'
You get a hex string with no leading 0x. The digits A-F are always in uppercase. This is the default.
 
'x'
A hex string preceded by 0x.

Input

The input value is always auto-converted into a string. How this string interpreted depends on the BinaryAsStr property.

If this property has a false value, the string is passed as-is as a binary value to SQL Server.

If BinaryAsStr has a true value (the default), the string must be a hex string, with or without leading 0x. Win32::SqlServer attempts to convert the hex string to a binary value. If the conversion fails, the current message handler is invoked.

uniqueidentifier

Output

Always a string in the traditional GUID representation surrounded by braces, for instance {902A1763-561D-4F66-85B1-D18ABE916FE0}.

Input

Must be a Perl string formatted as a GUID. Enclosing braces are permitted but not required. Other formats causes conversion to fail.

sql_variant

Output

On output the sql_variant value is handled as the underlying base type. Thus for a datetime value, Win32::SqlServer will look at DatetimeOption, for a binary value at BinaryAsStr etc. Note that if the underlying data type is char or varchar, the interpretation of the string depends on which OLE DB provider you are using. With SQLNCLI11 and earlier, Win32::SqlServer will assume that the code page of the string is the same as of the database collation, since the OLE DB provider does not return information about collation or code page for the value. With the MSOLEDBSQL provider, the OLE DB provider will always coerce the string into the code page of the client. (More precisely, this is a change that was introduced in version 18.4 of the MSOLEDBSQL provider.)

Input

On input, Win32::SqlServer only supports conversion to some possible base types for the SQL variant value, more precisely int, bigint, float, datetime, date, time, datetime2, datetimeoffset, varchar and nvarchar. The conversion depends on the Perl data type of the input value and to some extend on the database collation. Win32::SqlServer applies these rules in order:

hash reference
If the value is a reference to a hash, Win32::SqlServer examines if the hash is a datetime hash, and in such case passes the value to a date/time data type in SQL Server. If the SQL Server version is SQL 2008 or later, and the provider is SQLNCLI10 or later, Win32::SqlServer will select a type according to this scheme:

If the SQL Server version is SQL 2005 or earlier, or the provider is SQLNCLI or earlier, Win32::SqlServer passes the value as datetime, if the keys Year, Month and Day are present in the hash.

For any other hash reference, an incomplete datetime hash or something completely different, Win32::SqlServer will pass the value as a string with regular Perl string representation of a hash reference, e.g. HASH(0x01234abcd). If the required keys are present, but the values are incorrect, you may get an error message or the reference may silently be passed as a string.

integer number
Win32::SqlServer passes the value as int, unless the value falls outside the boundary of int, in which case Win32::SqlServer passes the value as bigint.
 
floating-point value
Win32::SqlServer passes the value as a float.
 
 
 
string
 
 
Win32::SqlServer will pass the string as nvarchar if any of these are true: When none of the above applies, Win32::SqlServer will pass the value as varchar.

xml and FOR XML

See also note about large data types below.

Output

Values of the xml data type is passed as Unicode data. That is, they are passed as strings, and they will always have the UTF-8 bit set.

Data produced with the FOR XML clause is handled as binary data, and you will get a binary string or a hex string depending on the setting of BinaryAsStr. No, I am not pulling your legs. This is how OLE DB relay FOR XML data.

Note: when using the SQLOLEDB provider you cannot call a procedure through sql_sp(), that has an OUTPUT parameter of the xml data type. The same applies scalar user-defined functions of which the return type is xml.

Input

The input value must be a string that is a legal XML fragment, or else conversion fails. As for specifying a schema collection, please see the description of the functions sql() and enterparameter().

An XML document can include a character-set specification, which always appears first in the document as in this example:

<?xml version="1.0" encoding="utf-8"?><ROOT>Text</ROOT>

Win32::SqlServer inspects an input value of the xml data type for a charset specification, to determine how it should pass the XML document to SQL Server. The following applies:

CLR UDTs

What is said here, applies both to built-in CLR types such as hierarchyid and user-defined CLR types. To muddle the waters, Win32::SqlServer tend to refer to both kinds as UDTs.

Output

The value is handled as a binary value, according to the setting of BinaryAsStr.

Note: when using the SQLOLEDB provider you cannot call a procedure through sql_sp(), that has an OUTPUT parameter of some CLR user-defined data type. The same applies scalar user-defined functions of which the return type is a CLR UDT.

Input

The value is handled as a binary value, according to the setting of BinaryAsStr. Note that the binary value must map to a proper serialisation of the UDT. See the description of the functions sql() and enterparameter() for how to actually specify the name of a CLR UDT.

Large CLR UDTs

Large CLR UDTs are types that are defined with MaxByteSize = -1, and thus can exceed 8000 bytes. Large UDTs were added in SQL 2008, and as long as you are using SQLNCLI10 or later, Win32::SqlServer handles large UDTs in the same way as regular UDTs. (But see the general caveats on large types below).

If you use SQLNCLI, you cannot receive OUTPUT parameters of large UDTs, but you can still pass values to input parameters of large UDTs. If you use SQLOLEDB, you cannot use parameters of large UDTs at all, neither for input nor for output. You can still receive large UDT data in result sets with any provider.

table

SQL 2008 adds the possibility to pass data in table-valued parameters; only for input, you cannot retrieve data this way. The way you pass a table-valued parameter is quite different between the high-level routines and the mid-level routines.

With the high-level routines, you pass an array of rows and each row is represented by a hash or an array of column values. You don't have to provide any definition of the table type, as Win32::SqlServer will retrieve the table-type definition from the server on first reference, and caches the definition for future use. See further the section Working with Table Parameters.

With the mid-level routines you first need to define the columns of the table type with definetablecolumn(), and you need do it every time you pass a table-valued parameter. You then pass each row with a call to inserttableparam(). As with the high-level routines, a row is a hash or an array of column values. See further the description of these routines, as well as the description of enterparameter().

You can only use table-valued parameters if you use the SQLNCLI10 provider or later; you cannot use table parameters with older providers.

Large Data Types

Large data types are types that may exceed 8000 bytes, that is text, ntext, image, varchar(MAX), nvarchar(MAX), varbinary(MAX), xml and CLR types with MaxByteSize = -1.

Win32::SqlServer has no particular support for these data types, but these are handled as just as any other types. This should be OK for values of moderate size. However, with values of several megabytes, the performance becomes unacceptable, and it can take several seconds to retrieve a 10 MB value. For LOB values of these size, you should consider using FILESTREAM and use the the OpenSqlFilestream() interface which should give you a lot better performance at those sizes.

Note: if you are using the SQLOLEDB provider, be aware of that output parameters of the MAX data types will be truncated at 8000 bytes by SQL Server. You cannot call user-defined functions with a return value of these data types from SQLOLEDB.

Connection Routines

There are two ways to create a Win32::SqlServer object. One is to use sql_init(), which creates an object and connects to the database in one go. The other way is to use the traditional new() method, and the use the function setloginproperty() to set server, database etc. You connect with connect() or you set the property AutoConnect which means that Win32::SqlServer connects and disconnects for each query.

sql_init()

Creates a Win32::SqlServer object, connects to SQL Server and returns the object.

$sqlsrv = [Win32::SqlServer::]sql_init([$server, [$username,[$password,
                                       [$database, [$provider]]]]]);
$sqlsrv
A newly created Win32::SqlServer object.
 
$server
The server to connect to. Default is the local server.
 
$username
User name for SQL authentication. The default is to connect with Windows Authentication.
 
$password
Password to use when you also have specified $username. Ignored when $username is undef.
 
$database
Database to connect to. Default is tempdb. That is, the default database for the login is not honoured.
 
$provider
Which provider to connect with. Default is PROVIDER_DEFAULT. Specify any of the values permitted for the Provider attribute.

All parameters are optional, but in order to specify $database only, you need to specify undef for the first three. Since this is a module routine, you are supposed to call it as Win32::SqlServer::sql_init(), but for now Win32::SqlServer->sql_init() works as well.

If the login fails, this aborts execution of the Perl script. You can only catch this condition with eval. If you want to handle login failures in a message handler, create the Win32::SqlServer object with new() and set ErrInfo elements (or define your own message handler) and then use setloginproperty() and connect described below.

Win32::SqlServer saves the first object you create with sql_init(), and then uses this a default handle, so you when say things like:

sql('SET NOUCOUNT ON');

without any $sqlsrv-> in front, Win32::SqlServer will use the default handle. However, this functionality exists only to provide compatibility with the older module MSSQL::Sqllib and is deprecated. If you want to refer to sql(), sql_sp() etc this way, you need to import them explicitly when you use Win32::SqlServer or use an export tag. Note that it is only the functions of which the name starts with sql_ that you can use in this way. For setloginproperty(), connect(), disconnect(), isconnected() and the mid-level query functions you must always specify the Win32::SqlServer object.

new

Creates an un-connected Win32::SqlServer object.

$sqlsrv = new Win32::SqlServer;

setloginproperty()

Sets a login property such as server, database, packet size etc.

$sqlsrv->setloginproperty($property, $value);
$property
The name of a login property. See below for a list of available properties. Names of login properties are not case-sensitive.
 
$value
The value to set for the property. The type of the value depends on the property.

You can only set a login property when you are disconnected. (So you don't get the idea that they would have any effect on the existing connection.) If you try to set a property when you are connected, Win32::SqlServer croaks. You can check whether you are connected with isconnected().

Each login property maps to an OLE DB authorisation or initialisation property, either a general OLE DB property (names starting with DBPROP_) or an SQL Server-specific property (names starting with SSPROP_). I will have to admit that for some of these, I have only a vague notion of what they are good for. So I will have to refer you to the OLE DB documentation for complete description in several cases. A start is the OLE DB Docs topic Using Connection String Keywords with OLE DB Driver for SQL Server.

Not all OLE DB properties are exposed by setloginproperty(), as not all are applicable in the context of Win32::SqlServer. Note below that when I say that Win32::SqlServer does not set a default value for a property, this means that any default value set by the OLE DB provider applies. The same applies if you set a property to undef – this means that Win32::SqlServer will not set it, leaving room for whatever default value the provider fancies.

AccessToken

Data typestringDefault undef OLE DB Property SSPROP_AUTH_ACCESSTOKEN

Specifies an access token to authenticate to Azure Active Directory. This property requires MSOLEDBSQL 18.2 or later. See the OLE DB Docs topic Using Azure Directory in for details.

ApplicationIntent

Data typestringDefault "ReadWrite" OLE DB Property SSPROP_INIT_APPLICATIONINTENT

This parameter is applicable when connecting to an Availability Group with a read-only replica in SQL Server 2012 or later, and controls whether you are directed to the main instance or the read-only replica. The possible values are ReadWrite (the default) and ReadOnly. This property is only available with the SQLNCLI11 provider or later.

Appname

Data typestringDefault name of your Perl script, excluding directory path OLE DB PropertySSPROP_INIT_APPNAME

A string that represents the application name, and which can be retrieved in SQL Server with the APP_NAME() function. If you set it to undef, OLE DB will apply it's default, which will be Strawberry Perl or somesuch.

AttachFilename

Data typestringDefaultundef OLE DB PropertySSPROP_INIT_FILENAME

The name of the primary database file for a database. In case the database you specify with Database is not available, then SQL Server looks for this file and attaches it.

Authentication

Data typebooleanDefault undef OLE DB PropertySSPROP_AUTH_MODE

This property specifies by which means of authentication you want to log in. Permitted values are SqlPassword (that is, you use Username and Password), ActiveDirectoryIntegrated (more or less the same as IntegratedSecurity = 'SSPI'), ActiveDirectoryPassword (Azure Active Directory, that is), ActiveDirectoryInteractive, ActiveDirectoryServicePrincipal and ActiveDirectoryMSI. See the topic Using Azure Directory in OLE DB Docs for further details.

Autotranslate

Data typebooleanDefault false OLE DB PropertySSPROP_INIT_AUTOTRANSLATE

This setting is deprecated and will be removed from a future version of Win32::SqlServer. Any attempt to set it renders a warning through current message handler and the value is ignored. The setting controls how data to (var)char columns is converted, and this is on by default with OLE DB. However, since Win32::SqlServer itself performs character conversion from Perl's internal UTF-8 representation to the code page of the database as discussed in the section on char, varchar and text, Win32::SqlServer forces this setting to false.

ConnectionString

Data typestringDefaultundef OLE DB PropertyDBPROP_INIT_PROVIDERSTRING

Instead of setting all properties individually, you can set all in one bang with a connection string, just as they do in other interfaces. If you set this property, Win32::SqlServer flushes the settings for all other login properties, including its default values. So, for instance, if you set the ConnectionString property, but do not include Initial Catalog in the connection string, the user's default database will apply, not tempdb. If you then go on to set other login properties with setloginproperty(), these take precedence over the connection string.

There are two keywords you should not include in the connection string:

For a full list of available keywords for the connection string, please see this topic in the OLE DB Docs. (Beware that this topic applies to the most recent provider MSOLEDBSQL, and not all keywords may be available with older providers.)

ConnectRetryCount

Data type int Default 1 OLE DB PropertySSPROP_INIT_CONNECT_RETRY_COUNT

This property specifies how many times the OLE DB provider should retry to re-establish the connection, if an idle connection is broken.

This property requires MSSQLOLEDB 18.6. The default value given, is the default value from the OLE DB provider. Win32::SqlServer only sets this property if you explicity set it. For details, see the topic Idle connection resiliency in the OLE DB Driver in the OLE DB Docs .

ConnectRetryInterval

Data type int Default 10 OLE DB Property SSPROP_INIT_CONNECT_RETRY_INTERVAL

This property specifies with which frequency the the OLE DB provider should retry to re-establish a broken idle connection. The property is ignored if ConnectRetryCount is 0.

This property requires MSSQLOLEDB 18.6. The default value given, is the default value from the OLE DB provider. Win32::SqlServer only sets this property if you explicity set it. For details, see the topic Idle connection resiliency in the OLE DB Driver in the OLE DB Docs.

ConnectTimeout

Data typeintegerDefault15 OLE DB PropertyDBPROP_INIT_TIMEOUT

How many seconds Win32::SqlServer should wait before giving up when trying to connect to SQL Server. (This is not the same as the command timeout, which you can set with the Win32::SqlServer property CommandTimeout.)

Database

Data typestringDefault"tempdb" OLE DB PropertyDBPROP_INIT_CATALOG

Which database to connect to initially. Note that by default Win32::SqlServer sets tempdb as the initial database. If you want to connect to whichever database that is registered as the default in SQL Server, set this property to undef.

Encrypt

Data typebooleanDefaultfalse OLE DB PropertySSPROP_INIT_ENCRYPT

Whether the connection is to be encrypted. Please refer to the OLE DB Docs for further details.

FailoverPartner

Data typestringDefaultundef OLE DB PropertySSPROP_INIT_FAILOVERPARTNER

Failover partner for database mirroring. Please see the OLE DB Docs for further discussion. This property is unavailable with the SQLOLEDB provider and applies only to SQL 2005 and later.

FailoverPartnerSPN

Data typestringDefaultundef OLE DB PropertySSPROP_INIT_FAILOVERPARTNERSPN

The SPN for the failover partner. Please see the OLE DB Docs for details. This property requires the SQLNCLI10 provider or later.

Hostname

Data typestringDefault$ENV{COMPUTERNAME} OLE DB PropertySSPROP_INIT_WSID

A string that represents the machine you are connecting from and which resurfaces in the SQL function HOST_NAME().

IntegratedSecurity

Data typestringDefault"SSPI" OLE DB PropertyDBPROP_AUTH_INTEGRATED

Specifies whether Win32::SqlServer should connect with Windows Authentication and this is the default. As far as I know, "SSPI" is the only available value. setloginproperty() accepts the number 1 as the equivalent to "SSPI". Note that if you set the login property Username, Win32::SqlServer automatically clears IntegratedSecurity.

Language

Data typestringDefaultundef OLE DB PropertySSPROP_INIT_CURRENTLANGUAGE

A string that represents an SQL Server language. This property has the same effect as the SET LANGUAGE command. When you do not set a language, the default language defined for the server login applies.

LCID

Data typeintegerDefault see below. OLE DB PropertyDBPROP_INIT_LCID

The locale ID. My interpretation of Books Online is that it does not have much effect. Nevertheless, Win32::SqlServer sets a default value by calling the Windows function GetUserDefaultLCID.

MultiSubnetFailover

Data type booleanDefault falseOLE DB PropertySSPROP_INIT_MULTISUBNETFAILOVER

Set this property to a true value when connecting to an availability group. This property is only available with the MSOLEDBSQL provider.

Netlib

Data typestringDefaultundef OLE DB PropertySSPROP_INIT_NETWORKLIBRARY

Which network library to use for the connection. Please refer to the OLE DB Docs for details.

NetworkAddress

Data typestringDefaultundef OLE DB PropertySSPROP_INIT_NETWORKADDRESS

A network address for the server defined by the Server property.

OldPassword

Data typestringDefaultundef OLE DB PropertySSPROP_AUTH_OLD_PASSWORD

This property is only applicable when you use SQL authentication. When set, it should be the current password for the SQL login specified by the Username login property. The Password property should in this case the hold the new password for the SQL login. Thus, you can use this property to change the password for an SQL login when connecting. This property is unavailable with the SQLOLEDB provider and applies only to SQL 2005 and later.

PacketSize

Data typeintegerDefaultundef OLE DB PropertySSPROP_INIT_PACKETSIZE

A number between 512 and 32767 which sets the network packet size. When undef, the default is taken from the server-side configuration parameter network packet size, which you can set with sp_configure.

Password

Data typestringDefaultundef OLE DB PropertyDBPROP_AUTH_PASSWORD

This property applies only if you also have specified the Username property. It is the password for that SQL login. See also OldPassword on how to change the password for an SQL login when connecting.

Pooling

Data typebooleanDefaulttrue OLE DB PropertyDBPROP_INIT_OLEDBSERVICES

Whether connection pooling is enabled. Connection pooling is handled by OLE DB core services. When connection pooling is in effect, the physical SQL Server connection is not terminated when you call disconnect() or when the Win32::SqlServer object goes out of scope. Instead, the OLE DB provider retains the connection, and if you reconnect with the same login properties, the connection is likely to be reused. If there is no reconnection for some time, normally 60 seconds, OLE DB disconnects. (You cannot change this setting through Win32::SqlServer, as it is a registry setting. Please refer to the MSDN topic Resource Pooling.)

When this setting is false, the connection is physically disconnected when you call disconnect() or when the Win32::SqlServer object goes out of scope.

Pooling permits you to connect and disconnect, for instance by having a local Win32::SqlServer object in each sub without any overhead. However, if you have poor transaction handling, any outstanding transaction on the pooled connection is not rolled back until the connection is reused or physically disconnected. This can lead to blocking scenarios. Furthermore, since Win32::SqlServer works a lot with cached procedure definitions, it is much better to have a Win32::SqlServer stays in scope.

(Readers well versed in OLE DB may know that DBPROP_INIT_OLEDBSERVICES is in fact a bit mask that controls other OLE DB services as well. Currently Win32::SqlServer always turns off these other services.)

Prompt

Data typeinteger DefaultDBPROMPT_NOPROMPT(4) OLE DB PropertyDBPROP_INIT_PROMPT

OLE DB can display a small window where the user can specify login information. This is controlled by Prompt. This property can take any of these four different values (Text from MSDN):

Win32::SqlServer does not declare these constants for you, so you will have to declare them yourself or use the numbers.

Server

Data typestringDefault"(local)" OLE DB PropertyDBPROP_INIT_DATASOURCE

Which SQL Server instance to connect to.

ServerSPN

Data typestringDefault undef OLE DB Property SSPROP_INIT_SERVERSPN

The SPN for the server you are connecting to. Please see the OLE DB Docs for details. This property is only available with the SQLNCLI10 provider or later.

 TransparentNetworkIPResolution

Data type boolean Default false OLE DB Property SSPROP_INIT_TNIR

Please see the topic Using Transparent Network IP Resolution in the OLE DB Docs for all details. This property requires MSOLEDBSQL 18.4.

 TrustServerCert

Data typebooleanDefaultfalse OLE DB PropertySSPROP_INIT_TRUST_SERVER_CERTIFICATE

Whether you trust the server certificate when you use encryption. Please refer to the OLE DB Docs for further details. This property is unavailable with the SQLOLEDB provider and applies only to SQL 2005 and later.

Username

Data typestringDefaultundef OLE DB PropertyDBPROP_AUTH_USERID

Specifies that you want to log in with SQL authentication with the given username. When you set this property, the login property IntegratedSecurity is automatically cleared. (Since if both are set, the provider honours integrated security.)

connect()

Connects to SQL Server using the current login properties.

$ret = $sqlsrv->connect()
$ret
True if connection succeeded, else false.

It's an error to call this function, if you are already connected. You can use isconnected() to check whether you are connected.

If the login to SQL Server as such fails, connect() invokes the current message handler. If an error occurs when Win32::SqlServer calls the OLE DB provider to submit the login properties, Win32::SqlServer aborts and prints the error message from the OLE DB provider, but this message may be very obscure. In this situation, it may help to set the PropsDebug property to get a dump of the current login properties.

disconnect()

Disconnects from SQL Server and frees up any resources allocated for queries.

$sqlsrv->disconnect()

Note that if connection pooling is active, the connection to SQL Server is not physically closed, only returned to the connection pool. See the Pooling login property for a further discussion on connection pooling.

It's permitted to call this function, even if you are already disconnected.

If a Win32::SqlServer object goes out of scope, it automatically disconnects from SQL Server (but the physical connection may remain in the pool.)

isconnected()

Returns whether you are connected to SQL Server or not.

$ret = $sqlsrv->isconnected();
$ret
1 if you are currently connected to SQL Server, 0 if you are disconnected.

This function returns Win32::SqlServer's internal connection state. If the physical connection somehow was severed – network error, severe SQL Server error – but Win32::SqlServer has not discovered this yet, isconnected() returns 1.

Note that if even this function returns 0, the connection may still exist in SQL Server, due to connection pooling.

High-Level Query Functions

The intention is that 99% of the time, you will be running one of sql() and sql_sp() to run your queries. sql() is for batches of one of more SQL commands while sql_sp() is for running a stored procedure (or a scalar user-defined function). The two share a common feature: the four parameters $colinfostyle, $rowstyle, $resultstyle and \@keys that you use to specify how you want the result set(s) from the command batch/stored procedure to be structured in Perl. This is described in detail in the sections Row Styles and Result Styles and Getting Column Information under the topic of sql(). Both functions also accept parameters to the command batch/stored procedure, but there is a difference between the two: sql() requires you to specify the data types, whereas sql_sp() retrieves the parameter profile from SQL Server or from an internal cache. This chapter also covers sql_one() which is for queries that should return exactly one row and sql_insert() that inserts data into a table from hash. There is also a special section Working with Table Parameters that details how to use this feature.

The high-level query functions makes use of Perl's flexible nature, and permits you leave out most parameters, even in the middle of the parameter list.

sql()

Runs a batch of one or more SQL commands. The batch may be parameterised.

($|@|%)result = $sqlsrv->sql($batch
                             [, \@unnamed_parameters] [, \%named_parameters]
                             [, $colinfostyle] [, $rowstyle] [, $resultstyle [, \@keys]]);

Parameters

($|@|%)result
The result set(s) from $batch. Depending on the $rowstyle and $resultstyle parameters, this can be an array, a hash or a scalar. If the result set(s) come back as an array or a hash, and you receive the return value into a scalar, you get a reference to the array/hash. This is described more in detail in the section Row Styles and Result Styles below.
 
$batch
A batch of one or more SQL statements. (Keep in mind that GO is not an SQL command, so don't include that in your command batch.)
 
\@unnamed_parameters
A reference to an array with input for unnamed parameters. Unnamed parameters appears as ? in $batch. Each entry in the array is an inner array with two or three entries. The first entry is the data type of the parameter, the second is the value for the parameter, or a reference to the value if the parameter is an output parameter. The third is used only when the data type is xml, table or UDT, in which case it specifies an XML schema collection or the name of the table type or the UDT. See further the section Details on the Parameter Specification.
 
\%named_parameters
A reference to a hash with input for named parameters. Named parameters appears as T-SQL variables, e.g. @p1, in $batch. The key is the parameter name, and you can specify it with or without the leading @. Each entry in the hash is an array as described for the parameter \@unnamed_parameters.
 
$colinfostyle
Specifies if and how you want information about the columns in the result set(s). Possible values are COLINFO_NONE (no information), COLINFO_NAMES (names only), COLINFO_POS (column numbers only) and COLINFO_FULL (a hash with detailed information about the columns). See the section Getting Column Information below for details. The default is COLINFO_NONE.
 
$rowstyle
Specifies how each row in the result is to be represented. Possible values are HASH (each row is a hash keyed by column name), LIST (each row is an array) and SCALAR (each row is a scalar value). See the section Row Styles and Result Styles below for details. Default is HASH.
 
$resultstyle
Specifies how the result sets are to be structured. Possible values are MULTISET (an array of result sets), MULTISET_RC (an array of result sets and row counts for action statements), SINGLESET (an array of rows), SINGLEROW (a single row according to $rowstyle), KEYED (a hash, keyed by the data in the result set), NORESULT (no result set returned) or a reference to a callback routine that is to be called for each row. See the section Row Styles and Result Styles below for details. Default is SINGLESET.
 
\@keys
This parameter is only applicable when $resultstyle is KEYED, in which case it's mandatory. It specifies which column(s) in the result set to use for keys.

Passing Parameters to SQL Batches

This section starts with two subsections on guidelines, before going into the reference stuff. If you are in a hurry, you may want to jump directly to Unnamed and Named Parameters, or even Details on the Parameter Specification.

Why Parameterise SQL Commands at All?

There are three reasons 1) simplicity, 2) security and 3) performance.

The first two reasons are actually two angles of the same problem. Say that you have a script that permits a user to enter a part of a name and the script looks up matching rows. A simple-minded version would perhaps be:

$batch = <<SQLEND;
    SELECT CompanyName
    FROM   Customers
    WHERE  CompanyName LIKE '$userentry%'
SQLEND
$result = $sqlsrv->sql($batch, Win32::SqlServer::SCALAR);

Quick testing indicates that this appears to work. But then a poor user enters Let's whereupon the query dies with a syntax error because of the odd number of single quotes, instead of returning Let's stop N shop. That was the poor and innocent user. Next user is outright malicious, and he enters:

La'; DROP TABLE Customers --

The resulting batch to sent to SQL Server becomes:

SELECT CompanyName
FROM   Customers
WHERE  CompanyName LIKE 'La'; DROP TABLE Customers -- %'

And if this script runs with enough privileges, the table goes away. This technique is known as SQL injection, and is a common line of attack on web sites that passes user input to a DBMS (could be any; not just SQL Server).

As you see, what all the fuzz is about is handling string delimiters in the input data. You could do something like $userentry =~ s/'/''/g, on your own or call sql_string() for the task. But if you have several string parameters your code gets bulky, not the least if you need to use $userentry later in it's original form. This is where parameterised commands come to the rescue:

$batch = <<SQLEND;
   SELECT CompanyName
   FROM   Customers
   WHERE  CompanyName LIKE ? + '%'
SQLEND
$result = $sqlsrv->sql($batch, [['nvarchar', $userentry]],
                       Win32::SqlServer::SCALAR);

This will eventually result in a remote procedure call, where $userentry will be a parameter and it will not matter if there are any single quotes in it. Thus, there is no way an intruder can get in here, and there is no risk a user gets a strange error with some data.

Another slant of this is datetime values. If you include the string "2006-08-21" in a T-SQL batch, SQL Server may take it as just that if the DATEFORMAT setting is mdy or ymd, but if the setting is dmy, SQL Server will consider it an error. By passing datetime values as parameters, you let Win32::SqlServer do the interpretation with better odds for a correct interpretation.

The third reason, as I said is performance. When SQL Server receives the query text, it hashes the text and looks up the hash in the plan cache and in case of a match an existing plan is reused. If you interpolate parameter values into the query string, the hash value will be different and SQL Server will have to compile the query every time. A parameterised query is constant, and thus the execution plan will be reused which can mean a huge performance gain for queries that are submitted with high frequency.

When You Should Not Parameterise

Yes, there are a few cases where you should not use parameterised statements. An unparameterised command batch is passed to SQL Server as-is, a batch in the top scope of the connection. (A scope in T-SQL is a stored procedure, a trigger, a function or a batch of dynamic SQL; all very reminiscent of Perl.) A parameterised batch, on the other hand, is executed through sp_executesql. This creates a new scope, which means that if your command batch creates a temp table, the temp table will be dropped when the batch exits. Thus, if you create a temp table what you want to reuse in later command batches, you must use an unparameterised command batch. The same issue applies to SET commands: the effect of a SET command is reverted when the scope exits, so if you want, say, SET NOCOUNT ON to have effect for the rest of connection, you should issue it in an unparameterised batch.

There is one more case worth mentioning. You cannot parameterise everything. This does not fly:

$result = $sqlsrv->sql("SELECT * FROM ?", [['nvarchar', 'MyTable']]):

This will result in an error that @P1 is not declared. This is because you can only use parameters where variables are permitted in the T-SQL syntax, and T-SQL does not permit you to use a variable for a table name, but in this case expects a table variable or a table-valued parameter.

Unnamed and Named Parameters

There are two ways to specify a parameter in a command batch: by using ? as a parameter placeholders and by using a standard T-SQL parameter name starting with @. I refer to the former as unnamed parameters, and the latter I call named parameters.

Win32::SqlServer expands the ? to @P1, @P2, @P3 etc. However, ? in string literals, quoted names and comments are not expanded. Here is a contrived example:

SELECT ?, '?' AS x FROM [Questions?] WHERE col = ? -- eh?

This command batch is expanded to:

SELECT @P1, '?' AS x FROM [Questions?] WHERE col = @P2 -- eh?

The parameter \@unnamed_parameters is really just a shortcut for entries in \%named_parameters where the keys are @P1, @P2 etc. From this follows, that it's perfectly possible to mix parameter placeholders and named parameters in a command batch.

Parameter placeholders are convenient for single occurrences in short batches, but if you need to refer to the same parameter in the same command batch, named parameters are easier to use.

SELECT col1, col2 FROM tbl WHERE last_name = @name OR first_name = @name

Had you used ? here, you would in fact have had two parameters and not one. Named parameters are also more palatable when you have very many parameters. That ? over there, is that @P5 or @P6?

When you construct %named_parameters, you can leave out the @ from the parameter names; Win32::SqlServer will add these. This saves you some typing. Compare:

{'@myparam' => ['nchar', $myvalue]}

with

{myparam => ['nchar', $myvalue]}

(Win32::SqlServer could have left it to the OLE DB provider to expand the ?, but for two reasons Win32::SqlServer expands ? itself : 1) to support named parameters. 2) The error messages from the OLE DB provider when you have errors with the parameters range from the vague and imprecise to the completely opaque and impenetrable ones. Win32::SqlServer's error messages are hopefully more informative.)

Details on the Parameter Specification

An entry in \@unnamed_parameters and \%named_parameters can be either a scalar value or a short array with two or three elements. If the entry is a scalar value, this is the value for the parameter. But this is only meaningful in one case: you want to pass undef (that is NULL) for the parameter. If you supply any other value, Win32::SqlServer applies a default data type of varchar and at the same time issues a warning through the current message handler if Perl warnings are enabled.

From this follows that in the normal case you will need to pass the short arrays. Here are the entries.

Index 0 – data type
The data type for the parameter. This can be a system type or a user-defined type. When using a system type, you must use the name as it appears in sys.types; you cannot use the data-type synonyms recognised by SQL Server. That is, you must use int, char and not integer, character etc. There is one exception: you can use rowversion for timestamp. When using a system type, the name is always case-insensitive, that is INT for int is accepted. Regarding user-defined types, see below.
 
Index 1 – the value + input/output
The value for the parameter and whether the parameter is an output parameter or not. To specify that a parameter is an output parameter, pass a reference to a scalar (or a datetime hash). The input value for the output parameter is the value in the referenced scalar. Anything else than a reference to a scalar or a hash, is an input-only parameter.
 
Index 2 – extra type info
Applies only if the data type is xml, UDT or table. See further under these data types below.

Here are remarks for specific data types that may require extra information beside the type name.

varchar, nvarchar, varbinary
You can enter a complete specification, e.g. varchar(20), or just the type name, e.g. varchar. In the latter case, Win32::SqlServer applies a default which is equal to the maximum length for the data type and the SQL Server version, that is 8000 for varchar and varbinary, and 4000 for nvarchar. If you are on SQL 2005 or later, and the value exceeds the max length for the type, Win32::SqlServer will apply MAX.
 
char, ncharbinary
As with varchar etc, you can enter a complete specification, e.g. char(20), or just the type name, e.g. char. In difference to the variable-length types, Win32::SqlServer will use the actual length of the string as the default, as using the max length for the type could yield unexpected behaviour. However, this has the distinct disadvantage that calls to the same query with parameters of different length will yield different cache entries (as the parameter list is part of the cache entry). For this reason, leaving out the length with fixed-length string and binary types is deprecated, and Win32::SqlServer will emit a warning if warnings are enabled when this happens.
 
decimal, numeric
If you leave out precision and/or scale, Win32::SqlServer will apply the defaults 18 and 0, respectively and at the same time pass a warning through the current message handler unless the value is undef. That is, best practice is to always supply precision and scale.
 
User-defined types
You can specify a user-defined type using one-, two- or three-part notation. The type can be a plain alias type, a CLR UDT or a table type.

If any component includes a special character such as space or period, you must quote the name with brackets ([]) or double quotes ("") according to the same rules as in SQL Server. Here are some examples:

['MyDatabase.MySchema.YourType', $value]
['[My Database].MySchema.[Your Type]', $value]
['[This-Type]', $value]
['"This.Type"', $value]
Win32::SqlServer looks up the definition in the system catalog, and thus the case-sensitivity is the same as in the system catalog. That is, any database portion follows the server collation, and the name and schema portions follow the database collation, unless you have a contained database where all metadata is case-insensitive.

Win32::SqlServer caches the type definitions in the object property usertypes which is a hash, keyed by the type string exactly as you entered it. That is, if you use both mytype and dbo.mytype for the same type, that will be two lookups in the system catalog, and two entries in usertypes. Note that Win32::SqlServer never clears this cache. That is, if you first connect to one database where mytype is defined as varchar(5), and with the same Win32::SqlServer object then connects to another database where mytype is defined as bigint, Win32::SqlServer will still use the definition varchar(5). The only permissible action with usertypes is to delete an entry; the content is strictly off-limits. 

On SQL 2000 the only permitted schema is dbo, since user-defined types were not schema-bound in these old versions.

xml
For xml, you can specify an XML schema collection. You can do this in two ways: in parentheses after xml, or in the third entry (that is, index = 2, "typeinfo") in the parameter array. You must use the latter method, if the name includes parentheses. The name can be a one-, two- or three-part name. Thus, all these are fine:
['xml', $myxmldoc]                         # Untyped xml.
['xml(my_schema_collection)', $value)]     # Typed xml.
['xml', $myxmldoc, 'my_schema_collection'] # Equvivalent to the previous.
['xml', $mydoc, '[my-schema-collection]']  # Must quote this name.
['xml("My Database"."That Schema"."My.Collection")', $mydoc]  # Ditto.
 
CLR UDTs
The simplest way to specify a CLR user-defined type is to use the type name directly as discussed under User-defined types above. In compatibility with older versions of Win32::SqlServer, you can also specify UDT (must be upper case) and then specify the name of the type in the same way as you specify schema collections for xml. That is, either in parentheses after UDT, or in the typeinfo entry in the parameter array.
 
Table-valued parameters
Specify a table type by passing the name, in one- or two-part notation; for table types you cannot specify a database component. (Because lack of support in SQL Server.) In compatibility with older versions of Win32::SqlServer, you can also use table as the type name, and specify the name of the type in parentheses after table or use the third entry in the parameter array. For details on how to pass the table data, see the section Working with Table-Parameters.

Examples Anyone?

By now, I can hear the reader screaming for example of all this mess. OK, so I'm lazy and point you to the Quick Examples earlier in the document. One conclusion you can draw from the examples is that it's perfectly normal to use anonymous arrays and hashes to pass the parameter information.

Row Styles and Result Styles

The parameters $colinfostyle, $rowstyle, $resultstyle and \@keys are all about shaping the result set(s) into the fashion you want to digest the data from Perl. The styles are denoted by constants that are not imported by default. You can refer to them with the long name, for instance Win32::SqlServer::HASH. You can also import them when you say use Win32::SqlServer, either by explicit name or by using the appropriate export tag.

Note that while the syntax graph for the call displays $colinfostyle, $rowstyle and $resultstyle in that order, you can specify the parameters in any order, and if you only need to specify the result style, you do not need to use undef as placeholders for the others. The only requirement is that if you specify \@keys, it must be the last parameter.

And while $colinfostyle is listed as the first of these four parameters, I will save it to a later section to describe it. In this section, all explanations of where you find a certain piece of data assumes the default colinfo style, COLINFO_NONE.

The row-style constants are fairly easy to get a grip of, so we start with these. They describe how a single row is represented.

HASH (the default)
Each row appears as a hash, keyed by column name. Win32::SqlServer names nameless columns as Col 1, Col 2 etc. If the same column name appears more than once in the result set, you will get a warning if Perl warnings are enabled.
 
LIST
This gives you an array with the columns appearing in the order as they appear in the query, with the first column at index 0.
 
SCALAR
Each row is returned as a scalar value. As you may guess, SCALAR is intended for one-column result sets. But if result set has more than one column, you get one string for the entire row with the column values are separated by the global Win32::SqlServer variable, $Win32::SqlServer::SQLSEP. The default value for this variable is \022, a control character. Note that if you change it, this affects all Win32::SqlServer objects.

Note: above when I talk about "all columns", this is really "all non-key columns" in the case the result style is KEYED.

The result styles are more to the number, and may also be more bewildering at first sight. They describe how the result set(s) of your batch are structured.

SINGLESET (the default)
The return value is an array of rows, or – if you receive the return value as a scalar – a reference to an array of rows. Each array entry is a reference to a hash, a reference to an array or a scalar value depending on the value of $rowstyle. The order of the rows is the same as the order served by SQL Server. (Keep in mind that if you want a certain order, you must use ORDER BY, else SQL Server is free to use any order.)

SINGLESET is intended for the common case when a command batch returns only one result set. However, if the command batch returns several result sets, they are all included in the array, and it's up to you to find the boundaries between them.

Say that you have this command call to sql():

@result = $sqlsrv->sql(<<SQLEND, {orderid => ['int', $orderid]});
   SELECT OrderDate, CustomerID, EmployeeID, ShippedDate
   FROM   Orders
   WHERE  OrderID = \@orderid
   SELECT ProductID, UnitPrice, Quantity, Discount
   FROM   [Order Details]
   WHERE  OrderID = \@orderid
   ORDER  BY ProductID
SQLEND

To refer to the customer ID, you would say $result[0]{CustomerID}. To refer to the Product IDs, you would say $result[$i]{ProductID} where $i starts at 1 and goes to $#result.

If you instead say:

$result = $sqlsrv->sql(<<SQLEND, ...

you refer to the customer ID as $$result[0]{CustomerID}, as you now get a reference to an array.

If we change the row style to LIST, the customer ID is now found at $result[0][1], and the ProductIDs at $result[$i][0] where $i is as above. (Or $$result[0][1] and $$result[$i][0], if we receive the return value as $result = $sqlsrv->sql(...)

MULTISET
You get an array of a result sets, or – if you receive the return value as a scalar – a reference to an array of result sets. Each array entry is a reference to an array of rows. Note that empty result sets will be retained and appear as empty arrays. Using the same example as for SINGLESET above, but throwing in a result style of MULTISET, the customer ID is now at $result[0][0]{CustomerID}, or $$result[0][0]{CustomerID} when receiving the result as a reference to an array. The ProductID is at $result[1][$i]{ProductID} where $i now goes from 0 to $#{$result[1]}.

(If you as a reader feel overwhelmed by all these combinations of $, brackets and braces, you have my sympathy. However, I am not going into details to explain what all that comes from, as this is a reference for a Perl module, not on text on Perl itself. I can recommend thorough study of chapter 4 of the second edition of the Camel book or similar material, if you are not acquainted with arrays of arrays, hashes of hashes etc.)

MULTISET_RC
This is very similar to MULTISET. The one difference is what happens with T-SQL statements that do not return a result set, but merely produce a row count. With MULTISET you get an empty array, with MULTISET_RC you get the row count as a scalar value (and not as an element in an nested array). Say that you have:
@result = $sqlsrv(<<SQLEND, MULTISET_RC);
    INSERT #temp (colA, colB)
        SELECT thiscol, thatcol FROM sometable  -- Inserts 27 rows.
    SELECT colA, colB FROM #temp
SQLEND

At $result[0] you will find the number 27. At $result[1], you will find a reference to an array with 27 entries, and you could refer to colA in the 13th row as $result[1][12]{'colA'}.

SQL Server returns such row counts for INSERT, UPDATE and DELETE statements, as well as a few more. For PRINT and RAISERROR you also appear to get a row count that is -1. Row counts are not generated when the setting SET NOCOUNT ON is in effect. The normal use for MULTISET_RC would be a batch which only performs INSERT/UPDATE/DELETE operations, and you like to know the number of affected rows.

Note that if a SELECT query returns an empty result, this will appear as an empty array also with MULTISET_RC.

SINGLEROW
The return value is supposed to be a single row. If you specify HASH for $rowstyle, the return value is thus a hash keyed by column names, or – if you receive the return value as a scalar – a reference to a hash. If $rowstyle is LIST, you get an array, or a reference to an array. And if $rowstyle is SCALAR you get the scalar value (but not a reference to it!). Would the command batch return more than one row, Win32::SqlServer just keeps on adding the data and it may or may not make sense. (There is also sql_one() that returns exactly one row.)

We modify the example above somewhat:

$batch = <<SQLEND;
   SELECT OrderDate, CustomerID, EmployeeID, ShippedDate
   FROM   Orders
   WHERE  OrderID = \@orderid
SQLEND
%result = $sqlsrv->sql($batch, {orderid => ['int', $orderid]},
                       SINGLEROW);
The customer ID is at $result{CustomerID}. When getting the result into $result we find it at $$result{CustomerID}. With row style LIST, you would say:
$batch = <<SQLEND;
   SELECT OrderDate, CustomerID, EmployeeID, ShippedDate
   FROM   Orders
   WHERE  OrderID = \@orderid
SQLEND
@result = $sqlsrv->sql($batch, {orderid => ['int', $orderid]},
                       LIST, SINGLEROW);
You refer to the CustomerID as $result[1] or $$result[1] in the reference case. And with SCALAR? Here is an example:
$batch = <<SQLEND;
   SELECT COUNT(*) FROM [Order Details] WHERE OrderID = \@orderid
SQLEND
$count = $sqlsrv->sql($batch, {orderid => ['int', $orderid]},
                      SCALAR, SINGLEROW);
The reader may be appalled by the fact that this even looks simple and understandable.
 
KEYED
The return value when you use KEYED is a hash, or – if you receive the return value as a scalar – a reference to a hash. This hash is keyed by the data in the result set. The value of each hash is the non-key rows, and they appear as a reference to a hash, a reference to an array or as scalar value depending on $rowstyle.

When you use KEYED, you must specify \@keys to specify the keys in the result set. For the HASH row style you refer to them by column name, for LIST and SCALAR, you refer to them by column number, with the first column at number 1. Here is an example:

$batch = <<SQLEND;
   SELECT ProductID, UnitPrice, Quantity, Discount
   FROM   [Order Details]
   WHERE  OrderID = \@orderid
SQLEND
%result = $sqlsrv->sql($batch, {orderid => ['int', 11000]},
                       HASH, KEYED, ['ProductID']);
To get the UnitPrice for product 77 on order 11000, you would say $result{'77'}{UnitPrice}. As you see in the example, passing \@keys as an anonymous array is a normal thing to do.

Here is an example with the LIST row style and a two-column key:

$batch = <<SQLEND;
   SELECT O.CustomerID, OD.ProductID, OD.UnitPrice, OD.Quantity,
          OD.Discount
   FROM   Orders O
   JOIN   [Order Details] OD ON O.OrderID = OD.OrderID
   WHERE  O.OrderID = \@orderid
SQLEND
$result = $sqlsrv->sql($batch, {orderid => ['int', 11000]},
                       LIST, KEYED, [1, 2]);
Now the unit price for product 77 is at $$result{RATTC}{'77'}[0]. (RATTC is the customer on order 11000). The customer ID is the first since we in \@keys said 1 before 2. The index for UnitPrice is 0, because UnitPrice is the first non-key column.

The keys you provide in \@keys are supposed to be the unique keys of your result set, and appear in all rows of the result set(s). If a key listed in \@keys does not appear in one of the rows, this is an error, and Win32::SqlServer will croak. If a key value appears more than once in the result, Win32::SqlServer emits a warning if Perl warnings are activated. It is undefined which of the duplicate rows that end up in the result set. Finally, if there are NULL values in your key columns, they wind up as undef on the Perl side, and Perl will give you warnings about this. You may prefer to use COALESCE() or ISNULL() in your query to avoid this.

NORESULT
This means that whatever result set there may be, throw it away. The return value is undef or an empty array. The gains with NORESULT are limited. Win32::SqlServer performs a cancelresultset() on each result set, but most data probably crosses the wire anyway. But may you save some client-side cycles on not building arrays of hashes and that.
 
Callbacks
Rather getting all data back at once, you can receive them one at a time by passing a reference to a Perl sub for $resultstyle. Win32::SqlServer calls this sub once for each row that is returned from the query. $rowstyle then controls how the row is passed:
$retstat = &$callback(\%row, $resultset_no); # HASH
$retstat = &$callback(\@row, $resultset_no); # LIST
$retstat = &$callback($row,  $resultset_no); # SCALAR
$resultset_no tells you which result set the row comes from. The result sets are numbered from 1 and up.

The callback should return any of the integer numbers as detailed below. The return value from the last call to the callback is the return value from sql().

RETURN_NEXTROW
Tells Win32::SqlServer to give you the next row (if there is one, that is). This is the "normal" return value. I usually don't leak constant values, but if you think RETURN_NEXTROW is too noisy, I can reveal that 1 will do the same task.
 
RETURN_NEXTQUERY
Quit returning rows from the current result set, and move to the next result set.
 
RETURN_CANCEL
RETURN_ERROR
Quit returning rows, and cancel the rest of the query. For Win32::SqlServer there is not really any difference, but you get a different return value back from sql(). Use RETURN_CANCEL when you have found what you were looking for, RETURN_ERROR when something went wrong. RETURN_ERROR has the convenient value of 0.

Important: if you cancel the query with RETURN_CANCEL or RETURN_ERROR, you will not receive the value of any output parameters nor the return value from stored procedures.

RETURN_ABORT
Something went seriously wrong and you don't want to live any more. Rather than issuing die yourself, you should use this return code. Win32::SqlServer will then free up resources tied to the result set, before it aborts execution. This is important in the case you catch the condition with eval, so you don't have an unprocessed result set when you come back from eval

A few goods and bads with callbacks:

All and all, callbacks are not really deprecated, but they do smell a little funny.

If you want more examples on row styles and result styles, please refer to the Quick Examples above. There is also an example of using a callback in the topic of sql_sp().

This table summarises how you could refer the column col in the first row in the first result set, given a row style, result style and the type of return value. The colinfo style is assumed to be COLINFO_NONE. For SCALAR, the assumption is that there is only one column to retrieve. If a cell is greyed, that combination is not useful or not applicable.

  HASHLISTSCALAR
MULTISET
MULTISET_RC
%res      
@res $res[0][0]{'col'} $res[0][0][$colno] $res[0][0]
$res $$res[0][0]{'col'} $$res[0][0][$colno] $$res[0][0]
SINGLESET %res      
@res $res[0]{'col'} $res[0][$colno] $res[0]
$res $$res[0]{'col'} $$res[0][$colno] $$res[0]
SINGLEROW %res $res{'col'}    
@res   $res[$colno] $res[0]
$res $$res{'col'} $$res[$colno] $res
KEYED %res $res{'key'}{'col'} $res{'key'}[$colno] $res{'key'}
@res      
$res $$res{'key'}{'col'} $$res{'key'}[$colno] $$res{'key'}

Getting Column Information


Sometimes you want to get information about the columns in the result set(s) you are receiving. For instance, you want to write a generic export utility that performs SELECT * on some table or views or runs a set of stored procedures, and you don't want the utility to have knowledge of what columns they produce. You just want to write them to a file, with the column names on the first line, and maybe you also want to put character columns in quotes. To this end, you can use the fourth parameter of sql(), $colinfostyle. There are four possible values for this parameter:

COLINFO_NONE (Default)
Do not return any column information. This is the only permitted colinfo style, if the result style is any of SINGLEROW, NORESULT or KEYED.
 
COLINFO_NAMES
Prepend an extra row to the result set(s) with the column names. Note that in difference to the hash keys with row style HASH, the names are exactly those as returned by SQL Server. Nameless columns will appear as the empty string, and if there are duplicate names, they are retained. This colinfo style is mainly useful with the LIST and SCALAR row styles. It is still permitted with HASH.
 
COLINFO_POS
Prepend an extra row to the result set(s) with the column positions. The positions are numbered from 1 and up. This colinfo style is mainly useful with the HASH row style, but it is still permitted with LIST and SCALAR.
 
COLINFO_FULL
Prepend a extra row to the result set(s) where each entry is a reference to a hash with the following keys: Colno, Name, Type, Maxlength, Precision, Scale, Maybenull and Readonly. For further details of these values, see the description of getcolumninfo(). COLINFO_FULL is only permitted with the row styles HASH and LIST; it is not permitted with SCALAR.

To understand this a little better, let's look at this example:

$orderid = 11000;
$sqlstring = <<SQLEND;
   SELECT OrderDate, CustomerID, EmployeeID, ShippedDate
   FROM   Orders
   WHERE  OrderID = \@orderid
   SELECT ProductID, UnitPrice, Quantity, Discount
   FROM   [Order Details]
   WHERE  OrderID = \@orderid
   ORDER  BY ProductID
SQLEND
@result = $sqlsrv->sql($sqlstring, {orderid => ['int', $orderid]},
                       MULTISET, LIST, COLINFO_NAMES);

In $result[0][0][0] you will find OrderDate, the name of the first column in the first result set. Similarly, $result[0][0][2] is EmployeeID, and $result[0][1][1] is RATTC, the customer for order 11000 in the Northwind database. $result[1][0][1] has the value UnitPrice, the name of the second column in the second result set. And $result[1][2][2] is 30, the quantity for the second product on order 11000.

If we change the colinfo style to COLINFO_FULL, to find the string OrderDate, you would now have to say $result[0][0][0]{Name}, and if you look in $result[1][0][1]{Type} you would find that the data type for UnitPrice is money.

Let's now move back to COLINFO_NAMES, and change MULTISET to SINGLESET. Not surprisingly, the string OrderDate is now at $result[0][0], and at $result[1][1] is the customer ID, RATTC. But what is at $result[2][0]? No, it is not the string ProductID, but instead the value 4, the ID for the first product of order 11000. This is because with SINGLESET you only get column information for the first result set, even if there happens to be multiple result sets. After all, SINGLESET is mainly intended for batches that return a single result set, and the prime use for multiple results with SINGLESET is maybe when all sets have the same columns.

Thus, the only result styles for which you can get full column information is MULTISET, MULTISET_RC and callbacks. For SINGLESET you can only get it for first result set. And for SINGLEROW, NORESULT and KEYED, COLINFO_NONE is the only permitted value.

See also Example 3 in the Quick Examples section for an example using COLINFO_NAMES.

sql_one()

Executes a command batch that should return exactly one row and croaks if it does not.

(%|@|$)result = $sqlsrv->sql_one($batch [, \@unnamed_parameters]
                                        [, \%named_paraeters] [, $rowstyle]);
(%|@|$)result
The result set from the command batch. If $rowstyle is HASH (the default), the result is a hash or a reference to a hash. It $rowstyle is LIST, the result is an array or a reference to an array. If $rowstyle is SCALAR, the result is a scalar value.
 
$batch
A batch of one or more SQL statements that is expected to return exactly one row.
 
\@unnamed_parameters
A reference to an array with input for unnamed parameters. This parameter works exactly as the namesake parameter for sql(), see further this function and particularly the section Details on the Parameter Specification.
 
\%named_parameters
A reference to a hash with input for named parameters. This parameter works exactly as the namesake parameter for sql(), see further this function and particularly the section Details on the Parameter Specification.
 
$rowstyle
Specifies how each row in the result is to be represented. Possible values are HASH (each row is a hash keyed by column name), LIST (each row is an array) and SCALAR (each row is a scalar value). See the section Row Styles and Result Styles under sql() for details. Default is HASH.

sql_one() is similar to sql() with $resultstyle set to SINGLEROW, but there is one important difference: sql_one() will croak if the command batch returns more than one row or no row at all.

sql_sp()

Executes a stored procedure or a scalar user-defined function.

($|@|%)result = $sqlsrv->sql_sp($SP_name
                                [, \$retvalue]
                                [, \@positional_parameters]
                                [, \%named_parameters]
                                [, $colinfostyle] [, $rowstyle]
                                [, $resultstyle [, \@keys]]);

Parameters

($|@|%)result
The result set(s) from the stored procedure. The result set is returned in exactly the same fashion as for sql(), please refer to that function and the section Row Styles and Result Styles.
 
$SP_name
The name of a stored procedure or a scalar user-defined function. This can be a one-, two- or three-part name. That is, you can supply database and/or schema. Four-part names including a server-component are not permitted. If any component of the name includes special characters such as space or period, quote that component with brackets or double quotes as you would in T-SQL, for instance:
$sqlsrv->sql_sp('[My.Database].thisschema."that sp"');
\$retvalue
A reference to a scalar that will receive the return value of the stored procedure or the user-defined function. See further the section Return Values below.
 
\@positional_parameters
A reference to an array that holds unnamed parameters. The parameters must appear in the order they appear in the declaration of the stored procedure or UDF. The entries in the array are scalar values, or reference to scalars. See further the section Passing Parameters to Stored Procedures below. Note that parameters to stored procedures works differently from parameters to command batches with sql().
 
\%named_parameters
A reference to a hash for named parameters. The keys in the hash are the parameter names, with or without the leading @. (If you specify both, Win32::SqlServer discards one of them and issues a warning.) The hash entries are scalar values, or reference to scalars. If you have specified the same parameter in \@positional_parameters and \%named_parameters, the entry in \@positional_parameters takes precedence and the other value is discarded, yielding a warning through the current message handler, if Perl warnings are enabled. For further details, see the section Passing Parameters to Stored Procedures below. Note that parameters to stored procedures works differently from parameters to command batches with sql().
 
$colinfostyle
Specifies if and how you want information about the columns in the result set(s). Possible values are COLINFO_NONE (no information), COLINFO_NAMES (names only), COLINFO_POS (column numbers only) and COLINFO_FULL (a hash with detailed information about the columns). This parameter works exactly as for sql(). See the section Getting Column Information under that function for details. The default is COLINFO_NONE.
 
$rowstyle
Specifies how each row in the result is to be represented. Possible values are HASH (each row is a hash keyed by column name), LIST (each row is an array) and SCALAR (each row is a scalar value). This parameter works exactly as for sql(). See the section Row Styles and Result Styles under that function. Default is HASH.
 
$resultstyle
Specifies how the result sets are structured. Possible values are MULTISET (an array of result sets), MULTISET_RC (an array of result sets and row counts for action statements), SINGLESET (an array of rows), SINGLEROW (a single row according to $rowstyle), KEYED (a hash, keyed by the data in the result set), NORESULT (no result set returned) or a reference to a callback routine called for each row. This parameter works exactly as for sql(). See the section Row Styles and Result Styles under that function. Default is SINGLESET.
 
\@keys
This parameter is only applicable when $resultstyle is KEYED, in which case it's mandatory. It specifies which column(s) in the result that is/are the keys. See further the section Row Styles and Result Styles under the topic of sql().

General

sql_sp() uses the RPC mechanism to call the stored procedure or UDF, and does thus not issue any EXEC statement. The RPC mechanism is a faster way to call a stored procedure.

Note that there is one case you will not get the return value or the value of the output parameters: this is when you use a callback routine as the result style, and you return RETURN_CANCEL or RETURN_ERROR from the callback. In this case, the query is cancelled, whereupon output parameters and return value are lost.

Return Values

If a stored procedure returns another value than zero, the default behaviour of sql_sp() is to croak. The assumption is that a stored procedure returns 0 on successful execution, and any non-zero value indicates failure. There is no requirement in T-SQL that you must use return values this way, but it's definitely best practice to do so. To return actual data values from stored procedures, use output parameters instead.

You can override this behaviour with the CheckRetStat and RetStatOK elements of the ErrInfo property.

This does not apply when you call scalar user-defined functions. In this case sql_sp() never aborts execution because of the return value.

Parameters to Stored Procedures

(For brevity, I'm saying "stored procedures" in this section, but everything which is said here applies to scalar user-defined functions as well. Except, then, that they don't have output parameters.)

Win32::SqlServer Retrieves and Caches Parameter Profiles

When you call a stored procedure with sql_sp(), you don't have to bother about the data types of the parameters, because the first time you call the procedure Win32::SqlServer queries SQL Server to get the parameter profile of the stored procedure. On subsequent calls, Win32::SqlServer retrieves the parameter profile from its cache in the Win32::SqlServer property procs. This property is a hash keyed by the procedure name, exactly as you specified it, so my_sp and dbo.my_sp result in two entries in procs. Win32::SqlServer never clears the cache. This means that if you first connect to one database and call my_sp in that database and then use the same Win32::SqlServer object to connect to another database and call a my_sp in that database, Win32::SqlServer will use the cached parameter profile. If the two databases are instances of the same database schema, this makes sense. If not, either prepend the database name to the procedure name in the call to sql_sp(), or delete the old procedure entry from procs. Or simply access the two databases from different Win32::SqlServer objects. The same applies if your script would recreate a procedure with a different parameter profile. (As for the contents of the entry in procs, that is off-limits. Any meddling is entirely unsupported.)

A consequence of this is that there are some special system stored procedures that you cannot call with sql_sp() because they have dynamic parameter lists. One such example is sp_executesql. For such procedures, call them through sql() using EXEC instead, or use the mid-level query routines.

Passing Values In and Out

Each entry in \@positional_parameters and \%named_parameters is a parameter value, or a reference to a parameter value. The latter is necessary for output parameters when you use an anonymous array or hash to pass the parameters.

Say that you have this stored procedure:

CREATE PROCEDURE some_sp @in int,
                         @out int OUTPUT AS
The proper way to call this procedure is:
my ($out);
$sqlsrv->sql_sp('some_sp', [1, \$out]);

That is, in the array you must pass a reference to the variable that is to receive the value in the output parameter. If you just passed $out, then Win32::SqlServer would modify the entry in the anonymous array, but $out would be unchanged. For this reason, sql_sp() issues a warning, if an array/hash entry for an output parameter is not a reference to a scalar. The warning is only issued if Perl warnings are enabled.

(Yes, if you use a real array and not an anonymous one, like this:

my @params = (1, undef);
$sqlsrv->sql_sp('some_sp', \@params);

$params[1] is set to the value of @out, despite not being a reference. Thus, in this case the warning is incorrect. But there is no way for sql_sp() to tell whether you passed an anonymous or a real array, and I expect anonymous arrays to be used more often than real.)

As for how to specify the actual values for different data types, see the section Data-type Representation. For table-valued parameters, see just below.

Examples

sp_helpdb returns two result sets. Here is an example using a callback that prints out the result sets in a fairly simple manner:

sub print_hash {
   my($hash, $ressetno) = @_;
   my ($col);
   print "$ressetno: ";
   foreach $col (%$hash) {
      print "$col: $$hash{$col} ";
   }
   print "\n";
   RETURN_NEXTROW;
}
$sqlsrv->sql_sp("sp_helpdb", ['tempdb'], HASH, \&print_hash);

Here is an example with a procedure that takes two dates as parameters to count the number of records in that interval. Passing NULL means no limit in that direction. The SP permits you to restrict the count to records of a certain flavour. The count is returned in an output parameter. There are no result sets.

CREATE PROCEDURE putte_sp @startdate datetime = NULL,
                          @enddate   datetime = NULL,
                          @flavour   smallint = NULL,
                          @no_of_rec int      OUTPUT AS

Say we want to know how many records there are from 1997 and on, of all flavours, and we also want the return value. In T-SQL the call would be:

EXEC @ret = putte_sp '19970101', @no_of_rec = @no_of_rec OUTPUT

In Win32::SqlServer this translates to:

$sqlsrv->sql_sp('putte_sp', \$ret, ['19970101'], {'no_of_rec' => \$no_of_rec});

Notice how we pass a reference to $no_of_rec, rather than the variable itself, so we can retrieve the output value later on. We also left out the @ from the hash key, knowing that Win32::SqlServer sorts it out anyway.

sql_insert()

Inserts a row in to table from a hash, using the keys in the hash as column names.

$sqlsrv->sql_insert($table, \%values);
$table
The name of a table or a view to insert into. The name can be a one-, two- or three-part name. That is, you can specify database, schema and table. You cannot use a four-part name to specify a remote table. If any component includes special characters, you must put that component in brackets or double quotes as in this example:
$sqlsrv->sql_insert('"My.Database"..[Order Details]', \%order_details);
\%values
A reference to a hash which holds the values to insert. The keys of the hash should agree with the column names of the table.

On the first invocation for a certain table, sql_insert() inquires the system tables in SQL Server to find out the data types of all columns. This information is saved in the Win32::SqlServer property tables, and sql_insert() uses the cached information on subsequent calls. tables is a hash. You can force a refresh by deleting the hash key for a table. The key is exactly as you entered it, thus "Orders" and "orders" are different tables in this context, even if you have a case-insensitive database.

As for specifying the data in the columns, please see the section Data-type Representation.

sql_insert() results in a parameterised call to sql(), and eventually sp_executesql. Thus, there will be a cached query plan, which is good for performance. Nevertheless, if you have a lot of data to insert, there are several alternatives that are better: table-valued parameter (SQL 2008 or later), XML or BCP.

Working with Table Parameters

Table-valued parameters were added in SQL Server 2008. To use table-valued parameters, you need to use the SQLNCLI10 provider or later. Table-valued parameters are read-only. This section covers how you work with table parameters with the high-level routines sql(), sql_one() and sql_sp().

When you pass a table-valued parameter to sql() and sql_one(), you need to provide the type name, but that's all. On the first time you present the table type, Win32::SqlServer will retrieve the definition of the table type from SQL Server. Win32::SqlServer saves the definition in the property tabletypes, and on future references to the type, Win32::SqlServer will retrieve the definition from the cache. Note that the cache is keyed by the string exactly as you passed it, so dbo.mytabletype, mytabletype and MyTableType will be three cache entries, even if they refer to the same type.

As you recall, sql_sp() retrieves the parameter profile for the stored procedure from SQL Server or the Win32::SqlServer property procs. When sql_sp() finds that a parameter is a table, it will look up the type name in tabletypes, and if it is absent, sql_sp() queries SQL Server for the definition and saves it in the cache.

To pass a value for a table-valued parameter, you pass a reference to an array of rows where each row is a hash or an array with the column values. It's perfectly possible to mix hashes and arrays in the same table array if you feel like it.

When you pass row as a hash, the hash should be keyed by the column names (case-sensitive!). If you leave out a column, this is the same as specifying undef for the column, that is NULL; there is no way you can specify that you want any default value for a column to apply. If the hash includes a key that does not match a column in the table type, Win32::SqlServer emits a warning through the current message handler, if Perl warnings are enabled. Likewise, Win32::SqlServer issues a warning if the hash includes a key for a column that is not writable, for instance an IDENTITY column or a computed column.

When you pass a row as an array, you must pass the columns exactly in the order they come in the table definition. Even if a column is not writable (for instance an IDENTITY column), there still has to be an element in that slot, although it does not matter what you put there. You can pass an array with fewer elements than there are columns in the table type, in which case the columns missing from the array will be set to NULL. If your array has more columns than there are columns in the type, Win32::SqlServer issues a warning through the current message handler, if Perl warnings are enabled.

As for the column values, you pass the data types as you would in other contexts, see the section Data-type Representation.

To specify an empty table, you can pass [] or undef. With sql_sp(), you can leave out the parameter entirely.

Confession: there is unfortunately a considerable overhead for passing TVPs with Win32::SqlServer. In a test I conducted, it took around two seconds to pass 50000 rows for a one-column table.

Let's now look at some examples. In all examples we will work with this type.

CREATE TYPE mytabletype AS TABLE
       (ident   int         IDENTITY,
        chr     char(1)     NOT NULL,
        intcol  int         NOT NULL,
        datecol date        NULL,
        string  varchar(40) NULL)        

Note: I'm not sure that I see the point with an IDENTITY column in a table-valued parameter. I have included one here to be able to demonstrate how you work with non-writable columns.

Let's first look at using table types with stored procedures. Here is an example procedure that you cannot accuse for being overly useful, but I wanted to keep the examples brief.

CREATE PROCEDURE myproc @chr char(1),
                        @tvp mytabletype READONLY AS

SELECT totalcnt = COUNT(*),
       identsum = SUM(ident),
       chrcnt   = SUM(CASE chr WHEN @chr THEN 1 ELSE 0 END),
       datecnt  = SUM(CASE WHEN datecol IS NOT NULL THEN 1 ELSE 0 END),
       strcnt   = SUM(coalesce(len(string), 0))
FROM   @tvp

In the case when you set up the table rows in your own code, I think hashes is the best choice. This makes the code more readable, and it also makes it easier to leave out columns you want to leave NULL. So in the first example, we pass the rows as hashes:

use strict;
use Win32::SqlServer;

my $sqlsrv = sql_init();

my @rows;

# Our first row. Here we provide all columns, but ident which we don't
# have control over anyway.
$rows[0]{'chr'}     = 'A';
$rows[0]{'intcol'}  = 14;
$rows[0]{'datecol'} = '2009-01-01';
$rows[0]{'string'}  = 'Typewriter';

# Our second row. For this row, we leave out datecol and string.
$rows[1]{'chr'}    = 'B';
$rows[1]{'intcol'} = 9;

# Our third and foruth rows. By now we have realised that we can specify
# the entire hash at once:
$rows[2] = {'chr' => 'A', 'intcol' => 5,  'datecol' => '1632-11-06'};
$rows[3] = {'chr' => 'C', 'intcol' => 12, 'string' => 'Luxury'};

# Let's now call our procedure. We need to pass a reference to the array.
my %result = $sqlsrv->sql_sp('dbo.myproc', ['A', \@rows],
                              Win32::SqlServer::SINGLEROW,
                              Win32::SqlServer::HASH);

# Output the result.
print "totalcnt: $result{'totalcnt'}\n";
print "identsum: $result{'identsum'}\n";
print "chrcnt:   $result{'chrcnt'}\n";
print "datecnt:  $result{'datecnt'}\n";
print "strcnt:   $result{'strcnt'}\n";

The output is:

totalcnt: 4
identsum: 10
chrcnt:   2
datecnt:  2
strcnt:   16

The way we filled in the rows in this example was somewhat tedious. Perl permits you to do this more compactly:

use strict;
use Win32::SqlServer;

my $sqlsrv = sql_init();

# Here define all rows at once in an array constructor.
my $rows = [{'chr' => 'A', 'intcol' => 14, 'datecol' => '2009-01-01',
             'string' => 'Typewriter'},
            {'chr' => 'B', 'intcol' => 9},
            {'chr' => 'A', 'intcol' => 5, 'datecol' => '1632-11-06'},
            {'chr' => 'C', 'intcol' => 12, 'string' => 'Luxury'}];

# This time we pass the parameters as named in a hash.
my %result = $sqlsrv->sql_sp('dbo.myproc', {chr => 'A', 'tvp' => $rows},
                              Win32::SqlServer::SINGLEROW,
                              Win32::SqlServer::HASH);

# Output the result, same as above.
print "totalcnt: $result{'totalcnt'}\n";
print "identsum: $result{'identsum'}\n";
print "chrcnt:   $result{'chrcnt'}\n";
print "datecnt:  $result{'datecnt'}\n";
print "strcnt:   $result{'strcnt'}\n";

Let's now look at how you pass the rows as arrays instead. Arrays are probably mainly of interest when the table type has a small number of columns, at least in the case you populate the rows yourself.

use strict;
use Win32::SqlServer;

my $sqlsrv = sql_init();

my @rows;

# The first row. We must include a placeholder for ident, since this is
# the first column.
push(@rows, [undef, 'A', 14, '2009-01-01', 'Typewriter']);

# The second row. Since datecol and string are NULL, we can just leave
# them out.
push(@rows, [undef, 'B', 9]);

# The third row. What we specify for ident does not matter. It just has
# to be there.
push(@rows, [4711, 'A', 5, '1632-11-06']);

# The fourth row. Since string has a value, we need to explicitly
# provide an undef for datecol.
push (@rows, [0, 'C', 12, undef, 'Luxury']);

my %result = $sqlsrv->sql_sp('dbo.myproc', {chr => 'A', 'tvp' => \@rows},
                              Win32::SqlServer::SINGLEROW,
                              Win32::SqlServer::HASH);

# Output the result, same as before.
print "totalcnt: $result{'totalcnt'}\n";
print "identsum: $result{'identsum'}\n";
print "chrcnt:   $result{'chrcnt'}\n";
print "datecnt:  $result{'datecnt'}\n";
print "strcnt:   $result{'strcnt'}\n";

Let's now look at examples where we use table-valued parameters with sql(). Here is the example with the hash rows again:

use strict;
use Win32::SqlServer;

my $sqlsrv = sql_init();

# It's the same old rows.
my $rows = [{'chr' => 'A', 'intcol' => 14, 'datecol' => '2009-01-01',
             'string' => 'Typewriter'},
            {'chr' => 'B', 'intcol' => 9},
            {'chr' => 'A', 'intcol' => 5, 'datecol' => '1632-11-06'},
            {'chr' => 'C', 'intcol' => 12, 'string' => 'Luxury'}];

# The SQL statement. It's the same as the body of the stored procedure.
# We put SQLEND in single quotes to protect the @ in the SQL variabels.
my $sqlcode = <<'SQLEND';
   SELECT totalcnt = COUNT(*),
          identsum = SUM(ident),
          chrcnt   = SUM(CASE chr WHEN @chr THEN 1 ELSE 0 END),
          datecnt  = SUM(CASE WHEN datecol IS NOT NULL THEN 1 ELSE 0 END),
          strcnt   = SUM(coalesce(len(string), 0))
   FROM   @tvp
SQLEND

# Set up the parameter list. Since the batch uses named parameters, we need
# to specify the parameter list as a hash. First the character parameter.
my %params;
$params{'@chr'} = ['char(1)', 'A'];

# And then the table parameter. We can use the name of the table type directly
# sql() will look it up and find that it is a table type.
$params{'@tvp'} = ['mytabletype', $rows];

# Now we call the batch.
my %result = $sqlsrv->sql($sqlcode, \%params,
                          Win32::SqlServer::SINGLEROW,
                          Win32::SqlServer::HASH);

# Output the same old result.
print "totalcnt: $result{'totalcnt'}\n";
print "identsum: $result{'identsum'}\n";
print "chrcnt:   $result{'chrcnt'}\n";
print "datecnt:  $result{'datecnt'}\n";
print "strcnt:   $result{'strcnt'}\n";

Overall, I think named parameters is to prefer, and even more so when you work with table-valued parameters. But if you want to work with positional parameters, you can. Here is an example of this. This example also passes the rows an array constructed in one go. Here we explicitly say that the parameter is a table, and pass the name of the table type as the third element in the array:

use strict;
use Win32::SqlServer;

my $sqlsrv = sql_init();

# The rows, now as arrays.
my $rows = [[undef, 'A', 14, '2009-01-01', 'Typewriter'],
            [undef, 'B', 9],
            [undef, 'A', 5, '1632-11-06'],
            [undef, 'C', 12, undef, 'Luxury']];

# The SQL statement. Now with positional parameters.
my $sqlcode = <<'SQLEND';
   SELECT totalcnt = COUNT(*),
          identsum = SUM(ident),
          chrcnt   = SUM(CASE chr WHEN ? THEN 1 ELSE 0 END),
          datecnt  = SUM(CASE WHEN datecol IS NOT NULL THEN 1 ELSE 0 END),
          strcnt   = SUM(coalesce(len(string), 0))
   FROM   ?
SQLEND

# Now we call the batch. This time the parameters are in an anonymous array.
my %result = $sqlsrv->sql($sqlcode, [['char(1)', 'A'],
                                     ['table', $rows, 'dbo.mytabletype']],
                           Win32::SqlServer::SINGLEROW,
                           Win32::SqlServer::HASH);

# Output the result.
print "totalcnt: $result{'totalcnt'}\n";
print "identsum: $result{'identsum'}\n";
print "chrcnt:   $result{'chrcnt'}\n";
print "datecnt:  $result{'datecnt'}\n";
print "strcnt:   $result{'strcnt'}\n";

In all these examples, I have put the rows for the table-valued parameters in a separate variable. But if you want to show off, you can put it all in the parameter list:

my %result = $sqlsrv->sql($sqlcode,
                          [['char(1)', 'A'],
                           ['dbo.mytabletype',
                              [[undef, 'A', 14, '2009-01-01', 'Typewriter'],
                               [undef, 'B', 9],
                               [undef, 'A', 5, '1632-11-06'],
                               [undef, 'C', 12, undef, 'Luxury']]]],
                           Win32::SqlServer::SINGLEROW,
                           Win32::SqlServer::HASH);

Or why not:

my %result = $sqlsrv->sql($sqlcode,
             {chr => ['char(1)', 'A'],
              tvp => ['table(mytabletype)',
                [{'chr' => 'A', 'intcol' => 14,
                  'datecol' => '2009-01-01', 'string' => 'Typewriter'},
                 [undef, 'B', 9],
                 {'chr' => 'A', 'intcol' => 5, 'datecol' => '1632-11-06'},
                 {'chr' => 'C', 'intcol' => 12, 'string' => 'Luxury'}]
                ]},
              Win32::SqlServer::SINGLEROW, Win32::SqlServer::HASH);

But don't blame me if you get lost in this orgy of nested brackets and braces. This example has a third way of specifying the table type: table followed by the type name in parentheses.

We now change the first data row a bit:

{'ident' => 1, 'chr' => 'A', 'intcol' => 14, 'datecol' => '2009-01-01',
 'stringcol' => 'Typewriter'}

That is, we have included the IDENTITY column, and we have erred on one column name. When we run this (assuming we specifed -w that is!), we get these two warnings:

Message -1 from 'Win32::SqlServer', Severity: 10
Warning: input hash to inserttableparam includes key 'stringcol',
   but no such column has been defined for this table parameter.
Message from Win32::SqlServer at C:\temp\slask.pl line 34
Message -1 from 'Win32::SqlServer', Severity: 10
Warning: input hash to inserttableparam includes key 'ident',
   but this column has been defined with usedefault=1 and the value is ignored.
Message from Win32::SqlServer at C:\temp\slask.pl line 34

As these checks are performed by the mid-level interface, the error messages are worded from that horizon. You will have to live with that.

Finally some examples on how to pass an empty table. All these are valid:

$sqlsrv->sql_sp('dbo.myproc', ['A', [] ]);
$sqlsrv->sql_sp('dbo.myproc', ['A', undef]);
$sqlsrv->sql_sp('dbo.myproc', ['A']);

The last example works, because in SQL Server there is an implicit default value of the empty table for all table-valued parameters. (On the other hand, you are not permitted to pass NULL for table parameters, so when Win32::SqlServer permits you to specify undef it bends the rules a bit.) For sql() you have these choices:

$sqlsrv->sql($sqlcode, {chr => ['char(1)', 'A'],
                        tvp => ['mytabletype', [] ]});
$sqlsrv->sql($sqlcode, {chr => ['char(1)', 'A'],
                        tvp => ['mytabletype', undef]});
$sqlsrv->sql($sqlcode, {chr => ['char(1)', 'A'],
                        tvp => ['mytabletype']});

Since the batch does not compile if you don't define the parameter, you can't escape it entirely.

Mid-Level Query Functions

The high-level query functions are implemented in Perl, and they in their turn use the mid-level functions that are implemented in C++ (save for get_result_sets()). In their turn they call the low-level functions in the OLE DB API, not exposed directly to Perl.

If you think that the high-level functions puts you too much into a straightjacket (or are just too bewildering for you), you can call the mid-level functions directly. But... there is a disclaimer: the extensive test suite for Win32::SqlServer does not include any tests of direct use of the mid-level functions. Thus, if your usage of these functions goes beyond what the high-level functions use, you are on somewhat less solid ground.

While the high-level functions presents the Win32::SqlServer as a stateless object by submitting the query and getting the result sets in one single call, the mid-level functions makes the Win32::SqlServer object very stateful, and there is a certain order in which you must call these functions:

1. First define the text of the command batch with initbatch().
2. For each parameter in the batch, define the parameter with enterparameter().
2a. Define the columns of each table-valued parameter with definetablecolumn(), and populate the table parameters with inserttableparam(). (You can do this once the table-parameter has been entered, or you can wait until all parameters have been entered.)
3. Execute the command batch with executebatch().
4. Iterate over all result sets with nextresultset() until this function returns a false value.
5. Within a result set, iterate over all rows with nextrow() until it returns false, alternatively skip the result set by calling cancelresultset(). As long as you have not reached the end of the result set, you can retrieve column information with getcolumninfo().
6. When you have retrieved all result sets, get any output parameters from the command batch with getoutputparams().

If all you need is more freedom when you specify the parameters, but you still want the result sets and the rows packaged according to row styles and result styles, you can perform steps 4 and 5 in one go by calling get_result_sets().

You cannot initiate a new command until you have retrieved all result sets and the output parameters, unless you first call cancelbatch(). You can find out in which state you are by calling getcmdstate().

There are a couple of features in Win32::SqlServer that are not supported by the mid-level functions (because the code to implement them  is in the high-level query functions). Here is a list of general restrictions:

I list further restrictions with the function or parameter they apply to.

initbatch()

Defines the text for a command batch.

$ret = $sqlsrv->initbatch($batch)
$ret
True if the command was entered sucessfully, false if not.
$batch
The text for the batch. The batch can be parameterised, with parameters being indicated with ? as placeholder. In difference to sql(), names starting with @ are not understood as parameters. (If you want to use named parameters, an alternative is to wrap the command batch in a call to sp_executesql.) See also below about ODBC call syntax.

Once you have called initbatch(), you cannot call initbatch() again until you have retrieved all result sets and the output parameters or cancelled the batch with cancelbatch().

As why you should parameterise your command in most cases, and when you should not, please see the subsections Why Parameterising SQL Commands at All? and When You Should Not Parameterise in the sql() topic.

You must be connected to call initbatch(), unless you have set AutoConnect (in which case the call to initbatch will trigger a connection).

Using ODBC Call Syntax

To initiate a call of a stored procedure through RPC, use the ODBC call syntax, for instance:

{? = call dbo.some_sp(?, ?)}

The first ? is for the return value (which you can omit, if you opt to ignore the return value), and the ? in the parentheses represent the parameters to the stored procedure. You should only supply as many ? as you actually intend to provide. For instance, assume that some_sp actually looks like this:

CREATE PROCEDURE some_sp @par1 int = 19,
                         @par2 char(2) = NULL,
                         @par3 datetime = NULL,
                         @par4 bit = 0 AS
You intend to provide a value only for @par2 and @par4 when you call enterparameter(). In this case the parameter list in the ODBC call should look like above, that is include two ? only.

You can provide values directly in the command batch like this:

{? = call dbo.some_sp(?, ?, ?, 1)}

(But please only do this when you supply all parameters. I have no idea what happens if you leave out some.) You can also use named parameters:

{? = call dbo.some_sp(@par2 = ?, @par4 = ?)}

OLE DB permits you to mix ODBC call syntax with regular commands, or to provide two calls for the same command batch. However, I have not tested this, nor analysed the ramifications of it. Be warned.

enterparameter()

Defines a parameter for a parameterised command batch.

$ret = $sqlsrv->enterparameter($nameoftype, $maxlen, $paramname,
                               $isinput, $isoutput [, $value
                               [, $precision [, $scale [, $typeinfo]]]]);
$ret
True if the parameter was entered successfully, false if not.
 
$nameoftype
The name of the type, without any specification of length, precision etc. That is, only varchar, not varchar(3). This must be a system type; in difference to sql(), you cannot use the name of a user-defined type created with CREATE TYPE or sp_addtype. The name must be the main name for the type; the name that appears in sys.types. You cannot use the data-type synonyms that ships with SQL Server. That is, you must use int and char, not integer or character. There is one exception: you can use rowversion for timestamp. For user-defined CLR types, use UDT. For table types, use table. The name must be in lowercase, except for UDT which must be all uppercase.
 
$maxlen
 
$paramname
The name for the parameter. This works differently depending on the context where the parameter appears. This is something that is happening in the OLE DB provider, not in Win32::SqlServer. From my investigations, I've identified four different cases.

  1. Parameters in regular SQL batch.
  2. ODBC Call Syntax – the return value.
  3. ODBC Call Syntax – named parameters, {? = call some_sp(@par3 = ?)}.
  4. ODBC Call Syntax – positional parameters, e.g. {? = call some_sp(?, ?)}.

The first three cases appear to behave the same, whereas the fourth is different.

Case 1-3 – you can set the parameter name to undef, or you can use whatever name you like, as long as it starts with @. (Note: for the return value in case 2, the SQLOLEDB provider will actually accept a name that does not start with @. But this is a bug! SQL Native Client never accepts names without @ in front.) The name is never passed to SQL Server, but you can use it when you retrieve the value of an output parameter, see getoutputparams(). For a regular SQL batch, the OLE DB provider will use names like @P1, @P2 etc when it constructs the call to sp_executesql.

Case 4 – in this case, if you set the parameter name to undef for, say, the third ?, this means that you pass a value for the second parameter. (Since the first ? is the return value). If you specify a name, this must be the actual name of a parameter to the stored procedure. It appears that you can in fact mix named and unnamed parameters entirely here, and provide a name for the first parameter (that is, the second ?), but leave it out for the second. I would not recommend this, though.

$isinput
1 if the parameter is an input parameter. Most parameters are input parameters, but return values from stored procedures are not. Here is another example of a parameter that is not an input parameter:
SELECT ? = @@version
$isoutput
1 if the parameter is an output parameter.
 
$value
The value for the parameter. You can leave this parameter out, if you are passing NULL, or you have specified 0 for $isinput. If you specified 1 for $isinput, Win32::SqlServer will attempt to convert the value to type specified in $nameoftype, as described in the chapter Data-type Representation. If the parameter is a table-valued parameter, you must specify undef for $value, or else Win32::SqlServer will croak.
 
$precision
$scale
Precision and scale for decimal and numeric. If you don't specify these, the default values are 18 and 0 respectively.
 
$typeinfo
This parameter applies only to parameters of the types xml, UDT and table. For xml, it specifies a schema collection and is optional. For UDT and table, it specifies the name of the actual CLR type or table type and is mandatory. All names can be specified with three-part notation on the form database.schema.object. (Although, you may not actually be able to specify a type in a different database.) If there are parts that include special characters such as space or period, you must quote these in brackets or double quotes. This parameter is not applicable when you use SQLOLEDB.

You must call initbatch() prior to calling enterparameter(). You cannot call enterparameter(), once you have called executebatch().

If Win32::SqlServer cannot convert $value to $nameoftype, enterparameter() returns a false value. Once this has happened for a parameter, you will not be able to execute the batch with executebatch(). Nevertheless, you can continue to enter more parameters. This makes it possible to catch all parameter errors in one go. Each parameter value that cannot be converted results in a warning communicated through the current message handler..

You must call enterparameter() exactly as many times as there are parameter holders in your command batch. Specifying too many parameters, will yield the message Multiple-step OLE DB operation generated errors. But this is a generic message and can appear in other situations as well.

If any of the parameters is a table-valued parameter, you must define the columns in the table type by calling definetablecolumn() for each column. You then enter rows with inserttableparam().

Note that far from all parameter errors are detected by enterparameter(). Some errors will not raise an error message until you call executebatch(), for instance an illegal parameter name.

definetablecolumn()

Defines a column in a table-variable parameter previously entered with enterparameter().

$ret = $sqlsrv->definetablecolumn($tblname, $colname, $nameoftype
                                  [, $maxlen [, $prec, [, $scale
                                  [, $usedefault [, $typeinfo]]]]]);
$ret
True if the call completed successfully, else false.
 
$tblname
The name of the table-valued parameter for which you are defining a column. This is the name you specified in the $paramname parameter to enterparameter(). If you leave $tblname as undef, this is understood as the most recently entered parameter, even if this is not a table parameter. Thus, if you work with unamed parameters, you must define your table columns and insert your rows directly after you have entered the parameter. If the parameter $tblname refer to is not a table parameter, definetablecolumn() will croak.
 
$colname
The name of the column. You don't have to use the actual names in the table type, although it's best practice to do so. If you pass rows as hashes to inserttableparam(), the names you use in $colname will be your hash keys.
 
$nameoftype
The data type for the parameter. The same rules as for enterparameter() apply. That is, you must use the system names, and you should not include length or similar. Not surprisingly, the type table is not permitted here, but all other types are legit.
 
$maxlen
Maximum length for character and binary data types (including UDT). For large data types, that is (n)text, image, the (MAX) types and xml, specify -1. The length is in not bytes, so specify 20 for nvarchar(20).
 
$precision
$scale
Precision and scale for decimal and numeric. If you don't specify these, the default values are 18 and 0 respectively.
 
$usedefault
This is a boolean parameter with a default value of false. When true, this specifies that the default value for this column should apply to all rows inserted into the table-valued parameter. You must set $usedefault to true for columns that cannot be set explicitly: IDENTITY columns, timestamp/rowversion columns and computed columns.
 
$typeinfo
Type information for xml and UDT columns, in the same vein as for enterparameter().

You should call definetablecolumn() once for column in the table type. You need to define the columns in the order they are defined in the table type. You cannot define the fifth column first etc.

You specify the number of columns in the table type with the $maxlen parameter to enterparameter(). Once you have defined that many columns, you can start entering rows with inserttableparam().

inserttableparam()

Inserts one row into a table-valued parameter.

$ret = $sqlsrv->inserttableparm($tblname, $inputref);
$ret
Returns true if the row was inserted successfully, and all values were convertible. False, if one or more values were not convertible to the SQL type in question.
 
$tblname
The name of the table-valued parameter you are insert a row into. This is the name you specified in the $paramname to enterparameter(). As with definetablecolumn(), undef refers to the most recently entered parameter, even if this is not a table parameter.
 
$inputref
A reference to a hash or an array of column values for the row. See below for details.

Before you can call inserttableparam() for a table-valued parameter, you should have called definetablecolumn() for all columns in the table type.

When you provide the values in a hash, the hash should be keyed with the names you used when you set up the table type with definetablecolumn(). If you leave out a column, that column is simply set to NULL. If your hash includes a key that does not match a column in the table, inserttableparam() emits a warning if Perl warnings are enabled. inserttableparam() also gives a warning, if you specify a value for a column for which you have specified $usedefault = 1.

When you provide the values in an array, the values should appear in the order they appear in the table definition, including columns with $usedefault = 1, even if the values you provide will be ignored. If your array has fewer elements than the table type, the last columns will be set to NULL. If your array has more elements than there are columns in the table type, inserttableparam() issues a warning, if Perl warnings are enabled.

As with enterparameter(), if a value fails to convert, this only yields a warning at this point, but when you call executebatch(), there will be an error and the batch will not be executed. This permits you to detect all parameter errors in one go.

executebatch()

Executes a batch previously defined with initbatch().

$ret = $sqlsrv->executebatch();
$ret
True if the command batch was successfully submitted for execution, else false.

You must first define a command batch with initbatch() and enter definitions for all parameters to the batch before you can call executebatch(). Once you have executed the command batch, you cannot resubmit the command.

If you entered a table-valued parameter with enterparameter(), but you did not define as many columns as you defined with the $maxlen parameter, executebatch() will croak.

Note that the return status reports whether the submission of the batch was successful. That is, errors you get at this point are those detected by Win32::SqlServer and the OLE DB provider. Errors from SQL Server are not reported until you call nextresultset(). (And if your batch returns result sets before the error, you will not see the error until you have called nextresultset() for these result sets.) If the command submission fails, the batch is cancelled, and you cannot perform any further work with it.

Most errors are communicated through the current message handler, but pure usage errors like calling executebatch() without having called initbatch() first will cause Win32::SqlServer to croak.

nextresultset()

Moves on to the next result set from the command batch.

$more_results = $sqlsrv->nextresultset([$rowsaffected});
$more_results
True if there was indeed a result set. False if there were no more result sets.
 
$rowsaffected
An optional output parameter that reports the number of affected rows for an INSERT, DELETE or UPDATE statement. You don't get any row counts if SET NOCOUNT ON is in effect

You can only call nextresultset(), if you previously have successfully submitted a command batch with executebatch(). You cannot call nextresultset(), if there are more rows to retrieve from the current result set. You must either iterate over nextrow() until this function returns false, or discard the rows with cancelresultset() to be able to move to the next result set.

For each command batch you will need to call nextresultset() at least twice. First once to move to the first result set which is always there, even if there are no rows. And then a second time to move past the last result set. It is first at this point that output parameters will be available. Once nextresultset() have returned false, you cannot call nextresultset() again for the current command batch.

Not all result sets have rows. Errors and other messages from SQL Server also yield a "result set", as does the row count from INSERT, UPDATE and DELETE statements. And there are entirely empty result sets. For result sets of this kind, you can move on the next result set directly without calling nextrow(). But if you want to play safe, always enter a loop over nextrow().

If the command batch results in SQL errors, they are communicated through the current message handler when you call nextresultset(). Note that if a batch returns several result sets, an error is not reported until you have retrieved previous result sets. Consider this batch.

SET NOCOUNT OFF
UPDATE tbl SET ... WHERE
INSERT tbl (...) VALUES ...
SELECT @@identity
DELETE tbl2 WHERE ...
Assume now that the DELETE statement causes a foreign-key violation. This will not be reported until you call nextresultset() the fourth time. The two first result sets will bring you the row counts from the UPDATE and INSERT statements, the third the value of @@identity.

nextrow()

Retrieves the next row from the current result set.

$more_rows = $sqlsrv->nextrow($hashref, $arrayref);
$more_rows
True, if a row was returned, and there may be more rows. False means that no row was returned, because you have reached the end of the result set.
 
$hashref
A scalar variable that on output receives the reference to a hash that holds the data for the row. The keys in the hash are the column names. For nameless columns, you get Col 1, Col 2 etc. Note that you must pass a scalar variable. Passing a reference to a hash on the form \%hash won't do. If you don't care about the hash, you can pass undef.
 
$arrayref
A scalar variable that on output receives the reference to an array that holds the data for the row. As for $hashref, you must pass a scalar, \@array will not work. If you don't care about the array, pass undef. You cannot omit the parameter.

You can only call nextrow() when you have an active result set, that is you have previously called nextresultset() and you have not yet reached the end of that result set. Once nextrow() has returned false, you cannot call nextrow() again, until you have advanced to the next result set.

As noted under nextresultset(), some result sets convey other information than rows from queries. You can still call nextrow() once for these result sets.

getcolumninfo()

Retrieves information about the columns in the current result set.

$sqlsrv->getcolumnnames($hashref, $arrayref)
$hashref
A scalar variable that on output receives the reference to a hash that holds the column information as detailed below. The keys in %$hashref are the column names in the query. As with nextrow(), you get Col 1, Col 2 etc for nameless columns. You must pass a scalar variable, \%hash will not work. If you don't care about the hash, you can pass undef.
 
$arrayref
A scalar variable that on output receives the reference to an array that holds the column information as detailed below. The value of each array entry is a reference to a hash  with the keys detailed below. As for $hashref, you must pass a scalar; you cannot pass \@array. If you don't care about the array, pass undef. You cannot omit the parameter.

You can call getcolumninfo() exactly in the same situations you can call nextrow(). That is, you must previously have called nextresultset() and nextrow() has not yet returned false. Some result sets conveys row counts, and have no column information. For these result sets %$hashref and @$arrayref will be empty.

For each column you get a hash with eight keys:

Colno
The position of the column in the result set; the first column has number 1.
 
Name
The name of the column. Note that this is the name of the column as returned by SQL Server. That is, a nameless column will here appears as the empty string, not Col 1 as when then column name appears as a hash key.
 
Type
The data type of the column. The type names that appear are the main names used in the system catalogue. That is, always int and timestamp, never integer and rowversion, nor the names of any user-defined data types. Furthermore, any column of the type numeric appears as decimal. The types text, ntext and image appear as varchar, nvarchar and varbinary respectively, with Maxlength set to undef. A CLR type appears as UDT; the actual name of the type is not available. This applies to both user-defined CLR types and built-in CLR types.
 
Maxlength
The maximum length for the column. Mainly relevant for character and binary data types, but SQL Server appears to populate it for other types as well. Maxlength is undef for large data types as well for types where it is not applicable.
 
Precision
The precision for the column. Mainly relevant for decimal and numeric , but SQL Server appears to populate it for other types as well. Precision is undef for types where it is not applicable.
 
Scale
The scale for the column. Relevant for decimal, numeric, time, datetime2 and datetimeoffset. Scale is undef for types where it is not applicable.
 
Maybenull
1 if there may be NULL values in the column, 0 if NULL values cannot appear.
 
Readonly
Reflects whether SQL Server thinks it is permissible to update this column directly in the rowset. Since you don't operate on the rowset directly anyway with Win32::SqlServer, this piece of information is of esoteric interest. But if you ever worked with ADO, and received  a weird error when you tried to update a field in a recordset, it probably was because the column was read-only.

Note that the column-information hash is not a tied hash. Thus, misspelling of any of the hash keys will go unnoticed.

get_result_sets()

Retrieves all result sets and rows for a command batch, and returns them on the specified form.

($|@|%)result = $sqlsrv->get_result_sets([$colinfostyle][, $rowstyle]
                                         [, $resultstyle] [\@keys]);
($|@|%)result
The result set(s) from the command batch. Depending on the $rowstyle and $resultstyle parameters, this can be an array, a hash or a scalar. If the result is an array or a hash, you can always use a scalar to receive the return value as a reference to the array/hash.
 
$colinfostyle
Specifies if and how you want information about the columns in the result set(s). Possible values are COLINFO_NONE (no information), COLINFO_NAMES (names only), COLINFO_POS (column numbers only) and COLINFO_FULL (a hash with detailed information about the columns). See the section Getting Column Information in the description of sql() for details. The default is COLINFO_NONE.
 
$rowstyle
How rows are represented in the result set. Can be HASH (the default), LIST (to get an array) or SCALAR. See the section Row Styles and Result Styles in the description of sql() for details.
 
$resultstyle
Specifies how the result sets are to be structured. Possible values are MULTISET (an array of result sets), MULTISET_RC (an array of result sets and row counts for action statements), SINGLESET (an array of rows), SINGLEROW (a single row according to $rowstyle), KEYED (a hash, keyed by the data in the result set), NORESULT (no result set returned) or a reference to a callback routine that is called on each row. See the section Row Styles and Result Styles in the description for sql() for details. Default is SINGLESET.
 
\@keys
This parameter is only applicable when $resultstyle is KEYED, in which case it's mandatory. It specifies which column(s) in the result that is/are the keys.

You can only call get_result_sets() when you have executed a command batch with executebatch(). When get_result_sets() have completed, you can go on and fetch output parameters with getoutputparams(). If there are no output parameters, all resources allocated for the batch are released, and Win32::SqlServer is ready to accept a new call to initbatch() or a high-level query function.

get_result_sets() is in fact the inner part of sql() and sql_sp(), and the return value and the style parameters works exactly as for these two. See the sections Row Styles and Result Styles and Getting Column Information in the topic of sql() for details.

getoutputparams()

Retrieves the output parameters from a command batch.

$sqlsrv->getoutputparams($hashref, $arrayref);
$hashref
A scalar variable that on output receives a reference to a hash. The keys in the hash are the parameter names (don't forget the leading @) you provided with enterparameter(). For parameters for which you passed undef for the name, the keys are Par 1, Par 2 etc (without any @.) You must pass a scalar, you cannot pass a reference to hash like \%hash. You can pass undef if you don't care about the hash.
 
$arrayref
A scalar variable that on output, receives a reference to an array. The output parameters appear in the array in the order you defined them. There are no empty slots for input-only parameters. That is, if you enter nine parameters, and parameters 2, 7 and 9 are output parameters, they will appear at $$arrayref[0], $$arrayref[1] and $$arrayref[2].
You can only call getoutputparams() when you have retrieved all result sets with nextresultset() or get_result_sets(). You can only call getoutputparams() if the command batch actually have any output parameters. If you are uncertain, you can investigate this with getcmdstate(). Once you have called getoutputparams() all resources allocated for the batch are released.

cancelresultset()

Discards all rows in the current result set.

$sqlsrv->cancelresult()

When you call cancelresultset(), Win32::SqlServer moves directly to the end of the result set without retrieving the rows. You must still call nextresultset() to get to the next result set.

If there is no active result set, cancelresultset() has no effect.

cancelbatch()

Cancels the current command batch and frees all resources allocated for it.

$sqlsrv->cancelbatch()

You can call cancelbatch() at any time. Beware that cancelling a batch does not cause SQL Server to roll back any active transaction, unless the setting XACT_ABORT is ON.

Since cancelling a command batch actually means that part of the batch may not be executed at all, using cancelbatch() casually can lead to unexpected results. Note also that cancelling a batch before you have retrieved all result sets, may cause SQL errors that have occurred to go unnoticed.

If you just want to discard all data, it's better to call get_result_sets() with $resultstyle = NORESULT, or call cancelresultset() for each result set.

getcmdstate()

Returns the current command state for the Win32::SqlServer object.

$cmdstate = $sqlsrv->getcmdstate()
$cmdstate
The current command state. See below for possible values.

You can call getcmdstate() if you need to examine what to do next, for instance if there are any output parameters to retrieve. These are the possible return values:

CMDSTATE_INIT
There is no active command batch, and to initiate one, you need to call initbatch() (or a high-level query function).
 
CMDSTATE_ENTEREXEC
A command batch have been entered with initbatch(), but it has not yet been submitted for execution. You can now define parameters with enterparameter() or execute it with executebatch(). You can also define columns for table parameters with definetablecolumn() or enter rows to a table parameter with inserttableparam().
 
CMDSTATE_NEXTRES
The command batch has been executed, and you can now retrieve the next result set with nextresultset(). You also get this value when you have reached the end of the result set with nextrow() or cancelresultset().
 
CMDSTATE_NEXTROW
There is an active result set, and you should retrieve rows from it with nextrow() or skip it with cancelresultset().
 
CMDSTATE_GETPARAMS
You have fetched all result sets, and there are output parameters available. You should fetch these with getoutputparams().
These constants are not imported by default when you use Win32::SqlServer. Either refer to them as Win32::SqlServer::CMDSTATE_INIT etc or import the constants you need explicitly or with an export tag.

Mid-Level Functions Examples

Here are some examples using the mid-level functions. The first example has a plain command batch with an output parameter and a result set.

use strict;
use Win32::SqlServer qw(CMDSTATE_GETPARAMS);

# Log in to local server with SQL authentication. Stay in tempdb.
my $sqlsrv = new Win32::SqlServer;
$sqlsrv->setloginproperty('database', 'tempdb');
$sqlsrv->connect();

my $batch = <<'SQLEND';
   SELECT ? = COUNT(*)
   FROM   Northwind..Orders
   WHERE  CustomerID = ?
     AND  OrderDate  > ?

   SELECT OrderID, OrderDate, Freight
   FROM   Northwind..Orders
   WHERE  CustomerID = ?
     AND  OrderDate > ?
SQLEND
$sqlsrv->initbatch($batch);

# Enter the parameters. $ok will stay 1 as long as all params are OK,
# This first is an output parameter. To make it easier to retrieve, we
# give it a name.
my $ok = $sqlsrv->enterparameter('int', -1, '@cnt', 0, 1);

# Then the parameters for the query. Since they both occur twice, we need
# to supply both twice. We don't care about the names.
$ok &= $sqlsrv->enterparameter('nchar',    5,     undef, 1, 0, 'ALFKI');
$ok &= $sqlsrv->enterparameter('datetime', undef, undef, 1, 0, '1997-06-01');
$ok &= $sqlsrv->enterparameter('nchar',    5,     undef, 1, 0, 'ALFKI');
$ok &= $sqlsrv->enterparameter('datetime', undef, undef, 1, 0, '1997-06-01');

if ($ok) {
  $ok = $sqlsrv->executebatch();
}

if ($ok) {
   while ($sqlsrv->nextresultset()) {
      my ($rowref);
      while ($sqlsrv->nextrow($rowref, undef)) {
         print "OrderID: $$rowref{OrderID}   ";
         print "OrderDate: " . substr($$rowref{OrderDate}, 0, 10), "   ";
         print "Freight: $$rowref{Freight}\n";
      }
   }

   if ($sqlsrv->getcmdstate() == CMDSTATE_GETPARAMS) {
      my ($paramref);
      $sqlsrv->getoutputparams($paramref, undef);
      print "The count is: $$paramref{'@cnt'}.\n";
   }
}
else {
   # Something went wrong somewhere. Issue a cancelbatch to be sure.
   $sqlsrv->cancelbatch();
}

And here is an example that creates a stored procedure with the same contents as above. Here we use the $arrayref for nextrow() and getoutputparams() for the sake of the example.

use strict;
use Win32::SqlServer qw(:cmdstates);

# Log in to local server with SQL authentication. Stay in tempdb.
my $sqlsrv = new Win32::SqlServer;
$sqlsrv->setloginproperty('database', 'tempdb');
$sqlsrv->connect();

my $prc = <<'SQLEND';
CREATE PROCEDURE #orderswithcnt @custid       nchar(5),
                                @orderdate    datetime,
                                @no_of_orders int OUTPUT AS
   SELECT @no_of_orders = COUNT(*)
   FROM   Northwind..Orders
   WHERE  CustomerID = @custid
     AND  OrderDate  > @orderdate

   SELECT OrderID, OrderDate, Freight
   FROM   Northwind..Orders
   WHERE  CustomerID = @custid
     AND  OrderDate > @orderdate
SQLEND

# Create the procedure. We don't call nextrow here, since there should be
# no rows from this batch.
$sqlsrv->initbatch($prc);
$sqlsrv->executebatch();
1 while $sqlsrv->nextresultset();

# Now let's try to run the procedure.
$sqlsrv->initbatch('{? = call #orderswithcnt(?, ? , ?)}');

# Enter the parameters. $ok will stay 1 as long as all params are OK,
# Now the first parameter is the return value.
my $ok = $sqlsrv->enterparameter('int', -1, undef, 0, 1);

# Then the parameters for the query.
$ok &= $sqlsrv->enterparameter('nchar', 5, '@custid', 1, 0, 'ALFKI');
$ok &= $sqlsrv->enterparameter('datetime', undef, '@orderdate', 1, 0, '1997-06-01');

# And the output parameter. But T-SQL has no OUTPUT-only parameter,
# so this is input and output.
$ok = $sqlsrv->enterparameter('int', -1, '@no_of_orders', 1, 1, undef);


if ($ok) {
   $sqlsrv->executebatch();
}
else {
   # Some parameter failed. Drop out.
   $sqlsrv->cancelbatch();
   exit;
}

# We can use getcmdstate to see if everything is OK. (Actually, we would
# not be here if there was an error, as the default behaviour is to abort
# in case of error.)
if ($sqlsrv->getcmdstate() == CMDSTATE_NEXTRES) {
   while ($sqlsrv->nextresultset()) {
      my ($rowref);
      # In this example we use the arrayref to retrieve the data.
      while ($sqlsrv->nextrow(undef, $rowref)) {
         print "OrderID: $$rowref[0]   ";
         print "OrderDate: " . substr($$rowref[1], 0, 10), "   ";
         print "Freight: $$rowref[2]\n";
      }
   }
}

if ($sqlsrv->getcmdstate() == CMDSTATE_GETPARAMS) {
   my ($paramref);
   $sqlsrv->getoutputparams(undef, $paramref);
   print "The return value is: $$paramref[0].\n";
   print "The count is: $$paramref[1].\n";
}

Here is an example with a table valued-parameter, using definetablecolumn() and inserttableparam(). The type and procedure is the same as in the examples  for the high-level routines.

use strict;
use Win32::SqlServer;

# Get an object and connect.
my $sqlsrv = new Win32::SqlServer;
$sqlsrv->setloginproperty('Database', 'tempdb');
$sqlsrv->connect();

# Define the SQL batch. This time we ignore the return value from
# the stored procedure for brevity.
$sqlsrv->initbatch('{call myproc(?,?)}');

# Enter the first parameter.
$sqlsrv->enterparameter('char', 1, '@chr', 1, 0, 'A');

# Enter the table-valued parameter.
$sqlsrv->enterparameter('table',        # It's a table-valued parameter.
                        5,              # Number of columns in the table.
                        '@tvp',         # The name of the parameter.
                        1, 0,           # Is input. Is not output.
                        undef,          # $value. Must be undef here.
                        undef, undef,   # Prec & scale.
                        'mytabletype'); # The table type.

# Now we set up the table. First we define the IDENTITY column. For this
# column we need to specify $usedefault = 1.
$sqlsrv->definetablecolumn('@tvp', 'ident', 'int', 0, undef, undef, 1);

# The remaining columns. Since there is no requirement for an exact
# match, we use initial uppercase for all columns, so that can use the
# column names as keys in a hash without quoting them. For the character
# columns we need to specify the max length. Note that we don't have to
# specify nullability.
$sqlsrv->definetablecolumn('@tvp', 'Chr',     'char', 1);
$sqlsrv->definetablecolumn('@tvp', 'Intcol',  'int');
$sqlsrv->definetablecolumn('@tvp', 'Datecol', 'date');
$sqlsrv->definetablecolumn('@tvp', 'String',  'varchar', 40);

# We can now insert the rows. First we set up some test data.
my @rows = ({Chr => 'A', Intcol => 14, Datecol => '2009-01-01',
             String => 'Typewriter'},
            {Chr => 'B', Intcol => 9},
            {Chr => 'A', Intcol => 5, Datecol => '1632-11-06'},
            {Chr => 'C', Intcol => 12, String => 'Luxury'});

# Then we insert the rows one by one.
foreach my $row (@rows) {
   $sqlsrv->inserttableparam('@tvp', $row);
}

# Execute the procedure.
$sqlsrv->executebatch();

# We use get_result_sets to get the output, to keep the example shorter.
my %result = $sqlsrv->get_result_sets(Win32::SqlServer::SINGLEROW,
                                      Win32::SqlServer::HASH);

# Output the result.
print "totalcnt: $result{'totalcnt'}\n";
print "identsum: $result{'identsum'}\n";
print "chrcnt:   $result{'chrcnt'}\n";
print "datecnt:  $result{'datecnt'}\n";
print "strcnt:   $result{'strcnt'}\n";

OpenSqlFilestream()

The OpenSqlFilestream is an API that permits you get a file handle to a FILESTREAM column, so that you can access it with Windows routines such ReadFile and WriteFile. This provides for faster access to BLOB data, than going through SQL Server. This is not the least applicable when you use Win32::SqlServer which is very slow when the BLOB exceeds1MB in size. Win32::SqlServer exposes the OpenSqlFilestream API, somewhat modified to fit it into its own mould. To access the file, you would use the module Win32API::File. For a very brief introduction, there is an example at the end of this section.

To use OpenSqlFilestream(), the current provider must be SQLNCLI10 or later. If you invoke OpenSqlFilestream() with the Provider property set to an earlier provider, Win32::SqlServer croaks. You may be aware of that OpenSqlFilestream() is a static routine, in difference to everything else which is COM-based. However, this has no importance in the context of Win32::SqlServer which loads the API dynamically, to permit Win32::SqlServer to run on systems with older versions with only SQLOLEDB or SQLNCLI.

Note: my testing indicates that OpenSqlFilestream() in Win32::SqlServer is entirely broken for SQL 2008. More precisely the calls to the Win32API rountines ReadFile and WriteFile fail. With SQL 2008 R2, I see mixed results, but it is not wholly reliable. For connections to SQL 2012 and up, I see no issues.

For full details on the FILESTREAM feature and the OpenSqlFilestream API, please see Books Online. The description here is restricted to what you need to know when you use OpenSqlFilestream() with Win32::SqlServer.

$fh = $sqlsrv->OpenSqlFilestream($path, $access, $context [, $options [, $alloclen]])
$fh
The returned file handle to use with Windows file operations. See below what applies in the case OpenSqlFilestream() fails.
 
$path
A path to the FILESTREAM column that you have retrieved from SQL Server with the PathName method.
 
$access
Any of the constants FILESTREAM_READ, FILESTREAM_WRITE or FILESTREAM_READWRITE. These constants are not imported by default. Either denote them as Win32::SqlServer::FILESTREAM_READ, or import them explicitly or with an export tag.
 
$context
A binary value that you have retrieved from SQL Server using the function GET_FILESTREAM_TRANSACTION_CONTEXT. $context is interpreted according the setting of BinaryAsStr in the same way as when you pass binary values to other Win32::SqlServer routines. This permits you to retrieve the context token from SQL Server and then pass it to OpenSqlFilestream(), no matter the setting of BinaryAsStr.
 
$options
A bit mask of file-access options that controls how the flags are opened. See the topic on OpenSqlFilestream in Books Online for more details. You can use the names SQL_FILESTREAM_OPEN_FLAG_ASYNC etc, if you either prefix them as Win32::SqlServer::SQL_FILESTREAM_OPEN_FLAG_ASYNC or use an export tag. Default is 0.
 
$alloclen
Books Online says "Specifies the initial allocation size of the data file in bytes. It is ignored in read mode." You can specify this parameter as an integer value or as a reference to a hash with the keys High and Low. If you are using 64-bit Perl, you can always use an integer value. With 32-bit Perl and 32-bit integers, you cannot specify a value higher than 2^32-1 if you use an integer. By using the hash keys High and Low, you can overcome this limitation. Caveat: this parameter does not seem to work as intended when you have 32-bit Perl with 64-bit integers, but I don't have a full understanding of the situation.

If the OpenSqlFilestream API returns an error condition, Win32::SqlServer reports this through the current message handler. The source will be set to "OpenSqlFilestream" and the error number is the error number set by the OpenSqlFilestream() API, although with a negative number (since positive numbers are reserved for SQL Server messages). The severity is 16, which means that with the default error handling in Win32::SqlServer, the script is aborted. If the settings in ErrInfo are such that execution continues, OpenSqlFilestream() returns the Windows constant INVALID_HANDLE_VALUE, which I assume is a negative number.

Here is a simple example of how to use OpenSqlFilestream() together with Win32API::File to read and write a FILESTREAM column through the file system. The example assumes that you already have a database with a FILESTREAM file group.

use strict;
use Win32::SqlServer qw(:filestream :rowstyles :resultstyles);

# You need Win32API::File to use ReadFile and WriteFile.
use Win32API::File;

my ($path, $context, $fh, $buffer);

# Connect to server. Change server and database as needed to test
my $sqlsrv = Win32::SqlServer::sql_init(undef, undef, undef, 'filestream_test');

# Create a table with a FILESTREAM column and insert two columns. One with
# data, and one empty that we will write through the file system.
$sqlsrv->sql(<<'SQLEND', NORESULT);
CREATE TABLE fstest (guid uniqueidentifier          NOT NULL ROWGUIDCOL UNIQUE,
                     name varchar(23)               NOT NULL PRIMARY KEY,
                     data varbinary(MAX) FILESTREAM NULL)

INSERT fstest (guid, name, data)
   VALUES(newid(), 'One',
          cast('This string was written from T-SQL.' AS varbinary(MAX))),
         (newid(), 'Two', 0x)
SQLEND

# Let's read the data we wrote from T-SQL. First retrive the file path and
# transaction context. Note that we must start a transaction.
($path, $context) = $sqlsrv->sql(<<SQLEND, LIST, SINGLEROW);
BEGIN TRANSACTION
SELECT data.PathName(), get_filestream_transaction_context()
FROM   fstest
WHERE  name = 'One'
SQLEND

# Open the filestream.
$fh = $sqlsrv->OpenSqlFilestream($path, FILESTREAM_READ, $context);

# Read the file. For more info on ReadFile see the manual page for
# Win32API::File and the documentation for the Platform SDK. Note that
# for large files, you may want to read in chunks.
Win32API::File::ReadFile($fh, $buffer, 200000, [], []);
print "We read this from the file: <$buffer>\n";

# Close this transaction. We must close the file first.
Win32API::File::CloseHandle($fh);
$sqlsrv->sql('ROLLBACK TRANSACTION');

# Now we go writing.
($path, $context) = $sqlsrv->sql(<<SQLEND, LIST, SINGLEROW);
BEGIN TRANSACTION
SELECT data.PathName(), get_filestream_transaction_context()
FROM   fstest
WHERE  name = 'Two'
SQLEND

# Get the file handle.
$fh = $sqlsrv->OpenSqlFilestream($path, FILESTREAM_WRITE, $context);

# Write to the file. Again, for a large file, you may want to write in
# chunks.
$buffer = 'This text was written with WriteFile.';
Win32API::File::WriteFile($fh, $buffer, 0, [], []);

# Close the transaction.
Win32API::File::CloseHandle($fh);
$sqlsrv->sql('COMMIT TRANSACTION');

# Did it work?
$buffer = $sqlsrv->sql_one(<<SQLEND, SCALAR);
SELECT convert(varchar(MAX), data)
FROM   fstest
WHERE  name = 'Two'
SQLEND

print "We read this from T-SQL: <$buffer>\n";

# Clean up
$sqlsrv->sql('DROP TABLE fstest');

Character-set Conversion Routines

These routines permits you to request that Win32::SqlServer should convert data from one code page to another when sending or receiving data to/from SQL Server. The typical scenario is when you read data from a file in the OEM code page like CP850, and you server uses an ANSI code page like CP1252. Or vice versa: that is you have data in an ANSI file, but your server uses an arcane collation with an OEM code page. OEM data is, hopefully, a rare thing these days.

sql_set_conversion()

Activates a character-set conversion.

$sqlsrv->sql_set_conversion([$client_cs [, $server_cs [, $direction]]]);
$client_cs
The character set for your client-side data. Specify the character set as a code page, with or without CP. That is, both "850" and "CP850" are good values. You can also specify ANSI or OEM to specify the ANSI or OEM code pages for the machine. The default value is the OEM character set for the client.
 
$server_cs
The character set of the server data. As for $client_cs you can specify the code page with or without CP. The default value is the code page for the default collation on the server.
 
$direction
In which direction conversion is to take place. Three possible values: TO_CLIENT_ONLY (convert only data from SQL server) TO_SERVER_ONLY (convert only when sending data to SQL Server) and TO_SERVER_CLIENT (convert in both directions). These constants are not imported by default. Either denote them as Win32::SqlServer::TO_CLIENT_ONLY, import them explicitly or use an export tag.

If any of the code pages you specify does not exist, sql_set_conversion() croaks.

If $client_cs and $server_cs are the same, the net effect that any active conversion is removed.

Very important: when you activate a conversion, Win32::SqlServer converts about everything that comes its way. Blindly. Command text. Parameter names. Column names. Data. Without discrimination. There are at least two cases, when this is not likely to be desirable. when you work with the Unicode data types (nchar, nvarchar, ntext and xml) and binary data (binary, varbinary, image and UDT) when you have BinaryAsStr = 0. Thus, you should not activate a conversion under these conditions. If you still need to convert data, you can use codepage_convert() to convert selectively.

Beware that many character-set conversions are not fully roundtrip. For instance, if you send the simple statement SELECT 'Räksmörgås', and have set up a conversion with CP437 as the client set (the most commonly OEM set in the US, I believe) and CP1252 (Latin-1) as a the server set, you will get back RSksmörgss.

Warning: these character-set conversions are performed by Win32::SqlServer before it performs any of the conversions described in the section char, varchar and text. They are not really deprecated, but I don't have any plans to improve them or make them work with say UTF-8.

sql_unset_conversion()

Removes any previous conversion.

$sqlsrv->sql_unset_conversion([$direction]);
$direction
Specifies in which direction conversion is to be removed. Legal values are TO_CLIENT_ONLY (remove only conversion for data going to the client), TO_SERVER_ONLY (removes conversion only for data going to the server), TO_SERVER_CLIENT (removes all conversions). The default is TO_SERVER_CLIENT.

codepage_convert()

Converts a string from one code page to another.

$sqlsrv->codepage_convert($string, $from_cp, $to_cp);
$string
String to convert. Note that codepage_convert() is not a function, but $string is modified in-place.
 
$from_cp
The code page that $string is in. (Or is assumed to be in.) If codepage_convert() finds that $string has the UTF-8 bit set (this bit is set behind the scenes, and not directly settable from a Perl script), codepage_convert() ignores $from_cp. You cannot use "ANSI" or "OEM" here, but you can specify 0 for your ANSI code page, and 1 for your OEM code page.
 
$to_cp
The code page to convert $string to. As for $from_cp, you can specify 0 and 1 for your ANSI and OEM code pages respectively.

Internally, codepage_convert() first converts $string to Unicode and then to $to_cp. Beware that if a character in $string is not available in $to_cp, you will get some replacement character. This can be a similar character, for instance "a" in place of "å", or the general fallback character "?".

codepage_convert() is implemented in XS code, which implies some restrictions. $string must be a variable; it cannot be a constant string, nor can it be an expression that normally can be an l-value like substr($string, 3, 3) or $1.

Utility Routines

sql_has_errors()

Returns whether there have been any errors on the Win32::SqlServer object. Presumes that SaveMessages is in effect.

$haserrors = $sqlsrv->sql_has_errors([$keepinfomsgs]);
$haserrors
True if there are errors in the Messages array in ErrInfo, else false.
 
$keepinfomsgs
Normally, if there are only informational messages and warnings in Messages, sql_has_errors() deletes the Messages array from ErrInfo to clear it. Pass a true value for this parameter to keep the messages. Note that if there are errors in Messages, sql_has_errors() retains all messages in Messages, and you have to clear it yourself.

The Win32::SqlServer property ErrInfo has an element Messages into which sql_message_handler(), the default message handler for Win32::SqlServer, saves messages from SQL Server if the ErrInfo element SaveMessages is true. In this case you can check for errors by calling sql_has_errors().

If SaveMessages is not enabled when you call sql_has_errors(), and you have Perl warnings enabled, Win32::SqlServer issues a warning.

sql_get_command_text()

Returns the text for the current command batch.

$cmdtext = $sqlsrv->sql_get_command_text();
$cmdtext
If the current batch is parameterless, $cmdtext is simply the text of the batch. For a parameterised batch, $cmdtext is an EXEC of sp_executesql corresponding to the RPC call Win32::SqlServer actually performs. For a call to sql_sp(), you get the EXEC command your call translates to (although, again, the actual call is over RPC). If you use the mid-level query routines; $cmdtext is simply the text you passed to initbatch().

The one place where this routine can be useful to you, is if you write your own message handler, and you want to display the lines for the current command.

sql_string()

Quotes a string according to SQL rules.

$quotedstring = [$sqlsrv->]sql_string($string);
$quoutedstring
Is $string bracketed by single quotes('), and with any single quotes in $string doubled. For instance O'Brien becomes 'O''Brien'. If $string is undef, the return value is the string NULL.
 
$sqlsrv
A Win32::SqlServer object. You don't have to specify it (it's not really used anyway). sql_string() is one of the two items that are imported by default when you use Win32::SqlServer.
 
$string
An unquoted string that needs to be quoted to be included in an SQL string.

You can use sql_string() if you need to embed a string value into a string that is to hold an SQL command. However, rather than building complete SQL strings, you should parameterise your commands whenever possible.

sql_begin_trans(), sql_commit(), sql_rollback()

Routines to start, commit and rollback transactions.

$sqlsrv->sql_begin_trans()
$sqlsrv->sql_commit()
$sqlsrv->sql_rollback()

These routines are just plain wrappers saying $sqlsrv->sql("BEGIN TRANSACTION") etc. Win32::SqlServer does keep any track of its own of transactions.

They are only included to provide compatibility with MSSQL::Sqllib.

Object Properties

AutoConnect

Data typebooleanDefaultfalse

If you set AutoConnect to a true value, and you don't connect explicitly with connect() or sql_init(), Win32::SqlServer will connect when you submit a command batch and then disconnect when the command batch has been processed. If you use the high-level query functions only, connection and disconnection happens entirely in these. If you use the mid-level functions, connection happens at initbatch(), and disconnection when you have retrieved all result sets and the output parameters.

Note that the default behaviour of OLE DB is to pool connections, which means that there is a lag time before the physical disconnection occurs. Setting AutoConnect on, if you disable connection pooling is likely to be a poor idea. See further the login property Pooling.

When you use AutoConnect, you can change login properties between the queries. Note that this will result in a new physical connection to SQL Server, since connection pooling only applies to conections with the same connection propeties.

When false, you must connect with connect() or sql_init() and remain connected to submit queries.

BinaryAsStr

Data typeboolean/stringDefault1

Controls how input and output values of the binary data types are interpreted. Please see the topic on data-type representation of binary data above for details.

codepages

Data typeref to hashDefaultempty

This is a hash keyed by database name (exactly as the database is entered in sys.databases) which holds the code page for each database. Win32::SqlServer automatically fills in this cache the first time it runs a query in a database. You could possibly make some slight performance gain by filling it yourself, but if you fill in it wrong, you will get a mess with char and varchar data.

In difference to other caches that Win32::SqlServer maintains, this cache is cleared if you call setloginproperty() to set the server to connect to.

CommandTimeout

Data typeintegerDefault0

How long Win32::SqlServer should wait before timing out on a command. The default is 0, which means wait forever. A time-out error is communicated through the current message handler. Be aware of that what happens is that the OLE DB provider cancels the command, but it does not rollback any outstanding transactions, not even those started in the command batch. Thus, if you run into a command timeout, you should issue IF @@trancount > 0 ROLLBACK TRANSACTION to be safe.

If you are saving messages and have turned of the default to die on errors, and want to test for whether a command timeout has occurred, this example demonstrates:

my $msgs = $sqlsrv->{ErrInfo}{Messages};
print "Timeout expired!!!!\n" if $$msgs[$#$msgs]->{SQLstate} eq 'HYT00';

(You should test for the last message, since even if you cleared Messages before the call, the batch may have generated other messages prior to the timeout.) More about error handling in a later section in this manual.

CurrentDB

Data typestringDefault N/A

This is a read-only property that holds the name of the current database (which may change if your SQL code issues a USE statement).

DateFormat

Data typestringDefault "%Y%m%d %H:%M:%S"

Controls how datetime values are formatted when DatetimeOption has the value DATETIME_STRFMT. See further the section on the representation of datetime values.

DatetimeOption

Data typeenumeratedDefaultDATETIME_ISO

Controls how datetime value returned from SQL Server are represented in Perl. See further the section on the representation of datetime values.

DecimalAsStr

Data typeboolean Defaultfalse

Whether Win32::SqlServer should return data of the types decimal, numeric, money and smallmoney as strings or floating-point values. See further the section on representation of these data types.

ErrInfo

Data typeref to structDefaultN/A

A complex structure through which you can control how Win32::SqlServer should behave when there is an SQL error. This property is detailed in the chapter on Error Handling below.

LogHandle

Data typeref to file handleDefaultundef

When you assign the LogHandle property a file-handle reference, the high-level query functions will write the command batches they pass to SQL Server to this file.. This does not include queries they submit themselves to find the parameters of stored procedures etc. The mid-level functions do not perform any logging.

The logging is implemented with sql_get_command_text(), see this function of what you get in the log.

Example:

open(F, 'MyFile.log');
$sqlsrv->{LogHandle} = \*F;

MsecFormat

Data typestringDefault "%3.3d"

Controls how the milliseconds portion of a datetime value is formatted when DatetimeOption has the value DATETIME_STRFMT. See further the section on the representation of datetime values.

MsgHandler

Data typeref to subDefault \&Win32::SqlServer::sql_message_handler

The currently installed message handler, which is invoked when SQL Server sends a message (error, warning or informational). Also called in case of errors from the OLE DB provider and for warnings and errors from Win32::SqlServer itself. This property is detailed in the chapter on Error Handling.

NoExec

Data typebooleanDefaultfalse

When set, the high-level query functions will not submit your queries to SQL Server, and they will always return empty an result set. However they will still submit calls to SQL Server to retrieve metadata.

The mid-level query functions do not honour this flag.

procs

Data typeref to hashDefaultundef

This hash is used by sql_sp() to cache parameter profiles for stored procedures. You should never write anything to procs. The only permissible operation is to delete a key value if you want to force sql_sp() to refresh the cache.

PropsDebug

Data typebooleanDefaultfalse

If you get cryptic errors when login fails, because some login property has a funny value, it may help to set this property. Win32::SqlServer will then dump login properties and their status in case of an error at login.

Provider

Data typeenumerationDefaultsee below

There are five generations of OLE DB providers that Win32::SqlServer supports for connecting to SQL Server. They are here listed in reverse order of release:

There are six different values for this property.

PROVIDER_MSOLEDBSQL
Use Microsoft OLE DB Driver for SQL Server as the OLE DB provider. This is the default, if MSOLEDBSQL is installed.
 
PROVIDER_SQLNCLI11
Use SQL Server Native Client 11 as the OLE DB provider. This is the default if SQLNCLI11 is installed, but MSOLEDBSQL is not available.
 
PROVIDER_SQLNCLI10
Use SQL Server Native Client 10 as the OLE DB provider. This is the default if SQLNCLI10 is installed, but SQLNCLI11 and MSOLEDBSQL are not.
 
PROVIDER_SQLNCLI
Use SQL Native Client as the OLE DB provider. This is the default if SQLNCLI is installed, but SQLNCLI10 or later is not available.
 
PROVIDER_SQLOLEDB
Use the SQLOLEDB provider. This is the default if no later version is available.
 
PROVIDER_DEFAULT
Use this value when you want to set the provider to the default provider for the machine. When you set the property to this value, Win32::SqlServer will automatically change it to one of the other five.

As long as you are connecting to SQL 2005 or later, you can use any of the providers above. (But note that officially, MSOLEDBSQL only supports SQL 2012 and later.) To connect to SQL 2000, the most recent provdier you can use is SQLNCLI10.

You cannot set this property when you are connected. You can check whether you are connected with the function isconnected().

QueryNotification

Data typeref to hashDefault {}, that is, empty

You use this property to subscribe to notifications from SQL Server when the result set of a query has changed. This hash has three elements: Service (string, mandatory), Message (string, optional) and Timeout (integer, optional). You set these elements before you run a query. When the query has executed, Win32::SqlServer clears this hash. (So that you don't set up query notifications for everything by mistake.)

For more details, please see the section Using Query Notifications with Win32::SqlServer.

RowsAtATime

Data typeintegerDefault100

Controls how many rows at a time nextrow() requests from the OLE DB provider. The idea was that that if the OLE DB provider requests one row at a time from SQL Server, this could hurt performance. But I have not noticed any difference when I've set RowsAtATime to 1. So I suspect the OLE DB providers buffer rows as well.

SQL_version

Data typestringDefaultN/A

This is a read-only property that returns the version string, e.g. 14.00.3192 for the SQL Server isntance you are connected to. Note that the format differs slightly from what you see with SELECT serverproperty('ProductVersion') which reports something like 14.0.3192.2. That is, you only get three parts, and the middle parts is typically two zeroes. This is because Win32::SqlServer gets the value from the OLE DB provider.

tables

Data typeref to hashDefaultundef

This hash is used by sql_insert() to cache column information for tables. You should never write anything to tables. The only permissible operation is to delete a key if you want to force sql_insert() to refresh the cache.

tabletypes

Data typeref to hashDefaultundef

This hash is used by sql(), sql_one() and sql_sp() to cache information about table types. You should never write anything to tabletypes. The only permissible operation is to delete a key if you want to force Win32::SqlServer to refresh the cache.

TZOffset

Data typestringDefaultundef

This property sets a default time-zone for values of the datetimeoffset data type. On output, values are shifted into this time zone, and the time-zone indicator is dropped from the value. On input, the value of TZOffset is used as the time-zone indicator if none is present in the input value. The value should be on the form ±hh:mm, or "local" to indicate the local time zone of the client. See further the section on Date and time data types.

UserData

Data typeanyDefaultundef

This attribute is not referenced by Win32::SqlServer, but permits you to save your own data with a Win32::SqlServer object, would you need it. (You cannot make up a key value on your own, as a Win32::SqlServer object is a tied hash that will reject unknown keys.) If you need to store more than one value, you can make UserData a hash, for instance:

$sqlsrv->{UserData}{MyData1} = 4711;

usertypes

Data typeref to hashDefaultundef

This hash is used by sql() and sql_one() to cache information about user-defined types. You should never write anything to usertypes. The only permissible operation is to delete a key if you want to force Win32::SqlServer to refresh the cache.

Error Handling

Introduction

A very simple start: when SQL Server reports an error, Win32::SqlServer croaks, and if you want to catch that, you can use eval. However, this is merely the default behaviour, and there are several options to change this.

When you submit a command batch to SQL Server, the batch may produce messages. These messages may be errors or purely informational. Since T-SQL offers both RAISERROR and PRINT you may think of these as different things, but PRINT is really only syntactic sugar for RAISERROR('Message', 0, 1).

The mechanism Win32::SqlServer uses to communicate these messages to your script is a callback. You can register a callback that Win32::SqlServer invokes for each message from SQL Server by setting the MsgHandler property. You don't have to write your own message handler though, because Win32::SqlServer comes with a default message handler, sql_message_handler(). This handler is highly configurable through the Win32::SqlServer property ErrInfo, and it is also through ErrInfo your script can get information about the exact messages.

This chapter describes the ErrInfo property, and thus the behaviour of sql_message_handler(). Then it goes on to discuss the MsgHandler property and how to set up your own message handler. At the end of the chapter, there are some example scripts that show ErrInfo in play. But first two short digressions about possible sources for messages and about error messages and SQLstates.

Sources for Messages

SQL Server is the mostly likely source to produce an error or an informational message, but it is not the only one. This section looks at each possible source, and how Win32::SqlServer handles them.

SQL Server

SQL Server can produce errors or informational messages. Informational messages have severity level 0, errors have a severity of 11 or higher. (For more details on severity levels, see MaxSeverity below.) Win32::SqlServer passes all messages from SQL Server to the message handler defined by MsgHandler.

The OLE DB Providers

Win32::SqlServer uses the OLE DB API to call SQL Server; these API calls can result in errors. Win32::SqlServer has two different strategies for these errors. 1) croak. 2) Pass the error to the message handler defined by MsgHandler.

Win32::SqlServer has a preference the latter, but the provider does not always give enough information for Win32::SqlServer to work from. Consider this sequence:

my $sqlsrv = new Win32::SqlServer;
$sqlsrv->setloginproperty('IntegratedSecurity', 'Bogus');
$sqlsrv->connect();

This results in the very cryptic:

Internal error: init_ptr->Initialize failed with 80040e21.
No further error information was collected at F:\Test\example.pl line 7.

Provider errors are mainly related to the login sequence (for instance, the specified server cannot be found) and the use of the mid-level query routines. Win32::SqlServer tries to detect conditions that would cause a provider error and forestall the provider with its own error or warning message, as the provider errors often are obscure.

There are also provider messages that dress up as SQL Server messages, so that Win32::SqlServer cannot tell the difference. A typical example is Specified SQL server not found. Win32::SqlServer will tell you that this is SQL Server message 6, but there is no such message – and of course, SQL Server did not itself report that it wasn't found.

Win32::SqlServer

Win32::SqlServer can detect errors on its own, and as for OLE DB errors, Win32::SqlServer can 1) croak or 2) invoke the current message handler. Win32::SqlServer can also emit warnings, and these can be reported 1) with carp or 2) through the current message handler.

The basic philosophy is that Win32::SqlServer uses croak/carp when then error is related to Perl usage while it uses the message handler when the condition is related to SQL. So a call to executebatch() without first having called initbatch() results in an unconditional croak. Whereas if sql_sp() is not able to find the stored procedure you want it to call goes to the message handler. 

Note: don't rely too much on what is what, as a certain error could be treated differently in a future release. As a hint: it is more likely that croak/carp is replaced by a call to the message handler, than vice versa.

IDataConvert

IDataConvert is an OLE DB interface that Win32::SqlServer uses to convert data from Perl to SQL Server and vice versa. Errors when converting input data are communicated through the current message handler, as if they came from Win32::SqlServer itself; you will never see IDataConvert in an error message.

If an error occurs when converting data from SQL Server back to Perl, Win32::SqlServer will always croak. Such an error can only occur if there is a bug in Win32::SqlServer or if SQL Server produces garbage.

Other sources

Errors and warnings can also come from Perl, Win32 system calls and similar. They are never communicated through any message handler, but always through croak/carp. The one exception are errors that appear with OpenSqlFilestream(). They will be communicated through the current message handler with "OpenSqlFilestream" as the source for the error message.

Error Numbers and SQLstates

All errors from SQL Server have an error number, from 1 and up. Errors below 50000 are from SQL Server itself (or a system stored procedure or similar). Errors from 50000 and up are from RAISERROR. sql_message_handler() permits you to control for a specific error number whether Win32::SqlServer should abort on it or not.

Win32::SqlServer errors always have number -1. (Currently, I should hasten to add. A future version may use more negative numbers.) This is also true for provider errors (save for those that look like SQL Server messages).

SQLstates are five-letter codes, defined by the ODBC standard. They are set by the OLE DB provider for SQL Server errors and some of its own errors as well. For SQL Server errors these are of less interest, since an SQLstate can cover several error numbers from SQL Server. For provider errors, on the other hand, SQLstate is the only way to identify the error. (This includes the provider errors that dress up as SQL Server errors.) You can find a list of all SQLstates in the MSDN topic Appendix A: ODBC Error Codes.

Not all provider errors have SQLstate. For these errors, Win32::SqlServer passes the return code from the OLE DB API function that failed as the "SQLstate", formatted as an 8-character hex string. The main purpose of this hex string to diagnose problems in Win32::SqlServer itself.

Win32::SqlServer errors never have an SQLstate.

ErrInfo

ErrInfo is a hash and some of the elements are hashes or arrays in their turn. Here is a pseudo-code declaration of ErrInfo:

RECORD ErrInfo
   -- Abort control: On which messages to abort/disconnect.
   MaxSeverity    integer = 10;
   NeverStopOn    HASH OF boolean = undef;
   AlwaysStopOn   HASH OF boolean = undef;
   DisconnectOn   HASH OF boolean =
                  {'2745'  => 1,  '4003' => 1,  '5702' => 1, '17308' => 1,
                   '17310' => 1, '17311' => 1, '17571' => 1, '18002' => 1,
                   '08001' => 1, '08003' => 1, '08004' => 1, '08007' => 1,
                   '08S01' => 1};

   -- Print control: what parts of a message to print and where.
   PrintMsg       integer = 1;
   PrintText      integer = 0;
   PrintLines     integer = 11;
   CarpLevel      integer = 10;
   NeverPrint     HASH OF boolean = {'5701' => 1, '5703' => 1};
   AlwaysPrint    HASH OF boolean = {'3606' => 1, '3607' => 1, '3622' => 1};
   LinesWindow    integer = undef;
   ErrFileHandle  IO::File = STDERR;  -- Where to print errors.

   -- Abort control for return values.
   CheckRetStat   flag = 1;
   RetStatOK      HASH OF boolean = undef;

   -- Return error messages to caller
   SaveMessages   boolean = 0;
   Messages       ARRAY OF RECORD
                     Source    string;
                     Errno     integer;
                     Severity  integer;
                     State     integer;
                     Server    string;
                     Proc      string;
                     Line      string;
                     Text      string;
                     SQLstate  string;
                  END;
END

Like Win32::SqlServer itself, ErrInfo is a tied hash that permits only the defined keys to be used. Thus, if you try to access an non-existing element, Win32::SqlServer will croak.

Abort Control

These elements control whether sql_message_handler() will abort execution or disconnect() or because of the message.

MaxSeverity
The maximum severity level permitted. That is, if the severity level of the message equals MaxSeverity, sql_message_handler() will not abort execution, but if the severity level is higher, execution will be aborted. The default value is 10, which means to stop on any error from SQL Server.

Some guidelines may be in place here, by looking at how the severity levels are used:

LevelComment
0Level 0 is informational messages from SQL Server.
1-9As far as I know, SQL Server itself never generates messages with these levels, but you can use RAISERROR to produce such messages.
10SQL Server cannot produce messages with this level. If you specify level 10 with RAISERROR, you do in fact get a message with level 0. Win32::SqlServer, on the other hand, can issue messages with severity level 10, and they are warnings or provide supplementary message ahead of anticipated errors (for instance failed conversion of an input value).
11-16 SQL Server uses the levels 11-16 for normal SQL errors – what Books Online calls "user errors" – in a fairly arbitrary fashion. For instance, a NOT NULL violation is level 16, while a permission error is level 14. The severity of Win32::SqlServer errors and errors from the OLE DB provider is always 16.
17-25Level 17 and above are various resource errors, such as running out of disk space, or internal errors in SQL Server. In difference to levels 11-16, it really gets more fatal for each level. Then again, if the severity is 20 or higher, SQL Server terminates the connection, so for your script the difference between 20 and 25 is academic. See more about these levels in Books Online.

From this follows that the only reasonable values for MaxSeverity are 10 (stop on any error), 16 (permit users errors, but stop on resource errors), 17-19 (stop on real fatal errors only) and 25 (never stop).

If you decide to set MaxSeverity to a higher level than 10, you should have a plan to handle errors. If you say "I can't afford to let the script to abort", then ask yourself if you can afford to let the script continue if there was an unhandled error? Maybe you failed to write some essential data. Maybe a deadlock prevented you from reading important data, causing the script to produce a bogus result if you continue.

One strategy is to set SaveMessages, and then use the utility function sql_has_errors() after each call to a Win32::SqlServer function to see if there was an error. There are examples of this at the end of this section.

NeverStopOn
A hash, keyed by SQL Server message numbers or SQLstates from the OLE DB provider. A true value means that for this message, sql_message_handler() should not abort execution.

Say you want to delete a row if there are no references to it. Rather than doing an IF EXISTS for each referencing table, you can do this:

$sqlsrv->{ErrInfo}{NeverStopOn}{547}++;
$sqlsrv->{ErrInfo}{NeverPrint}{547}++;
$sqlsrv->sql('DELETE tbl WHERE keycol = ?', [['int', $value]]);
delete $sqlsrv->{ErrInfo}{NeverStopOn}{547};
delete $sqlsrv->{ErrInfo}{NeverPrint}{547}++;

This both suppresses the error message and prevents execution from being aborted. At the same time, if there are other errors that you don't handle, for instance a deadlock or a permission error, they will abort execution.

By default, NeverStopOn is empty.

AlwaysStopOn
A hash keyed by SQL Server message numbers or SQLstates from the OLE DB provider. A true value means that for this message, sql_message_handler() should always abort execution, even if the message has a severity level <= MaxSeverity.

By default, AlwaysStopOn is empty.

DisconnectOn
A hash keyed by SQL Server message numbers or SQLstates from the OLE DB provider. A true value means that for this message, sql_message_handler() should issue a call to disconnect() to make sure that the connection with SQL Server is terminated..

As you can see from the pseudo-declaration above, by default quite a few message numbers and SQLstates are added to this hash. All of these indicate that the physical connection with SQL Server has been severed. They appear in DisconnectOn, as the call to disconnect() is needed for Win32::SqlServer to change its internal state to "disconnected". Thus, you should never remove any of the default entries from DisconnectOn.

Besides the listed errors, sql_message_handler(), always disconnects if the severity level is >= 20, since in this case SQL Server has already severed the connection. This is not configurable.

In many cases, it makes sense to reconnect. For instance, if SQL Server was restarted, your first query will get a General Network Error message from the OLE DB provider with SQLstate 08S01. If you detect this, you can reconnect. If you run with AutoConnect on, you don't even have to look for it.

However, keep in mind that if you get disconnected, and you had some sort of transaction in progress, it would be wrong to continue with that transaction as if nothing happened, since the transaction would have been lost and rolled back.

You can check whether you are connected with the function isconnected(). (Note that this function reports Win32::SqlServer's internal connection state.)

Which messages would you add to DisconnectOn? Well, I can think of one: SQLstate HYT00, Timeout expired. Command timeouts are messy, because you don't really know what state the command batch was left in. Particularly, the batch may have started a transaction without your knowing. An easy way out could be to disconnect and reconnect to give you a known state. Note: by default command timeouts are not enabled in Win32::SqlServer, see further the property CommandTimeout.

Print Control

A full-blown error message from Win32::SqlServer looks like any of these samples:

SQL Server message 15025, Severity 16, State 1, Server KESÄMETSÄ
Procedure sp_addlogin, Line 57
The login 'sommar' already exists.
    1> EXEC sp_addlogin @loginame = N'sommar'
Message from SQL Server at F:\Test\example.pl line 8
Message HYT00 from 'Microsoft OLE DB Provider for SQL Server', Severity: 16
Timeout expired
    1> WAITFOR DELAY '00:00:05'
Message from Microsoft OLE DB Provider for SQL Server at F:\Test\example.pl line 9
Message -1 from 'Win32::SqlServer', Severity: 16
One or more parameters were not convertible. Cannot execute query.
    1> EXEC sp_executesql N'SELECT @P1',
    2>      N'@P1 datetime',
    3>      @P1 = '999'
Message from Win32::SqlServer at F:\Test\example.pl line 8

In the context of print-control in sql_message_handler(), these messages falls into four parts, each controlled by an ErrInfo element, as detailed below.

By default, errors are written to STDERR, this can be overridden with ErrFileHandle, see below.

PrintMsg
The minimum severity level for which sql_message_handler() prints the "header information" of the message. In the examples, this is the first two lines in the first example and the first line in the second and third examples. You get the message number, the severity level, the state as well as the server, the procedure and the line number if these are available. Fairly similar to what you see in SQL Server Management Studio.

For messages from the OLE DB provider, the message "number" is the SQLstate for the error. For some errors, OLE DB does not produce any SQLstate. In this case, you will in this case see an eight-character hex string which is the return code from the OLE DB API call that appears as "procedure". In this case, please ignore the return code and the procedure, and try to grasp the message text (although it is far from often helpful). Such errors can be the result of user errors – but this is also how internal errors in Win32::SqlServer appear if it makes in an incorrect API call.

The default is 1, which means that this information are printed for all messages except those with severity 0.

PrintText
The minimum severity level for which sql_message_handler() prints the text of the error message. That is, the third line in the first example and the second line in the second and third examples. The default is 0, which means that it is always printed.
 
PrintLines
The minimum severity level for which sql_message_handler() prints the text of the command batch that caused the error. The numbers to the left are simply line numbers. The idea is that if your program stops unexpectedly, you want a clue about the SQL code that caused the problem.

Exactly what command text you see here, depends a little on which Win32::SqlServer function you called. When you call sql_sp(), Win32::SqlServer submits an RPC call to SQL Server. The same is true if you submit a parameterised command with sql(). In both these cases, Win32::SqlServer produces a textual representation of the corresponding EXEC command. When you use parameterised commands with the mid-level functions, you will always see the command string you passed to initbatch() as-is. For some errors detected by Win32::SqlServer, this part is not printed, because Win32::SqlServer detects the error before it has constructed the command text, so there is nothing to print.

The default is 11. That is, lines are only printed for errors.

See also the element LinesWindow below, with which you can control how much of the batch that is printed.

CarpLevel
This property controls from which severity level sql_message_handler() supplements the message with a Perl warning (carp) to indicate on which line in your script you submitted the batch that caused the message. That is, the last line in the examples.

If there is a burst of messages, sql_message_handler() prints this part only once, after the last message. This part never appears on its own. Thus, if the settings of PrintMsg, PrintText and PrintLines preclude printing the message (or all messages in a burst), this part is not printed.

The default is 10. That is, this part is printed for warnings and errors.

NeverPrint
A hash, keyed by SQL Server error numbers or SQLstates from the OLE DB provider. For entries with true values, printing of all parts of the message is suppressed. See above under NeverStopOn for an example on how to use it.

Two SQL Server messages are added by default to NeverPrint. Messages 5701 Changed database context to and 5703 is Changed language setting to which are always generated on login. Most SQL tools suppress these messages.

AlwaysPrint
A hash, keyed by SQL Server error numbers or SQLstates from the OLE DB provider. For entries with true values, sql_message_handler() prints of all parts of the message.

Three messages are added to this hash by default. These are the informational messages for arithmetic errors you get if all of ANSI_WARNINGS, ARITHABORT and ARITHIGNORE are off. (When you connect with Win32::SqlServer, ANSI_WARNINGS is on by default.)

LinesWindow
If this property is defined, sql_message_handler() does not print the full text of the command batch that caused the error, but only the line on which the error occurred according to SQL Server and LinesWindow lines before and after. For instance, if you set LinesWindow to 5, you will get at most 11 lines of code printed in the error message.

By default, LinesWindow is undef, and thus all lines are printed.

ErrFileHandle
A file handle to which sql_message_handler() should write the messages. If undef – which is the default – sql_message_handler() prints the messages to STDERR.

Abort Control for sql_sp()

The default behaviour for sql_sp() is to abort execution on any non-zero return status from a stored procedure. (Return values from scalar user-defined functions are not checked.) ErrInfo has two elements to permit you control this:

CheckRetStat
This element controls whether sql_sp() checks return values at all. By default, CheckRetStat is true. Set it to a false value, if you never want return values checked.
 
RetStatOK
This is a hash, keyed by return values. If an entry has a true value, this means that sql_sp() should not abort execution for this return value.

Getting Messages Back

SaveMessages
When set, all messages that passes through sql_message_handler() are added to the Messages array. SaveMessages is off by default, but you should set SaveMessages if you set MaxSeverity to a value > 10.
 
Messages
An array with information about messages generated by SQL Server, the OLE DB provider or Win32::SqlServer itself. To check whether there are any errors in Messages, you can use the Win32::SqlServer function sql_has_errors(). Each entry in Messages can contain any of the elements below. Just like ErrInfo, the entries in Messages are tied hashes, so if you spell a key wrong, you will get an error.
Source
The source for the error message. undef for SQL Server messages. The name of the OLE DB provider for OLE DB messages. Win32::SqlServer for Win32::SqlServer messages.
 
Errno
The message number. A number > 0 for SQL Server messages. -1 for OLE DB and Win32::SqlServer messages.
 
Severity
The severity level of the message.
 
State
The state of the message. SQL Server uses state to convey some information that is not well documented. Always 1 for OLE DB and Win32::SqlServer messages. (This has nothing to do with SQLstate.)
 
Server
The server from which the message originated. Always undef for OLE DB and Win32::SqlServer messages. Can also be undef for SQL Server messages.
 
Proc
The name of the SQL module (stored procedure, trigger etc) where the error occurred. Also filled in for some OLE DB messages with internal Win32::SqlServer debug information.
 
Line
The line in the command batch or in the SQL module, that the message relates to. It appears that for non-error messages, SQL Server provides bogus line numbers.
 
Text
The text of the message.
 
SQLstate
The SQLstate for the message for SQL Server errors and OLE DB messages. Always undef for Win32::SqlServer messages.

Here is an example on how to refer to an entry in Messages:

$X->{ErrInfo}{Messages}[0]{Errno}

MsgHandler

Setting a Message Handler

The Win32::SqlServer property MsgHandler defines the current message handler. By default it is set to \&Win32::SqlServer::sql_message_handler. To install your own message handler, you can do any of:

$sqlsrv->{MsgHandler} = \&my_handler;
$sqlsrv->{MsgHandler} = "main::my_handler";
$sqlsrv->{MsgHandler} = "my_handler";       # Yields a warning.

Or even

$sqlsrv->{MsgHandler} = sub { warn "This is my handler!\n"};

When you pass the name of a sub, Win32::SqlServer resolves this name, and if the sub does not exist, Win32::SqlServer croaks. If the string does not include the name of a package, Win32::SqlServer emits a warning if Perl warnings are enabled, as the name may resolve to another package than you intended.

Consider this case:

$sqlsrv->{MsgHandler} = undef;

The effect of this is not that you are left without a message handler. When there is no Perl callback defined for MsgHandler, Win32::SqlServer invokes a second default message handler, implemented in C++. This is a simple, non-configurable handler that prints the message header if the severity level is >= 11, and always prints the message text. It never prints the SQL code and it never aborts execution.

Return Value and Parameters of a Message Handler

Win32::SqlServer invokes the message handler like this:

$status = MsgHandler($sqlsrv, $errno, $state, $severity, $text, $server,
                     $procedure, $line, $sqlstate, $source, $n, $no_of_errs);
$status
The return value of the message handler. If this is a false value, Win32::SqlServer croaks and aborts execution. You should not call die, croak or exit in your message handler, but leave this to Win32::SqlServer. This is because Win32::SqlServer needs to free resources associated with the current batch. (Even if you die/croak, someone might catch this with eval.)
 
$sqlsrv
The Win32::SqlServer object bound to the connection on which the message was generated.
 
$errno
The error number for the message. For SQL Server messages, this is a number > 0. For provider and Win32::SqlServer errors, this is always -1. (Except for provider errors that dress up as SQL Server errors.)
 
$state
The state for the message as reported by SQL Server.
 
$severity
The severity level of the message. See above under MaxSeverity for a discussion on severity levels.
 
$text
The message text.
 
$server
The server from which the message originates. undef for Win32::SqlServer messages and messages from the OLE DB provider.
 
$procedure
Name of the SQL module (stored procedure, UDF, trigger etc) where the error occurred. For messages from the OLE DB provider, it can be the name of the API call that failed (this is for debugging Win32::SqlServer itself). Always undef for Win32::SqlServer messages.
 
$line
Line number for the line where the error occurred. 0 for Win32::SqlServer messages and messages from the OLE DB provider.
 
$sqlstate
The SQLstate. See the section above for a discussion on SQLstates. Always undef for Win32::SqlServer messages.
 
$source
The source for the error message. For SQL Server messages $source is undef. For OLE DB messages, it is the name of the provider. For Win32::SqlServer messages, $source is Win32::SqlServer. For errors from OpenSqlFilestream(), source is OpenSqlFilestream.
 
$n
$no_of_errs
SQL Server may generate more than one message at a time. In this case, you may want to display all messages before you abort execution. $no_of_errs is the number of a message in the burst. When you get a burst, the message handler is first called with $n = 1, then 2 etc up to $no_of_errs.

Some Tips on Writing Message Handlers

If you simply want to change the format of the printed messages, you could simply copy the code of sql_message_handler(), and use ErrInfo as before. Beware, though, that sql_message_handler() makes use of some undocumented functions and properties that could disappear in a future version.

If you would prefer to replace ErrInfo with your own control structure, you can use the UserData property of the Win32::SqlServer object.

If you need to get the text of the current command, you can use the function sql_get_command_text(). Beware that in some situations this function can return undef, because Win32::SqlServer detects an error before it has started to build the command string.

If you are rolling your own entirely, you should consider to have a handling similar to what is described under DisconnectOn. That is, call disconnect() when the SQL Server message or provider message indicates that the connection with SQL Server has been terminated. Win32::SqlServer does not detect this situation on its own, but relies on the message handler to do this work.

Examples on Error Handling

Example 1

This script reads all files named .SP in a directory and passes these to SQL Server, assuming they contain stored procedures. The script assumes that the procedure name agrees with the file name, and generates a DROP PROCEDURE for the procedure prior to running the file. The script relies on the built-in printing supplied by sql_message_handler(). At the end, it prints out a summary of many files it attempted to load and how many that failed. To this end, it uses sql_has_errors().

use strict;
use Win32::SqlServer;

my ($no_of_errors) = 0;
my $dir = shift @ARGV;
my $db  = shift @ARGV;

# Don't buffer STDOUT, so that we get all output in order.
$| = 1;

# Log in to local server with Windows authentication.
my $sqlsrv = sql_init(undef, undef, undef, $db);

# Since we are loading stored procedures, we don't want to stop on
# compilation errors.
$sqlsrv->{ErrInfo}{MaxSeverity} = 17;

# And when there is an error, we only want the line that is flagged
# for the error.
$sqlsrv->{ErrInfo}{LinesWindow} = 0;

# We need to save messages to see if the file loaded successfully.
$sqlsrv->{ErrInfo}{SaveMessages} = 1;

# Get the files to load.
chdir($dir) or die "chdir to '$dir' failed: $!\n";
opendir (D, '.') or die "Opendir failed: $!\n";
my @files = grep(/\.sp$/i, readdir(D));
closedir(D);

foreach my $f (@files) {
   # Read the file.
   open (F, $f);
   my @filetext = <F>;
   close F;
   my $filetext = join('', @filetext);

   # Log that we try to load the file.
   print "$f\n";

   # Remove any old copy of the procedure.
   my ($procname) = $f;
   $procname =~ s/\.sp$//i;
   $sqlsrv->sql("IF object_id('$procname') IS NOT NULL " .
                "DROP PROCEDURE $procname", Win32::SqlServer::NORESULT);

   # Load the file. We must handle batching with go ourselves.
   my @batches = split(/\n\s*go\s*(\n|$)/i, $filetext);
   foreach my $batch (@batches) {
      next if $batch !~ /\S/;   # Skip blank batches.
      $sqlsrv->sql($batch, Win32::SqlServer::NORESULT);
   }

   # Check whether it loaded successfully.
   if ($sqlsrv->sql_has_errors()) {
      $no_of_errors++;
      # We must clear Messages ourselves.
      delete $sqlsrv->{ErrInfo}{Messages};
   }
}

my $no_of_files = scalar(@files);
print "Attempted to load $no_of_files files whereof " .
      ($no_of_errors ? "$no_of_errors failed" : "all loaded successfully") .
      ".\n";

Here is a sample output from some files that I had around (and that were not in sync with the database I loaded them to):

ais_acq_upd_sp.sp
ais_acs_upd_instrument_sp.sp
SQL Server message 207, Severity 16, State 3, Server KESÄMETSÄ
Procedure ais_acs_upd_instrument_sp, Line 166
Invalid column name 'sypvarchar'.
  166> SELECT @homecoun = sypvarchar FROM systemparameters WHERE sypcode = 'HOMECOUN'
Message from SQL Server at F:\Test\example.pl line 49
ais_acs_upd_money_sp.sp
SQL Server message 207, Severity 16, State 3, Server KESÄMETSÄ
Procedure ais_acs_upd_money_sp, Line 238
Invalid column name 'ismainaccountowner'.
  238>         IF NOT(SELECT natregno
Message from SQL Server at F:\Test\example.pl line 49
ais_activate_account_sp.sp
...
ais_sct_exec_one_sp.sp
ais_syp_get_nightjob_ready_ok_sp.sp
Attempted to load 89 files whereof 10 failed.

Example 2

This example reads a server name, a database name and a file name from the command line, and loads the specified file into the specified server/database. The script splits the file into batches and run them one by one. The script turns off printing of error messages in sql_message_handler(), and instead prints the error messages itself. The point is that the line number is modified to refer to a line in the file, and not in the batch. Thus, if the file first contains a batch for dropping the procedure if it exists, and there then is an error on Line 24 in the procedure, the example script will report this error on Line 28 in the file (if the DROP batch is four lines). This is nice, if you edit SQL files in an editor like EditPlus from which you can run command-line tools. You could format the error message, so that you could double-click the error message and be taken to the line where there error is.

use strict;
use Win32::SqlServer;

my $server = shift @ARGV;
my $db     = shift @ARGV;
my $file   = shift @ARGV;

# Don't buffer STDOUT, so that we get all output in order.
$| = 1;

# Log in to the server with Windows authentication.
my $sqlsrv = sql_init($server, undef, undef, $db);

# Since we are loading stored procedures, we don't want to stop on
# compilation errors.
$sqlsrv->{ErrInfo}{MaxSeverity} = 17;

# We will print error messages ourselves, so turn off printing:
$sqlsrv->{ErrInfo}{PrintMsg}   = 17;
$sqlsrv->{ErrInfo}{PrintText}  = 17;
$sqlsrv->{ErrInfo}{PrintLines} = 17;

# We need to save messages so we can print them.
$sqlsrv->{ErrInfo}{SaveMessages} = 1;

# Read the file.
open (F, $file) or die "Cannot read file '$file': $!\n";
my @filetext = <F>;
close F;

print "$file\n";

# Load the file, batch by batch.
my $batchtext = '';
my $batchstart = 0;
my $no_of_errs = 0;
foreach my $ix (0..$#filetext) {
   unless ($filetext[$ix] =~ /^\s*go\s*$/i) {
      # Just append.
      $batchtext .= $filetext[$ix];
      next;
   }
   else {
      # Do the batch.
      $no_of_errs += run_batch($sqlsrv, $file, $batchstart, $batchtext);

      # Set up for next;
      $batchstart = $ix + 1;
      $batchtext = '';
   }
}

# There is likely to be a batch at the end as well...
$no_of_errs += run_batch($sqlsrv, $file, $batchstart, $batchtext);

# Print summary message.
if ($no_of_errs == 0) {
   print "$file loaded successfully!\n";
}
else {
   print "Loading of $file resulted in $no_of_errs error(s).\n";
}

exit;

sub run_batch {
    my($sqlsrv, $file, $batchstart, $batchtext) = @_;

    my $no_of_errs = 0;

    # Skip empty batch.
    return 0 if (not $batchtext or $batchtext !~ /\S/);

    # Make sure Messages is empty.
    delete $sqlsrv->{ErrInfo}{Messages};

    # Run batch.
    $sqlsrv->sql($batchtext, Win32::SqlServer::NORESULT);

    # Check for messages.
    foreach my $mess (@{$sqlsrv->{ErrInfo}{Messages}}) {
       # Increase return value.
       $no_of_errs += $mess->{Severity} >= 11;

       # Translate line number in batch to line number in file.
       my $lineno = $mess->{Line} + $batchstart;

       # Print message header.
       print "Msg $mess->{Errno}, Level $mess->{Severity}, " .
             "Line $lineno, $file\n";

       # And the message text.
       print $mess->{Text}, "\n";
    }

    return $no_of_errs;
}

Sample output:

F:\Test\OLE DB\blafstest\variant_sp.sp
Msg 170, Level 15, Line 18, F:\Test\variant_sp.sp
Line 15: Incorrect syntax near '4567891234456789'.
Loading of F:\Test\variant_sp.sp resulted in 1 error(s).

Notice how the line number in the message text from SQL Server is different from the line number in the line above.

Miscellaneous Topics

Exported Names and Export Tags

To minimize namespace pollution, when you say:

use Win32::SqlServer;

only two items are imported by default: sql_init() and sql_string().

A whole lot more can be imported, either explicitly by name, or by an export tag. These tags are available:

:DEFAULT
The default import, that is sql_init() and sql_string()
 
:cmdstates
The possible return values from getcmdstate(): CMDSTATE_INIT, CMDSTATE_ENTEREXEC, CMDSTATE_NEXTRES, CMDSTATE_NEXTROW and CMDSTATE_GETPARAMS.
 
:colinfostyles
The possible values for the $colinfostyle parameter: COLINFO_NONE, COLINFO_NAMES, COLINFO_POS and COLINFO_FULL.
 
:consts
$SQLSEP
 
:datetime
The possible values for the property DatetimeOption: DATETIME_HASH, DATETIME_ISO, DATETIME_REGIONAL, DATETIME_FLOAT and DATETIME_STRFMT.
 
:directions
The possible values for the $direction parameter to sql_set_conversion() and sql_unset_conversion(): TO_SERVER_ONLY, TO_CLIENT_ONLY and TO_SERVER_CLIENT.
 
:filestream
Constants used in conjunction with OpenSqlFilestream(): FILESTREAM_READ, FILESTREAM_WRITE, FILESTREAM_READWRITE, SQL_FILESTREAM_OPEN_FLAG_ASYNC, SQL_FILESTREAM_OPEN_FLAG_NO_BUFFERING, SQL_FILESTREAM_OPEN_FLAG_NO_WRITE_THROUGH, SQL_FILESTREAM_OPEN_FLAG_SEQUENTIAL_SCAN and SQL_FILESTREAM_OPEN_FLAG_RANDOM_ACCESS
 
:providers
The possible values for the Provider property: PROVIDER_DEFAULT, PROVIDER_SQLOLEDB, PROVIDER_SQLNCLI, PROVIDER_SQLNCLI10, PROVIDER_SQLNCLI11 and PROVIDER_MSOLEDBSQL.
 
:resultstyles
The possible values for the $resultstyle parameter: NORESULT, SINGLEROW, SINGLESET, MULTISET, MULTISET_RC and KEYED
 
:returns
The possible return values from a callback that is used as a result style: RETURN_NEXTROW, RETURN_NEXTQUERY, RETURN_CANCEL, RETURN_ERROR and RETURN_ABORT.
 
:routines
All routines that start with sql_. Import these if you want to rely on the default handle. Note that using the default handle is deprecated. This is the complete import list for this tag: sql(), sql_one(), sql_sp(), sql_insert(), sql_has_errors(), sql_string(), sql_get_command_text(), sql_set_conversion(), sql_unset_conversion(), sql_begin_trans(), sql_commit() and sql_rollback().
 
:rowstyles
The possible values for the $rowstyle parameter: SCALAR, LIST and HASH.

Here is an example, on how to import sql_init(), the result styles, the row styles and the property value DATETIME_REGIONAL:

use Win32::SqlServer qw(:rowstyles :resultstyles sql_init DATETIME_REGIONAL);

All export tags must come before any explicit names. For more information about importing names and using export tags, please refer to the Perl documentation.

Getting the Win32::SqlServer Version  

If you need to check which version of Win32:SqlServer that is installed, there are two variables available: $Win32::SqlServer::VERSION and $Win32::SqlServer::Version. The former is just the version string, for instance 2.012. The latter also includes a copyright blurb. There are no export tags for these two.

Using Query Notification with Win32::SqlServer

Query notification is a feature added in SQL 2005 that uses the Service Broker infrastructure. It is a little funny in that you cannot set up a subscription query notification from plain T-SQL, you must do it from client code. In ADO .Net this is packaged in the SqlDependency class for a little more elegance, but the feature is available in OLE DB as well, and Win32::SqlServer gives you access to query notification through the QueryNotification hash.

For full information on Query Notification, please see topic Using Query Notifications in Books Online. Here follows a crash course in the context of Win32::SqlServer. The QueryNotification hash has three elements:

Service
A property-value string that specifies the service options for the notification. The string has this format:
service=service-name[;(local database=db|broker instance=instance)]
This element is mandatory. If you do not set this element, Win32::SqlServer will not set up a notification. If other elements in the hash are set, but Service is not, Win32::SqlServer emits a warning through the current message handler, if Perl warnings are enabled.
 
Message
This is a text in free format that reappears in the message body of the notification message, so that you can identify which notification fired, if you have submitted more than one. If you don't set this element, Win32::SqlServer will set a default message of Query notification set by Win32::SqlServer. The same applies of you set Message to the empty string.
 
Timeout
An integer value that specifies how many seconds you want the notification subscription to be active. It seems that Service Broker only clears out subscriptions about every minute or so, so if you set a small value, you may find that the subscription lives longer than you expected. If you do not specify any timeout, it appears that you get a timeout of 432000 seconds, or five days.

So how do you use this? You set the QueryNotification property before you submit a query batch, and the notification subscriptions then apply to the queries in that batch. Once Win32::SqlServer has submitted the batch, it clears out the QueryNotification hash, so you will have to set it again to set up more notification subscriptions. Note that you cannot set up query notification for any query, but queries must adhere to certain rules, similar to those that apply for indexed views. Please refer to Books Online for details.

Here is an example of how to use query notification with Win32::SqlServer. First run this in a query window:

CREATE QUEUE MyQueue WITH RETENTION = OFF
CREATE SERVICE OlleService ON QUEUE MyQueue
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification])
go
CREATE TABLE QNtest (a int NOT NULL PRIMARY KEY,
                     b nchar(5) NOT NULL,
                     c datetime NOT NULL)
go
INSERT QNtest (a, b, c)
SELECT 1, 'ALFKI', '19991212'

This snippet sets up a query notification on QNtest, and then polls to see if there are any notifications:

$sqlsrv->{QueryNotification}{Service} =
         'service=MyService;local database=somedatabase';
$sqlsrv->sql("SELECT a, b, c FROM dbo.QNtest WHERE b = N'ALFKI'");
my @notification;
while (not @notification) {
   @notification = $sqlsrv->sql(<<'SQLEND');
      DECLARE @xml TABLE (x xml NOT NULL);
      RECEIVE convert(xml, message_body) FROM MyQueue INTO @xml;
      WITH XMLNAMESPACES
         ('http://schemas.microsoft.com/SQL/Notifications/QueryNotification' AS qn)
      SELECT Message = c.value(N'(qn:Message)[1]', 'nvarchar(MAX)'),
             Source  = c.value(N'@source', 'nvarchar(255)'),
             Info    = c.value(N'@info', 'nvarchar(255)'),
             Type    = c.value(N'@type', 'nvarchar(255)')
      FROM @xml x
      CROSS APPLY x.x.nodes(N'/qn:QueryNotification') AS T(c)
SQLEND
   sleep(1) unles @notification;
} 

There is a lot of that may be new to you in this sample: The RECEIVE command, part of the Service Broker infrastructure, is how you receive your notifications. In this sample I take the message body (or bodies) which for query notifications are XML document and save them into a table. Then I use XQuery to extract the information from the XML into pieces that the Perl code then can work with.

There are several ways to achieve a notification: insert a row with b = ALFKI, delete the row there already is – or just drop the table. Thus, when you have received a notification, you need to inspect @notification to see what event(s) occurred. Note that if SQL Server cannot set up a subscription, for instance, because the query breaks the rules, you will not get an error when you run the query. Instead you will get an immediate notification with $$notification[0]{Source} set to 'statement'. Once you have received a notification from your subscription, the subscription is no longer active, but you would have to resubmit the query with the QueryNotification hash set to get further notifications.

Again, please refer to Books Online for more details on query notification. For instance, I have been entirely silent on permissions here.

Using Win32::SqlServer in Threaded Scripts

Win32::SqlServer is designed to be thread-safe and should work with Perl threads as in use threads, as well as running parallel Active-X scripts from tools like DTS or ISAPI.

When you create a new thread, Win32::SqlServer objects visible to the parent thread are copied to child thread as well. These child objects inherit some data but not all from the parent object:

Thus, a new-born child process will have to fill in login information and connect to the database (unless you have set AutoConnect). The child and parent objects are distinct objects, and changes to one will not affect the other.

share from the package threads::shared, does not work with Win32::SqlServer objects.

Bugs and Known Restrictions

Issue 1

When you retrieve char or varchar data with an embedded NUL character, the string may truncated at the position of the NUL character. That is, the string "abcd\0efghij" comes back as "abcd".

This does not exhibit if any of these two are true:

Because of this bug, there are five test cases in the test script 2_datatypes.t that fails if you have an older provider, unless the server collation is a UTF-8 collation.

Issue 2

When you run with AutoConnect, you cannot pass sql_variant data to table-valued parameters, but you get an message about a binding error. I have not been able to understand why, but I'm leaning towards that this is a bug in the OLE DB provider.

Issue 3

Messages issued with RAISERROR WITH NOWAIT are delayed, and appears one slot too late. Consider this batch:

RAISERROR('One', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:05'
RAISERROR('Two', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:05'
RAISERROR('Three', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:05'

It should print One immediately, after five seconds print Two, after ten seconds print Three and then five seconds later terminate. However, when Win32::SqlServer runs this batch, it does not print One until five seconds have elapsed, Two appears after ten seconds and Three not until 15 seconds as the script terminates.

This is a behaviour you can reproduce with several other APIs built on top of OLE DB, for instance ADO or the OleDb .Net Data provider. Nevertheless, if you run the batch above in SQLCMD for SQL 2005 and SQL 2008, which uses OLE DB, you get the correct result. Thus, Win32::SqlServer should be able do this correctly, but for now it doesn't.

Issue 4

Performance is poor when retrieving large objects. Retrieving a single 5MB varchar(MAX) value could take 5-10 depending on your hardware. A 50 MB value could take ten times as long. A similar issue exists with passing large amount of data to table-valued parameters.

The root cause is that Win32::SqlServer does not have any particular support for large types, and does not use streaming to retrieve them. A workaround may be to use the FILESTREAM feature.

Issue 5

You cannot use the share operator from the threads::shared package. I don't know if this is a problem in my code, or a restriction with threads::shared. Thus, you cannot share Win32::SqlServer objects between threads.

Issue 6

When all this is true:

then Win32::SqlServer fails to return the result set that comes directly after the error. (Subsequent result sets are returned.) Consider this procedure:

CREATE PROCEDURE errors_with_result @nocount bit AS
   IF @nocount = 1
      SET NOCOUNT ON
   ELSE
      SET NOCOUNT OFF
   RAISERROR('Hi there!', 16, 1)
   SELECT getdate()
   SELECT @@version

When you run this procedure and pass 1 for the @nocount parameter, you will not get the result set for getdate(), but you will get the result set for @@version.

This is due to some combination of bugs in SQL Server and the OLE DB providers, which have been fixed with the release of SQL 2005. But if you connect to SQL 2000 or earlier, or use the SQLOLEDB provider, you are exposed to this bug.

Issue 7

When you are using the SQLOLDEB provider, You cannot retrieve (var)char data from a UTF-8 column with a max length > 4000. When debugging, I can see that the meta-data suggests a length of 32767. (The expected value is -1, because SQL Server returns the data as nvarchar(MAX).) If left unguarded, this leads to that SQLOLEDB hangs without consuming any CPU. Win32::SqlServer mitigates the situation by croaking when it sees the length 32767. However, while you may get the error message, your script may still be hanging and not terminate by itself. I have reported this as a bug in SQL Server to Microsoft but as of CU11 of SQL 2019, the bug still exhibits.

Issue 8

OpenSqlFilestream appears to be entirely broken for SQL 2008, and in my tests it sometimes work with SQL 2008 R2 and sometimes not. No such problems are seen with later versions of SQL Server.

 

Acknowledgements

Once upon a time Larry Wall invented Perl. Somewhat later Michael Peppler wrote Sybperl for Unix. Christian Mallwitz ported Sybperl to Windows NT. Thanks to their work I was able to convert Sybase::DBlib into MSSQL::DBlib, and without that base to stand on, I would not have been able to develop the XS parts of Win32::SqlServer. The Sybperl distribution contained a simple sql() routine contributed by Gisle Aas. Simple as it may have been, it was from this routine that MSSQL::Sqllib was woven, and that interface made it to Win32::SqlServer as well.

I also need to thank the people in the perl.xs and perl.unicode newsgroups for answering my questions, and particularly Jarkko Hietaniemi and Nick Ing-Simmons.

Author, Copyright and Licence

© 2005-2019 Erland Sommarskog <esquel@sommarskog.se>.

This module is available under any license you want, as long as you don't claim that you wrote it yourself.


This file was last updated 21-07-12 23:09