# Test scripts for Win32::SqlServer

This text gives some information on the test suite for Win32::SqlServer.

## Requirements for Running the Scripts

Obviously you need SQL Server. It can be any version of SQL Server supported by Win32::SqlServer. There is no requirement that you have a certain edition; Express Edition will work just fine. Which tests that are performed depends on the SQL Server version: Generally, the higher the version, the more tests.

The test script assumes that you are running with the permissions needed in SQL Server. Several test scripts create databases and one creates a login and will fail if you don't have permission. Many scripts also create tables and executable objects in tempdb. You may not have be a member of sysadmin to run the scripts, but it's certainly the easiest way out. It goes without saying that you should run the tests on a production server.

If a script terminates normally, it should drop everything it creates. (If not, that's a bug in the test script.) But if a script crashes, you may have to clean up manually. Below, I detail what each script creates.

## Collations, Regional Settings etc

A challenge with writing these scripts is that they to some extent depends on settings for language, regional settings etc, both on the client computer running the tests and the SQL Server instance. I have mainly developed the tests under these conditions:

- Swedish regional settings client-side.
- The ANSI code page 1252 and the OEM code page 850, client-side.
- English version of the OLE DB provider (matters for error messages).
- Server collation Finnish_Swedish_CS_AS, which imples ANSI code page 1252 for varchar.
- The SQL Server language set to us_english (matters for error messages).

I have occasionally run the tests with different regional settings (to test DATETIME_REGIONAL), and some tests with Czech_CS_AS, which has a different ANSI code page in the server, I've tweaked/cheated to suppress test failures in this case. I have not tested with a different ANSI code page on the client than 1252 – most likely you will see errors in this case. If you use non-English version of SQL Server and/or SQL Server client, tests where I test on text in error messages will fail.

These shortcomings should not be construed as restrictions in what Win32::SqlServer supports, it is just the difficulty to cover all in a test script.

## Running the Test Scripts

All test scripts looks at the environment variable OLLEDBTEST. This variable controls which server(s) the test scripts should access. OLLEDBTEST has this format:

        Server1;User1;Pwd1;Server2;User2;Pwd2;Provider

If you don't specify Server1, the test script uses the default instance on the local machine. If you don't specify User1, the test scripts logs in with Windows authentication. Server2 is a second server, and is needed for some tests in the script 9_loginproperties.t. If you don't specify Server2, or it is the same as Server1, those tests are skipped. Example: to run the test at the server DEVSERVER with Windows authentication, and skip tests that require a second server, simply say:

       SET OLLEDBTEST=DEVSERVER

To run the test on your local server and DEVSERVER as a second server, now with SQL authentication, you would say:

       SET OLLEDBTEST=.;;;DEVSERVER;sa;notstrong

The last option in the string is Provider. Use this if you want to force a certain provider. You will neeed to use this if you want to test against a version of SQL Server which is not supported by the default provider, which always is the most recently released proivder. The values for Provider are: 1 – SQLOLEDB, 2 – SQLNCLI, 3 – SQLNCLI10, 4 – SQLNCLI11.

The simplest way to run the scripts is to run them with `MAKE test`. You can also run them from the source directory as:

       perl t/1_resultsets.t > test.out 2>&1

The search the output file for "not ok". Some test scripts run a awful lot of tests (because they iterate over combinations), so you really need to catch the output in a file.

## Reporting Errors from Tests

If you run a test script and one or more tests fail, please don't just tell me that test 324 in some script failed, put please send me the full output from the script. The test numbers are assigned dynamically in several of the scripts, so I will not be able to easily find which is test 324. I also need to know which version of SQL Server you are using and which provider.

## The Test Scripts

### 1_resultsets.t

Tests: the various row, result and colinfo styles.

Number of tests: 414.

Creates in SQL Server: Temporary tables only.

### 2_datatypes.t

Tests: that Win32::SqlServer handle all SQL Server data types correctly. As a side effect it also tests sql_sp(), sql_insert() and that the log file is correctly constructed.

Number of tests: Many. From a little below 1100 on SQL 6.5 to over 4100 on SQL 2008 when you use SQLNCLI10 and you have the CLR enabled.

Creates in SQL Server: Tables and stored procedures in tempdb, and on SQL 2005 also an XML schema collection and three CLR user-defined types (the latter only if CLR is enabled). If you have SQL 2012 and your server collation is one of the new SC (surrogate-aware) collations, the script creates a database, and runs all tests in that database rather than tempdb. (Because the old data types text and ntext are not supported with SC collations.)

### 3_retvalues.t

Tests: return values of different data types from stored procedures and user-defined functions for sql_sp().

Number of tests: 7 (on SQL 6.5 and SQL 7), 33-43 (SQL 2000 and SQL 2005), 79 (SQL 2008).

Creates in SQL Server: stored procedures and user-defined function in tempdb, and on SQL 2005 also an XML schema collection and CLR user-defined types (if the CLR is enabled).

### 4_conversion.t

Tests: the charset conversion you can set up with sql_set_conversion(). If your OEM code page is not CP850 or CP437, some of the tests are skipped. If the code page for your system collation is not 1252, the entire test is skipped.

Notes: the script requires Win32::Registry and will not compile if this module is not available. It is likely that some or all tests will fail if your ANSI code page is not 1252 (Latin-1).

Number of tests: 37. Two tests for output parameters are skipped on SQL 6.5 where output parameters are not supported. Some tests are skipped if the CLR is not enabled. Tests for table-valued parameters are only performed if you have SQL 2008 and SQLNCLI10 or later.

Creates in SQL Server: CLR user-defined types and a table type, if these features are available. Else only temporary objects.

### 5_errors.t

Tests: the error handling.

Number of tests: 237. Some tests are skipped depending on the SQL Server version.

Creates in SQL Server: On SQL 2008 and later the test creates a table type in tempdb. Else only temporary objects.

### 6_paramsql.t

Tests: passing parameters to sql().

Number of tests: 113. On SQL 6.5 the major bulk of them are skipped, since 6.5 does not have all features, and Win32::SqlServer does not support named parameter and output parameters on SQL 6.5.With SQL 7/2000 fewer tests are skipped and even fewer for later versions. For all tests to be run, you must have SQL 2008 or later, SQLNCLI10 or later and the CLR must be enabled.

Creates in SQL Server: User-defined types in tempdb. On SQL 2005 and later, it creates an XML schema collection in tempdb, as well as four CLR user-defined types if the CLR is enabled.

### 7_objectnames.t

Tests: how Win32::SqlServer looks up the procedure and table names for sql_sp() and sql_insert(), as well as the names for user-defined types for sql() and also how it parses the "typeinfo" argument for the UDT and xml data types in SQL 2005.

Number of tests: From 121 for SQL 6.5 to 3641 (SQL 2008 or later with SQLNCLI10 or later). While the latter combination has the highest number of tests due to the available feature set, you will also need to run for SQL 2005 or SQLNCLI to tests all apsects of user-defined data types. (Because for SQL 2008 and SQLNCLI10, the script tests user-defined types with table types only, but these types cannot be used across databases, which Win32::SqlServer supports for regular user-defined types.)

Creates in SQL Server: This script creates no less than five databases (two on SQL 6.5). Four of them have names starting in "OlleDB" or Olle$DB", and the fifth has all Greek letters, Ρεκσμοερνγως. The script also creates objects in these databases and adds guest as user.

### 8_columninfo.t

Tests: the type information returned when the colinfo style is COLINFO_FULL.

Number of tests: 36, of which some are skipped depending on the SQL Server version, the OLE DB provider, the collation and the availability of the CLR.

Creates in SQL Server: Temp tables. On SQL 2005, it creates an XML schema collection in tempdb, as well as four CLR user-defined types, if the CLR is enabled.

### 9_loginproperties.t

Tests: setloginproperty() and other connection routines.

Notes: to perform all tests, this script requires that you have defined a second test server with the OLLEDBTEST environment variable.

Number of tests: 44, whereof three will be skipped if no second server is defined. Some tests are skipped depending on the SQL Server and the provider. Only with SQL 2005 or later and SQL Native Client and a second server all tests will be performed. And even in that case, two tests will be skipped if SQL Server is configured for Windows authentication only.

Creates in SQL Server: Creates a database which it detaches and then reattaches with a different name. Both names start with "OlleDB". On SQL 2005 and later, if SQL Server authentication is enabled, the script creates a login with the name starting in "Olle" followed by random digits. The script creates the login with CHECK_POLICY = OFF. As all other objects the test script creates, the login is dropped if the script terminates normally. No objects are created in the second server.

### A_tableparam.t

Tests: table-valued parameters in general (some aspects of TVPs are tested in other scripts).

Notes: this test is skipped entirely unless you have SQL 2008 or later and the provider is SQLNCLI10 or later.

Number of tests: 1554 (somewhat fewer if the CLR is disabled).

Creates in SQL Server: various table types, a XML schema collection and four CLR UDTs if the CLR is enabled.

### B_filestream.t

Tests: the OpenSqlFilestream method.

Notes: this test is skipped entirely, if any these are true: 1) The version is SQL 2005 or earlier. 23) Filestream is not enabled for remote access according to sys.configurations. 3) You run the test suite with SQL authentication. If the provider is SQLOLEDB or SQLNCLI, there is only a single test to verify that Win32::SqlServer croaks with the correct error message.

Note that the Filestream feature is configured in two places. In SQL Server with sp_configure and on Windows level through the SQL Server Configuration Manager. I have not been able to find out how to check for the latter reliably, so in case sys.configurations says that Filestream is enabled for remote access, but this is not the fact on Windows level, the test will fail big time.

Number of tests: 9.

Creates in SQL Server: the scripts creates a database, Olle$DB, with a FILESTREAM file group. It creates a table with filestream data in that database. The database is dropped at the end of the script.

### Y_rowsetprops.t

Tests the properties CommandTimeout and QueryNotification.

Notes: This script runs for almost a minute, because of a 45-second WAITFOR to test CommandTimeout. That's why it is demoted to always be next-to-last in the test suite.

Number of tests: 11 (With SQLOLEDB), 9 (SQL Native Client and SQL 2000 or earlier), 18 (With SQL Native Client and SQL 2005 or later).

Creates in SQL Server: On SQL 2005 or later when you have SQLNCLI or later, the script creates a database, OlleQN, in which it creates a broker service service and a queue. In the other test cases, no objects are created.

### Z_threads.t

Tests: Win32::SqlServer with Perl threads.

Number of tests: 24.

Creates in SQL Server: nothing.

Note: this script often hangs in Perl 5.8, due to a bug in the Perl threads package. See http://rt.perl.org/rt3//Public/Bug/Display.html?id=39839 for details. This is why it has a name to make sure it's the last in the test suite. According to the link, the issue has been fixed, but since the poster don't know why and this apparently is some race condition, I don't take that for granted. However, I have not see the script hanging with Perl 5.10 and later.

## Subdirectories to t

There are two subdirectories in the t directory:

Helpers - Here you find the source code for the four CLR types that are used in the test, and a Perl script to create them. Here is also a short program in VB6 which is used to generate and interpret date in regional settings for the DATETIME_REGIONAL setting. There is also test data for the spatial data types geometry and geography and the queries to generate that data.

Output - This directory holds output files from some of the test scripts. Most of them comes from2_datatypes.t, and 5_errors.t, but also A_tableparam.t and Z_threads.t generate output files.

Last updated 12-09-23 23:00