History Win32::SqlServer

2012-08-25 – 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.