Easysoft ODBC-SQL Server Driver User's Guide - Configuration

Driver = value

The name of the ODBC driver to use with this data source. To connect to a SQL Server 2000 or 2005 instance over an encrypted connection, set this attribute value to Easysoft ODBC-SQL Server SSL. Otherwise, set this attribute value to Easysoft ODBC-SQL Server.

Description = value

A single line of descriptive text that may be retrieved by some applications to describe the data source.

Server = value

The SQL Server instance that you want to connect to. To connect to the default SQL Server instance, type:

machinename

where machinename is the name or IP address of the host where SQL Server is running.

Note that if you are connecting to a SQL Server 2005 instance that is listening on an IPv6 address, set the IPv6 attribute to 1.

To connect to a named instance, type:

machinename\instancename

where instancename is the SQL Server instance.

To connect to the default SQL Server Express named instance, type:

machinename\sqlexpress

Connection Failover

If your SQL Server database is available on more than one SQL Server machine, you can define a primary server for the database and additional fallback database servers. By default, the Easysoft ODBC-SQL Server Driver will try to connect to the first server that you specify. If that server is unavailable the Easysoft ODBC-SQL Server Driver will try to connect to the next server in the list and so on. Use the format:

Server =primaryserver[:port] [,fallbackserver[:port]...]

where:

• primaryserver is the name or IP address of the primary SQL Server machine on which your database is available.
• port is the TCP port on which the instance is listening. If omitted, the driver will try to connect to the instance that is listening on port 1433.
• fallbackserver is the name or IP address of an alternative SQL Server machine on which your database is available.

For example:

Server = sqlsrvhostA,sqlsrvhostB,sqlsrvhostC:1583

Connection attempts continue until either a connection is successfully made or all the servers in the list have been tried once.

Note that your SQL Server login (as specified by User and Password) needs to be valid on each SQL Server machine in the list. The SQL Server login must have permission to access the database on each SQL Server machine.

If you want to balance the load between database servers, configure the driver to randomly choose the database server it connects to. To do this, set the ClientLB attribute to 1.

Port = num

The TCP port that SQL Server is listening on.

If you are connecting to a default instance that is listening on port 1433, the Port setting can be omitted.

If the SQL Server Browser or the SQL Server 2000 listener service is running, the Easysoft ODBC-SQL Server Driver will automatically detect the port number and the Port setting can be omitted.

By default, named instances of SQL Server use dynamic ports, which means that an available port is assigned when the instance starts. If a SQL Server instance is listening on a dynamically allocated port number, you must omit the Port setting and let the Easysoft ODBC-SQL Server Driver use the browser or listener to detect the port number.

If the SQL Server Browser or listener is not running at your site, your database administrator will have configured each SQL Server instance to listen on a specific TCP port. You need to specify this port with the Port setting.

If your database administrator has hidden the SQL Server instance from the SQL Server Browser or listener, you need to specify the port number of the hidden instance.

If your database administrator has configured the SQL Server instance to listen on multiple ports, use the Port setting to specify the appropriate port number from the available alternatives.

User = value

The SQL Server login name to use when connecting to SQL Server.

If the SQL Server instance uses Windows Authentication (also known as trusted connections), the Windows user name to use to authenticate the connection. Use this format:

domain\username

where:

• domain is the name of the Windows domain that the SQL Server machine is in or one that the SQL Server machine trusts.
• username is the user name of a user who belongs to this domain.

If the SQL Server instance permits SQL Server Authentication, you can also specify a SQL Server user name.

To specify the login name in the connection string, use UID rather than User. For more information about specifying Easysoft ODBC-SQL Server Driver attributes in the connection string, see DSN-less Connections.

Password = value

The password for the login name specified by User.

To specify the login password in the connection string, use PWD rather than Password.

Database = value

The default database to use for the connection.

If you omit this attribute, the connection uses the default database defined for the login in SQL Server. The default database for users who do not have their own SQL Server login depends on the local group on the SQL Server machine that they belong to. The default database for members of the local Administrators group is the one defined for the BUILTIN\Administrators login. The default database for members of the local Users group is the one defined for the BUILTIN\Users login (SQL Server 2005 Express Edition only).

If the database does not exist or the login does not have permission to access the database, the connection will fail.

Note that using the default database for the login ID is more efficient than specifying a default database in the ODBC data source.

QuotedId = 0 | 1

When ON (set to 1), QUOTED_IDENTIFIERS is set to ON for the connection. SQL Server will then follow the SQL-92 rules regarding the use of quotation marks in SQL Statements. Double quotes can only be used for identifiers, such as column and table names. Character strings must be enclosed in single quotes:

SELECT CompanyName

FROM "Customer and Suppliers by City"

WHERE City = 'New York'

If a single quotation mark is part of the literal string, it can be represented by two single quotation marks.

When OFF, QUOTED_IDENTIFIERS is set to OFF for the connection. SQL Server then follows the legacy Transact-SQL rules regarding the use of quotation marks. Identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. Literals can be delimited by either single or double quotation marks.

For more information about the QUOTED_IDENTIFIERS option, see the SQL Server Transact-SQL documentation.

By default, QuotedId is ON.

AnsiNPW = 0 | 1

When ON (set to 1), the ANSI_NULLS, ANSI_WARNINGS, and ANSI_PADDING options are set to ON for the connection.

When ANSI_NULLS is ON, SQL Server enforces ANSI rules for handling NULL comparisons. The ANSI syntax IS NULL or IS NOT NULL must be used for all NULL comparisons. For example:

SELECT *

FROM MyTable

WHERE MyColumn IS NULL

The Transact-SQL syntax = NULL and <> NULL are not supported.

When ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators must be used to make comparisons with NULL and nonnull values in a table.

When ANSI_WARNINGS is ON, SQL Server generates warning messages for conditions that violate ANSI rules but do not violate the rules of Transact-SQL. For example, SQL Server will generate error and warning messages for divide-by-zero errors, string too large for database column errors and when NULL values are encountered when using aggregate functions. When SET ANSI_WARNINGS is OFF, these errors and warnings are not raised.

When ANSI_PADDING is ON, trailing blanks on varchar values and trailing zeroes on varbinary values are not automatically trimmed.

For more information about the ANSI_NULLS, ANSI_WARNINGS, and ANSI_PADDING options, see the SQL Server Transact-SQL documentation.

By default, AnsiNPW is ON.

Language = value

The national language to use for SQL Server system messages. Use this format:

Lanaguage =language

where language is one the language aliases contained in the sys.syslanguages table.

For example:

Lanaguage = French

If no language is specified, the connection uses the default language specified for the login on the server.

Appname = value

The name SQL Server uses to identify the application that connects using this data source. For example, the following entry identifies an application as isql:

Appname = isql

The default value is ODBC.

SQL Server stores the application name in the master.dbo.sysprocesses column program_name. The name is returned by the APP_NAME function.

MARS_Connection = 0 | 1

When ON (set to 1), multiple active result sets (MARS) are enabled on the connection if the server is SQL Server 2005. MARS allows applications to have more than one pending request per connection, and in particular, to have more than one active default result set per connection. Applications can execute other statements (for example, INSERT, UPDATE, DELETE, and stored procedure calls) while result sets are open. For example, an application might retrieve unprocessed items from an Orders table and then, while looping through the active result set, use an UPDATE statement to mark each order as processed.

For non-MARS connections (MARS_Connection turned OFF) and earlier versions of SQL Server, applications cannot maintain multiple active statements on a connection. Applications that attempt to do this fail with the error "connection is busy with results of another hstmt". The application has to process or cancel all result sets from one batch before it can execute any other batch on that connection. Note that server-side cursors can be used to work around this limitation. There is a performance penalty associated with server-side cursors however.

For more information about MARS, see the Microsoft article Multiple Active Result Sets (MARS) in SQL Server 2005.

By default, MARS_Connection is OFF.

PreserveCursor = 0 | 1

When ON (set to 1), the Easysoft ODBC-SQL Server Driver preserves cursors when SQLEndTran commits or rolls back a transaction.

By default, PreserveCursor is OFF, which means that cursors are closed when a transaction is committed or rolled back by using SQLEndTran.

This behaviour can also be configured by setting SQL_COPT_SS_PRESERVE_CURSORS with SQLSetConnectAttr. For more information and a code sample, see SQL_COPT_SS_PRESERVE_CURSORS.

Wsid = value

The workstation ID. The default value is the host name of the machine where the ODBC application is running. SQL Server stores the workstation ID in the master.dbo.sysprocesses column hostname. The ID is returned by sp_who and the HOST_NAME function.

Version7 = 0 | 1

Set to Version7 to 1 if you are connecting to a SQL Server 7.0 database.

When initiating the connection, the Easysoft ODBC-SQL Server Driver tries to discover the version of the SQL Server instance. Setting Version7 to 1 reduces the number of steps in the discovery process for SQL Server 7.0 databases. This results in a slightly quicker connection time.

By default, Version7 is OFF (set to 0).

VarMaxAsLong = 0 | 1

When OFF (set to 0), the Easysoft ODBC-SQL Server Driver returns a varchar(max) column as a SQL_VARCHAR with a zero length, which means the maximum size is unlimited. Some applications may interpret this to mean that the column size is zero bytes rather than unlimited and allocate a buffer that is too small for the column data. To work around this, try setting VarMaxAsLong to 1. When ON (set to 1), the Easysoft ODBC-SQL Server Driver returns a varchar(max) column as a SQL_LONGVARCHAR.

By default, VarMaxAsLong is OFF.

ClientLB = 0 | 1

Whether the Easysoft ODBC-SQL Server Driver tries to balance the load between the servers specified by the Server setting. The ClientLB setting only has an effect if you specify a primary server and additional fallback servers with Server.

When ClientLB is ON (set to 1), the Easysoft ODBC-SQL Server Driver randomly selects a server to connect to. If the server is unavailable, the Easysoft ODBC-SQL Server Driver then moves sequentially through the list of other servers.

When ClientLB OFF (set to 0, the default), the Easysoft ODBC-SQL Server Driver tries to connect to the servers in the order that they are defined in. (Primary server first and then each additional fallback server.)

Example

You specify a primary server (sqlsrvhostA) and two fallback servers (sqlsrvhostB and sqlsrvhostC):

Server = sqlsrvhostA,sqlsrvhostB,sqlsrvhostC:1583

When ClientLB is ON, the Easysoft ODBC-SQL Server Driver will randomly choose a server to connect to. If, for example, the driver tries to connect to sqlsrvhostB first, it will then try to connect to sqlsrvhostC (if sqlsrvhostB is unavailable) and sqlsrvhostA (if sqlsrvhostC is unavailable).

When ClientLB is OFF, the Easysoft driver will try to connect to sqlsrvhostA and then sqlsrvhostB (if sqlsrvhostA is unavailable) and finally sqlsrvhostC (if sqlsrvhostB is unavailable).

Failover_Partner = value

Use Failover_Partner to specify the current mirror database server. If the initial connection to the principal database server fails, the Easysoft ODBC-SQL Server Driver will attempt a connection to the server specified by Failover_Partner.

For more information about database mirroring, see Database Mirroring.

DisguiseGuid = 0 | 1

When ON (set to 1), the Easysoft ODBC-SQL Server Driver describes the UNIQUEIDENTIFIER data types as CHAR rather than GUID. This is a workaround for applications such as Oracle's HSODBC that do not recognise UNIQUEIDENTIFIER types and therefore fail to return data from tables containing these column types.

By default, DisguiseGuid is OFF (set to 0).

DisguiseLong = 0 | 1

When ON (set to 1), the Easysoft ODBC-SQL Server Driver describes IMAGE and TEXT data types as VARBINARY and VCHAR. This is a workaround for applications such as Oracle's HSODBC that cannot handle IMAGE and TEXT types and therefore fail to return data from tables containing these column types.

By default, DisguiseLong is OFF (set to 0).

LimitLong = num

The maximum size in bytes that the Easysoft ODBC-SQL Server Driver returns for IMAGE, NTEXT, TEXT, NVARCHAR(MAX), VARBINARY(MAX) and VARCHAR(MAX) columns. Use LimitLong to restrict the size returned by the driver when describing these data types.

By default, LimitLong is OFF (set to 0), which means that the Easysoft ODBC-SQL Server Driver returns the maximum size for NTEXT and TEXT columns. For MAX data types, LimitLong's default value means that the returned column size is SQL_SS_LENGTH_UNLIMITED (0). This value indicates that the column size is "unlimited".

ConvToUtf = 0 | 1

When ON (set to 1), the Easysoft ODBC-SQL Server Driver converts UCS-2 encoded data to UTF-8 and vice versa. This enables applications running on UTF-8 platforms to work with Unicode data stored in NCHAR, NVARCHAR and NTEXT columns.

SQL Server uses UCS-2 to encode data in NCHAR, NVARCHAR and NTEXT columns. If your application expects UTF-8 encoded data, it will be unable to process Unicode data stored in NCHAR, NVARCHAR and NTEXT columns. To work around this, add this line to your ODBC data source.

ConvToUtf = 1

By default, ConvToUtf is OFF (set to 0).

Example: Retrieving Data

We ran OpenOffice.org 2.0 on Ubuntu from a shell in which the LANG environment was set to en_GB.UTF-8. With ConvToUtf set to 0, we connected to a SQL Server data source in OOo Base and ran this SQL:

use Northwind

select CompanyName from Suppliers where SupplierID = 29

SQL Server stores data in the CompanyName column as a UCS-2 encoded NVARCHAR type.

The results for this query should be:

Forêts d'érables

Instead, we got these results:

For?ts d'?rables

The ? symbols indicate that application was unable to convert the character from the server encoding scheme to the client encoding scheme.

In OOo Writer, we used the Insert > Special Character command to insert ê and é into a new document. We did this to show that OpenOffice.org running on this system and environment was capable of rendering these two characters. We then saved the document as a Text file and ran the following command at the shell prompt:

$ file ooo_chars.txt

ooo_chars.txt: Unicode text, UTF-8

The file command's output indicates that the encoding scheme OpenOffice was using is UTF-8.

We set ConfToUTF to 1 and reconnected to the data source in Base. Running the same query returned the expected results. This is because the Easysoft ODBC-SQL Server Driver converts the UCS-2 encoded data to UTF-8, the encoding OpenOffice expects.

Example: Inserting Data

We created a SQL file named insert-northwind-shipper.sql on a Ubuntu machine:

-- Insert new record into the Northwind shippers table

USE Northwind;

INSERT INTO Shippers (CompanyName, Phone) VALUES (N'♦ Diamond Shipping', '(11) 555-2167');

SELECT * FROM Shippers;

To create the file, we used the Vi IMproved (vim) text editor from a shell in which the LANG environment was set to en_GB.UTF-8. To insert the ♦ character in vim, we typed CTRL+V u2666. (u+2666 is the Unicode code point for this character.) The N prefix before the INSERT statement value tells SQL Server that the string contains a Unicode character. To confirm that the SQL file was UTF-8 encoded, we ran the file command:

$ file insert-northwind-shipper.sql

insert-northwind-shipper.sql: Unicode text, UTF-8

In the same shell, we used insert-northwind-shipper.sqlas an input file to isql:

/usr/local/easysoft/unixODBC/bin/isql -v SQLSERVER_SAMPLE < insert-northwind-shipper.sql

The SQLSERVER_SAMPLE data source connects to a SQL Server instance that serves the Northwind database. In the data source, ConfToUTF was set to 1. The command's output confirmed that the new record had been successfully inserted and that the Ubuntu machine was capable of rendering the ♦ character:

SQL>--+---------------------+----------------------+

| ID | CompanyName | Phone |

+-----+------------------------+-------------------------+

| 1 | Speedy Express | (503) 555-9831 |

| 2 | United Package | (503) 555-3199 |

| 3 | Federal Shipping | (503) 555-9931 |

| 4 | ♦ Diamond Shipping | (11) 555-2167 |

+------+-----------------------+-------------------------+

Limitations

ConvToUTF has no effect on values bound to statement parameters (SQLBindParameter).

ConvToUTF has no effect on column names.

DisguiseWide = 0 | 1

When ON (set to 1), the Easysoft ODBC-SQL Server Driver describes NVARCHAR and NCHAR data types as VARCHAR and CHAR. This is a workaround for applications such as Oracle's HSODBC that do not recognise NVARCHAR and NCHAR types and therefore fail to return data from tables containing these column types.

By default, DisguiseWide is OFF (set to 0).

Trusted_Connection = 0 | 1

Whether to use Windows or SQL Server authentication to validate the connection.

Trusted_Domain = value

The Windows domain that the user specified with User belongs to.

If the user belongs to the same domain as the one that the SQL Server machine is in, you can omit Trusted_Domain. The Easysoft ODBC-SQL Server Driver automatically detects the domain in this case.

If you specify a Domain with Trusted_Domain, set Trusted_Connection to 1 and omit the domain from User. For example:

# Windows authentication

User = mylocalmachineuser

Password = mypassword

Trusted_Connection = 1

Trusted_Domain = mymachinename

NTLMv2 = 0 | 1

If the server your SQL Server machine validates Windows logins with uses NTLMv2 authentication, set NTLMv2 to 1. Otherwise, leave NTLMv2 set to its default value 0 (OFF).

IPv6 = 0 | 1

Set IPv6 to 1 when connecting to a SQL Server 2005 instance that is listening on an IPv6 address.

By default, IPv6 is OFF (set to 0), which means that the Easysoft ODBC-SQL Server Driver assumes that the target SQL Server instance is listening on an IPv4 address.

For more information about IPv6, see Connecting to SQL Server 2005 by Using IPv6.

ConnectionTimeout = num

The number of milliseconds to wait for any request on the connection to complete before returning to the application. After the initial connection to the SQL Server machine has been established, the Easysoft ODBC-SQL Server Driver will wait num milliseconds each time it needs a response from SQL Server. If no response is received from SQL Server before the timeout expires, the Easysoft ODBC-SQL Server Driver returns the error Timeout expired.

The default value 0 means that no connection timeout is applied by the Easysoft ODBC-SQL Server Driver.

A timeout set by calling SQLSetConnectAttr with the SQL_ATTR_CONNECTION_TIMEOUT connection attribute will override ConnectionTimeout.

LogonTimeout = num

The number of milliseconds to wait for a TCP connection to the SQL Server machine to be established before returning to the application. When you define a timeout, the initial connection phase lasts for num milliseconds. If the Easysoft ODBC-SQL Server Driver is unable to connect to the target SQL Server machine before the timeout expires, it returns the message Connection timeout expired. Note that if you specify a named instance in the Server attribute value, the driver returns a different timeout message: Failed to get datagram from socket.

The default value 0 means that no initial connection timeout is applied by the Easysoft ODBC-SQL Server Driver.

The Easysoft ODBC-SQL Server Driver classes the connection phase as obtaining the IP address of the SQL Server machine and connecting to it. This means that if you specify the Server attribute value as a machine name rather than an IP address, your system resolver library will be used (possibly examining /etc/hosts or doing a DNS query). On some operating systems, gethostbyname(), the call used to resolve a machine name into an IP address, cannot be interrupted and the connection timeout will not work. If this is a problem for you, either specify the SQL Server machine as an IP address or tell your resolver library to consult /etc/hosts before DNS and place an entry in /etc/hosts.

A timeout set by calling SQLSetConnectAttr with the SQL_ATTR_LOGIN_TIMEOUT connection attribute will override LogonTimeout.

SHLIB_PATH

Some versions of HP-UX. For example:

SHLIB_PATH=$SHLIB_PATH:/usr/local/easysoft/lib

export SHLIB_PATH

LD_LIBRARY_PATH

Linux, Solaris, some versions of HP-UX. For example:

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/easysoft/lib

export LD_LIBRARY_PATH

LIBPATH

AIX. For example:

LIBPATH=$LIBPATH:/usr/local/easysoft/lib

export LIBPATH