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. The module can use any of the two OLE DB providers SQLOLEDB and SQLNCLI (SQL Native Client). 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.
If you have previously used my module MSSQL::Sqllib, you should know that Win32::SqlServer carries over the same high-level interface, and as long you have not used any routines from MSSQL::DBlib, your modules should port with only minor modifications, see the section Notes on Migration from MSSQL::Sqllib.
Originally, I released Win32::SqlServer as MSSQL::OlleDB. However, the friendly people that maintains the Perl module list, convinced me that the module should be in the Win32 space. OlleDB was originally an internal working name, but I was not able to think of anything better. The Olle name still appears in some corners, and if you work with the test suite or venture into the code, you may see it in a few places.
And, oh, 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 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 possibilities 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 five chapters. Connection functions, High-level query functions. Mid-level query functions, 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, some notes on migrating code from MSSQL::Sqllib, some information about threaded scripts and the inevitable tale of sorrow about known bugs and restrictions. Finally, you find acknowledgements and author information.
For SQLOLEDB you need MDAC 2.6 or later. Note that if you have Windows 2000 without SQL Server, you are likely to have MDAC 2.5 only. (MDAC 2.6 was released with SQL 2000.) Windows NT does not come with MDAC at all. You can download the MDAC from http://download.microsoft.com/.
To use SQLNCLI, you need SQL Native Client which comes with the SQL 2005 as a separate install, which is redistributable. Your MDAC version does not matter when you use SQLNCLI.
If you have downloaded a binary, you do not need any compiler.
The functions of Win32::SqlServer fall into five groups: 1) connection routines. 2) high-level query funcitons. 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.
$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
$colinfostyle,
$resultstyle
,
$rowstyle
and \@keys
$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 $SP
is returned in $retval
.$sqlsrv->sql_insert($table, \%data);
\%data
$table
,
using the keys in \%data
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->executebatch(\$rows_affected);
$more = $sqlsrv->nextresultset(\$rows_affected);
$more = $sqlsrv->nextrow($hashref, $arrayref);
%$hashref
@$arrayref
$sqlsrv->getcolumninfo($hashref, $arrayref);
%$hashref
@$arrayref
$result = $sqlsrv->get_result_sets($colinfostyle, $rowstyle, $resultstyle,
\@keys);
$sqlsrv->getoutputparams($hashref, $arrayref);
%$hashref
@$arrayref
$sqlsrv->cancelbatch();
$sqlsrv->cancelresultset();
$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. However, to be compatible with
MSSQL::Sqllib, initial lowercase is permitted. (e.g.
Win32::SqlServer is a tied hash, and if you refer to an undefined property
Win32::SqlServer croaks
.
datetime
values are formatted when DatetimeOption has the value
DATETIME_STRFMT. datetime
values returned from SQL Server are represented in Perl.
Default is ISO format, YYYY-MM-DD HH:MM:SS.fff
. bigint
(on x86 only), decimal
and
money
values
are returned from SQL
Server are represented in Perl. Floating point (the default) or as string.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
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,
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
$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
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. This database does not ship with SQL 2005, but you can download it here. Alas, Northwind will not install on SQL 6.5.
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. @ neeeds 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";
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 that up. sp_helpdb returns two result # set, 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 some more examples this document. Under the description of
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
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 SQL values of this type.
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
-w
.)
In 32-bit Perl, bigint
is handled in the same manner as
decimal
and money
,
see below.
On 64-bit, bigint
is handled in the same manner as
int
. That is, full precision of the value is retained.
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
on 32-bit Perl, but not 64-bit.
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. This option can have five different values:
YYYY-MM-DD HH:MM:SS.fff
smalldatetime
, seconds and milliseconds are not
included.) This is the default setting. %Y%m%d %H:%M:%S
and for MsecFormat the default is .%3.3d
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:
| Abbreviated/full month name. |
| Date and time representation appropriate for locale. |
| Day of month as decimal number (01 – 31) |
| Hour in 24/12-hour format (00 – 23)/(01 - 12) |
| Month as decimal number (01 – 12) |
| Minute as decimal number (00 – 59) |
| Current locale's A.M./P.M. indicator for 12-hour clock. |
| Second as decimal number (00 – 59) |
| 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.
These constants are not imported by default. To refer to them, you need
to say e.g.
or
import them when you use Win32::SqlServer
, explicitly or with an
export tag.
On input, Win32::SqlServer looks at the Perl data type only, and does not consider DatetimeOption.
YYYY-MM-DD
or undelimited YYYYMMDD
. The date
and time portion can be separated with space or with a T. (The format with T
is produced with format 126 to the T-SQL function "20050730 20:30"
"2005-07-30 20:30"
"2005-07-30T20:30"
"2005-07-30Z"
. (This also comes from XML.)If the string does not conform to ISO format, Win32::SqlServer attempts to interpret the strings according to regional settings. Note that conversion to regional settings will fail if the time portion has milliseconds specified.
If the string can neither be interpreted according to regional settings fails, the conversion fails and the current message handler is invoked. Thus, Win32::SqlServer never considers the format defined by DateFormat and MsecFormat.
For (n)varchar(MAX)
and (n)text
, see
also note about large data types below.
The SQL value is a placed in a Perl string. If the data type is one of the
Unicode data types (nchar
, nvarchar
,
ntext
), the return string will always have the
Note: if you are using the SQLOLEDB provider and varchar(MAX)
and
nvachar(MAX)
will be truncated at 8000 and 4000 characters respectively. You cannot call
user-defined functions with a return value of these data types from SQLOLEDB.
The Perl value is auto-converted to a string. If the SQL data type is
any of the non-Unicode data types (char
, varchar
,
text
), and the string is a
For varbinary(MAX)
and image
, see
also note about 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'
Note: if you are using the SQLOLEDB provider and
will be truncated at 8000 bytes. You cannot call user-defined functions with
a return value of this data type from SQLOLEDB.
varbinary(MAX)
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
.
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.
On input, Win32::SqlServer only supports conversion to some possible base types for the
SQL variant value, more precisely int
,
bigint
, float
,
datetime, varchar
and nvarchar
.
The conversion depends on the Perl data type of the input value:
datetime
, Win32::SqlServer passes this datetime
value to SQL Server.int
. On
64-bit Perl, if the value falls outside the boundary of int
, Win32::SqlServer
passes the value as bigint
.float
.nvarchar
. (Normally,
Perl only sets this bit if there really are characters that do not fit into
the current 8-bit character set. However, as noted above, Win32::SqlServer always set
this bit for output values for nvarchar
/nchar
/ntext
data.)varchar
.See also note about large data types below.
Values of the xml
data type is passed as Unicode data. That
is as strings, and they will always have the
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 SQLOLEDB and SQLNCLI relay FOR XML data. A future version of Win32::SqlServer may have some setting to handle FOR XML better.
Note: when using the SQLOLEDB provider you cannot call a procedure
through 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
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, to
see if there is a charset specification, to determine how it should pass the
XML document to SQL Server. The following applies:
ntext
,
and thus as 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
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
Large data types are types that may exceed 8000 bytes, that is text
,
ntext
,
image
, varchar(MAX)
,
,
and xml
.
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.
I hope that a future version of Win32::SqlServer will provide better options to deal with large values.
There are two ways to create a Win32::SqlServer object. One is to use
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
$provider
All parameters are optional, but in order to specify $database
only, you need to specify undef
for the first three.
If the login fails, this aborts execution of the Perl script. You can only
catch this condition with eval
. If you want to handle log in
failures in a message handler, create the
Win32::SqlServer object
with
Win32::SqlServer saves the first object you create with
sql('SET NOUCOUNT ON');
without any
in front, Win32::SqlServer will use
the default handle. However, this
functionality exists only to provide compatibility with MSSQL::Sqllib, and
is deprecated. If you want to refer to 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
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
$value
You can only set a login property when you are disconnected. (So you don't
get the idea that they would have effect directly.) If you try to set a
property when you are connected, Win32::SqlServer croaks
. You can check
whether you are connected with
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 Books Online for complete description in several cases.
In Books Online for SQL 2000, you find these properties at
Building SQL Server Applications
OLE DB and SQL Server
Programming OLE DB SQL Server Applications
Data Source Objects
Initialization and Authorization Properties
In the Books Online for SQL 2005 the location is
SQL Server Programming Reference
SQL Native Client Programming
SQL Native Client (OLE DB)
Data Source Objects
Initialization and Authorization Properties
You can also find information about general properties (those starting with DBPROP) in the MDAC Books Online, which also is included in MSDN Library.
Not all OLE DB properties are exposed by undef
– this
means that Win32::SqlServer will not set it, leaving room for whatever default value
the provider fancies.
string | Default | name of your Perl script, excluding directory path | SSPROP_INIT_APPNAME |
---|
A string that represents the application name, and which can be retrieved
in SQL Server with the undef
, OLE DB will apply it's default, which will be
ActivePerl 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 | true | OLE DB Property | SSPROP_INIT_AUTOTRANSLATE |
---|
A boolean property, which, well I'm foggy on the exact effect. Books Online
says "OEM/ANSI character conversion", but I don't think that is correct. I
believe this property comes into play for the char
,
varchar
and text
data
types when the client's ANSI code page does not match the code page of the
server collation. Anyway, Win32::SqlServer does not set this property, but according to Books Online this property is on by default.
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 as they do in other interfaces. If you set this
property, Win32::SqlServer flushes the settings for all other login properties, including
its default values. So, for instance, if you set the ConnectionString
property, but do not include Initial Catalog in the connection string,
the user's default database will apply, not tempdb. If you then go on to set
other login properties with
There is one property you should not include in the connection string, and that is the Provider keyword. To set which provider to use you must use the Win32::SqlServer property Provider.
For a full list of available keywords for the connection string, please see Books Online.
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 | boolean | Default | false | OLE DB Property | SSPROP_INIT_ENCRYPT |
---|
Whether the connection is to be encrypted. Please refer to Books Online for further details.
Data type | string | Default | undef |
OLE DB Property | SSPROP_INIT_FAILOVERPARTNER |
---|
Failover partner for database mirroring. Please see Books Online for a further discussion. This property is available only with the SQLNCLI provider and applies to SQL 2005 only.
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
Data type | string | Default | "SSPI"
| OLE DB Property | DBPROP_AUTH_INTEGRATED |
---|
Specifies whether Windows Authentication is enabled. As far as I know, "SSPI" is the only
available value.
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. The property does not work with SQL 6.5.
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 | string | Default | undef
| OLE DB Property | SSPROP_INIT_NETWORKLIBRARY |
---|
Which network library to use for the connection. Please refer to Books Online 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 available only with the SQLNCLI provider, and only if you connect to SQL 2005.
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. With connection pooling on,
When this setting is false, connection is actually severed when you call
The advantage of connection pooling is that if you connect and disconnect frequently, for instance because you use the AutoConnect property, there is no overhead for this. 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. Also, be aware that if you are using application roles, you cannot use connection pooling on SQL 2000. On SQL 2005, it is possible, but you need to take some precautions, as discussed in Books Online.
(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 services.)
integer | Default | DBPROMPT_NOPROMPT(4) | 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 the MDAC Books Online):
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 | boolean | Default | false | OLE DB Property | SSPROP_INIT_TRUST_SERVER_CERTIFICATE |
---|
Whether you trust the server certificate when you use encryption. Please refer to Books Online for further details. This property is only available when you use the SQLNCLI provider, and you connect to SQL 2005.
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()
$ret
It's an error to call this function, if you are already connected. You
can use
If the login to SQL Server as such fails,
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.
Returns whether you are connected to SQL Server or not.
$ret = $sqlsrv->isconnected();
$ret
This function returns Win32::SqlServer's internal connection state. If the physical
connection somehow was severed – network error, severe SQL Server
error – but Win32::SqlServer has not discovered this yet,
Note that if even this function returns 0, the connection could still exist in SQL Server, due to connection pooling.
The intention is that 99% of the time, you will be running one of
$colinfostyle
, $rowstyle
,
$resultstyle
and \@keys
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. The third is used only
when the data type is xml
or UDT
, in
which case it specifies an XML schema collection or the name of the UDT. Ser
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
.
You cannot use named parameters with SQL 6.5.$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 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. Now 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 $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. If you submit an ad-hoc batch, SQL Server attempts to auto-parameterise and save a plan that can be used for a similar query, but this does not always happen. Thus, it is not unlikely that if the batch above is executed many times, SQL Server builds a query plan each time. With a parameterised query, you get what we in Perl-speak would call an anonymous stored procedure. The plan for this query is saved in cache, and will be reused on subsequent invocations. Not the least for INSERTs that can come in massive bursts, this can have considerable effect on performance.
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.
(Note: if you are on SQL 6.5 the above is not true. There is no sp_executesql on 6.5, and parameters are expanded before they are passed to SQL Server.)
There is one more case worth mentioning. You cannot parameterise everything. This does not fly:
$result = $sqlsrv->sql("SELECT * FROM ?", [['nvarchar', 'MyTable']]):
This results in a syntax error on SQL 7 and
earlier. On SQL 2000 and later, you are told
that
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. (In
fact that would be a table variable, but you cannot pass these as
parameters.)
There are two ways to specify a parameter in a command batch: by using ?
as
a parameter placeholders and by using a standard T-SQL parameter name starting with
@
. The former I refer to as unnamed parameters, the latter as
named parameters. Note that if you are on SQL 6.5, you can only use unnamed
parameters.
Win32::SqlServer expands the ?
to
,
,
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
is really just a shortcut for entries in \%named_parameters
,
etc. From this follows, that it's perfectly possible to mix parameter
placeholders and named parameters in a command batch.
Parameter placeholders are convenient for single occurrences in short batches, but if you need to refer to the same parameter in the same command batch, named parameters are easier to use.
SELECT col1, col2 FROM tbl WHERE last_name = @name OR first_name = @name
Had you used
here, you would in fact have had two
parameters and not one. Named parameters are also more palatable when you have very many parameters. That ?
?
over
there, is that
or
?
When you construct
, 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 this only happens on SQL 6.5. There are two reasons why
Win32::SqlServer expands ? itself on SQL 7 and later: 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
and
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
char
and at the same time issue 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.
integer
for int
, with
one exception: you can use rowversion
for
timestamp
. For an alias data type (those defined with
sp_addtype or CREATE TYPE FROM), use the underlying
base type. For CLR user-defined types, use UDT
. See
further remarks per data type below. The type name is case-insensitive.xml
or UDT
.
See further under these data types below.Here are remarks for specific data types that may require extra information beside the type name.
char
, varchar
, nchar
,
nvarchar
, binary
, varbinary
varchar(20)
,
or just the type name, e.g. varchar
. In the latter case,
Win32::SqlServer will infer the length from the actual value. Thus if you say
nchar
and the value is ALFKI,
Win32::SqlServer will use
nchar(5)
. (If the value is an empty string,
Win32::SqlServer will use a length of 1.)
If the length of the value exceeds what the SQL Server version supports,
Win32::SqlServer
uses the appropriate max value. That is, 255 for SQL 6.5, 4000/8000 for SQL 7 and
SQL 2000. On SQL 2005,
Win32::SqlServer will infer
(MAX)
.
Note: While this is convenient, each new length of the type will result in a new entry in the SQL Server cache. So best practice is to specify the length, particularly if you run this from a loop.
decimal
, numeric
undef
. That
is, best practice is to always supply precision and scale.UDT
['UDT(dbo.ComplexNumber)', $cmplx] ['UDT', $cmplx, 'dbo.ComplexNumber']You must use the latter method, if the type name includes special characters, notably then parentheses.
Whatever the method, you need to quote any part of the name that is not a regular T-SQL identifier with brackets or double quotes. For instance::
['UDT([My Database].MySchema.[Your UDT])', $value)] ['UDT', $value, '[My Database].MySchema.[Your UDT]'] ['UDT', $value, '[This-Type]'] ['UDT("This-Type")', $value]
xml
xml
, you can specify an XML schema collection.
The mechanism is the same as for specifying a type name for UDT
,
with one single difference: it's optional to specify a schema collection.
Thus, all these are fine:
['xml', $myxmldoc] # Untyped xml ['xml(my_schema_collection)', $value)] # Typed xml ['xml', $myxmldoc, 'my_schema_collection'] # Ditto ['xml', $mydoc, '[my-schema-collection]'] # Must quote this name.
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.
And, one thing I have not said elsewhere: you cannot use output parameters
with
The parameters $colinfostyle
, $rowstyle
, $resultstyle
and
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
use
Win32::SqlServer
, either by explicit name or by using an 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
, 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, it 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
@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
.
To refer to the Product IDs, you would say
where $i
starts at 1 and goes to
.
If you instead say:
$result = $sqlsrv->sql(<<SQLEND, ...
you refer to the customer
ID as
, 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]
where
$i
is as above. (Or
and
, 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 perform 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 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}
.$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
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
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
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 values 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
undef
or an empty array. The gains with NORESULT
are limited. Win32::SqlServer performs a $resultstyle
pass a reference to a Perl
sub
. Win32::SqlServer calls this sub
once for each row
that is returned from the query. $rowstyle
then controls how
that calls looks like:
$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
Important: when you call a stored procedure with
die
yourself, you should use this return
code. Win32::SqlServer will then free up resources tied to the result set, before it
aborts execution. This is important in the case you catch the
condition with eval
, so you don't have an unprocessed result set
when you come back from eval
A few goods and bads with callbacks:
All and all, callbacks are not really deprecated, but they smell a little funny. However, I don't really know what will happen when I add better support for large object in the future.
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.
HASHASH | LIST | SCALAR | ||
---|---|---|---|---|
MULTISET MULTISET_RC |
|
|
|
|
|
$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[0]{'col'} |
$res[0][$colno] |
$res[0] | |
$res |
$$res[0]{'col'} |
$$res[0][$colno] |
$$res[0] | |
SINGLEROW |
|
$res{'col'} |
|
|
|
|
$res[$colno] |
$res[0] | |
$res |
$$res{'col'} |
$$res[$colno] |
$res | |
KEYED |
|
$res{'key'}{'col'} |
$res{'key'}[$colno] |
$res{'key'} |
|
|
|
| |
$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
on some table or views, or that runs
a set of 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 $colinfostyle
.
There are four possible values for this parameter:
To understand this a little better, let's look at this example:
$orderid = 11000;00; $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,
is EmployeeID, and
is RATTC, the customer for order 11000 in the
Northwind database.
has the value
UnitPrice, the name of the second column in the second result set. And
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
,
and if you look in
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
,
and at
is the customer ID, RATTC. But
what is at
? 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 fails 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
$rowstyle
$resultstyle
set to SINGLEROW, but there is one important
difference: 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 $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 \@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 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 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
(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
A consequence of this is that there are some special system stored procedures
that you cannot call with
Each entry in
and
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 ASThe 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,
(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
, despite not being a
reference. Thus, in this case the warning is incorrect. But there is no way for
As for how to specify the actual values for different data types, see the section Data-type Representation.
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 value 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 SELECT @err = @@error IF @err <> 0 OR @ret <> 0 GOTO error_exit
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. Finally, note that the
bulky error handling in T-SQL is not present in the Perl code, as by default
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);
\%values
On the first invocation for a certain table,
As for specifying the data in the columns, please see the section Data-type Representation.
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
If you think that the high-level functions puts 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 uses, 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:
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
You cannot initiate a new command until you have retrieved all
result sets and the output parameters, unless you first call
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):
Defines the text for a command batch.
$sqlsrv->initbatch($batch)
$batch
?
as placeholder. In
difference to @
is
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
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
You do not have to be connected to call
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 ASYou intend to provide a value only for
@par2
and @par4
when you can
?
only.
You can provide values directly in the command batch like this:
{? = call dbo.some_sp(?, ?, ?, 1)}
(Put 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
$nameoftype
varchar
, not varchar(3)
.
This must be a system type, and it must be the main name for the type; you
cannot use the data-type synonyms that ships with SQL Server, for instance
integer
for int
, with one exception: you can use
rowversion
for timestamp
. For CLR UDTs, use
UDT
. The name must be in lowercase, except for UDT
which must be all uppercase. $maxlen
varchar(20)
parameter (as well as for
nvarchar(20)
; the length is not in bytes). If
you leave it undef
, you get a default value of 1, which may not be what you
want. Use -1, for large types, that is (n)text
, image
, the
(MAX)
types and xml
$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
,
etc when it constructs the call to sp_executesql.
Case 4 – in this case, if you set the parameter name to undef
for, say, the third ?
, this means that you pass a value for the
second parameter. (Since the first ?
is the return value). If you specify a
name, this must be the actual name of a parameter to the stored
procedure.
It appears that you can in fact mix named and unnamed parameters entirely
here, and provide a name for the first parameter (that is, the second ?
),
but leave it out for the second. I would not recommend this, though.
$isinput
SELECT ? = @@version
$isoutput
$value
$isinput
. If you
specified 1 for $isinpu
t, Win32::SqlServer will attempt to convert the value to type
specified in $nameoftype
, as described in the chapter
Data-type Representation.$precision
$scale
decimal
and numeric
.
If you don't specify these, the default values are 18 and 0 respectively.$typeinfo
xml
and
UDT
, and only when you use SQL Native Client as the OLE DB
provider. For xml
it specifies a schema collection and is
optional. For UDT
it specifies the name of the actual CLR
type and is mandatory. Both names can be specified with three-part notation on
the form database.schema.object. If there are parts that
include special characters such as space or period, you must quote these in
brackets or double quotes. You must call
If Win32::SqlServer cannot convert $value
to
$nameoftype
,
You must call
Note that far from all parameter errors are detected by
Executes a batch previously defined with
$ret = $sqlsrv->executebatch();
$ret
You must first define a command batch with
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
Most errors are communicated through the current
message handler, but pure usage errors like calling croak
.
Moves on to the next result set from the command batch.
$more_results = $sqlsrv->nextresultset([$rowsaffected});
$more_results
$rowsaffected
You can only call
For each command batch you will need to call
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
If the command batch results in SQL errors, they are communicated through the
current message handler when you call
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
@@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
As noted under
Retrieves information about the columns in the current result set.
$sqlsrv->getcolumnnames($hashref, $arrayref)
$hashref
%$hashref
\%hash
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 %$hashref
@$arrayref
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 user-defined type appears as
UDT
; the actual name of the type is not available.undef
for large data types as well for types where it is
not applicable.undef
for types
where it is not applicable.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 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 \@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
Retrieves the output parameters from a command batch.
$sqlsrv->getoutputparams($hashref, $arrayref);
$hashref
@
)
you provided with 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
If there is no active result set,
Cancels the current command batch and frees all resources allocated for it.
$sqlsrv->cancelbatch()
You can call
Since cancelling a command batch actually means that part of the batch may not be executed at all, using $resultstyle
= NORESULT, or call
Returns the current command state for the Win32::SqlServer object.
$cmdstate = $sqlsrv->getcmdstate()
$cmdstate
You can call
use Win32::SqlServer
. Either refer to them as Win32::SqlServer::CMDSTATE_INIT
etc or import the constants you need implicitly 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', -1, undef, 1, 0, '1997-06-01'); $ok &= $sqlsrv->enterparameter('nchar', 5, undef, 1, 0, 'ALFKI'); $ok &= $sqlsrv->enterparameter('datetime', -1, 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
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', -1, '@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"; }
These routines are retained from
MSSQL::Sqllib,
although I have reimplemented them to use routines in the Windows API, rather
than rely on files in SYSTEM32. Character-set conversion is something you would
use when you have data in one
Caveat: I have only tested with an ANSI server – really what the OLE DB provider does with an server using an OEM code page I don't know.
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.$direction
Win32::SqlServer::TO_CLIENT_ONLY
, or import them
explicitly or with an export tag.If any of the code pages you specify does not exist, croaks
.
If $clent_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 it ways. 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
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 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.
Removes any previous conversion.
$sqlsrv->sql_unset_conversion([$direction]);
$direction
Converts 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
$string
has the
$from_cs
. 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 codepages respectively.
Internally, $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 "?".
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
$keepinfomsgs
The Win32::SqlServer property ErrInfo has an element Messages into which
If SaveMessages is not enabled when you
call
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. (On SQL 6.5 you get a faked call, since
sp_executesql does not exist in 6.5.) For a call to $cmdtext
is simply the text you passed to 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
.$sqlsrv
use Win32::SqlServer
.$string
You can use
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
Note that the default behaviour of OLE DB is to pool connections, which means that there is a lag time before the actual disconnection occurs. Setting AutoConnect on, if you disable connection pooling is likely to be a poor idea. See further the login property Pooling.
When false, you must connect with
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 | 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
to be safe.
ODBC, ADO and ADO .Net all have a default timeout of 30 seconds, but in my opinion command timeouts are a really poor idea, and should only be used if you have a real reason.
Data type | string | Default |
|
---|
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 bigint
(in 32-bit Perl),
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. 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
Example:
open(F, 'MyFile.log'); $sqlsrv->{LogHandle} = \*F;
Data type | string | Default |
|
---|
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 be 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 meta-data.
The mid-level query functions do not honour this flag.
Data type | ref to hash | Default | undef |
---|
This hash is used by
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 |
---|
Win32::SqlServer can connect to SQL Server though one of two OLE DB providers: SQLOLEDB (Microsoft OLE DB Provider for SQL Server), which comes with the MDAC and Windows, and SQLNCLI (Microsoft SQL Native Client) which is a redistributable component that comes with SQL 2005. Only SQL Native Client has full support for SQL 2005. Only SQLOLEDB supports connections to SQL 6.5.
There are three different values:
You cannot set this property when you are connected. You can check whether
you are connected with the function
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
Data type | string | Default | N/A |
---|
This is a read-only property, that returns the version string, e.g. 8.00.2039
, for the SQL Server you are connected to. If you try to retrieve the value before you have
connected the first time, and neither have set AutoConnect, this will fail,
as Win32::SqlServer in this case tries to connect to get the version number.
Once you have connected, Win32::SqlServer will use a cached value. If you change any of the login properties ConnectionString, NetworkAddress or Server, Win32::SqlServer will flush the cached value.
Data type | ref to hash | Default | undef |
---|
This hash is used by
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 use any key value, 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;
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. Normally you don't
have to write your own message handler though, because Win32::SqlServer comes with a default message
handler,
This chapter describes the ErrInfo property, and thus the behaviour of
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, if 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 are often 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 and the message handler when the
condition is related to SQL. So a call croak
. While if
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
.
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
.
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. set by the OLE DB provider for SQL Server errors and some 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 MDAC Books Online, or in MSDN Library. The section is entitled Appendix A: ODBC Error Codes and is found in the ODBC Programmer's Reference.
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
. In order to provide
compatibility with MSSQL::Sqllib, initial lowercase is permitted. (And, yes,
neverPrint and NeverPrint are the same.)
These elements control whether
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 never itself 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). |
For normal SQL errors – what Books Online calls "user errors" – SQL Server uses the levels 11-16, in a fairly arbitrary fashion. For instance, a NOT NULL violation is level 16, while a permission error is level 13. The severity of Win32::SqlServer errors or 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) 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
Say you want to delete a row if there are no references to it. Rather that doing an IF EXISTS for each referencing table, you can to 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 for which 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 to
Besides the listed errors,
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
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
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 a 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
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,
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. These settings are brought over from MSSQL::Sqllib, which uses DB-Library and that has all these settings off by default.
By default, LinesWindow is undef
, and thus all lines
are printed.
undef
– which is the default –
The default behaviour for
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
.
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 print the SQL code and it never aborts execution.
Win32::SqlServer calls the message handler like this:
$status = MsgHandler($sqlsrv, $errno, $state, $severity, $text, $server, $procedure, $line, $sqlstate, $source, $n, $no_of_errs);
$status
croaks
and aborts execution. You should not call die
, croak
or exit
in your message handler, but leave this to
Win32::SqlServer. This
is because Win32::SqlServer needs to free resources associated with the current batch.
(Even if you die
/croak
, someone might catch this
with
eval
.)$sqlsrv
$errno
$state
$severity
$text
$server
undef
for
Win32::SqlServer messages and messages from the OLE DB provider.$procedure
undef
for
Win32::SqlServer messages.$line
$sqlstate
undef
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. $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
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
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
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
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
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:
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 :providers
:resultstyles
$resultstyle
parameter:
:returns
:routines
:rowstyles
$rowstyle
parameter: Here is an example,
on how to import
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 have existing scripts that uses MSSQL::Sqllib, it should be fairly easy to port these to use Win32::SqlServer. This chapter highlights some incompatibilities.
MSSQL::Sqllib includes all functions in the export list. The idea was that
when you called
without using an object handle like
in front. You can still do this in Win32::SqlServer, however,
this usage is now deprecated. In order to refer to
If you are using functions from MSSQL::DBlib, these are not available with Win32::SqlServer. Here is a list of the functions that you are most likely to have used, and suggested replacements, if any.
dbclose | Replace with
|
dbexit | Just remove any calls to dbexit. |
BCP routines | Win32::SqlServer has no BCP capability. If you are using the BCP routines, you are probably better off staying with MSSQL::Sqllib for now. |
text/image functions. | Win32::SqlServer currently has no particular support for large data types. Functionally, you can handle fields of any size, but performance starts to become poor when the size exceeds 1MB. |
dbsetopt | Just send the corresponding
SET command with |
DBSETLxxx | Most of these have
corresponding login properties you can set with
|
dbmsghandle | You can establish your own message handler with the MsgHandler attribute. |
In MSSQL::Sqllib, if you did not specify a username for the second parameter
to
MSSQL::DBlib::DBSETLSECURE()
With DB-Library, all options controlled by SET are off by default. With OLE DB, and thus Win32::SqlServer these settings are on by default: ANSI_NULLS, ANSI_NULL_DFLT_ON, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL and QUOTED_IDENTIFIER. For further details on these SET options, see Books Online.
In MSSQL::Sqllib and MSSQL::DBlib many properties have initial lowercase, but mixed case inside, for instance errInfo, dateFormat. Win32::SqlServer uses leading uppercase: ErrInfo, DateFormat (save two exceptions: the all-lowercase procs and tables). This also applies to the ErrInfo elements and to the elements of the Messages array.
However, to retain compatibility, Win32::SqlServer performs a second lookup with initial lowercase if first lookup fails. Thus, you can still use errInfo, dateFormat etc with Win32::SqlServer, with some slight performance penalty.
The default date format with MSSQL::Sqllib was
YYYYMMDD
, whereas Win32::SqlServer has YYYY-MM-DD
YYYYMMDD
format, set the
property DatetimeOption to DATETIME_STRFMT.
The MSSQL::DBlib properties dateFormat and
msecFormat are present in Win32::SqlServer as
well, and applies then DatetimeOption
has the value
DATETIME_STRFMT. However, the format codes for day of the week or
day of the year does not work with Win32::SqlServer.
Most of the MSSQL::DBlib attributes have not been brought over to Win32::SqlServer:
dbNullIsUndef | NULL is always
represented as undef in Win32::SqlServer. |
dbKeepNumeric | Integer values (except bigint on 32-bit Perl) and real/float are always numbers with Win32::SqlServer. Bigint, decimal and money are controlled by the property DecimalAsStr. |
dbBin0x | Replaced by the property BinaryAsStr. |
dateFormat, msecFormat | Retained, see above about date format. |
cloneFlag | This functionality is not available in Win32::SqlServer. |
DBstatus | This functionality is not available in Win32::SqlServer. |
ComputeID | Not applicable to Win32::SqlServer, see also below about COMPUTE BY. |
All attributes from MSSQL::Sqllib are available with Win32::SqlServer. (But here known as properties.)
MSSQL::Sqllib had just a plain hash to keep track of its properties. As a
side effect, you would not get an error if you specified a non-existing
property. Win32::SqlServer uses a tied hash for its properties as well for ErrInfo and
the Messages array, and croaks
on all non-defined key names. If
you have used the Sqllib object to store your own data, you can use the
UserData property in Win32::SqlServer.
With MSSQL::Sqllib you got the columns from a COMPUTE BY clause as special columns within the result set, and with the HASH row style there was an extra column COMPUTEID. With Win32::SqlServer, each COMPUTE BY row is returned in a result set of its own and splits the result of the query into several result sets. There is no COMPUTEID column. This is because OLE DB has no special support for COMPUTE BY. (The only API to provide this is DB-Library.) Then again, COMPUTE BY is a highly deprecated SQL Server feature – and with a good reason.
Query notification is a new feature in SQL 2005 that uses the new 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 SQL Native Client as well, and Win32::SqlServer gives you access to query notification through the QueryNotification hash.
For full information on Query Notification, please see Books Online: SQL Server Database Engine → Accessing and Changing Database Data → Manipulating Result Sets → Using Query Notifications, for the full story in Query Notification. 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 new SQL 2005 features 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 recieved a notification, you
need to inspect
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
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 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
parameter, you will not get the result set for
, but you
will get the result set for
.
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.
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, the new command-line tool that comes with SQL 2005, 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.
The root cause is that Win32::SqlServer does not have any particular support for large types, and does not use streaming to retrieve them. I hope to add this in a latter release.
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.
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
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-2007 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.