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.
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.
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!
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.
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.32. 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.
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.
Win32::SqlServer::SetDefaultForEncryption($EncryptOption, $TrustServerCert, $HostNameInCert);$sqlsrv = Win32::SqlServer::sql_init($server, $user, $pw, $database,
$provider);$sqlsrv = Win32::SqlServer->new();$sqlsrv->setloginproperty($property, $value);$stats = $sqlsrv->connect();$sqlsrv->disconnect();$ret = $sqlsrv->isconnected();$result = $sqlsrv->sql($batch, \@unnamed, \%named,
$colinfostyle, $rowstyle, $resultstyle, \@keys);\@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);$batch
should return
a single row, and croaks if it does not.$result = $sqlsrv->sql_sp($SP, \$retval, \@unnamed, \%named,
$colinfostyle, $rowstyle, $resultstyle, \@keys);$SP, else similar to sql(). The
return value from $SP is returned in $retval.$sqlsrv->sql_insert($table, \%data);\%data into the table $table,
using the keys in \%data as column names.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);$status = $sqlsrv->enterparameter($typename, $maxlen, $paramname,
$isinput, $isoutput, $value,
$prec, $scale, $typeinfo);$status = $sqlsrv->definetablecolumn($tblname, $colname,
$nameoftype,
$maxlen, $prec, $scale,
$usedefault, $typeinfo);$status = $sqlsrv->inserttableparam($tblname, $inputref);$status = $sqlsrv->executebatch(\$rows_affected);$more = $sqlsrv->nextresultset(\$rows_affected);$more = $sqlsrv->nextrow($hashref, $arrayref);%$hashref
and @$arrayref. Returns false, if there are no more rows
in the result set. $sqlsrv->getcolumninfo($hashref, $arrayref);%$hashref
and @$arrayref about the columns in the current result set.$result = $sqlsrv->get_result_sets($colinfostyle, $rowstyle, $resultstyle,
\@keys);$sqlsrv->getoutputparams($hashref, $arrayref);%$hashref
and @$arrayref. You cannot call getoutputparams()
until you have retrieved all result sets and all rows.$sqlsrv->cancelbatch();$sqlsrv->cancelresultset();$fh = $sqlsrv->OpenSqlFilestream($path, $access, $context [, $options [,
$alloclen]])Win32::API.Caveat: 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);$sqlsrv->sql_unset_conversion($direction);$sqlsrv->codepage_convert($string, $from_cp, $to_cp);$string from one code page to another.$ret = $sqlsrv->sql_has_errors($keepinfo);$cmdtext = $sqlsrv->sql_get_command_text();$quotedstring = $sqlsrv->sql_string($string);$string, doubling any embedded quote characters in
$string and returns the quoted string.$sqlsrv->sql_begin_trans();$sqlsrv->sql_commit();$sqlsrv->sql_rollback();Property names are case-sensitive. Win32::SqlServer is a tied hash, and if you refer to an undefined property
Win32::SqlServer croaks.
char and varchar data.datetime values are formatted when DatetimeOption has the value
DATETIME_STRFMT.
YYYY-MM-DD hh:mm:ss.fffffff ±hh:mm. decimal and
money values
are returned from SQL
Server are represented in Perl. Floating point (the default) or as strings.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. datetimeoffset data type.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
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.
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.
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";
}
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";
}
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";
}
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";
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";
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";
}
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";
}
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";
}
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";
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.
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.)
SQL's special value NULL always maps to Perl's special value undef,
even though they don't have exactly the same semantics.
You get an integer number in Perl from an SQL value of any these types.
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 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.
You get a floating-point number in Perl.
Input values are auto-converted to floating-point according to the standard rules in Perl.
What is said here, also applies to the bigint data type if you have 32-bit Perl with 32-bit integers.
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:
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.
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:
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.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.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.
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.%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, %B | Abbreviated/full month name. |
%c | Date and time representation appropriate for locale. |
%d | Day of month as decimal number (01 – 31) |
%H, %I | Hour in 24/12-hour format (00 – 23)/(01 – 12) |
%m | Month as decimal number (01 – 12) |
%M | Minute as decimal number (00 – 59) |
%p | Current locale's A.M./P.M. indicator for 12-hour clock. |
%S | Second as decimal number (00 – 59) |
%y, %Y | Year 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.
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.
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.
±hh:mm,
Win32::SqlServer saves this value and strips it from the string.When examining whether a string may be an ISO string, Win32::SqlServer considers these variations:
YYYY-MM-DD hh:mm:ss.ffffff,
where year-month-day is mandatory. The time portion may be left out
entirely, but if the hour appears in the string, minutes must also be
included. Seconds and fractions are always optional. The default for all
time parts is 0.YYYYMMDD hh:mm:ss.ffffff,
with the same rules as above.YYYY-MM-DDThh:mm:ss.ffffff,
where the year, month, day, time and minute hour are mandatory. Seconds and fractions are
optional with a default of 0. The T represents itself, and appears in
T-SQL in convert style 126.YYYY-MM-DDZ, where the
date is mandatory, and no time portion is not permitted. This date format
was introduced in SQL 2005.hh:mm:ss.ffffff, where
hours and minutes are mandatory. For the date there is a default of
1899-12-30 and for seconds and fractions there is a default of 0. Obviously,
this format is mainly intended for the time data type.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.
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.
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.)
For nvarchar(MAX) and ntext, see
also large data types below.
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.
For varchar(MAX) and text, see
also large data types below.
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.
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:
(var)char column with a collation with a different code page than the database collation, you need to use an n(var)char parameter to avoid data loss with the conversion. (Because in SQL Server, parameters and variables of the types (var)char always have the collation of the database.)(var)char values are interpreted according to the code page of the current database. Switch to the other database with a USE statement first.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.
For varbinary(MAX) and image, see
also large data types below.
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:
unpack, write
to a binary file or do whatever you want to do with it.'x'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.
Always a string in the traditional GUID representation surrounded by braces,
for instance {902A1763-561D-4F66-85B1-D18ABE916FE0}.
Must be a Perl string formatted as a GUID. Enclosing braces are permitted but not required. Other formats causes conversion to fail.
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 string will always be interpreted according to the code page of the database collation, as the OLE DB provider does not return information about collation or code page for the value.
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:
date.
datetimeoffset(7).datetime2(7).time(7).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.
int, unless the value falls outside the boundary of int, in which case Win32::SqlServer
passes the value as bigint. float.nvarchar if any of these are true:
varchar.
See also note about large data types below.
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.
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:
ntext,
and thus as UCS-2.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.
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.
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 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.
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 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.
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. Since the encryption options can be a little tricky, Win32::SqlServer offers a way to set a default for these.
With the introduction of the MSOLEDBSQL19 provider, Microsoft changed the default for the Encrypt setting, that is, whether the data sent to/from SQL Server, should be encrypted. Win32::SqlServer respects these defaults. However, this can be problematic if you are moving your scripts between different environments with different providers installed, and you generally don't expect encryption to work. (Because typically there are no trusted certificates installed.) For this reason, Win32::SqlServer offers this module-level routine to set your encryption options once for all.
Win32::SqlServer::SetDefaultForEncryption($EncryptOption [, $TrustServerCert
[,$HostNameInCert [, $ServerCert]]]);
Observe that this routine is not exported – you must include Win32::SqlServer:: in the call.
Note that the intended usage is like this:
use Win32::SqlServer qw(:DEFAULT :consts);
Win32::SqlServer::SetDefaultForEncryption('Optional');
That is, you call it more or less call directly after use. There is nothing to stop you from calling it all over the place and changing the defaults as you move on, but this is nothing I have tested. And since it deals with global values (in the C++ code), the routine is definitely not thread-safe.
$EncryptOptionundef, Optional, Mandatory and Strict. undef means that you use the default of the provider. Optional means that you permit the conection to be unencrypted. Mandatory means that you require the connection to be encrypted. Strict means that also pre-login packets should be encrypted. The latter option is only functional with the MSOLEDBSQL19 provider and later and only if you connect to an engine that supports TDS 8.0, for instance SQL 2022. For older providers, Strict and Mandatory are synonymous. Beware that if you specify Strict and the provider is MSOLEDBSQL19 and you connect to SQL 2019 or earlier, connection will fail.$TrustServerCertundef or a true or false value and sets the default for the setting TrustServerCertificate. undef means that you go with the provider default. Keep in mind that by setting this option to a true value, you are susceptible to a man-in-the-middle attack, where someone one the network pretends to be your SQL Server instance and steals or distorts data.$HostNameInCert$EncryptOption, the name in the certificate must agree with the name you specified for the server with sql_init() or setloginproperty(). This can be unpractical if you need to connect by, for instance, IP-address for DNS reasons. This setting permits you to spefiy the server name you expect in the certificate. This setting was introduced in MSOLEDBSQL19 and is ignonred by earlier versions.$ServerCertBeware that there are also registry values that affect the encryption settings, and they may specify that Mandatory is the lowest permitted level. Please refer to the topic Encryption and Certificate Validation in the OLE DB docs for details.
Creates a Win32::SqlServer object, connects to SQL Server and returns the object.
$sqlsrv = [Win32::SqlServer::]sql_init([$server, [$username,[$password,
[$database, [$provider]]]]]);
$sqlsrv$server$username$password$username. Ignored when
$username is undef.$database$providerAll 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.
Creates an un-connected Win32::SqlServer object.
$sqlsrv = new Win32::SqlServer;
Sets a login property such as server, database, packet size etc.
$sqlsrv->setloginproperty($property, $value);
$property$valueYou 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.
Far from all properites are available with all OLE DB providers. If you specify that is not at all available with a certain provider, setloginproperty() will ignore it entirely. However, if you are trying to use a login property that was introduced in a later point version of your provider (for instance, you have 19.1 and the option was introduced in 19.2), setloginproperty() will attempt to set it, which will result in an error. (There does not seem be any civilised to find out what point version of the provider that is installed.)
| Data type | string | Default |
undef |
OLE DB Property | SSPROP_AUTH_ACCESS_TOKEN |
|---|
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.
| Data type | string | Default | "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.
| Data type | string | Default | name of your Perl script, excluding directory path | OLE DB Property | SSPROP_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.
| Data type | string | Default | undef |
OLE DB Property | SSPROP_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.
| Data type | boolean | Default | undef |
OLE DB Property | SSPROP_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.
| Data type | string | Default | undef
| OLE DB Property | DBPROP_INIT_PROVIDERSTRING |
|---|
Instead of setting all properties individually, you can set all in one bang with a connection string, just like they do in other interfaces. If you set this property, Win32::SqlServer flushes the settings for all other login properties, with the exception of those listed below. 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(), they take precedence over the connection string.
There are four login properties for which Win32::SqlServer does not flush the settings of when you specify a connection string. The first is Autotranslate, which Win32::SqlServer always forces to false. The other three are Encrypt, TrustServerCert and HostNameInCertificate. This is to permit SetDefaultForEncryption() to have effect also when you are using a 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 MSOLEDBSQL19, and not all keywords may be available with older providers.)
Note: when testing, I have found that not all available connection-string keywords to actually have any effect, but I have not been able to understand why. The important ones to specify authentication, server and database work, but for instance the Encrypt keyword appears to have no effect.
| Data type | int | Default | 1 | OLE DB Property | SSPROP_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 or later. The default value given, is the default value of 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 .
| 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 or later. The default value given, is the default value of 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.
| Data type | integer | Default | 15 | OLE DB Property | DBPROP_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.)
| Data type | string | Default | "tempdb"
| OLE DB Property | DBPROP_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.
| Data type | string | Default | undef |
OLE DB Property | SSPROP_INIT_ENCRYPT |
|---|
Whether the connection is to be encrypted. Originally, this was a boolean property, but it was changed to be a string value with introduction of the MSOLEDBSQL19 provider. There are three possible settings, which for compatibility reasons can be expressed in alternate ways (all values are case-insensitive):
Win32::SqlServer only sets this option if you specify it through setloginproperty() or SetDefaultForEncryption(), else the default of the provider will apply. Note that up to MSOLEDBSQL, the default was false, that is, encryption was not required, but starting with MSOLEDBSQL19, the default is Mandatory.
Also beware that registry settings may mandate that Mandatory is the lowest permitted level, regardless to what you pass to setloginproperty(). See the topic Encryption and Certificate Validation in the OLE DB docs for details.
| Data type | string | Default | undef |
OLE DB Property | SSPROP_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.
| Data type | string | Default | undef |
OLE DB Property | SSPROP_INIT_FAILOVERPARTNERSPN |
|---|
The SPN for the failover partner. Please see the OLE DB Docs for details. This property requires the SQLNCLI10 provider or later.
| Data type | string | Default | $ENV{COMPUTERNAME}
| OLE DB Property | SSPROP_INIT_WSID |
|---|
A string that represents the machine you are connecting from and which resurfaces in the SQL function HOST_NAME().
| Data type | string | Default | undef
|
OLE DB Property | SSPROP_INIT_HOST_NAME_CERTIFICATE |
|---|
When you specify that the connection should encrypted (or this is the default set by the provider), the server's certificate must be trusted, which includes that name of the server must appear in the certificate. This can cause problems if you connect by an alternate name (IP address, FQDN etc). With older OLE DB providers, you could give the server carte blanche with the TrustServerCert login property. MSOLEDBSQL19 removes this potential security risk with the option HostNameInCertificate, which permits you to specify the server name you expect in the certificate.
You can also set this property once for all with SetDefaultForEncryption().
| Data type | string | Default | "SSPI"
| OLE DB Property | DBPROP_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.
| Data type | string | Default | undef |
OLE DB Property | SSPROP_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.
| Data type | integer | Default | see below. | OLE DB Property | DBPROP_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.
| Data type | boolean | Default | false | OLE DB Property | SSPROP_INIT_MULTISUBNETFAILOVER |
|---|
Set this property to a true value when connecting to an availability group. This property is only available with the MSOLEDBSQL provider and later.
| Data type | string | Default | undef
| OLE DB Property | SSPROP_INIT_NETWORKLIBRARY |
|---|
Which network library to use for the connection. Please refer to the OLE DB Docs for details.
| Data type | string | Default | undef
| OLE DB Property | SSPROP_INIT_NETWORKADDRESS |
|---|
A network address for the server defined by the Server property.
| Data type | string | Default | undef
| OLE DB Property | SSPROP_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.
| Data type | integer | Default | undef |
OLE DB Property | SSPROP_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.
| Data type | string | Default | undef |
OLE DB Property | DBPROP_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.
| Data type | boolean | Default | true | OLE DB Property | DBPROP_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 object that 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.)
| Data type | integer | Default | DBPROMPT_NOPROMPT(4) | OLE DB Property | DBPROP_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.
| Data type | string | Default | "(local)" |
OLE DB Property | DBPROP_INIT_DATASOURCE |
|---|
Which SQL Server instance to connect to.
| Data type | string | Default | undef
|
OLE DB Property | SSPROP_INIT_SERVER_CERTIFICATE |
|---|
Normally, when the OLE DB provider looks up the server's certificate in the certificate store of the local computer. This option permits you instead specify a file path where the certificate is located. This parameter is only available with the MSOLEDBSQL19 provider, starting with version 19.2.0. Furthermore, to use this option, you must set the Encrypt option to Srtict, as required by the OLE DB provider.
You can also set this property once for all with SetDefaultForEncryption().
| Data type | string | Default |
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.
| 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 or later.
| Data type | boolean | Default | false | OLE DB Property | SSPROP_INIT_TRUST_SERVER_CERTIFICATE |
|---|
Whether you trust the server certificate when you use encryption. You would use this option when you don't have access to the server certificate and want an encrypted connection. Note that trusting the server certificate makes you susceptible to a man-in-the-middle attack, where someone on the network pretends to be your SQL Server and steals or distorts your data. Thus, the best solution is to have the server certificate in your certificate store or have it on a file (which requires use of the login property ServerCertificate). This property is unavailable with the SQLOLEDB provider and applies only to SQL 2005 and later.
You can also set this property once for all with SetDefaultForEncryption().
| Data type | string | Default | undef |
OLE DB Property | DBPROP_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.)
Connects to SQL Server using the current login properties.
$ret = $sqlsrv->connect()
$retIt'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.
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.)
Returns whether you are connected to SQL Server or not.
$ret = $sqlsrv->isconnected();
$retThis 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.
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.
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]]);
($|@|%)result$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\@unnamed_parameters? 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@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$rowstyle$resultstyle$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$resultstyle is
KEYED, in which case it's mandatory. It specifies which column(s) in the
result set to use for keys.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.
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.
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.
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 ?? 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.)
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.
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.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, varbinaryvarchar(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, nchar, binaryvarchar 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, numericundef. That
is, best practice is to always supply precision and scale.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.
xmlxml, 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.
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 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.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.
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.
$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.
$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(...)
$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.)
@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.
$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.
$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.
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. 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().
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.
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 evalA few goods and bads with callbacks:
All and all, callbacks are not really deprecated, but they do smell a little funny.
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.
| HASH | LIST | SCALAR | ||
|---|---|---|---|---|
| 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'} |
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:
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.
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$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\@unnamed_parameters\%named_parameters$rowstylesql_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.
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]]);
($|@|%)result$SP_name$sqlsrv->sql_sp('[My.Database].thisschema."that sp"');
\$retvalue\@positional_parameters\%named_parameters@. (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$rowstyle$resultstyle$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$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(). 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.
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.
(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.)
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.
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.
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.
Inserts a row in to table from a hash, using the keys in the hash as column names.
$sqlsrv->sql_insert($table, \%values);
$table$sqlsrv->sql_insert('"My.Database"..[Order Details]', \%order_details);
\%valuesOn 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.
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.
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:
(var)char data as n(var)char to make things work more smoothly. With the mid-level routines, you must take care of this yourself.I list further restrictions with the function or parameter they apply to.
Defines the text for a command batch.
$ret = $sqlsrv->initbatch($batch)
$ret$batch? 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).
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.
Defines a parameter for a parameterised command batch.
$ret = $sqlsrv->enterparameter($nameoftype, $maxlen, $paramname,
$isinput, $isoutput [, $value
[, $precision [, $scale [, $typeinfo]]]]);
$ret$nameoftypevarchar, 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. $maxlenvarchar(20) (as well as for
nvarchar(20); the length is not in bytes).
If you leave $maxlen as undef, you will get a default of 1, which is not likely
to be what you want.
(n)text,
image, the (MAX) types,
xml and large UDTs, specify -1.
table, this
parameter has a special meaning: $maxlen holds the number of columns in the
table type.
$maxlen to the size of
the data type or leave it undef.
$paramname{? = call some_sp(@par3 = ?)}.{? = 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.
$isinputSELECT ? = @@version
$isoutput$value$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$scaledecimal and numeric.
If you don't specify these, the default values are 18 and 0 respectively.$typeinfoxml,
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.
Defines a column in a table-variable parameter previously entered with enterparameter().
$ret = $sqlsrv->definetablecolumn($tblname, $colname, $nameoftype
[, $maxlen [, $prec, [, $scale
[, $usedefault [, $typeinfo]]]]]);
$ret $tblname$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$colname will be your hash keys.$nameoftypetable is
not permitted here, but all other types are legit.$maxlen
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$scaledecimal and numeric.
If you don't specify these, the default values are 18 and 0 respectively.$usedefault$usedefault
to true for columns that cannot be set explicitly: IDENTITY columns,
timestamp/rowversion columns and computed columns.$typeinfoxml 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().
Inserts one row into a table-valued parameter.
$ret = $sqlsrv->inserttableparm($tblname, $inputref);
$ret$tblname$paramname to enterparameter(). As with
definetablecolumn(), undef refers to the most recently entered parameter,
even if this is not a table parameter.$inputrefBefore 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.
Executes a batch previously defined with initbatch().
$ret = $sqlsrv->executebatch();
$retYou 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.
Moves on to the next result set from the command batch.
$more_results = $sqlsrv->nextresultset([$rowsaffected});
$more_results$rowsaffectedYou 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.
Retrieves the next row from the current result set.
$more_rows = $sqlsrv->nextrow($hashref, $arrayref);
$more_rows$hashref
\%hash won't do. If you don't care about the hash, you
can pass undef.$arrayref$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.
Retrieves information about the columns in the current result set.
$sqlsrv->getcolumnnames($hashref, $arrayref)
$hashref%$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$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:
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.undef for large data types as well for types where it is
not applicable.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.decimal, numeric, time, datetime2 and datetimeoffset. Scale is undef for types where it is not applicable.Note that the column-information hash is not a tied hash. Thus, misspelling of any of the hash keys will go unnoticed.
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
$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$rowstyle$resultstyle$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$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.
Retrieves the output parameters from a command batch.
$sqlsrv->getoutputparams($hashref, $arrayref);
$hashref@)
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$$arrayref[0], $$arrayref[1]
and $$arrayref[2]. 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.
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.
Returns the current command state for the Win32::SqlServer object.
$cmdstate = $sqlsrv->getcmdstate()
$cmdstateYou 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:
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.
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";
The Win32API::File
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
For full details on the
FILESTREAM feature and the
$fh = $sqlsrv->OpenSqlFilestream($path, $access, $context [, $options [, $alloclen]])
$fh$path$accessWin32::SqlServer::FILESTREAM_READ, or import them
explicitly or with an export tag. $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$alloclenIf the
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');
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.
Activates a character-set conversion.
$sqlsrv->sql_set_conversion([$client_cs [, $server_cs [, $direction]]]);
$client_cs$server_cs$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.$directionWin32::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
Removes any previous conversion.
$sqlsrv->sql_unset_conversion([$direction]);
$directionConverts a string from one code page to another.
$sqlsrv->codepage_convert($string, $from_cp, $to_cp);
$string$string is modified in-place.$from_cp$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$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.
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$keepinfomsgsThe 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.
Returns the text for the current command batch.
$cmdtext = $sqlsrv->sql_get_command_text();
$cmdtext$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.
Quotes a string according to SQL rules.
$quotedstring = [$sqlsrv->]sql_string($string);
$quoutedstring$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.$sqlsrvuse Win32::SqlServer.$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.
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.
| Data type | boolean | Default | false |
|---|
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.
| Data type | boolean/string | Default | 1 |
|---|
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.
| Data type | ref to hash | Default | empty |
|---|
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.
| Data type | integer | Default | 0 |
|---|
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.
| Data type | string | Default | 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).
| Data type | string | Default |
"%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.
| Data type | enumerated | Default | DATETIME_ISO |
|---|
Controls how datetime value returned from SQL Server are represented in Perl. See further the section on the representation of datetime values.
| Data type | boolean | Default | false |
|---|
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.
| Data type | ref to struct | Default | N/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.
| Data type | ref to file handle | Default | undef |
|---|
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;
| Data type | string | Default |
"%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.
| Data type | ref to sub | Default | \&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.
| Data type | boolean | Default | false |
|---|
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.
| Data type | ref to hash | Default | undef |
|---|
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.
| Data type | boolean | Default | false |
|---|
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.
| Data type | enumeration | Default | see below |
|---|
There are six 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 seven different values for this property. When you do not specify the provider, as a default, Win32::SqlServer selects the newest provider it can find on the computer.
As long as you are connecting to SQL 2005 or later, you can use any of the providers above. (But note that officially, MSOLEDBSQL19 and MSOLEDBSQL have a lower cap in what they support.) 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().
| Data type | ref to hash | Default |
{}, 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.
| Data type | integer | Default | 100 |
|---|
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.
| Data type | string | Default | N/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.
| Data type | ref to hash | Default | undef |
|---|
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.
| Data type | ref to hash | Default | undef |
|---|
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.
| Data type | string | Default | undef |
|---|
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.
| Data type | any | Default | undef |
|---|
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;
| Data type | ref to hash | Default | undef |
|---|
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.
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.
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 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.
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 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 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.
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
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 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.
These elements control whether sql_message_handler() will abort execution or disconnect() or because of the message.
Some guidelines may be in place here, by looking at how the severity levels are used:
| Level | Comment |
|---|---|
| 0 | Level 0 is informational messages from SQL Server. |
| 1-9 | As far as I know, SQL Server itself never generates messages with these levels, but you can use RAISERROR to produce such messages. |
| 10 | SQL 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-25 | Level 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.
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.
By default, AlwaysStopOn is empty.
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.
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.
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.
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.
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.
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.
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.)
By default, LinesWindow is undef, and thus all lines
are printed.
undef – which is the default –
sql_message_handler() prints the messages to STDERR.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:
undef for SQL Server
messages. The name of the OLE DB provider for OLE DB messages.
Win32::SqlServer for Win32::SqlServer messages.undef for OLE DB and
Win32::SqlServer messages. Can also be undef for SQL Server messages.undef for
Win32::SqlServer messages.Here is an example on how to refer to an entry in Messages:
$X->{ErrInfo}{Messages}[0]{Errno}
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.
Win32::SqlServer invokes the message handler like this:
$status = MsgHandler($sqlsrv, $errno, $state, $severity, $text, $server,
$procedure, $line, $sqlstate, $source, $n, $no_of_errs);
$statuscroaks 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
$errno$state$severity$text$serverundef for
Win32::SqlServer messages and messages from the OLE DB provider.$procedureundef for
Win32::SqlServer messages.$line $sqlstateundef for Win32::SqlServer messages.$source$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
$n$no_of_errs
$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.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.
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.
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.
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:cmdstates:colinfostyles$colinfostyle
parameter: :consts$SQLSEP:datetime:directions$direction parameter to sql_set_conversion()
and sql_unset_conversion():
:filestream:providers:resultstyles$resultstyle parameter:
:returns:routines:rowstyles $rowstyle
parameter: 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.
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.
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=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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
© 2005-2022 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.