DataDirect Connect ODBC for SQL Server

Copyright 1998 INTERSOLV Inc. All rights reserved. INTERSOLV and DataDirect are registered trademarks of INTERSOLV, Inc. Connect ODBC is a trademark of INTERSOLV, Inc. Other company or product names mentioned herein may be trademarks or registered trademarks of their respective companies.


About Connect ODBC for SQL Server

Connect ODBC for SQL Server (the “SQL Server driver”) supports the SQL Server 6.x database systems available from Microsoft, Inc.

The driver file name is IVSS6nn.DLL, where nn is the revision level.


SQL Server System Requirements
System Requirements

To use the SQL Server driver, you must have the appropriate Microsoft SQL Server DB-Library and Net-Library version installed (version 6.0 for access to 6.0 DBMS; version 6.5 for access to 6.5 DBMS).

Note: DataDirect Connect ODBC for SQL Server for Windows NT and Windows 9x requires Microsoft client software; it does not work with Sybase System 10 or 11 software.

Your database must support catalog stored procedures.

The DB-Library for Windows NT or Windows 9x is NTWDBLIB.DLL. The Net-Library you need depends on the network protocol used to connect to the SQL Server. For example, Named Pipes requires DBNMPNTW.DLL, and TCP/IP requires DBMSSOCN.DLL. Contact your Microsoft SQL Server vendor to obtain the appropriate DB-Library and Net-Library.

If you attempt to configure a data source and you do not have NTWDBLIB.DLL on your path or in your Windows NT \SYSTEM32 or Windows 9x \SYSTEM directory, the following message appears:

The setup routines for the INTERSOLV 3.00 32-BIT SQL Server6 ODBC driver could not be loaded due to system error code 126.

When you click OK, the following message appears:

Could not load the setup or translator library.


Configuring SQL Server Data Sources
Configuring Data Sources

To configure a SQL Server data source, do the following:

1	Start the ODBC Administrator to display a list of data sources.

2	If you are configuring an existing data source, select the data source name and click Configure to display the ODBC SQL Server Driver Setup dialog box.

If you are configuring a new data source, click Add to display a list of installed drivers. Select Microsoft SQLServer6 and click Finish to display the ODBC SQL Server Driver Setup dialog box.

3	Specify a data source name, a server name, a database name and optionally, a description. Click Apply.

4	Click the Advanced tab  to configure optional data source settings, such as server list and database list. Click Apply.

5	Click Translate to display the Select Translator dialog box, where you can select a translator to translate your data from one character set to another. INTERSOLV provides a translator named INTERSOLV OEM ANSI that translates your data from the IBM PC character set to the ANSI character set. Click OK to close this dialog box and perform the translation.

The translators that are listed in this dialog box are determined by the values listed in the ODBC Translators section of the system information.

6	Click OK or Cancel. If you click OK, the values you have specified become the deraults when you connect to the data source. You can change these defaults by using this procedure to reconfigure your data source. You can override these defaults by connecting to the data source using a connection string with alternate values.


Connecting to SQL Server Using a Logon Dialog Box
Connecting to a Data Source Using a Logon Dialog Box

Some ODBC applications display a Logon dialog box when you are connecting to a data source. In these cases, the data source name has already been specified.

In the Logon dialog box, do the following:

1	Type the name of the server containing the SQL Server database tables you want to access (case-sensitive), or select the name from the Server Name drop-down list box, which displays the server names you specified in the ODBC SQL Server Driver Setup dialog box.

2	If required, type your case-sensitive login ID.

3	If required, type your case-sensitive password for the system.

4	Optionally, click Options to display the SQL Server Logon Options dialog box and specify the initial SQL Server database to connect to and the name of your workstation.

5	Click OK to log on to the SQL Server database installed on the server you specified and to update the values in the system information.


Connecting to SQL Server Using a Connection String
Connecting to a Data Source Using a Connection String

If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in system information. These values are not written to the system information.

You can specify either long or short names in the connection string.  The connection string has the form:

DSN=data_source_name[;attribute=value[;attribute=value]...]

An example of a connection string for SQL Server is:

DSN=Accounting;DB=PAYROLL;UID=JOHN;PWD=XYZZY

The paragraphs that follow give the long and short names for each attribute, as well as a description. The defaults listed are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information. If you specified a value for the attribute when configuring the data source, that value is your default.

ApplicationName (APP): The name SQL Server uses to identify your application.

ApplicationUsingThreads (AUT): ApplicationUsingThreads={0 | 1}. Ensures that the driver works with multi-threaded applications. The default is 1, which makes the driver thread-safe. When using the driver with single-threaded applications, you may set this option to 0 to avoid additional processing required for ODBC thread safety standards.

Cancel (CAN): Cancel={0 | 1 | 2}. A value that specifies how a previously executed statement should be canceled. Valid values are:

Cancel=0 fetches all remaining records if the statement was a Select.

Cancel=1 cancels the statement by calling dbcancel.  Set Cancel=1 if dbcancel is supported in your client/server configuration.  This is the initial default.

Cancel=2 closes the connection to the server for the statement. Set Cancel=2 only if dbcancel is not supported for your configuration and the performance of fetching all remaining records is unacceptable.

CharConv (CC): A value that controls the character set conversion between SQL Server and a client application. Common values include iso_1 for ISO-8859-1, cp850 for Code Page 850, roman8 for the Roman8 character set, and SJIS for a Japanese character set. See your SQL Server documentation for a complete list of values.

CursorCacheSize (CCS): The number of cursors the cursor cache can hold. The driver creates a cache of statements; each statement represents an open connection to SQL Server. The cursor cache increases performance but uses database resources. The initial default is 1.

Database (DB): The name of the database to which you want to connect.

DataSourceName (DSN): A string that identifies a SQL Server data source configuration in the system information. Examples include "Accounting" or "SQL Server-Serv1."

EnableQuotedIdentifiers (EQI): EnableQuotedIdentifiers={0 | 1}. Enables quoted identifiers; that is, identifiers in SQL Server can be quoted using a quoting character. The default is 0.

EnableScrollableCursors (ESC): EnableScrollableCursors={0 | 1}. Provides access to scrollable cursors. If ESC=1, the driver will support Keyset_Driven cursors and will support SQLSetPos with Update and Delete functionality. The default is 0.

Scrollable cursors require the following attributes:

A unique index must be available on all Selected tables.

The Select statement cannot contain any of the following: Into, For Browse, Compute, Union, Compute By, Aggregate functions, Table aliases.

If the Select statement includes a view, the From clause must include only a single view (no other tables or views).

The select list must include all unique index columns of the base tables.

InitializationString (IS): The value of this option is a string containing one or more SQL Server commands that you want to run when the data source connection is initialized. Multiple commands must be separated by a semicolon (;).

Language (LANG): The national language to be used by the client. The initial default is English.

LogonID (UID): The case-sensitive logon ID used to connect to your SQL Server database. A logon ID is required only if security is enabled on your database. If so, contact your system administrator to get your logon ID.

ModifySQL (MS): ModifySQL={0 | 1}. This attribute is provided for backward compatibility. It determines whether the driver modifies SQL statements to conform to ODBC specifications or passes the SQL statement directly to SQL Server. Specify ModifySQL=1 to have the driver modify the SQL statement to conform to ODBC specifications. Specify ModifySQL=0 to have the driver understand SQL dialects found in earlier drivers. The default is 1.

PacketSize (PS): PacketSize={-1 | 0 | x}. A number that determines the number of bytes per network packet transferred from the database server to the client. The correct setting of this attribute can improve performance.

When set to 0, the initial default, the driver uses the default packet size as specified in the server configuration.

When set to -1, the driver computes the maximum allowable packet size on the first connect to the data source and saves the value in the system information.

When set to x, an integer from 1 to 10, which indicates a multiple of 512 bytes (for example, PacketSize=6 means to set the packet size to 6 * 512 = 3072 bytes).

Note that the ODBC specification specifies a connect option, SQL_PACKET_SIZE, that offers this same functionality. To avoid conflicts with applications that may set both the connection string attribute and the ODBC connect option, the ODBC connect option will take precedence.

Password (PWD): A case-sensitive password.

ServerName (SRVR): The name of the server containing the SQL Server tables you want to access.

TwoPhaseCommit (TPC): TwoPhaseCommit={0 | 1}. This attribute lets you have two or more active statements within a transaction, using the SQL Server two-phase commit services. Set TwoPhaseCommit=1 to use two-phase commit. The active statements may deadlock if they reference the same SQL Server table. Otherwise, set TwoPhaseCommit=0 (the initial default).

UseNTAuthentication (UNA): UseNTAuthentication={0 | 1}. Enables Windows NT security. When enabled (1), the Default Logon ID field is inactive because Windows NT security passes the logon ID and password.  Not enabled (0) is the default.

WorkstationID (WKID): The workstation ID used by the client.


SQL Server Data Types
Data Types

The SQL Server data types are mapped to the standard ODBC data types as follows:

SQL Server	ODBC Data Type
binary	SQL_BINARY
bit	SQL_BIT
char	SQL_CHAR
datetime	SQL_TYPE_TIMESTAMP
decimal	SQL_DECIMAL
decimal() identity	SQL_DECIMAL
float	SQL_FLOAT
image	SQL_LONGVARBINARY
int	SQL_INTEGER
int identity	SQL_INTEGER
money	SQL_DECIMAL
numeric	SQL_NUMERIC
numeric() identity	SQL_NUMERIC
real	SQL_REAL
smalldatetime	SQL_TYPE_TIMESTAMP
smallint	SQL_SMALLINT
smallint identity	SQL_SMALLINT
smallmoney	SQL_DECIMAL
sysname	SQL_VARCHAR
text	SQL_LONGVARCHAR
timestamp	SQL_VARBINARY
tinyint	SQL_TINYINT
tinyinit identity	SQL_TINYINT
varbinary	SQL_VARBINARY
varchar	SQL_VARCHAR


Isolation and Lock Levels Supported by SQL Server
Isolation and Lock Levels Supported

SQL Server supports isolation levels 1 (read committed) and 3 (serializable). SQL Server supports page-level locking.


SQL Server ODBC Conformance Level
ODBC Conformance Level

The API functions supported are listed in Supported ODBC Functions, found in the General Help on DataDirect ODBC Drivers

In addition, the following level 2 functions are supported:

SQLColumnPrivileges

SQLForeignKeys

SQLPrimaryKeys

SQLProcedureColumns

SQLProcedures

SQLTablePrivileges

If EnableScrollableCursors=1, then scrollable cursors are supported with SQLExtendedFetch and SQLFetchScroll, and SQLSetPos is supported.

The driver supports the minimum SQL grammar.


Number of Connections and Statements Supported by SQL Server
Number of Connections and Statements Supported

The SQL Server database system supports multiple connections. With two-phased commit, SQL Server supports multiple statements per connection. Otherwise, SQL Server supports a single statement per connection if SQL_AUTOCOMMIT is 0, and multiple statements per connection if SQL_AUTOCOMMIT is 1.


SQL Server ODBC Setup
General Tab, ODBC SQL Server Driver Setup Dialog Box

Use the ODBC SQL Server Driver Setup dialog box to create new SQL Server data sources or configure existing data sources.

Data Source Name: A string that identifies this SQL Server data source configuration in the system information. Examples include "Accounting" or "SQL Server-Serv1."

Description: An optional long description of a data source name. For example, "My Accounting Database" or "SQL Server on Server number 1."

Server Name: The name of the server that contains the desired database.

Database Name: The name of the database to which you want to connect by default. If you do not specify a value, the default database defined by SQL Server is used.

Advanced Tab

Displays the Advanced tab on the ODBC SQL Server Driver Setup dialog box, where you can configure optional data source settings, such as server list and database list.

OK

Cancel

Apply


Advanced Options for Configuring Data Sources
Advanced Tab, ODBC SQL Server Driver Setup Dialog Box

Use the Advanced tab on the ODBC SQL Server Driver Setup dialog box to specify optional settings when you create new SQL Server data sources or configure existing data sources.

Server List: A comma-separated list of servers that will appear in the Logon dialog box.

Database List: The databases that will be available in the SQL Server Logon Options dialog box. Separate the names with commas.

Default Logon ID: The default logon ID used to connect to your SQL Server database. This ID is case-sensitive. A logon ID is required only if security is enabled on your database. Your ODBC application may override this value, or you may override this value in the Logon dialog box or connection string.

Language: The national language to be used by the client. The default is English.

Application Name: The name SQL Server uses to identify your application.

Workstation ID: The workstation ID used by the client.

Cursor Cache Size: The number of cursors the cursor cache can hold. The driver creates a cache of statements; each statement represents an open connection to SQL Server. The cursor cache increases performance but uses database resources. The default is 1 (one cursor).

Cancel Behavior: A value that specifies how a previously executed statement should be canceled. Valid values are:

0 fetches all of the remaining records if the statement was a Select.

1 cancels the statement by calling Dbcancel. This is the default and should be used if Dbcancel is supported in your client/server configuration.

2 closes the connection to the server for the statement. Use this value only if dbcancel is not supported for your configuration and the performance of fetching all remaining records is unacceptable.

Character Conversion: This value controls the character set conversion between SQL Server and a client application. If you omit this value, no character conversion takes place on your server.

Common values include iso_1 for ISO-8859-1, cp850 for Code Page 850, roman8 for Roman8 character set, and SJIS for a Japanese character set. See your SQL Server documentation for a complete list of values.

Initialization String: The value of this option is a string containing one or more SQL Server commands that you want to run when the data source connection is initialized. Multiple commands must be separated by a semicolon (;).

Packet Size: A value that determines the number of bytes per network packet transferred from the database server to the client. The correct setting of this attribute can improve performance.

When set to 0, the initial default, the driver uses the default packet size as specified in the server configuration.

When set to -1, the driver computes the maximum allowable packet size on the first connect to the data source and saves the value in the system information.

When set to x, an integer from 1 to 10, which indicates a multiple of 512 bytes (for example, Packet Size=6 means to set the packet size to 6 * 512 = 3072 bytes).

Note that the ODBC specification specifies a connect option, SQL_PACKET_SIZE, that offers this same functionality. To avoid conflicts with applications that may set both the connection string attribute and the ODBC connect option, the ODBC connect option will take precedence.

Two-Phase Commit: This check box, when selected, enables you to have two active statements within a transaction, using the SQL Server two-phase commit services. The active statements may deadlock if they reference the same SQL Server table.

Application Using Threads: A setting that ensures that the driver works with multi-threaded applications. You can clear this check box when using the driver with single-threaded applications. Turning off this setting avoids additional processing required for ODBC thread safety standards.

Enable Scrollable Cursors: A setting that provides access to scrollable cursors. If selected, the driver will support Keyset_Driven cursors and will support SQLSetPos with Update and Delete functionality. The default is not selected. Scrollable cursors require the following attributes:

A unique index must be available on all Selected tables.

The Select statement cannot contain any of the following: Into, For Browse, Compute, Union, Compute By, Aggregate functions, Table aliases.

If the Select statement includes a view, the From clause must include only a single view (no other tables or views).

The select list must include all unique index columns of the base tables.

Enable Quoted Identifiers: Enables quoted identifiers; that is, identifiers in SQL Server can be quoted using a quoting character. The default is not selected.

Use NT Authentication: Enables Windows NT security. When enabled, the Default Logon ID field is inactive because Windows NT security passes the logon ID and password.  Activating this checkbox also activates the corresponding checkbox on the Logon Dialog.

Translate Button

Displays the Select Translator, where you can translate your data from one character set to another. Choose the INTERSOLV OEM ANSI translator to translate your data from the IBM PC character set to the ANSI character set.

OK

Cancel

Apply


Logon to SQL Server
Logon to SQL Server Dialog Box

Server Name: Type the name of the server containing the SQL Server database tables you want to access (case-sensitive) or select the name from Server Name drop-down list box, which displays the server names you specified in the ODBC SQLServer Setup dialog box.

Login ID: If required, type your Login ID (case-sensitive).

Password: If required, type your password for the system (case-sensitive).

Use NT Authentication: Enables Windows NT security. When enabled, the Login ID and Password fields are inactive because Windows NT security passes the login ID and password.  Activating this checkbox also activates the corresponding checkbox on the Advanced Tab.

Options Button

Displays the SQL Server Logon Options  dialog box, where you can specify a database name and workstation ID.


SQL Server Logon Options
SQL Server Logon Options Dialog Box

Database Name: Type the name of the initial SQL Server database to connect to, or select the name from the drop-down list box. If not supplied, the default database defined by SQL Server is used.

Workstation ID: Type the name of your workstation.


Apply Button

Writes the settings you have specified to the system information. These settings remain in effect until you change them in this dialog box. Clicking Cancel does not affect settings that have been applied.


OK Button

Writes the settings you have specified to the system information and closes the dialog box.


Cancel Button

Closes the dialog box without saving settings that have not been applied.