History Win32::SqlServer

2022-07-25 – 2.015

• Platform support:
  • This version includes binaries for these Perl versions: 5.32, 5.34, 5.36, 5.38 and 5.40. Yes, that means that four versions have been shaved off since 2.014, but recall that my basic policy is to support five Perl versions back.
• Breaking Changes:
  • The deprecated login property AutoTranslate has been removed. Since 2.012, the property has not had any effect.
• Feature enhancements:
  • New login property ServerCertificate, which is only available with MSOLEBDSQL19, version 19.2.0 and later.
  • Added one more parameter to SetDefaultForEncryption to support server certificate.
• Bug fixes:
  • sql_variant data where the underlying data type was varchar data with a UTF-8 collation would come back garbled with recent versions of the MSOLEDBSQL and MSOLEDBSQL19 providers. This was due to a change in Win32::SqlServer 2.013 (see below) where I tried to adapt to changes in the OLE DB providers. However, it seems that one of these changes were deemed to be a bug, which Microsoft fixed MSOLEDBSQL 18.6.4 and MSOLEDBSQL 19.1.0. Beware that if you have older versions of these providers, you may face issues with varchar data in sql_variant values.
  • Fixed a long-standing problem in the test for FILESTREAM, where one test would always fail with 32-bit, and the same test could under some circumstance blue-screen your machine. I've rewritten that particular test so that it is less "wild", and now seem to pass under all circumstances. At least in my environments. :-)
  • Some tests in 9_loginproperties.t would fail if you ran against an instance that had been enabled for Entra ID authentication. The test script now simply skips these test on such a server.

2022-06-12 – 2.014

• Platform support:
  • Support added for the new OLE DB provider MSOLEDBSQL19, which incurs some breaking changes, see below.
  • This version includes binaries for these Perl versions: 5.24, 5.26, 5.28, 5.30, 5.32, 5.34 and 5.36. All versions up to Perl 5.32 have been tested with Strawberry Perl and/or ActivePerl. For 5.34, only the 64-bit binary have been tested, since there is no free binary distribution of 32-bit Perl for that version. This version of Win32:SqlServer was released very shortly after the release of Perl 5.36, and no binary distribution was available at the time.
  • I have tested Win32::SqlServer 2.014 against SQL 2022, CTP 2.0, which was the current pre-release version of SQL 2022 at the time of the release of 2.014.
• Breaking changes:
  • Beware that MSSQLOLEDB19 defaults to requiring that the connection to SQL Server is encrypted by a trusted certificate.
• Feature enhancemets:
  • New login property HostNameInCertificate, which requires the new provider MSOLEDBSQL19.
  • New module-level routine SetDefaultForEncryption() which permits you to set the default for the login properties Encrypt, TrustServerCertificate and HostNameInCertificate directly after use. This is intended to mitigatge the change for encryption in MSOLEDBSQL19.
• Bug fixes:
  • It seems that 2.013 introduced a bug by which some login properties were ignored. The property GeneralTimeout was always ignored together with one more, exactly which dependend on which OLE DB provider you were using. This has been fixed.

2021-07-16 – 2.013

• Platform support:
  • This version includes binaries for these Perl versions: 5.24, 5.26, 5.28, 5.30, 5.32 and 5.34. All binaries up version 5.32 have been tested with Strawberry Perl. I have not tested with Strawberry Perl 5.34, as it was not avilable when I completed my release.
  • Thus, I no longer supply binaries for Perl 5.20 and 5.22, nor have I tested with these versions. The module itself continues to accept all Perl versions from 5.12 and up, though.
  • The module have been tested with MSOLEDBSQL 18.6, which was the most recent version of this provider at the time of release of 2.013.
  • Win32::SqlServer now workts when you have set your ANSI and OEM code pages in Windows 10 to UTF-8 with these restructions:
    • You must be connecting to SQL 2019 or later.
    • You need to use at least version 18.5 of the provider MSOLEDBSQL.
    These restrictions are due to the limitations in the OLE DB providers.
• Breaking changes. There are some breaking changes with string data and the sql_varaint data type, due to changes that Microsoft introduced in version 18.4 of the MSOLEDBSQL provider.
  • In 2.012, Win32::SqlServer always sent string data for sql_variant as varchar when the database had an UTF-8 collation, no matter the client code page. Because of the changes in MSOLEDBSQL 18.4, I have removed this rule, and in 2.013 string data for sql_variant is always passed as nvarchar when the database code page does not agree with the ANSI code page of the client. This applies also if you are using an older provider than MSOLEDBSQL. Win32::SqlServer will only pass the data as varchar, if the ANSI code page of the client agrees with the code page of the database collation.
  • When receiving varchar data in sql_variant, Win32::SqlServer now assumes that the data is in the ANSI code page if you are using the MSOLEDBSQL proivider (including versions 18.3 and earlier). If you are using an older provider such as SQLNCLI11 or earlier, Win32::SqlServer will assume that the data is the database collation, that is, the same as in earlier versions.
• Feature enhancements. There are five new login properties that support new connection properties added in the MSOLEDBSQL provider: AccessToken (MSOLEDBSQL 18.2), Authentication (18.2), ConnectRetryCount (18.6), ConnectRetryInterval (18.5) and TransparentNetworkIPResolution (18.4). I will need to add the caveat that my testing of these properties has been limited.
• Bug fixes:
  • There was an issue in Win32::SqlServer 2.012 with embedded NUL characters in value (var)char columns, so that the string "abcd\0efghij" incorrectly same back to Perl as "abcd". This issue has been resolved, but only if you use version 18.5 or later of the MSOLEDBSQL provider. (The issue is a bug in the OLE DB providers, not in Win32::SqlServer.)

2019-07-22 – 2.012

Beware: the name of the install script has changed. It is now called Win32-SqlServer-install.pl (and not activeperl-copy.pl). Apart from the name change, you run it as in earlier versions.

• Changes in platform support:
  • SQL 7 no longer supported (as announced previously).
  • Perl 5.18 no longer supported.
  • Binaries are available for Perl 5.20, 5.22, 5.24, 5.26, 5.28 and 5.30. With the release of Win32::SqlServer 2.011, I committed myself to support five Perl versions, but I saw on https://www.cpan.org/src/README.html that they have 5.20 as the oldest supported version, so I decided to align with that for this time. My commitment is still only five versions.
  • Since ActiveState now requires a regisration for ActivePerl, I am no longer testing with their builds, but I only test with Strawberry Perl. I still expect the binaries to work with ActivePerl. Please let me know if you run into problems.
  • If you are using the new MSOLEDBSQL provider, you are adviced to make sure that you have at least version 18.2.1 of the provider. I have only tested with this version, and Microsoft have made some enhancements for UTF-8 collations which I assume are missing from earlier versions of this provider.
• Feature enhancements:
  • I have changed how I send and receive char and varchar data. Previously, I would always coerce data into the ANSI code page of the client. This meant if you wanted to insert the value αβγ into a varchar column in a database with a Greek collation, you had to pass the value as nvarchar, or else the value would be converted to aß? and the same would happen if you retrieved the value αβγ from a varchar column with a Greek collation. The introduction of UTF-8 collations in SQL 2019 made this arrangement untenable, and when sending (var)char data to SQL Server, I convert the data to the code page of the collation of the current database. When receiving data, I receive the data as nvarchar to ensure that there is no undesired code conversion. See further the section char, varchar and text in the manual.
       Beware that these enchancements has lead to a few breaking changes, as detailed below.
  • New object property CurrentDB which holds the value of the current database. This is a read-only property which is updated automatically if you change the current database with the USE statement.
  • New object property codepages. This is a hashed keyed by database name and holds the code page for databases visited by your Perl script. This is a cache that Win32::SqlServer maintains.
  • For some reason, the constant PROVIDER_MSOLEDBSQL was not exported from Win32::SqlServer. This has been corrected.
• Breaking changes:
  • The login property Autotranslate is now deprecated and any attempt to set it is not honoured but results in a warning. This property is ON by default with the OLE DB provider, but Win32::SqlServer forces this setting to OFF, and performs all character conversion itself in order to implement the improvements for (var)char data. Likewise, if you supply the keyword AutoTranslate (or Auto Translate) with the ConnectionString property, Win32::SqlServer trumps your setting.
       I plan to remove Autotranslate as a login property iu a later version of Win32::SqlServer.
  • The format of the property SQL_version has changed. Previously, I got the value from serverproperty('ProductVersion'), but I now get it from the OLE DB provider which only returns three parts, and always has two digits for the second part. That is, what previously was returned as 14.0.3192.2 is now returned as 14.00.3192.
  • You must now be connected when you call initbatch() and if you use AutoConnect, connection happens in initbatch(). (This is because it is at this point Win32::SqlServer may have to call SQL Server to get the code page of the current database.)
  • There is an issue with embedded NUL characters in (var)char columns, so that the string "abcd\0efghij" incorrectly comes back to Perl as "abcd". This is because of the new way I use to retrieve (var)data from SQL Server exposes a bug in the OLE DB provider. I have reported the bug to Microsoft, but whether they will address it, I don't know. As a consequence of this issue, five tests in 2_datatypes.t fails. Curiously, the issue does not occur when the data comes from a column with a UTF-8 collation.
  • You cannot pass data to an sql_variant column table-valued parameter when you are running with AutoConnect. This is presumably not a new issue, but something I found when testing Win32::SqlServer 2.012. I was not able to find the cause, but I suspect a bug in the OLE DB provider.
  • Support for OpenSqlFilestream on SQL 2008 seems to be generally broken. In earlier versions, I thought it applied only Windows 10 clients, but I find that my test scripts seems to fail on all operating systems. With SQL 2008 R2, the test passes sometimes, sometimes not. From SQL 2012, I see no issues.

2018-04-22 – 2.011

This is a platform-support release:

• OLE DB Providers:
  • Win32::SqlServer now supports the new "undeprecated" OLE DB provider, Microsoft OLE DB Driver for SQL Server or MSOLEDBSQL for short. By default, Win32::SqlServer will connect with this provider when it is installed.
  • There is one new login property, MultiSubnetFailover. This property requires that you use the new Microsoft OLE DB Driver for SQL Server.
• Perl versions:
  • There are now binaries for Perl 5.26. They have been tested with ActivePerl and Strawberry Perl.
  • I no longer supply binaries for 5.12, 5.14 or 5.16. Nor do I officially support them, but building against them is not blocked.
  • I'm committing myself to provide binaries five versions back. That is, if Perl 5.28 has been released when the next version of Win32::SqlServer comes out, you can expect binaries for Perl 5.20 and up, but it is less likely there wil be a binary for Perl 5.18.
• Windows versions:
  • I have dropped support for Windows Vista and earlier. The lowest OS versions supported are now Windows 7 SP1 and Windows Server 2008 R2 SP1. For versions before Windows 10, you need to have the update in KB2999226 installed. The reason for the change is that I now build the binary with the C++ compiler that comes with Visual Studio 2017. (For fans of Windows Vista: I'm sorry that my deprecation notice for version 2.010 suggested that Vista and Windows Server 2008 would continue to be supported, but testing proved that it does not work.)
• SQL Server versions:
  • In contrast to the deprecation notice with 2.010b, connections to SQL 7 are still supported. However, the deprecation notice still applies: Next version of Win32::SqlServer may not support SQL 7!
  • There are minor corrections to the test suite to make it run without errors against SQL 2017.
• Bug fix: the rounding issue listed for 2.010 below has been fixed.
• Known issue: the test script for Filestream may cause a Blue Screen on the machine SQL Server is running on if there is an unfortunate combination of filter drivers. See Tests.html for more details.

2016-11-16 – 2.010b

This is a fairly critical bugfix: when Win32::SqlServer read the version of SQL Server it parsed @@version, but this failed when this string looked as in this example:

Microsoft SQL Server 2016 (RTM-CU3-GDR) (KB3194717) - 13.0.2186.6 (X64) 
	Oct 31 2016 18:27:32 
	Copyright (c) Microsoft Corporation
	Developer Edition (64-bit) on Windows 10 Pro N 6.3 <X64> (Build 10586: )

This has been rectified, and Win32::SqlServer again uses xp_msver if this procedure is available (it's not present in Azure SQL Database), else it uses serverproperty('ProductVersion').

As this is only a patch to the Perl code, and does not affect the binaries, I am not labelling this with a new version number.

I like to take the occasion to make two deprecation notices:

• Next version of Win32::SqlServer may not support Perl versions before 5.18!
• Next version of Win32::SqlServer may not support SQL 7!

2016-07-15 – 2.010

• There are now binaries for Perl 5.22 and Perl 5.24. They have been tested both with ActivePerl and Strawberry Perl.
• The module can now be built with Visual Studio 2015. (The binaries are nevertheless built with VS 2005.)
• Minor adaptions to code and test scripts for VS2015, Perl 5.24 and SQL 2016.
• Bugfix: passing a string value for a bigint parameter or table column in a table-valued parameter could fail despite the string being a correct bigint number.
• Known issue: OpenSqlFilestream does not seem to work on Windows 10, when you are connected to SQL 2008. It appears to work with SQL 2008 R2 and higher versions.
• Known issue: When you use a hash to pass datetime2 and datetimeoffset value, a fractional value like 130.23 may be passed to SQL Server as 130.2299, because lack of rounding. This is not a new issue, but it was uncovered with this release, because changes in SQL 2016 prompted me to change some test cases.
• This is the last version of Win32::SqlServer to support Windows 2000, Windows 2003 and Windows XP!

2015-05-17 – 2.009

This release is all about platform support.

• Binaries for ActivePerl 5.18 and 5.20 now available. These binaries have not been built with ActivePerl, since these versions of ActivePerl are shipped as having been compiled with GNU CC and MinGW. Instead I have built Perl from sources with Visual Studio, and built Win32::SqlServer in these environments. ActivePerl has a post where they say that Microsoft compilers are incompatible with their builds, but I have not encountered any problem.
• I'm supporting Strawberry Perl! It's as simple as this: the binaries that I ship for ActivePerl works with Strawberry Perl too. I have tested this for Perl 5.18 and 5.20. Users of Strawberry Perl will have to accept that the installation script is still called activeperl-copy.pl.
• Note that there are no 32-bit binaries for perl 5.18 and 5.20 without USE_64_BIT_INT.
• I have dropped support for SQL Server 6.5, as previously announced. I have also decided to drop support for Perl 5.8 and Perl 5.10. If you need support for any of these versions of SQL Server or Perl, you can use version 2.008 of Win32::SqlServer.

2012-09-29 – 2.008

Platform support:

• Win32::SqlServer now supports SQL Server Native Client 11 (That is, the version that ships with SQL 2012.)
• Binaries for ActivePerl 5.16 are now included.
• This is the last version of Win32::SqlServer to support SQL Server 6.5!

New Features:

• You can now use output parameters with sql() and sql_one(). To do this, pass a reference to a scalar, or, in the case of a datetime hash a reference to a hash.
• In a parameter array to sql() and sql_one(), the type name can now be a the name of a user-defined type. This can be the name of a so-called alias type, a CLR UDT or a table type. Here is an example that shows both these features in play. Say that you previously have executed CREATE TYPE custid_type FROM nchar(5) previously in SQL Server. Now you can run:

   my $cnt;
   sql('SELECT @cnt = COUNT(*) FROM Orders WHERE CustomerID = @custid', 
       {cnt    => ['int', \$cnt],
        custid => ['custid_type. $custid]};
   print "There are $cnt orders.\n";

For full details, see the section Details on the Parameter Specification in the manual.
• Three new loginproperties: ServerSPN, FailoverSPN and ApplicationIntent.

Changed Behaviour:

• Win32::SqlServer now uses @@version to get the SQL Server version. Previously, Win32::SqlServer used xp_msver, but xp_msver is not available in SQL Azure, and I've gotten reports that sites have revoked permission on xp_msver in a security-lockdown craze. As a result of this change, the SQL_version property no longer has a lot of trailing spaces on SQL 6.5. (Which I was unaware of previously.)
• Some occasional error messages have been changed.

Bugfixes:

• Not all variations with quoted identifiers were handled correctly. The test script 7_objectnames.t was supposed to test this, but there were bugs in the test script, why failing tests were not reported.
• There might have been potential for SQL injection in queries that retrieves metadata as the database name was inlined. This has been addressed for SQL 7 and later by using sp_executesql. However, this change does not apply to SQL 6.5 where the database name is still inlined. I should add that I have not found any actual way to make SQL injection this way.
• Arguments with non-ASCII characters could be displayed incorrectly in error messages.
• If you changed the provider for a Win32::SqlServer object after disconnecting and then reconnected, this was not always properly handled.

2011-08-07 – 2.007

• Binaries for ActivePerl 5.14 are now included.
• Bugfix: if an sql_variant column or output parameter held a value of the char or varchar data types, the value could have extraneous characters at the end. This was particularly prevalent if the value was the empty string.
• Fixed an error where the handle returned from OpenSqlFilestream was truncated in 64-bit if the handle was >= 2³¹.
• Various internal cleanups to avoid warnings when compiling for 64-bit.
• Various enhancements to test scripts for Denali and servers with non-1252 collations.

2010-10-29 – 2.006

A couple of smaller changes and fixes:

• Binaries for ActivePerl 5.12 are now included.
• You can now invoke sql_init() as either Win32::SqlServer::sql_init() or Win32::SqlServer->sql_init(). Since sql_init() is intended to be a static routine, the former is preferred, but several people tried the latter, misled by the manual that used -> in one place (and :: in another).
• Bugfix: Fixed an issue where table-valued parameters and possibly other values did not work properly with threads::shared.
• Bugfix: table-valued parameters did not work in the 64-bit version, once you had allocated 2 GB of memory.
• Fixed a memory leak that occurred that appeared if create Win32::SqlServer objects repeatedly, for instance in a loop.
• Fixed issues with how the log file was generated for table-variable parameters and the bit data type.
• Added an example to the entry for CommandTimeout in the manual how to check for timeout errors.

Note: If you are using ActivePerl 5.10, please beware that the included binaries do not run with earlier 10xx builds. I have not tested all builds, but the binaries did not run with build 1003. They do run with build 1007.

2009-06-21 – 2.005

This release brings one single change: there was a bug in the 64-bit version, so if your Perl process allocated more than 2 GB of memory, Win:32::SqlServer failed to instantiate. This bug has been fixed.

Apart from that, there are some updates in the README about known issues about building from sources.

2008-08-18 – 2.004b

It's called 2.004b, because there are no changes to the module itself. The sole changes are in the test scripts so that they run without errors against SQL 2008 RTM. There are also some changes in the build instructions to adapt for the changes from CTP6 to RTM.

2008-05-04 – 2.004

The big thing for this release is support for new features in SQL 2008, detailed below. At the time, SQL 2008 is still in beta, RTM is scheduled for Q3 of 2008. Thus I need to add the caveat that there may be issues with Win32::SqlServer 2.004 and the RTM release. Rest assured that I will monitor this, and relase a new version of Win32::SqlServer if required.

Another big thing is support for Perl 5.10; Win32::SqlServer now ships with binaries for ActiveState builds 10xx.

Changes for SQL 2008:

To have full access to these features, you need to use SQLNCLI10 provider that ships with SQL 2008. Win32::SqlServer will use this provider by default if it is installed.

• Table-valued parameters. See the section Working with Table Variables for details for the high-level interface. For the mid-level interface there are two new routines: definetablecolumn() and inserttableparam().
• The new data types date, time, datetime2 and datetimeoffset. See the section Date and Time Data Ttypes. There are some incompatibilities in that Win32::SqlServer previous silently accepted if there was garbage at the end of an ISO-formatted date string. Not so any more.
• FILESTREAM: Win32::SqlServer provides access to the OpenSqlFilesteam() API, so that you can access FILESTREAM columns using Win32::API functions ReadFile and WriteFile.
• Support for large UDTs, that is UDTs that can exceed 8000 bytes in size.
• The new built-in CLR types hierarchyid, geometry and geography are recognised, but apart from the name, Win32::SqlServer has no particular support for these; they are handled as any other CLR UDT.

Other changes and bugfixes:

• Behvaiour change: previously if you declared a varchar/nvarbinar/varbinary parameter to sql() without specifying the length, for instance ['nvarchar', $value], Win32::SqlServer would infer the length of the value of the parameter. This has been changed, so that Win32::SqlServer now uses the maximum value, that is 8000 for varbinary and varchar and 4000 for nvarchar. If the value exceeds this length, Win32::SqlServer uses MAX. The reason for this change is that the parameter list is part of the statement that is in SQL Server's plan cache. By using a consistent parameter length, Win32::SqlServer reduces the cache bloat. For char/nchar/binary, Win32::SqlServer still infers the length from the actual value when none is explicitly provided, but now issues a warning to inform you that this is not good practice.
• The variables $Win32:SqlServer::VERSION and $Win32::SqlServer::Version are now documented.
• Bugfix: character-set conversion did not work when there was no default handle. (Which there isn't if you connect with setloginproperty() and connect() rather than sql_init().)
• Despite that the manual said that you could use rowversion as an alternative to timestamp, this was not the case. This has been corrected.
• Bugfix: the check for duplicate column names in the result set was broken.
• The export tag :consts did not include the constants for command states.
• Consistent behaviour when you pass an empty command batch. No matter if you pass undef, an empty string or a string of blanks, the batch is accepted without errors. In 2.003, you got an error for undef and an empty string, but not for a string with or more spaces.
• If you call a Win32::SqlServer routine without specifying a handle, and there is no default handle, you will now get a clear error message up front. The next version of Win32::SqlServer will probnbly warn you rely on the default handle, as this is a deprecated feature.

To build Win32::SqlServer from sources, you now need Visual Studio 2005 (or the .Net 2.0 SDK), and the SDK for SQL Server Native Client 10 that ships with SQL 2008. See the README for the source distribution for further details.

2007-07-11 – 2.003

• Win32::SqlServer now runs on AMD64. In lieu of hardware to test on, IA64 is still unsupported. Note that the bigint data type is handled differently on 64-bit.
• Added a mid-level routine getcoluminfo() that returns information about the columns in the result set(s).
• Added a new parameter to sql() and sql_sp(), $colinfostyle, that permits you to specify that you want information about the columns in the result set included in the return structure. You can opt get only names, only position, or get a hash with detail information.
• Added a new result style, MULTISET_RC, that is very similar to MULTISET, but which has row counts instead of  empty arrays for INSERT, UPDATE and DELETE statements.
• Re-implemented the conversion initiated by sql_set_conversion() to use the Windows API, rather than relying on certain files being available in System32. This makes about any code-page conversion accessible from Win32::SqlServer. There is a new routine, codepage_convert(), to convert a single value.
• sql_init() now has a fifth parameter, $provider.
• Fixed bug that caused a crash on Vista when an sql_variant value had certain base types.