Easysoft ODBC-SQL Server Driver User's Guide - Technical Reference

Technical Reference for the Easysoft ODBC-SQL Server Driver

This section contains extra information relating to the deployment of the Easysoft ODBC-SQL Server Driver.

Appendix Guide

ODBC Conformance

The Easysoft ODBC-SQL Server Driver complies with the ODBC 3.81 specification.

The Easysoft ODBC-SQL Server Driver is Level 2 compliant.

ODBC API Support

All ODBC 3.81 calls are supported.

Cursor Support

The Easysoft ODBC-SQL Server Driver supports FORWARD_ONLY, KEYSET_DRIVEN, DYNAMIC and STATIC cursors.

Supported Data Types

The Easysoft ODBC-SQL Server Driver supports the following SQL Server data types:

The SQLGetTypeInfo Function

SQL Server treats identity as an attribute, whereas ODBC treats it as a data type. To resolve this mismatch, SQLGetTypeInfo returns the data types: int identity, smallint identity, tinyint identity, decimal() identity, and numeric() identity. The SQLGetTypeInfo result set column AUTO_UNIQUE_VALUE reports the value TRUE for these data types.

For varchar, nvarchar and varbinary data types, the Easysoft ODBC-SQL Server Driver continues to report 8000, 4000 and 8000 for the COLUMN_SIZE value, even though it is actually unlimited. This is to ensure backward compatibility.

For the xml data type, the Easysoft ODBC-SQL Server Driver reports SQL_SS_LENGTH_UNLIMITED for COLUMN_SIZE to denote unlimited size.

The SQLSetConnectAttr Function

The Easysoft ODBC-SQL Server Driver supports a number of driver-specific ODBC connection attributes. These are defined in /usr/local/easysoft/sqlserver/include/sqlncli.h. The Easysoft ODBC-SQL Server Driver may require that the attribute be set prior to connection, or it may ignore the attribute if it is already set:

Attribute Set before or after connection to server

SQL_COPT_SS_INTEGRATED_SECURITY

Before

SQL_COPT_SS_PRESERVE_CURSORS

Before

SQL_COPT_SS_TXN_ISOLATION

Either

SQL_COPT_SS_INTEGRATED_SECURITY

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

Value Description

SQL_IS_OFF

Default. Use SQL Server Authentication to authenticate the connection.

SQL_IS_ON

Use Windows Authentication to authenticate the connection.

Windows authentication examples:

#include <stdio.h>

#include <sql.h>

#include <sqlext.h>

#include <sqlncli.h>

.

.

.

/* Use Windows Authentication to validate the connection */

SQLSetConnectAttr(dbc, SQL_COPT_SS_INTEGRATED_SECURITY,

(void *) SQL_IS_ON, 0);

/* Specify a Windows user name and password. mywindowsuser belongs to the */

/* same domain as the SQL Server machine, so there is no need to specify it */

/* - the Easysoft ODBC-SQL Server Driver will automatically detect the domain */

SQLDriverConnect(dbc, NULL, "DRIVER={Easysoft ODBC-SQL Server};SERVER=myserver\\SQLEXPRESS;UID=mywindowsuser;PWD=mywindowspassword",

SQL_NTS, outstr, sizeof(outstr), &outstrlen,

SQL_DRIVER_COMPLETE);

¯ OR ¯

SQLSetConnectAttr(dbc, SQL_COPT_SS_INTEGRATED_SECURITY,

(void *) SQL_IS_ON, 0);

SQLDriverConnect(dbc, NULL, "DSN=MYDSN",

SQL_NTS, outstr, sizeof(outstr), &outstrlen,

SQL_DRIVER_COMPLETE);

 The Easysoft ODBC-SQL Server Driver data source specified in the SQLDriverConnect call needs to connect with a Windows user name and password. For example:

 [MYDSN]

 Driver = Easysoft ODBC-SQL Server Driver

 Server = myserver\SQLEXPRESS

 User = mywindowsuser

 Password = mywindowspassword

SQL_COPT_SS_PRESERVE_CURSORS

Whether the Easysoft ODBC-SQL Server Driver preserves cursors when SQLEndTran commits or rolls back a transaction.

(You can also configure this behaviour by using the PreserveCursor data source attribute. For more information, see Attribute Fields.)

Value Description

SQL_PC_OFF

Default. Cursors are closed when a transaction is committed or rolled back by using SQLEndTran.

SQL_PC_ON

Cursors are preserved when a transaction is committed or rolled back by using SQLEndTran.

This C code sample uses SQL_COPT_SS_PRESERVE_CURSORS to preserve a cursor following a positioned update:

#include <stdio.h>

#include <sql.h>

#include <sqlext.h>

#include <sqlncli.h>

main() {

SQLHENV env;

SQLHDBC dbc;

SQLHSTMT stmt_select, stmt_update;

SQLRETURN ret;

SQLCHAR last_name[ 64 ], first_name[ 64 ], cursor_name[ 64 ], update_sql[ 64 ];

SQLSMALLINT reports_to, cursor_len;

SQLLEN indicator[ 3 ];

/* Allocate an environment handle */

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);

/* We want ODBC 3 support */

SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION,

(void *) SQL_OV_ODBC3, 0);

/* Allocate a connection handle */

SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

/* Enable manual-commit mode */

SQLSetConnectAttr(dbc, SQL_ATTR_AUTOCOMMIT,

SQL_AUTOCOMMIT_OFF, 0);

/* Preserve cursors when transactions are committed/rolled */

/* back. Alternatively, add PreserveCursor = Yes to the DSN */

SQLSetConnectAttr(dbc, SQL_COPT_SS_PRESERVE_CURSORS,

(void *) SQL_PC_ON, 0);

/* Connect to Northwind through the sample DSN */

SQLDriverConnect(dbc, NULL,

"DSN=SQLSERVER_SAMPLE;Database=Northwind",

SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);

/* Allocate the statement handles */

SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt_select);

SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt_update);

/* Create dynamic, updateable cursor for the positioned update */

SQLSetStmtAttr(stmt_select, SQL_ATTR_CURSOR_TYPE,

(void *) SQL_CURSOR_DYNAMIC, 0);

SQLSetStmtAttr(stmt_select, SQL_ATTR_CONCURRENCY,

(void *) SQL_CONCUR_ROWVER, 0);

SQLExecDirect(stmt_select,

"SELECT LastName, FirstName, ReportsTo FROM Employees FOR UPDATE",

SQL_NTS);

SQLBindCol(stmt_select, 1, SQL_C_CHAR, last_name,

sizeof(last_name), &indicator[ 0 ]);

SQLBindCol(stmt_select, 2, SQL_C_CHAR, first_name,

sizeof(first_name), &indicator[ 1 ]);

SQLBindCol(stmt_select, 3, SQL_INTEGER, &reports_to,

0, &indicator[ 2 ]);

/* Get the cursor name for use in the update statement */

SQLGetCursorName(stmt_select, cursor_name,

sizeof(cursor_name), &cursor_len);

/* Move through the result set until the cursor is positioned */

/* on the row for Robert King */

do

ret = SQLFetch(stmt_select);

while ((ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) &&

(strcmp(first_name, "Robert") != 0 && strcmp(last_name, "King") != 0));

/* Positioned update of Robert King's line manager */

sprintf(update_sql,

"UPDATE Employees SET ReportsTo = 2 WHERE CURRENT OF %s",

cursor_name);

SQLExecDirect(stmt_update, update_sql, SQL_NTS);

/* Commit the transaction */

SQLEndTran(SQL_HANDLE_DBC, dbc, SQL_COMMIT);

/* The cursor is still open, because SQL_COPT_SS_PRESERVE_CURSORS is set to */

/* SQL_PC_ON. Reposition the cursor and fetch the updated record. */

SQLFetchScroll(stmt_select, SQL_FETCH_PRIOR, 0 );

SQLFetch(stmt_select);

/* Display updated record */

printf("%s %s reports to employee ID: %ld\n", first_name,

last_name, reports_to);

SQLCloseCursor(stmt_update); /* Close cursor */

SQLDisconnect(dbc); /* Disconnect from driver */

SQLFreeHandle(SQL_HANDLE_DBC, dbc);

SQLFreeHandle(SQL_HANDLE_ENV, env);

}

SQL_COPT_SS_TXN_ISOLATION

Sets the SQL Server 2005 or later snapshot isolation attribute.

Value Description

SQL_TXN_SS_SNAPSHOT

Indicates that from one transaction you cannot see changes made in other transactions and that you cannot see changes even when requerying.

.

For more information about snapshot isolation, see Snapshot Isolation.

The SQLSetStmtAttr Function

The Easysoft ODBC-SQL Server Driver supports the following driver-specific statement attributes:

SQL_SOPT_SS_DEFER_PREPARE

Whether the Easysoft ODBC-SQL Server Driver defers query preparation until execution time.

The attribute is only relevant to SQLServer 2000 and later.

Value Description

SQL_DP_ON

Default. After calling SQLPrepare, the Easysoft ODBC-SQL Server Driver defers statement preparation until SQLExecute or SQLDescribeCol is executed.

Any errors in the statement are not known until these functions are executed.

SQL_DP_OFF

The Easysoft ODBC-SQL Server Driver prepares the statement as soon as SQLPrepare is executed.

Any errors in the statement will cause the prepare to fail.

In this C code sample, deferred statement preparation is disabled. The invalid SQL statement the sample contains therefore fails as soon as SQLPrepare is called.

#include <stdio.h>

#include <string.h>

#include <sql.h>

#include <sqlext.h>

#include <sqlncli.h>

main() {

SQLHENV env;

SQLHDBC dbc;

SQLHSTMT stmt;

SQLRETURN ret;

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);

SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION,

(void *) SQL_OV_ODBC3, 0);

SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

SQLDriverConnect(dbc, NULL,

"DSN=SQLSERVER_SAMPLE",

SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);

/* Allocate the statement handle */

SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

/* Do not defer query preparation. Prepare the statement */

/* as soon as SQLPrepare is executed */

SQLSetStmtAttr(stmt, SQL_SOPT_SS_DEFER_PREPARE,

(SQLPOINTER) SQL_DP_OFF, 0);

/* Invalid statement */

ret = SQLPrepare(stmt, "select * from non_existent_table",

SQL_NTS);

if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {

ret = SQLExecute(stmt);

if (ret != SQL_SUCCESS || ret != SQL_SUCCESS_WITH_INFO) {

/* Because the invalid statement was prepared immediately, */

/* SQLPrepare (below) rather than SQLExecute returns the error. */

extract_error("SQLExecute", stmt, SQL_HANDLE_STMT);

}

} else {

/* The statement is invalid and so cannot be prepared. */

/* See "ODBC from C Tutorial Part 1", on the Easysoft web */

/* site for a definition of extract_error(). */

extract_error("SQLPrepare", stmt, SQL_HANDLE_STMT);

}

}

Note that if the statement contains parameters, SQLPrepare returns SQL_SUCCESS even if the statement is invalid. Any errors in the statement are not known until the statement is executed or SQLDescribeParam is called. This behaviour happens regardless of how SQL_SOPT_SS_DEFER_PREPARE is set.

For example, in the following code extract, SQLPrepare succeeds even though the parameterised statement is invalid:

/* Do not defer query preparation. */

SQLSetStmtAttr(stmt, SQL_SOPT_SS_DEFER_PREPARE,

(SQLPOINTER) SQL_DP_OFF, 0);

/* This statement is invalid. The parameter marker for the */

/* status column is missing. However, SQLPrepare still succeeds */

ret = SQLPrepare(stmt, "INSERT INTO Orders (OrderId, CustId, OpenDate, SalesPerson, Status) VALUES (?, ?, ?, ?)", SQL_NTS);

if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {

SQLBindParameter();

.

.

.

/* The errors in the statement are not known until this point */

SQLExecute(stmt);

}

Unicode Support

The Easysoft ODBC-SQL Server Driver is a Unicode driver that supports the Unicode version (with suffix "W") of the ODBC calls it implements. Using a Unicode driver with a Unicode application removes the need for the driver manager to map Unicode functions and data types to ANSI. This results in better performance and removes the restrictions inherent in the Unicode to ANSI mappings.

The Easysoft ODBC-SQL Server Driver supports the following SQL Server Unicode data types:

ANSI-Only Version of the Easysoft ODBC-SQL Server Driver

The Easysoft ODBC-SQL Server Driver distribution includes an ANSI-only version of the driver that does not support the Unicode ODBC APIs. This version of the driver should not normally be needed and is only provided for use with old and non-conformant Driver Managers.

If you do need to use the ANSI-only driver, first install the driver under unixODBC. To do this, open /etc/odbcinst.ini in a text editor. Copy the section for the standard driver and paste it below the existing section. Change the [driver name] in the new section. In the Driver entry, suffix the library name with _a . For example:

[Easysoft ODBC-SQL Server]

Driver = /usr/local/easysoft/sqlserver/lib/libessqlsrv.so

Setup = /usr/local/easysoft/sqlserver/lib/libessqlsrvS.so

Threading = 0

FileUsage = 1

DontDLClose = 1

UsageCount = 1

# Install the ANSI driver by adding a new odbcinst.ini section.

# This example odbcinst.ini extract is from a Linux installation

# of the Easysoft ODBC-SQL Server Driver.

[Easysoft ODBC-SQL Server ANSI APIs]

Driver = /usr/local/easysoft/sqlserver/lib/libessqlsrv_a.so

Setup = /usr/local/easysoft/sqlserver/lib/libessqlsrvS.so

Threading = 0

FileUsage = 1

DontDLClose = 1

UsageCount = 1

In your data source, specify the new driver name in the Driver entry. For example:

[SQLSERVER_SAMPLE]

Driver = Easysoft ODBC-SQL Server ANSI APIs

.

.

.

The xml Data Type

SQL Server 2005 introduced an xml data type for storing XML documents in table columns or Transact-SQL variables.

The Easysoft ODBC-SQL Server Driver supports the xml data type and its associated methods: query(), value(), exist(), modify() and nodes().

The query() method lets you use an XML Query (XQuery) definition to search XML data stored in columns and variables of the xml type. The XQuery language is a World Wide Web Consortium (W3C) standard for retrieving or defining a set of XML nodes that meet a set of criteria.

In the following example, an XQuery is specified against the Instructions column in the ProductModel table. The Instructions column data type is xml and therefore exposes the query() method. The ProductModel table is contained in the SQL Server sample database AdventureWorks.

SELECT Instructions.query('declare namespace AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";

/AWMI:root/AWMI:Location[@LocationID=10]

') as Result

FROM Production.ProductModel

WHERE ProductModelID=7

The XQuery includes a namespace declaration, declare namespace AWMI=..., and a query expression, /AWMI:root/AWMI:Location[@LocationID=10]. The namespace declaration identifies the XML namespace associated with elements in the Instructions column. The query expression retrieves only those records for which the LocationID attribute value is 10:

<AWMI:Location xmlns:AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" LaborHours="2.5"...LocationID="10">

...

This second example uses the query() method to construct an XML element named <Product>. The <Product> element has a ProductModelID attribute, in which the ProductModelID attribute value is retrieved from the database.

SELECT CatalogDescription.query(' declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; <Product ProductModelID="{ /PD:ProductDescription[1]/@ProductModelID }" /> ') as Result

FROM Production.ProductModel

The exist() method lets you filter XML data. For example, add the following WHERE clause to the previous query to find only records that contain a <Warranty> element.

where CatalogDescription.exist(' declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; /PD:ProductDescription/PD:Features/wm:Warranty ') = 1


Note

When querying or updating xml columns or variables with the xml data type methods, the data source attributes AnsiNPW and QuotedId must be set to Yes (the default value for these settings). Otherwise, queries and modifications will fail for xml data types.


Using Large-Value Data Types

SQL Server 2005 introduced the max specifier, which expands the storage capabilities of the varchar, nvarchar, and varbinary data types to allow storage of values as large as 2 gigabytes (GB). varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types.

The Easysoft ODBC-SQL Server Driver exposes the varchar(max), varbinary(max) and nvarchar(max) types as SQL_VARCHAR, SQL_VARBINARY, and SQL_WVARCHAR in ODBC API functions that accept or return ODBC SQL data types.

When reporting the maximum size of a column, the Easysoft ODBC-SQL Server Driver will report either:

¯ OR ¯

Snapshot Isolation

SQL Server 2005 introduced a new transaction isolation level: snapshot. A snapshot transaction does not block updates executed by another transaction and can continue to read (but not update) the version of the data that existed when it started. Snapshot isolation is also called row versioning because SQL Server keeps "versions" of rows that are being changed: the original version and the version being changed.

Snapshot isolation is enabled for a database when the ALLOW_SNAPSHOT_ISOLATION database option is set to ON. For example, to enable snapshot isolation for the pubs sample database:

ALTER DATABASE pubs SET ALLOW_SNAPSHOT_ISOLATION ON

By default, this database option is set to OFF.

The Easysoft ODBC-SQL Server Driver supports snapshot isolation through the SQLSetConnectAttr and SQLGetInfo ODBC API functions.

For snapshot transactions, ODBC applications need to call SQLSetConnectAttr and set the SQL_COPT_SS_TXN_ISOLATION attribute to SQL_TXN_SS_SNAPSHOT. SQL_TXN_SS_SNAPSHOT indicates that the transaction will take place under the snapshot isolation level. For example:

SQLSetConnectAttr(dbc, SQL_COPT_SS_TXN_ISOLATION, (SQLPOINTER *)SQL_TXN_SS_SNAPSHOT, 0);

The SQLGetInfo function supports the SQL_TXN_SS_SNAPSHOT value, which has been added to the SQL_TXN_ISOLATION_OPTION info type.

The SQL_COPT_SS_TXN_ISOLATION and SQL_TXN_SS_SNAPSHOT attributes are Easysoft ODBC-SQL Server Driver Driver-specific ODBC extensions. To use these attributes, ODBC applications need to include the sqlncli.h header file. sqlncli.h is installed in /usr/local/easysoft/sqlserver/include.

Performing Bulk Copy Operations

bcp Utility

The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of SQL.

Syntax

bcp {[[database_name.][schema].]{table_name | view_name} | "query"}

{in | out | queryout | format} data_file

[-m max_errors] [-f format_file] [-x] [-D logfile]

[-F first_row] [-L last_row] [-b batch_size]

[-n] [-c] [-N] [-w] [-V (70 | 80 | 90 | 100 | 110 | 120)]

[-q] [-C { RAW } ] [-t field_terminator]

[-r row_terminator] [-o output_file] [-a packet_size]

[-d database] [-e error_file] [-S server_name[\instance_name][:port]]

[-U login] [-P password] [-useNTLMv2] [-A APP_NAME]

[-T] [-v] [-k] [-g timeout] [-K] [-E] [-h"hint [,...n]"] [-Xe filename]

[-Xc cypher] [-G]

Arguments

database_name

The name of the database where table_name or view_name is located. The database can also be specified with the -d option. If no database is specified, the default database for login is used.

schema

The name of the schema to which table_name or view_name belongs.

table_name

The name of the destination table when importing data into SQL Server (in), and the source table when exporting data from SQL Server (out).

view_name

The name of the destination view when importing data into SQL Server (in), and the source view when exporting data from SQL Server (out).

"query"

An SQL query that returns a result set. If the query returns multiple result sets, such as a SELECT statement that specifies a COMPUTE clause, only the first result set is copied to the data file; subsequent result sets are ignored. If the database containing the target table or view is not the default for login, specify the database with the -d option.

in | out | queryout | format

The direction of the bulk copy:

data_file

The full path to the data file. When data is bulk imported into SQL Server, the data file contains the data to be copied into the specified table or view. When data is bulk exported from SQL Server, the data file contains the data copied from the table or view.

For the format option, you must specify nul as the value of data_file (format nul).

-m max_errors

The maximum number of times that bcp can return SQL_ERROR when bulk copying data from a query before the bcp operation is cancelled.

-f format_file

The full path to a format file:

-x

Used with the format and -f format_file options, the -x option generates an XML-based format file instead of the default non-XML format file. For example, bcp AdventureWorks.Sales.Currency format nul -f bcp.xml -x -c -U mydomain\\myuser -P mypassword -S mymachine\\sqlexpress.

-D logfile

Enables bcp logging and specifies the log file where the logging information is written. This can be a very useful debugging aid but it should be remembered that logging will slow bcp down, so remember to disable logging when you have finished debugging. Ensure that the user who is bcp has write permission to the log file (and to the directory containing it).

-F first_row

The number of the first row to export from a table or import from a data file. first_row should be a value greater than 0 but less than or equal to the total number rows. The default is the first row of the file.

-L last_row

The number of the last row to export from a table or import from a data file. last_row should be a value greater than 0 but less than or equal to the number of the last row. The default is the last row of the file.

-b batch_size

The number of rows per batch when importing data. Each batch is imported and logged as a separate transaction that imports the whole batch before being committed. By default, all the rows in the data file are imported as one batch. To distribute the rows among multiple batches, specify a batch_size that is smaller than the number of rows in the data file. If the transaction for any batch fails:

Batches already imported by committed transactions are unaffected by a subsequent failure.

-n

Use native (database) data types when importing and exporting data. This option does not prompt for each field; it uses the native values.

-c

Use character format when importing and exporting data. Character format uses the character data format for all columns. This option does not prompt for each field; it uses CHAR as the storage type, no prefixes, \t (tab character) as the field separator, and \n (newline character) as the row terminator.

-N

Use Unicode character format when importing and exporting character data. Use native format when importing and exporting non-character data.

-w

Use Unicode character format when importing and exporting data. Use this option when importing and exporting non-ASCII data stored in NCHAR, NVARCHAR and NTEXT columns. This option does not prompt for each field; it uses NCHAR as the storage type, no prefixes, \t (tab character) as the field separator, and \n (newline character) as the row terminator.

-V (70 | 80 | 90 | 100 | 110)

The version of SQL Server that bcp is connecting to, where:

70 = SQL Server 7.0

80 = SQL Server 2000

90 = SQL Server 2005

100 = SQL Server 2008

110 = SQL Server 2012 and above

-q

Executes the SET QUOTED_IDENTIFIERS ON statement in the connection between the bcp and an instance of SQL Server.

-C { RAW }

Specifies the code page of the data in the data file.

Value Description

RAW

No conversion from one code page to another occurs.

-t field_terminator

The field terminator. The default is \t (tab character).

-r row_terminator

The row terminator. The default is \n (newline character).

-o output_file

The name of a file that receives bcp output redirected from the command prompt. For example, if you specified -o /tmp/bcp.txt and exported some data, /tmp/bcp.txt would contain something similar to:

Starting copy...

105 rows successfully bulk-copied to host file. Total received: 105

Network packet size (bytes): 4096

The contents of output_file are overwritten each time you successfully import or export data.

-a packet_size

The packet size in bytes that bcp will request when sending data to and receiving data from SQL Server. The specified packet size must be lower than 65536 bytes.

The default packet size is 4096 bytes. After a successful import or export, the bcp output shows the packet size used.

-d database

The name of the database where table_name or view_name is located. If no database is specified, the default database for login is used.

-e error_file

The name of the file to write errors to.

-S server_name[ \instance_name]

The SQL Server instance that you want to connect to. To connect to the default instance of SQL Server on a server, specify only server_name. To connect to a named instance of SQL Server, specify server_name\instance_name. To include the port that the SQL Server instance is listening on, specify server_name:port.

-U login

The SQL Server login name to use when connecting to SQL Server. If the SQL Server instance uses Windows Authentication, specify the Windows user name to use to authenticate the connection. (Include the -T argument if you specify a Windows user name.) If the SQL Server instance permits SQL Server Authentication, you can also specify a SQL Server user name.

-P password

The password for login. You do not have to specify a password on the command line. If you omit the -P argument from the command line, bcp will prompt you for one when you run the command.

-useNTLMv2

If you want to use NTLMv2 to authenticate a Windows user specified with -U, include -useNTLMv2 in your bcp command. If NTLMv2 is enabled at your site and you omit the -useNTLMv2, argument, the bcp connection will fail with the error "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."

-A APP_NAME

The application name that bcp registers with SQL Server. This is returned by the SQL Server function APP_NAME(). The default application name is BCPTOOL. Use the -a argument to override the default name.

-T

Use Windows authentication to validate the connection. If this argument is specified, login must be a Windows user name.

-v

Reports the bcp version number.

-k

Empty columns retain a null value during an import, rather than have any default values for the columns inserted.

-g [timeout]

The number of milliseconds to wait for a TCP connection to the SQL Server machine to be established before returning to the bcp. For more information, see the LogonTimeout attribute in "Configuration" on page 86.

-G [req_flags]

Set the GSSFlag attribute via bcp. For more information, see the GSSFlag attribute in "Configuration" on page 86.

-K

Whether to access a SQL Server instance as a Kerberos service.

When you include this argument, the Easysoft ODBC-SQL Server Driver will attempt to obtain a service ticket for the following Service Principal Name (SPN):

MSSQLSvc/server

where:

server is the name or IP address of the SQL Server machine specified with the -S argument.

Do not supply a user name or password if you include the -K argument. The Kerberos application kinit must have already been used for authentication on the Easysoft ODBC-SQL Server Driver machine. For more information about kinit and accessing SQL Server as a Kerberos service, see the following Easysoft tutorial:

http://www.easysoft.com/products/data_access/odbc-sql-server-driver/kerberos.html

-M

The Service Principal Name (SPN) for a SQL Server instance that has been registered as a Kerberos service. If the SPN contains an instance name, you need to include the -M argument along with the -K argument. For example:

bcp -S "mysqlservermachine\myinstance" -K -M MSSQLSvc/mysqlservermachine:myinstance

-E

Use identity values in the imported data file for the identity column. If -E is not specified, identity values in the data file being imported are ignored, and SQL Server automatically assigns unique values based on the seed and increment values specified during table creation.

-h"hint [,...n]"

The hint or hints to be used during a bulk import of data into a table or view.

ORDER(column [ASC | DESC] [,...n])

The sort order of the data in the data file.

ROWS_PER_BATCH = num

Number of rows of data per batch. Used when -b is not specified, resulting in the entire data file being sent to the server as a single transaction. The server optimises the bulk load according to the value num. By default, ROWS_PER_BATCH is unknown.

KILOBYTES_PER_BATCH = num

Approximate number of kilobytes of data per batch. By default, KILOBYTES_PER_BATCH is unknown.

TABLOCK

A bulk update table-level lock is acquired for the duration of the bulk load operation; otherwise, a row-level lock is acquired.

CHECK_CONSTRAINTS

All constraints on the target table or view must be checked during the bulk-import operation. Without the CHECK_CONSTRAINTS hint, any CHECK and FOREIGN KEY constraints are ignored, and after the operation the constraint on the table is marked as not-trusted.

FIRE_TRIGGERS

Specified with the in argument, any insert triggers defined on the destination table will run during the bulk-copy operation. If FIRE_TRIGGERS is not specified, no insert triggers will run.

Remarks

-Xe filename

Use this option to specify a source for random data for an SSL connection. Use filename to specify a randomness device. You only need to use this option if bcp cannot find a source for random data on your system.

-Xc cypher

For an SSL connection, request a different encryption or data integrity algorithm to the ones negotiated during the SSL handshake. Separate multiple cypher suites with a colon. For example:

-Xc AES:3DES

Easysoft ODBC-SQL Server Driver Extensions - Bulk Copy Functions

In this section:

To build a program that uses the bulk copy extension library (esbcp), use this compile line format:

cc -I/usr/local/easysoft/unixODBC/include myprogram.c -I/usr/local/easysoft/sqlserver/include -o myprogram -L/usr/local/easysoft/unixODBC/lib/ -L/usr/local/easysoft/sqlserver/lib/ -lodbc -lodbcinst -lesbcp

If you did not install the Easysoft ODBC-SQL Server Driver under /usr/local amend the path accordingly.

bcp_batch

Commits all rows previously bulk copied from program variables and sent to SQL Server by bcp_sendrow.

Syntax

DBINT bcp_batch (HDBC

hdbc);

Arguments

hdbc

Is the bulk copy-enabled ODBC connection handle.

Returns

The number of rows saved after the last call to bcp_batch, or -1 in case of error.

Remarks

Bulk copy batches define transactions. When an application uses bcp_bind and bcp_sendrow to bulk copy rows from program variables to SQL Server tables, the rows are committed only when the program calls bcp_batch or bcp_done.

Examples

#include <stdio.h>

#include <string.h>

#include <sql.h>

#include <sqlext.h>

#include <sqlncli.h>

SQLHENV henv = SQL_NULL_HENV;

HDBC hdbc1 = SQL_NULL_HDBC;

DBINT idRow = 5;

char* pPart1 = "Text chunk 1.";

char* pPart2 = "Text chunk 2.";

char* pPart3 = "Text chunk 3.";

DBINT cbAllParts;

DBINT nRowsProcessed;

void Cleanup() {

if (hdbc1 != SQL_NULL_HDBC) {

SQLDisconnect(hdbc1);

SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

}

if (henv != SQL_NULL_HENV)

SQLFreeHandle(SQL_HANDLE_ENV, henv);

}

int main() {

RETCODE retcode;

/* Allocate the ODBC environment and save handle. */

retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);

if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {

printf("SQLAllocHandle(Env) Failed\n\n");

Cleanup();

return(9);

}

/* Notify ODBC that this is an ODBC 3.0 app. */

retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);

if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {

printf("SQLSetEnvAttr(ODBC version) Failed\n\n");

Cleanup();

return(9);

}

/* Allocate ODBC connection handle, set BCP mode, and connect. */

retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);

if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {

printf("SQLAllocHandle(hdbc1) Failed\n\n");

Cleanup();

return(9);

}

retcode = SQLSetConnectAttr(hdbc1, SQL_COPT_SS_BCP, (void *)SQL_BCP_ON, SQL_IS_INTEGER);

if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {

printf("SQLSetConnectAttr(hdbc1) Failed\n\n");

Cleanup();

return(9);

}

retcode = SQLDriverConnect(hdbc1, NULL, "DSN=MYDSN;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);

if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {

printf("SQLDriverConnect() Failed\n\n");

Cleanup();

return(9);

}

/* Initialise the bulk copy. */

retcode = bcp_init(hdbc1, "articles", NULL, NULL, DB_IN);

if ( (retcode != SUCCEED) ) {

printf("bcp_init(hdbc1) Failed.\n\n");

Cleanup();

return(9);

}

/* Bind program variables to table columns. */

retcode = (bcp_bind(hdbc1, (LPCBYTE) &idRow, 0, SQL_VARLEN_DATA, NULL, 0, SQLINT4, 1));

if ( (retcode != SUCCEED) ) {

printf("bcp_bind(hdbc1) Failed.\n\n");

Cleanup();

return(9);

}

cbAllParts = strlen(pPart1) + strlen(pPart2) + strlen(pPart3);

retcode = (bcp_bind(hdbc1, NULL, 0, cbAllParts, NULL, 0, SQLTEXT, 2));

if ( (retcode != SUCCEED) ) {

printf("bcp_bind(hdbc1) Failed.\n\n");

Cleanup();

return(9);

}

/* Now send this row, with the text value broken into three chunks. */

retcode = (bcp_sendrow(hdbc1));

if ( (retcode != SUCCEED) ) {

printf("bcp_sendrow(hdbc1) Failed.n\n");

Cleanup();

return(9);

}

cbAllParts=strlen( pPart1 );

retcode = (bcp_moretext(hdbc1, cbAllParts, pPart1));

if ( (retcode != SUCCEED) ) {

printf("bcp_moretext(hdbc1) Failed. 1\n\n");

Cleanup();

return(9);

}

cbAllParts=strlen( pPart2 );

retcode = (bcp_moretext(hdbc1, cbAllParts, pPart2));

if ( (retcode != SUCCEED) ) {

printf("bcp_moretext(hdbc1) Failed. 2\n\n");

Cleanup();

return(9);

}

cbAllParts=strlen( pPart3 );

retcode = (bcp_moretext(hdbc1, cbAllParts, pPart3));

if ( (retcode != SUCCEED) ) {

printf("bcp_moretext(hdbc1) Failed. 3\n\n");

Cleanup();

return(9);

}

/* We're all done. */

nRowsProcessed = bcp_batch(hdbc1);

/* Cleanup */

SQLDisconnect(hdbc1);

SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

SQLFreeHandle(SQL_HANDLE_ENV, henv);

}

bcp_bind

Binds data from a program variable to a table column for bulk copy into SQL Server.

Syntax

RETCODE bcp_bind (

HDBC hdbc,

LPCBYTE pData,

INT cbIndicator,

DBINT cbData,

LPCBYTE pTerm,

INT cbTerm,

INT eDataType,

INT idxServerCol);

Arguments

hdbc

Is the bulk copy-enabled ODBC connection handle.

pData

Is a pointer to the data copied. If eDataType is SQLTEXT, SQLNTEXT, SQLXML, SQLUDT, SQLCHARACTER, SQLVARCHAR, SQLVARBINARY, SQLBINARY, SQLNCHAR, or SQLIMAGE, pData can be NULL. A NULL pData indicates that long data values will be sent to SQL Server in chunks using bcp_moretext. The user should only set pData to NULL if the column corresponding to the user bound field is a BLOB column otherwise bcp_bind will fail.

cbIndicator

Is the length, in bytes, of a length or null indicator for the column's data. Valid indicator length values are 0 (when using no indicator), 1, 2, 4, or 8.

cbData

Is the count of bytes of data in the program variable, not including the length of any length or null indicator or terminator.

pTerm

Is a pointer to the byte pattern, if any, that marks the end of this program variable. If there is no terminator for the variable, set pTerm to NULL.

cbTerm

Is the count of bytes present in the terminator for the program variable, if any. If there is no terminator for the variable, set cbTerm to 0.

eDataType

Is the C data type of the program variable. The data in the program variable is converted to the type of the database column. If this parameter is 0, no conversion is performed.

The eDataType parameter is enumerated by the SQL Server data type tokens in /usr/local/easysoft/sqlserver/include/sqlncli.h, not the ODBC C data type enumerators.

idxServerCol

The ordinal position of the column in the database table to which the data is copied. The first column in a table is column 1. The ordinal position of a column is reported by SQLColumns.

Returns

SUCCEED or FAIL.

Remarks

Use bcp_bind for a fast, efficient way to copy data from a program variable into a table in SQL Server.

Make a separate bcp_bind call for every column in the SQL Server table into which you want to copy. After the necessary bcp_bind calls have been made, then call bcp_sendrow to send a row of data from your program variables to SQL Server. Rebinding a column is not supported.

Whenever you want SQL Server to commit the rows already received, call bcp_batch.

When there are no more rows to be inserted, call bcp_done. Failure to do so results in an error.

If pData for a column is set to NULL because its value will be supplied by calls to bcp_moretext, any subsequent columns with eDataType set to SQLTEXT, SQLNTEXT, SQLXML, SQLUDT, SQLCHARACTER, SQLVARCHAR, SQLVARBINARY, SQLBINARY, SQLNCHAR, or SQLIMAGE must also be bound with pData set to NULL, and their values must also be supplied by calls to bcp_moretext.

For new large value types, such as varchar(max), varbinary(max), or nvarchar(max), you can use SQLCHARACTER, SQLVARCHAR, SQLVARBINARY, SQLBINARY, and SQLNCHAR as type indicators in the eDataType parameter.

bcp_colfmt

Specifies the source or target format of the data in a user file. When used as a source format, bcp_colfmt specifies the format of an existing data file used as the source of data in a bulk copy to a SQL Server table. When used as a target format, the data file is created using the column formats specified with bcp_colfmt.

Syntax

RETCODE bcp_colfmt (

HDBC hdbc,

INT idxUserDataCol,

BYTE eUserDataType,

INT cbIndicator,

DBINT cbUserData,

LPCBYTE pUserDataTerm,

INT cbUserDataTerm,

INT idxServerCol);

Arguments

hdbc

Is the bulk copy-enabled ODBC connection handle.

idxUserDataCol

Is the ordinal column number in the user data file for which the format is being specified. The first column is 1.

eUserDataType

Is the data type of this column in the user file.

The eUserDataType parameter is enumerated by the SQL Server data type tokens in /usr/local/easysoft/sqlserver/include/sqlncli.h, not the ODBC C data type enumerators. For example, you can specify a character string, ODBC type SQL_C_CHAR, using the SQL Server-specific type SQLCHARACTER.

To specify the default data representation for the SQL Server data type, set this parameter to 0.

cbIndicator

Is the length, in bytes, of a length/null indicator within the column data. Valid indicator length values are 0 (when using no indicator), 1, 2, 4, or 8.

cbUserData

Is the maximum length, in bytes, of this column's data in the user file, not including the length of any length indicator or terminator.

The cbUserData value represents the count of bytes of data. If character data is represented by Unicode wide characters, a positive cbUserData parameter value represents the number of characters multiplied by the size, in bytes, of each character.

pUserDataTerm

Is the terminator sequence to be used for this column. This parameter is useful mainly for character data types because all other types are of fixed length or, in the case of binary data, require an indicator of length to accurately record the number of bytes present.

cbUserDataTerm

Is the length, in bytes, of the terminator sequence to be used for this column. If no terminator is present or desired in the data, set this value to 0.

idxServerCol

Is the ordinal position of the column in the database table. The first column number is 1. The ordinal position of a column is reported by SQLColumns.

If this value is 0, bulk copy ignores the column in the data file.

Returns

SUCCEED or FAIL.

Remarks

The bcp_colfmt function allows you to specify the user-file format for bulk copies.

The bcp_columns function must be called before any calls to bcp_colfmt.

You must call bcp_colfmt once for each column in the user file.

You do not need to copy all data in a user file to the SQL Server table. To skip a column, specify the format of the data for the column, setting the idxServerCol parameter to 0. If you want to skip a column, you must specify its type.

bcp_collen

Sets the data length in the program variable for the current bulk copy into SQL Server.

Syntax

RETCODE bcp_collen (

HDBC hdbc,

DBINT cbData,

INT idxServerCol);

Arguments

hdbc

Is the bulk copy-enabled ODBC connection handle.

cbData

Is the length of the data in the program variable, not including the length of any length indicator or terminator. Setting cbData to SQL_NULL_DATA indicates all rows copied to the server contain a NULL value for the column. Setting it to SQL_VARLEN_DATA indicates that a string terminator or other method is used to determine the length of data copied.

idxServerCol

Is the ordinal position of the column in the table to which the data is copied. The first column is 1.

Returns

SUCCEED or FAIL.

Remarks

The bcp_collen function allows you to change the data length in the program variable for a particular column when copying data to SQL Server with bcp_sendrow.

Initially, the data length is determined when bcp_bind is called. If the data length changes between calls to bcp_sendrow and no length prefix or terminator is being used, you can call bcp_collen to reset the length. The next call to bcp_sendrow uses the length set by the call to bcp_collen.

You must call bcp_collen once for each column in the table whose data length you want to modify.

Example

#include <stdio.h>

#include <string.h>

#include <sql.h>

#include <sqlext.h>

#include <sqlncli.h>

SQLHENV henv = SQL_NULL_HENV;

HDBC hdbc1 = SQL_NULL_HDBC;

DBINT idRow = 5;

char* pPart1 = "Text chunk 1.";

char* pPart2 = "Text chunk 2.";

char* pPart3 = "Text chunk 3.";

DBINT cbAllParts;

DBINT nRowsProcessed;

void Cleanup() {

if (hdbc1 != SQL_NULL_HDBC) {

SQLDisconnect(hdbc1);

SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

}

if (henv != SQL_NULL_HENV)

SQLFreeHandle(SQL_HANDLE_ENV, henv);

}

int main() {

RETCODE retcode;

/* Allocate the ODBC environment and save handle. */

retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);

if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {

printf("SQLAllocHandle(Env) Failed\n\n");

Cleanup();

return(9);

}

/* Notify ODBC that this is an ODBC 3.0 app. */

retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);

if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {

printf("SQLSetEnvAttr(ODBC version) Failed\n\n");

Cleanup();

return(9);

}

/* Allocate ODBC connection handle, set BCP mode, and connect. */

retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);

if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {

printf("SQLAllocHandle(hdbc1) Failed\n\n");

Cleanup();

return(9);

}

retcode = SQLSetConnectAttr(hdbc1, SQL_COPT_SS_BCP, (void *)SQL_BCP_ON, SQL_IS_INTEGER);

if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {

printf("SQLSetConnectAttr(hdbc1) Failed\n\n");

Cleanup();

return(9);

}

retcode = SQLDriverConnect(hdbc1, NULL, "DSN=MYDSN;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);

if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {

printf("SQLDriverConnect() Failed\n\n");

Cleanup();

return(9);

}

/* Initialise the bulk copy. */

retcode = bcp_init(hdbc1, "articles", NULL, NULL, DB_IN);

if ( (retcode != SUCCEED) ) {

printf("bcp_init(hdbc1) Failed.\n\n");

Cleanup();

return(9);

}

/* Bind program variables to table columns. */

retcode = (bcp_bind(hdbc1, (LPCBYTE) &idRow, 0, SQL_VARLEN_DATA, NULL, 0, SQLINT4, 1));

if ( (retcode != SUCCEED) ) {

printf("bcp_bind(hdbc1) Failed.\n\n");

Cleanup();

return(9);

}

cbAllParts = strlen(pPart1) + strlen(pPart2) + strlen(pPart3);

retcode = (bcp_bind(hdbc1, NULL, 0, cbAllParts, NULL, 0, SQLTEXT, 2));

if ( (retcode != SUCCEED) ) {

printf("bcp_bind(hdbc1) Failed.\n\n");

Cleanup();

return(9);

}

retcode = (bcp_collen(hdbc1, 100, 1));

if ( (retcode != SUCCEED) ) {

printf("bcp_collen(hdbc1) Failed.\n\n");

Cleanup();

return(9);

}

retcode = (bcp_colptr(hdbc1, NULL , 2));

if ( (retcode != SUCCEED) ) {

printf("bcp_colptr(hdbc1) Failed.\n\n");

Cleanup();

return(9);

}

/* Now send this row, with the text value broken into three chunks. */

retcode = (bcp_sendrow(hdbc1));

if ( (retcode != SUCCEED) ) {

printf("bcp_sendrow(hdbc1) Failed.n\n");

Cleanup();

return(9);

}

cbAllParts=strlen( pPart1 );

retcode = (bcp_moretext(hdbc1, cbAllParts, pPart1));

if ( (retcode != SUCCEED) ) {

printf("bcp_moretext(hdbc1) Failed. 1\n\n");

Cleanup();

return(9);

}

cbAllParts=strlen( pPart2 );

retcode = (bcp_moretext(hdbc1, cbAllParts, pPart2));

if ( (retcode != SUCCEED) ) {

printf("bcp_moretext(hdbc1) Failed. 2\n\n");

Cleanup();

return(9);

}

cbAllParts=strlen( pPart3 );

retcode = (bcp_moretext(hdbc1, cbAllParts, pPart3));

if ( (retcode != SUCCEED) ) {

printf("bcp_moretext(hdbc1) Failed. 3\n\n");

Cleanup();

return(9);

}

/* We're all done. */

nRowsProcessed = bcp_done(hdbc1);

/* Cleanup */

SQLDisconnect(hdbc1);

SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

SQLFreeHandle(SQL_HANDLE_ENV, henv);

}

bcp_colptr

Sets the program variable data address for the current copy into SQL Server.

Syntax

RETCODE bcp_colptr (

HDBC hdbc,

LPCBYTE pData,

INT idxServerCol);

Arguments

hdbc

Is the bulk copy-enabled ODBC connection handle.

pData

Is a pointer to the data to copy. If the bound data type is large value type (such as SQLTEXT or SQLIMAGE), pData can be NULL. A NULL pData indicates long data values will be sent to SQL Server in chunks using bcp_moretext.

If pData is set to NULL and the column corresponding to the bound field is not a large value type, bcp_colptr fails.

idxServerCol

Is the ordinal position of the column in the database table to which the data is copied. The first column in a table is column 1. The ordinal position of a column is reported by SQLColumns.

Returns

SUCCEED or FAIL.

Remarks

The bcp_colptr function allows you to change the address of source data for a particular column when copying data to SQL Server with bcp_sendrow.

Initially, the pointer to user data is set by a call to bcp_bind. If the program variable data address changes between calls to bcp_sendrow, you can call bcp_colptr to reset the pointer to the data. The next call to bcp_sendrow sends the data addressed by the call to bcp_colptr.

There must be a separate bcp_colptr call for every column in the table whose data address you want to modify.

bcp_columns

Sets the total number of columns found in the user file for use with a bulk copy into or out of SQL Server. bcp_setbulkmode can be used instead of bcp_columns and bcp_colfmt.

Syntax

RETCODE bcp_columns (

HDBC hdbc,

INT nColumns);

Arguments

hdbc

Is the bulk copy-enabled ODBC connection handle.

nColumns

Is the total number of columns in the user file. Even if you are preparing to bulk copy data from the user file to an SQL Server table and do not intend to copy all columns in the user file, you must still set nColumns to the total number of user-file columns.

Returns

SUCCEED or FAIL.

Remarks

This function can be called only after bcp_init has been called with a valid file name.

You should call this function only if you intend to use a user-file format that differs from the default. For more information about a description of the default user-file format, see bcp_init.

After calling bcp_columns, you must call bcp_colfmt for each column in the user file to completely define a custom file format.

bcp_control

Changes the default settings for various control parameters for a bulk copy between a file and SQL Server.

Syntax

RETCODE bcp_control (

HDBC hdbc,

INT eOption,

void* iValue);

Arguments

hdbc

Is the bulk copy-enabled ODBC connection handle.

eOption

Is one of the following:

BCPABORT

Stops a bulk-copy operation that is already in progress.

BCPBATCH

Is the number of rows per batch.

BCPDELAYREADFMT

If set to true, will cause bcp_readfmt to read at execution.

BCPFILEFMT

The version number of the data file format.

BCPFIRST

Is the first row of data to file or table to copy.

BCPFIRSTEX

For BCP out operations, specifies the first row of the database table to copy into the data file.

For BCP in operations, specifies the first row of the data file to copy into the database table.

BCPFMTXML

Specifies that the format file generated should be in XML format. It is off by default.

BCPHINTS

SQL Server bulk-copy processing hints or a Transact-SQL statement that returns a result set.

BCPKEEPIDENTITY

When iValue is TRUE, specifies that bulk copy functions insert data values supplied for SQL Server columns defined with an identity constraint. The input file must supply values for the identity columns. If this is not set, new identity values are generated for the inserted rows. Any data present in the file for the identity columns is ignored.

BCPKEEPNULLS

Specifies whether empty data values in the file will be converted to NULL values in the SQL Server table. When iValue is TRUE, empty values will be converted to NULL in the SQL Server table. The default is for empty values to be converted to a default value for the column in the SQL Server table if a default exists.

BCPLAST

Is the last row to copy.

BCPLASTEX

For BCP out operations, specifies the last row of the database table to copy into the data file.

For BCP in operations, specifies the last row of the data file to copy into the database table.

BCPMAXERRS

Is the number of errors allowed before the bulk copy operation fails.

BCPODBC

When TRUE, specifies that datetime and smalldatetime values saved in character format will use the ODBC timestamp escape sequence prefix and suffix. The BCPODBC option only applies to DB_OUT.

When FALSE, a datetime value representing January 1, 1997 is converted to the character string: 1997-01-01 00:00:00.000. When TRUE, the same datetime value is represented as: {ts '1997-01-01 00:00:00.000'}.

BCPROWCOUNT

Returns the number of rows affected by the current (or last) BCP operation.

BCPTEXTFILE

When TRUE, specifies that the data file is a text file, rather than a binary file.

BCPUNICODEFILE

When TRUE, specifies the input file is a Unicode file.

Returns

SUCCEED or FAIL.

Remarks

This function sets various control parameters for bulk-copy operations.

Example

#include <stdio.h>

#include <string.h>

#include <sql.h>

#include <sqlext.h>

#include <sqlncli.h>

SQLHENV henv = SQL_NULL_HENV;

HDBC hdbc1 = SQL_NULL_HDBC;

HDBC hdbc;

DBINT nRowsProcessed;

void Cleanup() {

if (hdbc1 != SQL_NULL_HDBC) {

SQLDisconnect(hdbc1);

SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

}

if (henv != SQL_NULL_HENV)

SQLFreeHandle(SQL_HANDLE_ENV, henv);

}

int main() {

RETCODE retcode;

/* Allocate the ODBC environment and save handle. */

retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);

if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {

printf("SQLAllocHandle(Env) Failed\n\n");

Cleanup();

return(9);

}

/* Notify ODBC that this is an ODBC 3.0 app. */

retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);

if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {

printf("SQLSetEnvAttr(ODBC version) Failed\n\n");

Cleanup();

return(9);

}

/* Allocate ODBC connection handle, set BCP mode, and connect. */

retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);

if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {

printf("SQLAllocHandle(hdbc1) Failed\n\n");

Cleanup();

return(9);

}

retcode = SQLSetConnectAttr(hdbc1, SQL_COPT_SS_BCP, (void *)SQL_BCP_ON, SQL_IS_INTEGER);

if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {

printf("SQLSetConnectAttr(hdbc1) Failed\n\n");

Cleanup();

return(9);

}

retcode = SQLDriverConnect(hdbc1, NULL, "DSN=MYDSN;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);

if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {

printf("SQLDriverConnect() Failed\n\n");

Cleanup();

return(9);

}

/* Initialise the bulk copy. */

/* Table definition: CREATE TABLE myTable(ColA varchar(25), ColB varchar(25),ColC varchar(25)) */

retcode = bcp_init(hdbc1, "myTable", "myTable.dat", "myErrors.log", DB_IN);

if ( (retcode != SUCCEED) ) {

printf("bcp_init(hdbc1) Failed.\n\n");

Cleanup();

return(9);

}

/* Set the number of rows per batch */

retcode = (bcp_control(hdbc1, BCPBATCH, (void*) 1));

if ( (retcode != SUCCEED) ) {

printf("bcp_control(hdbc1) Failed.\n\n");

Cleanup();

return(9);

}

/* There are three columns */

retcode = (bcp_columns(hdbc1, 3));

if ( (retcode != SUCCEED) ) {

printf("bcp_columns(hdbc1) Failed.\n\n");

Cleanup();

return(9);

}

bcp_colfmt(hdbc1, 1, SQLCHARACTER, 2, 25, "\t", 1, 1);

bcp_colfmt(hdbc1, 2, SQLCHARACTER, 2, 25, "\t", 1, 2);

bcp_colfmt(hdbc1, 3, SQLCHARACTER, 2, 25, "", 1, 3);

retcode = (bcp_writefmt(hdbc1, "myFmtFile.fmt"));

if ( (retcode != SUCCEED) ) {

printf("bcp_writefmt(hdbc1) Failed.\n\n");

Cleanup();

return(9);

}

retcode = (bcp_readfmt(hdbc1, "myFmtFile.fmt"));

if ( (retcode != SUCCEED) ) {

printf("bcp_readfmt(hdbc1) Failed.\n\n");

Cleanup();

return(9);

}

retcode = (bcp_exec(hdbc1, &nRowsProcessed));

if ( (retcode != SUCCEED) ) {

printf("bcp_exec(hdbc1) Failed. 1\n\n");

Cleanup();

return(9);

}

/* Cleanup */

SQLDisconnect(hdbc1);

SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

SQLFreeHandle(SQL_HANDLE_ENV, henv);

}

bcp_done

When bcp_sendrow is used to bulk copy rows from program variables into SQL Server tables, rows are committed only when the user calls bcp_batch or bcp_done.

Syntax

DBINT bcp_done (

HDBC hdbc);

Arguments

hdbc

Is the bulk copy-enabled ODBC connection handle.

Returns

The number of rows permanently saved after the last call to bcp_batch or -1 in case of error.

Remarks

Call bcp_done after the last call to bcp_sendrow or bcp_moretext. Failure to call bcp_done after copying all data results in errors.

bcp_exec

Executes a complete bulk copy of data between a database table and a user file.

Syntax

RETCODE bcp_exec (

HDBC hdbc,

LPDBINT pnRowsProcessed);

Arguments

hdbc

Is the bulk copy-enabled ODBC connection handle.

pnRowsProcessed

Is a pointer to a DBINT. The bcp_exec function fills this DBINT with the number of rows successfully copied. If pnRowsProcessed is NULL, it is ignored by bcp_exec.

Returns

SUCCEED, or FAIL. The bcp_exec function returns SUCCEED if all rows are copied. bcp_exec returns FAIL if a complete failure occurs Check the pnRowsProcessed parameter for the number of rows successfully copied.

Remarks

This function copies data from a user file to a database table or vice versa, depending on the value of the eDirection parameter in bcp_init.

Before calling bcp_exec, call bcp_init with a valid user file name. Failure to do so results in an error.

Example

#include <stdio.h>

#include <sql.h>

#include <sqlext.h>

#include <sqlncli.h>

main(int argc, char *argv[] )

{

SQLCHAR szDSN[]="MyDSN";

SQLCHAR szUser[]="myuser";

SQLCHAR szPass[]="mypassword";

char szTable[]="mytable";

char szInFile[]="mydatafile";

char szErrFile[]="mylog.log";

DBINT nRowsProcessed;

SQLRETURN ret;

SQLHANDLE hEnv;

SQLHANDLE hDbc;

SQLHANDLE hStmt;

SQLINTEGER cbLen;

SQLCHAR szResult[10];

printf("\n");

printf("Simple Input.\n");

printf("--------------\n");

printf("DSN : %s\n",szDSN);

ret = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv );

if ( ret != SQL_SUCCESS)

printf( "SQLAllocHandle failed" );

ret = SQLSetEnvAttr( hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_UINTEGER );

if ( ret != SQL_SUCCESS)

printf( "SQLSetEnvAttr conn failed" );

ret = SQLAllocHandle( SQL_HANDLE_DBC, hEnv, &hDbc );

if ( ret != SQL_SUCCESS)

printf( "SQLAllocHandle conn failed" );

ret = SQLSetConnectAttr(hDbc, SQL_COPT_SS_BCP, (SQLPOINTER) SQL_BCP_ON, SQL_IS_INTEGER);

ret = SQLConnect( hDbc, szDSN, SQL_NTS, szUser, SQL_NTS, szPass, SQL_NTS);

if ( SQL_SUCCEEDED( ret ))

{

printf("Connected successfully.\n");

}

else

{

printf("Connection failed.\n");

return(1);

}

printf("bcp_init\n Table : %s\n File : %s\n Errors : %s\n", szTable, szInFile, szErrFile );

ret = bcp_init(hDbc, szTable, szInFile, szErrFile, DB_IN);

printf("bcp_init ret %d\n",ret);

if (ret == FAIL )

{

printf("BCP bcp_init failed.\n");

return(1);

}

nRowsProcessed=0;

ret = bcp_exec(hDbc, &nRowsProcessed);

printf("bcp_exec ret %d\n",ret);

if (ret == FAIL )

{

if (nRowsProcessed == -1)

{

printf("No rows processed\n");

}

else

{

printf("Only %d rows processed\n",nRowsProcessed);

}

return(1);

}

printf("Rows : %d\n",nRowsProcessed);

ret = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);

SQLFreeHandle(SQL_HANDLE_STMT, hStmt);

SQLFreeHandle(SQL_HANDLE_DBC, hDbc);

SQLFreeHandle(SQL_HANDLE_ENV, hEnv);

return SQL_SUCCESS;

}

bcp_getcolfmt

Used to find the column format property value.

Syntax

HDBC hdbc,

INT field,

INT property,

void* pValue,

INT cbvalue,

INT* pcbLen);

Arguments

hdbc

Is the bulk copy-enabled ODBC connection handle.

field

Is the column number for which the property is retrieved.

property

Is one of the property constants.

pValue

Is the pointer to the buffer from which to retrieve the property value.

cbValue

Is the length of the property buffer in bytes.

pcbLen

Pointer to length of the data that is being returned in the property buffer.

Returns

SUCCEED or FAIL.

Remarks

Column format property values are listed in the bcp_setcolfmt topic. The column format property values are set by calling the bcp_setcolfmt function, and the bcp_getcolfmt function is used to find the column format property value.

Example

#include <stdio.h>

#include <string.h>

#include <sql.h>

#include <sqlext.h>

#include <sqlncli.h>

SQLHENV henv = SQL_NULL_HENV;

HDBC hdbc1 = SQL_NULL_HDBC;

HDBC hdbc;

DBINT nRowsProcessed;

void Cleanup() {

if (hdbc1 != SQL_NULL_HDBC) {

SQLDisconnect(hdbc1);

SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

}

if (henv != SQL_NULL_HENV)

SQLFreeHandle(SQL_HANDLE_ENV, henv);

}

int main() {

RETCODE retcode;

/* Allocate the ODBC environment and save handle. */

retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);

if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {

printf("SQLAllocHandle(Env) Failed\n\n");

Cleanup();

return(9);

}

/* Notify ODBC that this is an ODBC 3.0 app. */

retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);

if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {

printf("SQLSetEnvAttr(ODBC version) Failed\n\n");

Cleanup();

return(9);

}

/* Allocate ODBC connection handle, set BCP mode, and connect. */

retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);

if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {

printf("SQLAllocHandle(hdbc1) Failed\n\n");

Cleanup();

return(9);

}

retcode = SQLSetConnectAttr(hdbc1, SQL_COPT_SS_BCP, (void *)SQL_BCP_ON, SQL_IS_INTEGER);

if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {

printf("SQLSetConnectAttr(hdbc1) Failed\n\n");

Cleanup();

return(9);

}

retcode = SQLDriverConnect(hdbc1, NULL, "DSN=MYDSN;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);

if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {

printf("SQLDriverConnect() Failed\n\n");

Cleanup();

return(9);

}

/* Initialise the bulk copy. */

/* Table definition: CREATE TABLE myTable(ColA varchar(25), ColB varchar(25),ColC varchar(25)) */

retcode = bcp_init(hdbc1, "myTable", "myTable.dat", "myErrors.log", DB_IN);

if ( (retcode != SUCCEED) ) {

printf("bcp_init(hdbc1) Failed.\n\n");

Cleanup();

return(9);

}

/* There are three columns */

retcode = (bcp_columns(hdbc1, 3));

if ( (retcode != SUCCEED) ) {

printf("bcp_columns(hdbc1) Failed.\n\n");

Cleanup();

return(9);

}

bcp_colfmt(hdbc1, 1, SQLCHARACTER, 2, 25, "\t", 1, 1);

bcp_colfmt(hdbc1, 2, SQLCHARACTER, 2, 25, "\t", 1, 2);

bcp_colfmt(hdbc1, 3, SQLCHARACTER, 2, 25, "", 1, 3);

retcode = (bcp_writefmt(hdbc1, "myFmtFile.fmt"));

if ( (retcode != SUCCEED) ) {

printf("bcp_writefmt(hdbc1) Failed.\n\n");

Cleanup();

return(9);

}

retcode = (bcp_getcolfmt(hdbc1));

if ( (retcode != SUCCEED) ) {

printf("bcp_getcolfmt(hdbc1) Failed.\n\n");

Cleanup();

return(9);

}

retcode = (bcp_exec(hdbc1, &nRowsProcessed));

if ( (retcode != SUCCEED) ) {

printf("bcp_exec(hdbc1) Failed. 1\n\n");

Cleanup();

return(9);

}

/* Cleanup */

SQLDisconnect(hdbc1);

SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

SQLFreeHandle(SQL_HANDLE_ENV, henv);

}

bcp_gettypename

Returns the SQL type name for a specified BCP type token.

Syntax

RETCODE bcp_gettypename (

INT token,

DBBOOL fIsMaxType);

Arguments

token

A value indicating a BCP type token.

field

Indicates if token requested is a MAX type.

Returns

A string containing the SQL type name corresponding to the BCP type. If an invalid BCP type is specified, an empty string is returned.

Remarks

The BCP type tokens are defined in the /usr/local/easysoft/sqlserver/include/sqlncli.h header file.

bcp_init

Initialises the bulk copy operation.

Syntax

RETCODE bcp_init (

HDBC hdbc,

LPCTSTR szTable,

LPCTSTR szDataFile,

LPCTSTR szErrorFile,

INT eDirection);

Unicode and ANSI names:

bcp_initA (ANSI)

bcp_initW (Unicode)

Arguments

hdbc

Is the bulk copy-enabled ODBC connection handle.

szTable

Is the name of the database table to be copied into or out of. This name can also include the database name or the owner name. For example, pubs.gracie.titles, pubs..titles, gracie.titles, and titles are all valid table names.

If eDirection is DB_OUT, szTable can also be the name of a database view.

If eDirection is DB_OUT and a SELECT statement is specified using bcp_control before bcp_exec is called, bcp_init szTable must be set to NULL.

szDataFile

Is the name of the user file to be copied into or out of. If data is being copied directly from variables by using bcp_sendrow, set szDataFile to NULL.

szErrorFile

Is the name of the error file to be filled with progress messages, error messages, and copies of any rows that, for any reason, could not be copied from a user file to a table. If NULL is passed as szErrorFile, no error file is used.

eDirection

Is the direction of the copy, either DB_IN or DB_OUT. DB_IN indicates a copy from program variables or a user file to a table. DB_OUT indicates a copy from a database table to a user file. You must specify a user file name with DB_OUT.

Returns

SUCCEED or FAIL.

Remarks

Call bcp_init before calling any other bulk-copy function. bcp_init performs the necessary initialisations for a bulk copy of data between the Easysoft ODBC-SQL Server Driver and SQL Server.

The bcp_init function must be provided with an ODBC connection handle enabled for use with bulk copy functions. To enable the handle, use SQLSetConnectAttr with SQL_COPT_SS_BCP set to SQL_BCP_ON on an allocated, but not connected, connection handle. Attempting to assign the attribute on a connected handle results in an error.

When a data file is specified, bcp_init examines the structure of the database source or target table, not the data file. bcp_init specifies data format values for the data file based on each column in the database table, view, or SELECT result set. This specification includes the data type of each column, the presence or absence of a length or null indicator and terminator byte strings in the data, and the width of fixed-length data types. bcp_init sets these values as follows:

When copying to SQL Server, the data file must have data for each column in the database table. When copying from SQL Server, data from all columns in the database table, view, or SELECT result set are copied to the data file.

To change data format values specified for a data file, call bcp_columns and bcp_colfmt.

If no data file is used, you must call bcp_bind to specify the format and location in memory of the data for each column, then copy data rows to the SQL Server using bcp_sendrow.

bcp_moretext

Sends part of a long, variable-length data type value to SQL Server.

Syntax

RETCODE bcp_moretext (

HDBC hdbc,

DBINT cbData,

LPCBYTE pData);

Arguments

hdbc

Is the bulk copy-enabled ODBC connection handle.

cbData

Is the number of bytes of data being copied to SQL Server from the data referenced by pData. A value of SQL_NULL_DATA indicates NULL.

pData

Is a pointer to the supported, long, variable-length data chunk to be sent to SQL Server.

Returns

SUCCEED or FAIL.

Remarks

This function can be used in conjunction with bcp_bind and bcp_sendrow to copy long, variable-length data values to SQL Server in a number of smaller chunks. bcp_moretext can be used with columns that have the following SQL Server data types: text, ntext, image, varchar(max), nvarchar(max), varbinary(max), user-defined type (UDT), and XML. bcp_moretext does not support data conversions, the data supplied must match the data type of the target column.

If bcp_bind is called with a non-NULL pData parameter for data types that are supported by bcp_moretext, bcp_sendrow sends the entire data value, regardless of length. If, however, bcp_bind has a NULL pData parameter for supported data types, bcp_moretext can be used to copy data immediately after a successful return from bcp_sendrow indicating that any bound columns with data present have been processed.

bcp_readfmt

Reads a data file format definition from the specified format file.

Syntax

HDBC hdbc,

LPCTSTR szFormatFile);

Arguments

hdbc

Is the bulk copy-enabled ODBC connection handle.

szFormatFile

Is the path and file name of the file containing the format values for the data file.

Returns

SUCCEED or FAIL.

Remarks

After bcp_readfmt reads the format values, it makes the appropriate calls to bcp_columns and bcp_colfmt. There is no need for you to parse a format file and make these calls.

To persist a format file, call bcp_writefmt.

The bulk-copy utility (bcp) can also save user-defined data formats in files that can be referenced by bcp_readfmt.

bcp_sendrow

Sends a row of data to SQL Server.

Syntax

RETCODE bcp_sendrow (

HDBC hdbc);

Arguments

hdbc

Is the bulk copy-enabled ODBC connection handle.

Returns

SUCCEED or FAIL

Remarks

The bcp_sendrow function builds a row from variables bound with bcp_bind and sends it to SQL Server.

If bcp_bind is called specifying a long, variable-length data type, for example, an eDataType parameter of SQLTEXT and a non-NULL pData parameter, bcp_sendrow sends the entire data value. If, however, bcp_bind has a NULL pData parameter, bcp_sendrow returns control to the application immediately after all columns with data specified are sent to SQL Server. The application can then call bcp_moretext repeatedly to send the long, variable-length data to SQL Server, a chunk at a time. For more information, see bcp_moretext.

When bcp_sendrow is used to bulk copy rows from program variables into SQL Server tables, rows are committed only when the user calls bcp_batch or bcp_done. The user can choose to call bcp_batch once every n rows or when there is a lull between periods of incoming data. If bcp_batch is never called, the rows are committed when bcp_done is called.

Example

#include <stdio.h>

#include <string.h>

#include <sql.h>

#include <sqlext.h>

#include <sqlncli.h>

SQLHENV henv = SQL_NULL_HENV;

HDBC hdbc1 = SQL_NULL_HDBC;

DBINT idRow = 5;

char* pPart1 = "Text chunk 1.";

char* pPart2 = "Text chunk 2.";

char* pPart3 = "Text chunk 3.";

DBINT cbAllParts;

DBINT nRowsProcessed;

void Cleanup() {

if (hdbc1 != SQL_NULL_HDBC) {

SQLDisconnect(hdbc1);

SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

}

if (henv != SQL_NULL_HENV)

SQLFreeHandle(SQL_HANDLE_ENV, henv);

}

int main() {

RETCODE retcode;

/* Allocate the ODBC environment and save handle. */

retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);

if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {

printf("SQLAllocHandle(Env) Failed\n\n");

Cleanup();

return(9);

}

/* Notify ODBC that this is an ODBC 3.0 app. */

retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);

if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {

printf("SQLSetEnvAttr(ODBC version) Failed\n\n");

Cleanup();

return(9);

}

/* Allocate ODBC connection handle, set BCP mode, and connect. */

retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);

if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {

printf("SQLAllocHandle(hdbc1) Failed\n\n");

Cleanup();

return(9);

}

retcode = SQLSetConnectAttr(hdbc1, SQL_COPT_SS_BCP, (void *)SQL_BCP_ON, SQL_IS_INTEGER);

if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {

printf("SQLSetConnectAttr(hdbc1) Failed\n\n");

Cleanup();

return(9);

}

retcode = SQLDriverConnect(hdbc1, NULL, "DSN=MYDSN;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);

if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {

printf("SQLDriverConnect() Failed\n\n");

Cleanup();

return(9);

}

/* Initialise the bulk copy. */

retcode = bcp_init(hdbc1, "MyTable", NULL, NULL, DB_IN);

if ( (retcode != SUCCEED) ) {

printf("bcp_init(hdbc1) Failed.\n\n");

Cleanup();

return(9);

}

/* Bind program variables to table columns. */

retcode = (bcp_bind(hdbc1, (LPCBYTE) &idRow, 0, SQL_VARLEN_DATA, NULL, 0, SQLINT4, 1));

if ( (retcode != SUCCEED) ) {

printf("bcp_bind(hdbc1) Failed.\n\n");

Cleanup();

return(9);

}

cbAllParts = strlen(pPart1) + strlen(pPart2) + strlen(pPart3);

retcode = (bcp_bind(hdbc1, NULL, 0, cbAllParts, NULL, 0, SQLTEXT, 2));

if ( (retcode != SUCCEED) ) {

printf("bcp_bind(hdbc1) Failed.\n\n");

Cleanup();

return(9);

}

/* Now send this row, with the text value broken into three chunks. */

retcode = (bcp_sendrow(hdbc1));

if ( (retcode != SUCCEED) ) {

printf("bcp_sendrow(hdbc1) Failed.n\n");

Cleanup();

return(9);

}

cbAllParts=strlen( pPart1 );

retcode = (bcp_moretext(hdbc1, cbAllParts, pPart1));

if ( (retcode != SUCCEED) ) {

printf("bcp_moretext(hdbc1) Failed. 1\n\n");

Cleanup();

return(9);

}

cbAllParts=strlen( pPart2 );

retcode = (bcp_moretext(hdbc1, cbAllParts, pPart2));

if ( (retcode != SUCCEED) ) {

printf("bcp_moretext(hdbc1) Failed. 2\n\n");

Cleanup();

return(9);

}

cbAllParts=strlen( pPart3 );

retcode = (bcp_moretext(hdbc1, cbAllParts, pPart3));

if ( (retcode != SUCCEED) ) {

printf("bcp_moretext(hdbc1) Failed. 3\n\n");

Cleanup();

return(9);

}

/* We're all done. */

nRowsProcessed = bcp_done(hdbc1);

/* Cleanup */

SQLDisconnect(hdbc1);

SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

SQLFreeHandle(SQL_HANDLE_ENV, henv);

}

bcp_setbulkmode

bcp_setbulkmode lets you specify the column format in a bulk copy operation, setting all the column attributes in a single function call.

Syntax

RETCODE bcp_setbulkmode (

HDBC hdbc,

INT property,

void * pField,

INT cbField,

void * pRow,

INT cbRow

);

Arguments

hdbc

Is the bulk copy-enabled ODBC connection handle.

property

A constant of type BYTE. See the table in the Remarks section for a list of the constants.

pField

The pointer to the field terminator value.

pRow

The pointer to the row terminator value.

cbRow

The length in bytes of the row terminator value.

Returns

SUCCEED or FAIL

Remarks

bcp_setbulkmode can be used to bulk copy out of either a query or a table. When bcp_setbulkmode is used to bulk copy out a query statement, it must be called before calling bcp_control with BCP_HINT.

bcp_setbulkmode is an alternative to using bcp_setcolfmt and bcp_columns, which only let you specify the format of one column per function call.

bcp_setcolfmt

This function provides a flexible approach to specifying the column format in a bulk copy operation. It is used to set individual column format attributes. Each call to bcp_setcolfmt sets one column format attribute.

The bcp_setcolfmt function specifies the source or target format of the data in a user file. When used as a source format, bcp_setcolfmt specifies the format of an existing data file used as a data source of data in a bulk copy to a table in SQL Server. When used as a target format, the data file is created using the column formats specified with bcp_setcolfmt.

Syntax

RETCODE bcp_setcolfmt (

HDBC hdbc,

INT field,

INT property,

void* pValue,

INT cbValue);

Arguments

hdbc

Is the bulk copy-enabled ODBC connection handle.

field

Is the ordinal column number for which the property is being set.

property

Is one of the property constants. Property constants are defined in this table

Value Description

BCP_FMT_TYPE

BYTE

Is the data type of this column in the user file. If different from the data type of the corresponding column in the database table, bulk copy converts the data if possible.

The BCP_FMT_TYPE parameter is enumerated by the SQL Server data type tokens in /usr/local/easysoft/sqlserver/include/sqlncli.h, rather than the ODBC C data type enumerators. For example, you can specify a character string, ODBC type SQL_C_CHAR, using the SQLCHARACTER type specific to SQL Server.

To specify the default data representation for the SQL Server data type, set this parameter to 0.

BCP_FMT_DATA_LEN

DBINT

Is the length in bytes of the data (column length).

BCP_FMT_TERMINATOR

LPCBYTE

Pointer to the terminator sequence (either ANSI or Unicode as appropriate) to be used for this column. This parameter is useful mainly for character data types because all other types are of fixed length or, in the case of binary data, require an indicator of length to accurately record the number of bytes present.

BCP_FMT_SERVER_COL

INT

Ordinal position of the column in the database

BCP_FMT_COLLATION

LPCSTR

Collation name.

.

pValue

Is the pointer to the value to associate to the property. It allows each column format property to be set individually.

cbvalue

Is the length of the property buffer in bytes.

Returns

SUCCEED or FAIL.

Remarks

The bcp_setcolfmt function allows you to specify the user-file format for bulk copies.

You do not need to copy all data in a user file to the SQL Server table. To skip a column, specify the format of the data for the column, setting the BCP_FMT_SERVER_COL parameter to 0. If you want to skip a column, you must specify its type.

bcp_writefmt

Creates a format file containing a description of the format of the current bulk copy data file.

Syntax

RETCODE bcp_writefmt (

HDBC hdbc,

LPCTSTR szFormatFile);

Arguments

hdbc

Is the bulk copy-enabled ODBC connection handle.

szFormatFile

Is the path and file name of the user file to receive format values for the data file.

Returns

SUCCEED or FAIL.

Remarks

The format file specifies the data format of a data file created by bulk copy.

Table-Valued Parameters

Table-valued parameters, introduced in SQL Server 2008, allow multiple rows or values to be passed to a query or stored procedure in one call. Table-valued parameters decrease network latency by reducing network round trips. For example, given the task of updating multiple order line items an application traditionally would call one procedure to update the order and another procedure to update the line items. The second procedure would be called multiple times, once for each line item, therefore requiring multiple database round trips to fulfill the objective.

The Easysoft ODBC-SQL Server Driver enables data to be sent as a table-valued parameter with all values in memory.

The following example inserts an order and multiple order detail rows by passing a table-valued parameter to one stored procedure.

#include <stdio.h>

#include <sql.h>

#include <sqlext.h>

#include <stdlib.h>

#include <string.h>

#include "sqlncli.h"

main() {

SQLHENV env;

SQLHDBC dbc;

SQLHSTMT hstmt;

/* ODBC API return status */

SQLRETURN ret;

/* SQL parameters */

#define ITEM_ARRAY_SIZE 20

SQLCHAR CustCode[6];

SQLCHAR *TVP = (SQLCHAR *) "TVParam";

SQLINTEGER ProdCode[ITEM_ARRAY_SIZE], Qty[ITEM_ARRAY_SIZE];

SQLINTEGER OrdNo;

char OrdDate[23];

/* Indicator/length variables associated with SQL parameters */

SQLLEN cbCustCode, cbTVP, cbProdCode[ITEM_ARRAY_SIZE], cbQty[ITEM_ARRAY_SIZE], cbOrdNo, cbOrdDate;

/* Allocate an environment handle */

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);

/* We want ODBC 3 support */

SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);

/* Allocate a connection handle */

SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

/* Connect to the DSN mydsn.

* You will need to change mydsn to one you have created and tested

* The SQL Server database your DSN connects to needs:

*

* These tables:

*

* CREATE TABLE dbo.TVPOrder (

* CustCode varchar(5),

* OrdNo int identity,

* OrdDate datetime

* )

*

* CREATE TABLE dbo.TVPItem (

* OrdNo int,

* ProdCode int,

* Qty int

* )

*

*

* This user-defined table type:

*

* IF (SELECT COUNT(*) FROM sys.table_types

* WHERE name = 'TVPParam' AND schema_id = 1) = 0

* CREATE TYPE dbo.TVPParam AS TABLE(ProdCode integer, Qty integer)

*

* This procedure:

*

* CREATE PROCEDURE

* dbo.TVPOrderEntry

* (

* @CustCode varchar(5),

* @Items TVPParam READONLY,

* @OrdNo integer output,

* @OrdDate datetime output)

* AS

* SET @OrdDate = GETDATE();

*

* INSERT INTO TVPOrder (OrdDate, CustCode)

* VALUES (@OrdDate, @CustCode);

*

* SELECT @OrdNo = SCOPE_IDENTITY();

*

* INSERT INTO TVPItem (OrdNo, ProdCode, Qty)

* SELECT @OrdNo, ProdCode, Qty FROM @Items

*/

SQLDriverConnect(dbc, NULL, "DSN=SQLSERVER_2012_EXPRESS_64_BIT;", SQL_NTS,

NULL, 0, NULL, SQL_DRIVER_COMPLETE);

SQLAllocHandle(SQL_HANDLE_STMT, dbc, &hstmt);

/* Bind parameters for call to TVPOrderEntry */

/* 1 - CustCode input */

ret = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 5, 0, CustCode, sizeof(CustCode), &cbCustCode);

/* 2 - Items TVP */

ret = SQLBindParameter(hstmt,

2,

SQL_PARAM_INPUT,

SQL_C_DEFAULT,

SQL_SS_TABLE,

ITEM_ARRAY_SIZE, /* ColumnSize: For a table-valued parameter this is the row array size */

0, /* DecimalDigits: For a table-valued parameter this is always 0 */

TVP, /* ParameterValuePtr: For a table-valued parameter this is the type name of the */

/* table-valued parameter, and also a token returned by SQLParamData */

strlen(TVP), /* BufferLength: For a table-valued parameter this is the length of the type name or SQL_NTS */

&cbTVP); /* StrLen_or_IndPtr: For a table-valued parameter this is the number of rows actually used */

cbOrdNo = 0;

cbOrdDate = 0;

/* 3 - OrdNo output */

ret = SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT,SQL_C_LONG, SQL_INTEGER, 0, 0, &OrdNo,

sizeof(SQLINTEGER), &cbOrdNo);

/* 4 - OrdDate output */

ret = SQLBindParameter(hstmt, 4, SQL_PARAM_OUTPUT, SQL_C_CHAR,SQL_TYPE_TIMESTAMP, 23, 3, &OrdDate,

sizeof(OrdDate), &cbOrdDate);

/* Bind columns for the table-valued parameter (parameter 2) */

/* First set focus on parameter 2 */

ret = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 2, SQL_IS_INTEGER);

/* Col 1 - ProdCode */

ret = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, ProdCode, sizeof(SQLINTEGER), cbProdCode);

/* Col 2 - Qty */

ret = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, Qty, sizeof(SQLINTEGER), cbQty);

/* Reset parameter focus */

ret = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 0, SQL_IS_INTEGER);

/* Populate parameters */

cbTVP = 0; /* Number of rows available for input */

strcpy((char *) CustCode, "BEAUC"); cbCustCode = SQL_NTS;

ProdCode[cbTVP] = 555;cbProdCode[cbTVP] = sizeof(SQLINTEGER);

Qty[cbTVP] = 5;cbQty[cbTVP] = sizeof(SQLINTEGER);

cbTVP++; /* Number of rows available for input */

ProdCode[cbTVP] = 666;cbProdCode[cbTVP] = sizeof(SQLINTEGER);

Qty[cbTVP] = 6;cbQty[cbTVP] = sizeof(SQLINTEGER);

cbTVP++; /* Number of rows available for input */

/* Call the procedure */

ret = SQLExecDirect(hstmt, (SQLCHAR *) "{call TVPOrderEntry(?, ?, ?, ?)}",SQL_NTS);

}

Binding Procedure Parameters by Name

The Easysoft ODBC-SQL Server Driver supports named parameters, which allows an application to specify stored procedure parameters by name instead of by position in the procedure call.

This C code sample calls the AdventureWorks2008 stored procedure uspSearchCandidateResumes. The sample specifies the stored procedure's parameters by name.

#include <stdio.h>

#include <sql.h>

#include <sqlext.h>

main() {

SQLHENV env;

SQLHDBC dbc;

SQLHSTMT stmt;

SQLHANDLE ipd;

SQLRETURN ret;

/* Procedure input */

SQLCHAR param_name_1 [ 64 ], param_name_2 [ 64 ],

param_name_3 [ 64 ], param_name_4 [ 64 ];

/* Search candidate resumes for this text: */

SQLCHAR search_string[ 64 ] = "ISO9000";

/* Enable the default values for the other procedure */

/* parameters to be overridden. Initialise variables */

/* to the corresponding parameter's default value. */

BOOL use_inflectional = 0;

BOOL use_thesaurus = 0;

SQLSMALLINT language = 0;

/* Procedure output */

SQLCHAR col_1_name [ 64 ], col_2_name [ 64 ];

SQLINTEGER col_1;

SQLSMALLINT col_2, col_1_name_length, col_2_name_length;

SQLLEN len_1, len_2;

/* Allocate an environment handle */

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);

/* We want ODBC 3 support */

SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)

SQL_OV_ODBC3, 0);

/* Allocate a connection handle */

SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

/* Connect to the DSN mydsn */

/* Change mydsn to one you have created and tested */

SQLDriverConnect(dbc, NULL, "DSN=mydsn;", SQL_NTS,

NULL, 0, NULL, SQL_DRIVER_COMPLETE);

/* Allocate a statement handle */

SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

/* Prepare the statement that will call the procedure */

SQLPrepare(stmt, "{call uspSearchCandidateResumes (?, ?, ?, ?)}",

SQL_NTS);

/* Bind local variables to the parameters in the preceding */

/* statement. */

SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,

SQL_CHAR, 1000, 0, search_string, 0, 0);

SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_BIT, SQL_BIT,

1, 0, &use_inflectional, 0, 0);

SQLBindParameter(stmt, 3, SQL_PARAM_INPUT, SQL_C_BIT, SQL_BIT,

1, 0, &use_thesaurus, 0, 0);

SQLBindParameter(stmt, 4, SQL_PARAM_INPUT, SQL_INTEGER,

SQL_INTEGER, 1, 0, &language, 0, 0);

/* Bind columns in the procedure result set to local */

/* variables */

SQLBindCol(stmt, 1, SQL_INTEGER, &col_1, 0, &len_1);

SQLBindCol(stmt, 2, SQL_INTEGER, &col_2, 0, &len_2);

/* Get IPD handle. The IPD contains information about the */

/* statement parameters, such as their SQL data types, */

/* lengths, and nullability. */

SQLGetStmtAttr(stmt, SQL_ATTR_IMP_PARAM_DESC, &ipd, 0, 0);

/* Set the SQL_DESC_NAME field of the IPD to the parameter */

/* name */

SQLSetDescField(ipd, 1, SQL_DESC_NAME, "@searchString",

SQL_NTS);

SQLSetDescField(ipd, 2, SQL_DESC_NAME, "@useInflectional",

SQL_NTS);

SQLSetDescField(ipd, 3, SQL_DESC_NAME, "@useThesaurus",

SQL_NTS);

SQLSetDescField(ipd, 4, SQL_DESC_NAME, "@language", SQL_NTS);

/* Execute the prepared statement */

SQLExecute(stmt);

/* Retrieve the parameter names */

SQLGetDescField(ipd, 1, SQL_DESC_NAME, param_name_1,

sizeof(param_name_1), NULL);

SQLGetDescField(ipd, 2, SQL_DESC_NAME, param_name_2,

sizeof(param_name_2), NULL);

SQLGetDescField(ipd, 3, SQL_DESC_NAME, param_name_3,

sizeof(param_name_3), NULL);

SQLGetDescField(ipd, 4, SQL_DESC_NAME, param_name_4,

sizeof(param_name_4), NULL);

printf( "Procedure Input\n");

printf( "===============\n");

printf( "%s value:\t%s\n", param_name_1, search_string);

printf( "%s value:\t%d\n", param_name_2, use_inflectional);

printf( "%s value:\t%d\n", param_name_3, use_thesaurus);

printf( "%s value:\t%d\n", param_name_4, language);

printf( "\nProcedure Output\n");

printf( "===============\n");

/* Retrieve the column names for the procedure result set */

SQLColAttribute (stmt, 1, SQL_DESC_NAME, col_1_name,

sizeof(col_1_name), &col_1_name_length, 0);

SQLColAttribute (stmt, 2, SQL_DESC_NAME, col_2_name,

sizeof(col_2_name), &col_2_name_length, 0);

printf( "%s\t\t%s\n", col_1_name,col_2_name);

/* Fetch the procedure result set. */

while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {

printf( "%d\t\t\t%d\n", col_1, col_2);

}

/* Free up allocated handles and disconnect from the driver */

SQLFreeHandle(SQL_HANDLE_STMT, stmt);

SQLDisconnect(dbc);

SQLFreeHandle(SQL_HANDLE_DBC, dbc);

SQLFreeHandle(SQL_HANDLE_ENV, env);

}

When run, the sample produces output similar to that shown in the following shell session:

$ cc -I/usr/local/easysoft/unixODBC/include/ uspSearchCandidateResumes.c -o uspSearchCandidateResumes -L/usr/local/easysoft/unixODBC/lib/ -lodbc

$ chmod +x ./uspSearchCandidateResumes

$ ./uspSearchCandidateResumes

Procedure Input

===============

@searchString value: ISO9000

@useInflectional value: 0

@useThesaurus value: 0

@language value: 0

Procedure Output

===============

JobCandidateID RANK

1 9

7 9

10 12

SQL Server Authentication Modes

Users are granted access to SQL Server instances through a SQL Server login. SQL Server provides two ways to authenticate SQL Server logins: Windows Authentication (also known as trusted connections) and SQL Server Authentication.

Windows Authentication allows users to connect to SQL Server by using their Windows user account. SQL Server uses the Windows security system to validate these trusted connections.

SQL Server authentication uses passwords stored in SQL Server to validate the connection.

Windows Authentication is Microsoft's recommended SQL Server authentication mode because it provides the following advantages:

The Easysoft ODBC-SQL Server Driver supports both Windows Authentication and SQL Server Authentication. You specify the SQL Server login name and password with the User and Password data source attributes or the UID and PWD connection string attributes.

Windows Authentication

The Easysoft ODBC-SQL Server Driver asks SQL Server to use Windows Authentication to validate the connection if:

¯ OR ¯

The Easysoft ODBC-SQL Server Driver passes the domain name, the user name and password to SQL Server in an encrypted form. This process involves both the Data Encryption Standard (DES) encryption method and the MD4 hashing algorithm. The Easysoft ODBC-SQL Server Driver uses open source code for both these methods. The code is distributed under the terms of the GNU Lesser General Public License (LGPL). To read the license, see /usr/local/easysoft/sqlserver/crypt/COPYING.

To comply with the terms of the LGPL, the encryption functions are not included in the main Easysoft ODBC-SQL Server Driver library. Instead, they are provided in the shared library file /usr/local/easysoft/lib/libestdscrypt.so. The Easysoft ODBC-SQL Server Driver distribution includes the source files for this library. The source files are installed in /usr/local/easysoft/sqlserver/crypt. The supplied Makefile will build the library on your Easysoft ODBC-SQL Server Driver platform.

SQL Server Authentication

If the User attribute value does not contain a backslash, the Easysoft ODBC-SQL Server Driver asks SQL Server to use SQL Server Authentication to validate the connection. The Easysoft ODBC-SQL Server Driver sends the password to SQL Server in an encrypted form, although the encryption is less strong then that used for trusted connections. The SQL Server user name is sent in plain text.

Encrypting Connections to SQL Server

SQL Server 2000 and latercan use Secure Sockets Layer (SSL) to encrypt data transmitted across a network between an instance of SQL Server and a client application.

The Easysoft ODBC-SQL Server Driver with SSL Support lets Linux and Unix applications access SQL Server 2000 and later over an encrypted connection. The SSL version of driver is included in the Easysoft ODBC-SQL Server Driver distribution and should be used instead of the standard Easysoft SQL Server driver whenever an SSL connection is required.

In this section

Accessing SQL Server over an Encrypted Connection

Read this topic if you need to connect to a SQL Server instance over an encrypted connection. Database administrators should refer first to Configuring and Testing SSL Encryption for information about setting up SSL encryption on the client and SQL Server machine.

Before following the steps in this topic, contact your database administrator for the following information:

To access SQL Server over an encrypted connection

1.  In /etc/odbc.ini, find the SQLSERVER_SAMPLE_SSL data source.

2.  Edit the data source to connect to your SQL Server instance. For example:

[SQLSERVER_SAMPLE_SSL]

Driver = Easysoft ODBC-SQL Server SSL

Description = Easysoft SQL Server ODBC driver

Server = MYSQLSERVERMACHINE\MYINSTANCE

Port =

Database =

User = MYDOMAIN\myuser

Password = mypassword

.

.

.

 For more information about configuring ODBC data sources, see Setting Up Data Sources on Unix.

3.  If SSL encryption is enabled on the SQL Server instance, set the Encrypt attribute to No, and then skip to step 5. Otherwise, skip this step.

4.  Do one of the following:

5.  Use isql to test the data source. For example:

 cd /usr/local/easysoft/unixODBC/bin.

 ./isql -v SQLSERVER_SAMPLE_SSL

Configuring and Testing SSL Encryption

Refer to this section if you are a database administrator who needs to configure and test the Easysoft ODBC-SQL Server Driver with SSL Support. The section shows you how to configure the driver to request an encrypted connection and verify that data is encrypted.

The section also contains information about setting up SSL encryption on the SQL Server machine. This information is intended to supplement rather than replace the Microsoft SQL Server documentation.

Installing an SSL Certificate

Before you can access SQL Server 2000 over an encrypted connection, an SSL certificate needs to be installed on the SQL Server machine. SQL Server 2005 or later can use an SSL certificate from a trusted CA if available or generate a self-signed certificate.


Note

Even though SQL Server 2005 or later can make encryption available without an installed SSL certificate, Microsoft recommend using a certificate signed by a trusted authority whenever possible. SSL connections that are encrypted with a self-signed certificate protect against packet sniffing but do not protect against man-in-the-middle attacks. In a man-in the-middle attack, attackers route packets through their servers, which sniff the contents as they pass through.


If an SSL certificate has not yet been installed on the SQL Server machine, obtain a certificate from a certificate vendor that meets the following requirements:

http://support.microsoft.com/kb/318605

http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx

Refer to the following Microsoft documentation for installation information:

http://support.microsoft.com/kb/316898

http://msdn.microsoft.com/en-us/library/ms189067.aspx

Testing That SSL is Available on the SQL Server Machine

The following steps show how to check that SQL Server can successfully load the SSL certificate or generate its own certificate.

To check that SSL encryption is available on the SQL Server

1.  Do one of the following:

2.  Restart the instance.

3.  Check the SQL Server error log (drive:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG, by default) to verify that SQL Server did not report any errors when it started.


Note

You can verify that SQL Server has successfully generated a self-signed SSL certificate by checking the SQL Server error log for a line containing:

A self-generated certificate was successfully loaded for encryption.

If SQL Server is unable to generate a self-signed certificate, you will be unable to connect to the instance over an encrypted connection. Note that when testing SSL with SQL Server Express, we had to change the account used by the SQL Server instance from Network Service to Local System before the instance could generate a certificate.


Installing the Root CA Certificate

If encryption is enabled on the SQL Server machine, the client machine does not have to trust the CA that signed the SSL certificate used by the instance, and you can skip this section.

If you request encryption from the client machine rather than the SQL Server machine, the Easysoft ODBC-SQL Server Driver with SSL Support must be able to verify the ownership of the certificate used by the SQL Server instance. (Unless the SQL Server 2005 or later instance is using a self-generated certificate, in which case you should use TrustServerCertificate attribute to bypass the verification process.) If the server certificate was signed by a public or private CA for which the client machine does not have the public key certificate, you must install the public key certificate of the CA that signed the server certificate. If the client machine already has the public key certificate of the CA that signed the server certificate, this step is not necessary.

To install the root CA certificate on the client machine

1.  On the SQL Server machine, in the Windows Run dialog box, type:

 mmc

2.  In Microsoft Management Console, on the File Menu, click Add/Remove Snap-in, and then click Add.

3.  In the Add Standalone Snap-In dialog box, double-click Certificates. Click Computer account when prompted and then click Next. Click Finish.

4.  Click Close and then OK to close the Add/Remove Snap-in dialog boxes.

5.  In the Certificates Snap-in, locate the certificate for the CA that signed the SQL Server certificate. For example, if the CA certificate is in the Trusted Root Certificate Authorities store, double-click Trusted Root Certificate Authorities and click Certificates. In the right pane of the console window, right-click the CA certificate, point to All Tasks, and then click Export.

6.  Complete the Certificate Export wizard.

 When prompted to choose the export format, make sure that you choose Base-64 encoded X.509.

7.  Use FTP to copy the exported CA certificate to the client machine from which you want to access SQL Server.

Configuring the Client to Request an Encrypted Connection

SSL encryption can be enabled either on the SQL Server machine or the client machine. If you do not want to enable encryption globally on the SQL Server machine, you can enable encryption on a per-client basis.

To configure the client to request an encrypted connection

1.  Do one of the following:

2.  On the machine where the Easysoft ODBC-SQL Server Driver with SSL Support is installed, create an ODBC data source to connect to the SQL Server instance.

 In your data source, the Driver attribute must be set to Easysoft ODBC-SQL Server SSL.

 Set the Encrypt and TrustServerCertificate attributes to No. For example:

 [SQLSERVER_SSL_CONNECTION]

 Driver = Easysoft ODBC-SQL Server SSL

 Server = MYSQLSERVERMACHINE\MYINSTANCE

 User = MYDOMAIN\myuser

 Password = mypassword

 Encrypt = No

 TrustServerCertificate = No

3.  Use an application such as Microsoft Network Monitor, Snort or Ethereal to capture network traffic between this machine and the SQL Server machine.

 Using Network Monitor or a network sniffer tool lets you verify that you have successfully made an encrypted connection to the SQL Server machine. When testing the Easysoft driver, we used Network Monitor on the SQL Server machine and Snort on the client machine to capture network traffic.

 For information about using Network Monitor, see the Microsoft Knowledge Base article How to capture network traffic with Network Monitor ( http://support.microsoft.com/kb/148942/EN-US/).

4.  Use isql to connect to the data source and retrieve some data. For example:

 $ cd /usr/local/easysoft/unixodbc/bin

 $ ./isql -v SQLSERVER_SSL_CONNECTION

 SQL> select * from HumanResources.EmployeePayHistory where EmployeeID = 1

5.  In your network sniffer, verify that the data returned by the Easysoft ODBC-SQL Server Driver with SSL Support is not encrypted.

 This fragment of example output shows unencrypted data captured on the client machine by running snort -vde:

 8.E.m.p.l.o.y.e

 e.I.D.......=.R

 a.t.e.C.h.a.n.g

 e.D.a.t.e......

 <.R.a.t.e......

 0.P.a.y.F.r.e.q

 u.e.n.c.y......

 =.M.o.d.i.f.i.e

 d.D.a.t.e......

6.  Press RETURN to exit isql.

7.  In your data source, set the Encrypt attribute to Yes.

8.  Do one of the following:

 If the CA's public key was already installed on this machine, specify the path to the CA store that contains the public key. For example, CertificateFile = /usr/share/ssl/certs/ca-bundle.crt. If you exported the CA certificate on the SQL Server machine and copied it to this machine, specify the path to the certificate file. For example, CertificateFile = /usr/share/ssl/CA/MyCA.cer. For more information, see Installing the Root CA Certificate.

9.  Use isql to connect to the data source and retrieve the same data as you did in step 4.

10.  In your network sniffer, verify that the data returned by the Easysoft ODBC-SQL Server Driver with SSL Support is now encrypted.

 This example output shows encrypted SQL Server data captured in Snort.

 .E..{i.2.8.G.q..

 .n..{X.... 4..O.

 &....Lt..Z.wrH.8

 .W..{..........,

 ....1s_..).\k.6.

 ..4U..4..D...5.U

 &...I......+..w.

 l.W...&}x.......

 ....%......7...J

 ..C$...,j..52.~.

 .w. Q.qE.Q....]4

 .\.Y?...|R.VOr.S

 .....K.W.. 2.#.T

 .G..+..F.....T..

 @"..+-.......

Encrypting the Login Packet

When connecting to SQL Server, the Easysoft driver passes a user name and password to the SQL Server instance. For Windows user names, a domain name is also sent. These authentication details are stored inside a login packet that is transmitted between the Easysoft driver and SQL Server.

SQL Server 2005 or later will use SSL to encrypt the login packet, if you connect with the Easysoft ODBC-SQL Server Driver with SSL Support. Unless either the client or the server instance requests encryption, the connection is not encrypted beyond the login packet.


Note

For Windows logins, SQL Server transmits the domain and user name in plain text in the response to the login packet. If you do not want this information sent in plain text, you need to enable encryption either on the SQL Server or Easysoft ODBC-SQL Server Driver with SSL Support machine.


Easysoft ODBC-SQL Server Driver with SSL Support Attribute Fields

The following attributes may be set in the odbc.ini file:

Attribute Description

Encrypt = Yes | No

Whether the client requests an encrypted connection to SQL Server.

If you do not want to enable SSL encryption globally on the server, you can enable SSL encryption on a per client basis. To do this, set Encrypt to Yes.

Do not enable SSL encryption on both the server and client, use one or the other.

TrustServerCertificate = Yes | No

Enables the client to request encryption even when an SSL certificate has not been installed on the SQL Server 2005 or later machine.

If SQL Server cannot load a valid SSL certificate at startup time, it will generate a self-signed certificate to make encryption available. When the client requests encryption by setting Encrypt to Yes, the Easysoft ODBC-SQL Server Driver with SSL Support tries to validate the server certificate to verify the identity of the server machine. This is impossible to do with a self-signed certificate since it has not been signed by a trusted root authority. Setting TrustServerCertificate to Yes overrides the server validation.

If the SQL Server ForceEncryption option is enabled, the TrustServerCertificate value is ignored. When encryption is enabled on the SQL Server machine, the Easysoft ODBC-SQL Server Driver with SSL Support bypasses the validation of the server certificate.

Note that SQL Server 2000 cannot generate a self-signed certificate. SSL encryption is only available if the SQL Server 2000 instance is running on a computer that has a certificate assigned from a public certification authority.

By default, TrustServerCertificate is ON (set to Yes).

CertificateFile = filename

The file that contains the public key certificate of the CA that signed the SQL Server certificate. The CA certificate file must be in base-64 PEM format.

If the CA certificate is not installed on your client machine, you need to export the certificate on the SQL Server machine and install it on the client. For more information, see Installing the Root CA Certificate.

Examples

To load a CA certificate from the root CA certificate store supplied with the OpenSSL distribution, use:

CertificateFile = /usr/share/ssl/certs/ca-bundle.crt

To load a private CA certificate named MyCA.cer that you copied to /usr/share/ssl/CA, use:

CertificateFile = /usr/share/ssl/CA/MyCA.cer

Cypher = value

The cypher suite that the Easysoft ODBC-SQL Server Driver with SSL Support will request during the SSL handshake with the SQL Server machine.

A cypher suite is a set of authentication, encryption, and data integrity algorithms used to protect data exchanged between machines. During the SSL handshake part of the connection process, the SSL layer in the ODBC driver and the Schannel layer on the SQL Server machine negotiate to decide which cipher suite they will use.

To see which cypher suite is being used for a particular connection, enable Easysoft ODBC-SQL Server Driver with SSL Support logging. To do this, include these lines in your ODBC data source:

LOGFILE = /tmp/sql-server-driver.log

LOGGING = Yes

Connect and then examine the driver log file. Look for a log file entry similar to:

SSL using cypher 'RC4-MD5 SSLv3 Kx=RSA Au=RSA Enc=RC4(128) Mac=MD5'

This entry shows that the ODBC driver and the SQL Server machine negotiated the following cryptographic protection for the connection:

Encryption: RC4

Encryption strength: 128-bit

Cryptographic checksum: MD5

Authentication: RSA

 

(You can also display the cryptographic settings negotiated during the SSL handshake by enabling Schannel logging. Enable the "Log informational and success events" Schannel logging option to write this information to the Windows Event Viewer logs. For information about how to do this, see http://support.microsoft.com/kb/260729.)

Use the Cypher setting, if you want to request a different encryption or data integrity algorithm to the ones negotiated during the SSL handshake. For example:

# Request Triple DES (3DES) for data encryption.

# Request Secure Hash Algorithm (SHA) for data

# integrity protection.

Cypher = 3DES+SHA

¯ OR ¯

# Request Advanced Encryption Standard (AES) for data

# encryption. If AES is not available on the server,

# request 3DES.

Cypher = AES:3DES

¯ OR ¯

# Use Secure Hash Algorithm (SHA) to protect data

# integrity. Let the SSL layers negotiate which

# encryption algorithm to use.

Cypher = SHA

If you specify a cypher suite that is not available on the server machine, the Easysoft ODBC-SQL Server Driver with SSL Support returns the error "Required SSL (failed to receive packet)".

If you specify a cypher suite that the Easysoft ODBC-SQL Server Driver with SSL Support does not recognise, the driver returns the error "SSL3_CLIENT_HELLO:no ciphers available".

 

(For a complete list of valid Cypher values, see http://www.openssl.org/docs/apps/ciphers.html.)

Note that if you are connecting to a SQL Server instance that is running in FIPS 140-2 compliance mode, the remote Schannel layer will insist that the driver uses the appropriate cypher suite. There is no need to use the Cypher setting in this situation.

Federal Information Processing Standard (FIPS) is a U.S. government standard that defines security requirements for cryptographic modules. For more information about SQL Server and FIPS, see http://support.microsoft.com/kb/920995.

Entropy = filename

The Easysoft ODBC-SQL Server Driver with SSL Support needs a source of unpredictable data to work correctly. Many open source operating systems provide a "randomness device" (/dev/urandom or /dev/random) that serves this purpose. The Easysoft ODBC-SQL Server Driver with SSL Support tries to use /dev/urandom by default and will also try to use /dev/random if /dev/urandom is not available.

If the driver is unable to find a suitable randomness device, it will return the error "SSL connection failed in syscall (errno=2, there may be no source of entropy on this system, consult OpenSSL documentation)".

On systems without /dev/urandom or /dev/random, the EGD entropy gathering daemon can be used as an alternative source of random data. It provides a socket interface through which entropy (randomness) can be gathered. Use the Entropy attribute to specify the path to the EGD socket. For example, if you create the socket in /etc when you start the EGD daemon (egd.pl /etc/entropy), use :

Entropy = /etc/entropy

Figure 13: Easysoft ODBC-SQL Server Driver with SSL Support data source settings.

Database Mirroring

Database mirroring is a feature introduced in SQL Server 2005 that increases data availability by creating a standby copy of a database. In database mirroring, all updates to a database (the principal database) are automatically copied to a standby database (the mirror database). If the principal database server fails, the mirror database server takes over the role of principal server and brings its copy of the database online as the principal database.

For example, Partner_A and Partner_B are two partner servers, with the principal database initially on Partner_A as principal server, and the mirror database residing on Partner_B as the mirror server. If Partner_A goes offline, the database on Partner_B can fail over to become the current principal database. When Partner_A rejoins the mirroring session, it becomes the mirror server and its database becomes the mirror database.

In this section

Making the Initial Connection to a Database Mirroring Session

To establish the initial connection to a mirrored database, a data source needs to supply the current principal server instance (known as the initial partner). Optionally, the data source can also supply the current mirror server instance (known as the failover partner). This setting is used to connect to the mirror server if the initial connection to the principal server fails. The data source must also supply the database name. The Easysoft ODBC-SQL Server Driver will not attempt to failover to the partner database if this is not done.

In the following example data source, the principal server instance for the AdventureWorks database is 123.34.45.56:4724. The database is mirrored on 123.34.45.57:4724.

 [SQL Server 2005 Database Mirroring]

 Driver = Easysoft ODBC-SQL Server

 # The current principal server instance.

 Server = 123.34.45.56:4724

 # The current mirror server instance. If the initial attempt to

 # connect the principal server fails, try to connect to this server.

 Failover_Partner = 123.34.45.57:4724

 # You must specify the database to be mirrored.

 Database = AdventureWorks

 # This login must have permission to access the database on both

 # the principal and mirror database server.

 User = my_domain\my_username

 Password = my_password

For more information about setting up a data source for a mirrored database, see Data Source Attributes for a Mirrored Database.

When attempting to connect, the Easysoft ODBC-SQL Server Driver begins by using the initial partner name. If the specified server instance is available and is the current principal server instance, the connection attempt usually succeeds.

If the connection attempt to the initial partner fails, the Easysoft ODBC-SQL Server Driver tries the failover partner name, if specified. If the failover partner name is not specified, the original connection attempt continues until the network connection times out or an error is returned (just as for a non-mirrored database).

If the Failover_Partner attribute correctly identifies the current principal server, the Easysoft ODBC-SQL Server Driver normally succeeds in opening the initial connection.


Note

A database mirroring session does not protect against server-access problems that are specific to client machines, such as when a client machine is having a problems communicating with the network. A connection attempt to a mirrored database can also fail for a variety of reasons that are unrelated to the Easysoft ODBC-SQL Server Driver; for example, a connection attempt can fail because of a network error.


Data Source Attributes for a Mirrored Database

This section discusses the ODBC data source attributes that are relevant for connecting to a mirrored database. For information about all Easysoft ODBC-SQL Server Driver data source attributes, see Attribute Fields.



Attribute Notes

Server

Use the Server attribute to specify the current principal database server. You can use the following format for the attribute value: machinename[\instancename]. For example:

Server=partner_A_host

¯ OR ¯

Server=partner_A_host\instance_2

Note that when you specify a machine name, a DNS lookup is necessary to obtain the IP address of the server. In addition, if you specify an instance that is not listening on the default TCP port (1433), a SQL Server Browser query is also required. These lookups and queries can be bypassed by specifying the IP address and port number of the initial partner. This is recommended to minimise the possibility of external delays while connecting to that partner. To specify the IP address and port, the Server attribute takes the following form: Server=ip_address:port (IPv4 format) or Server=ip_address*port (IPv6 format). If you specify an IPv6 address, you also need to set the IPv6 attribute to 1. For example:

# IPv4 address

Server=123.34.45.56:4724

¯ OR ¯

# IPv6 address (SQL Server 2005 or later)

Server=2001:4898:23:1002:20f:1fff:feff:b3a3*7022

IPv6 = 1

Database

The data source must specify the name of the mirrored database. To do this, use the Database attribute. This is necessary to enable failover attempts by the Easysoft ODBC-SQL Server Driver.

Failover_Partner

Use the Failover_Partner setting 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 failover partner specified by Failover_Partner. If the specified server is not acting as a failover partner, the connection is refused by the server. If you omit the Failover_Partner setting and the initial partner specified by Server is unavailable, the connection attempt will fail.

The Failover_Partner attribute value takes the form machinename[\instancename]. For example:

Server=partner_B_host\instance_2

Alternatively, the IP address and port number of the failover partner can be supplied. If the connection attempt to the initial partner fails, the attempt to connect to the failover partner will be then be freed from relying on DNS and SQL Server Browser queries.

To find out the current failover partner for a mirrored database, use tdshelper. For information about how to do this, see The Impact of a Stale Failover Partner Name.

User

The login that you specify with the User attribute must have permission to access the database on the principal and mirror database server. Otherwise, you will be unable to access the database if the principal role switches and the former mirror server offers its database as the principal database.

Figure 14: Data source attributes for a mirrored database

Connection Retry Algorithm

When you specify a failover partner with the Failover_Partner attribute, connection attempts are regulated by a connection retry algorithm that is specific to database mirroring. The connection retry algorithm determines the maximum time (the retry time) allotted for opening a connection in a given connection attempt.

If a connection attempt fails or the retry time expires before it succeeds, the Easysoft ODBC-SQL Server Driver tries the other partner. If a connection is not opened by this point, the Easysoft driver alternately tries the initial and failover partner names, until a connection is opened or the login period times out. The default SQL Server login timeout period is 15 seconds.

The retry time is a percentage of the login period. The retry time for a connection attempt is larger in each successive round. In the first round, the retry time for each of the two attempts is 8 percent of the total login period. In each successive round, the retry algorithm increases the maximum retry time by the same amount. For example, the retry times for the first six connection attempts is as follows:

8%, 8%, 16%, 16%, 24%, 24%

The retry time is calculated by using the following formula:

RetryTime = PreviousRetryTime + ( 0.08 * LoginTimeout )

Where PreviousRetryTime is initially 0. For example:

Round Retry Per Attempt

1

0 + (0.08 * 15000) = 1200 msec

2

1200 + (0.08 * 15000) = 2400 msec

3

2400 + (0.08 * 15000) = 3600 msec

The Impact of a Stale Failover Partner Name

The database administrator can change the failover partner at any time. Therefore, a failover partner name specified in a data source might be out of date, or stale. For example, consider a failover partner named Partner_B that is replaced by another server instance, Partner_C. If the Easysoft ODBC-SQL Server Driver supplies Partner_B as the failover partner name, that name is stale. When the failover partner name is stale, the connection attempt will fail if the initial partner specified in the data source is unavailable.

To find out the current failover partner for a mirrored database, use tdshelper:

tdshelper -s initial_partner -p port -u username -a password -f database -v

where:

If the principal server instance reports the name of the failover partner, tdshelper displays the partner instance name in the last line of its output. For example:

cd /usr/local/easysoft/sqlserver/bin

./tdshelper -s my_initial_partner -u myuser -a mypassword -f my_mirroreddb -v

tdshelper: connecting to my_initial_partner

tdshelper: successfully opened connection

tdshelper: successfully logged into server with diagnostic records

tdshelper: diag record 01000:[Easysoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english.

tdshelper: diag record 01000:[Easysoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'my_mirroreddb'.

Connection: connected to my_initial_partner as myuser with mypassword, database='my_mirroreddb', partner='my_failover_partner'

Connection Failover

Connection failover maintains data availability by allowing an application to connect to a backup SQL Server machine if the primary server is unavailable.

To configure connection failover, specify a primary server and additional fallback servers in your ODBC data source. Do this with the Server attribute. For example:

 [SQL Server High Availability]

 Driver = Easysoft ODBC-SQL Server

Server = sqlsrvhostA,sqlsrvhostB,sqlsrvhostC:1583

 # This user name and password must be valid for all servers in the list.

 User = my_domain\my_user

 Password = my_password

 ClientLB = 0

By default, the Easysoft ODBC-SQL Server Driver will try to connect to the first server that you specify. If that server is unavailable (for example, because of a hardware or operating system failure), the Easysoft ODBC-SQL Server Driver will try to connect to next server in the list. Connection attempts continue until a connection is successfully made or until all the database servers in the list have been tried once.

To balance the load between database servers, set the ClientLB attribute to 1. When ClientLB is enabled, the server that the driver initially connects to is chosen at random.

Note that your SQL Server login (as specified by User and Password) needs to be valid on each SQL Server machine in the list. If the Easysoft ODBC-SQL Server Driver is unable to connect because SQL Server rejects the login information, the driver displays an error and does not try to connect to the next server in the list.

For more information about the Server and ClientLB attributes, see Attribute Fields.

Connecting to SQL Server 2005 or Later by Using IPv6

Internet Protocol version 6 (IPv6) is a revised version of the Internet Protocol (IP) designed primarily to address growth on the Internet. It is sometimes referred to as Internet Protocol Next Generation (IPng). The current version of IP, IP version 4 (IPv4), has proven to be robust but is over 20 years old and was not designed to support such widespread use as it does today.

The features of IPv6 include:

IPv6 support was introduced in SQL Server 2005. Currently, SQL Server supports IPv6 on Windows XP (Service Pack 2), Windows Server 2003, Windows Vista and Windows Server 2008.

On Windows XP and 2003 Server, IPv6 is a separate component. Windows Vista and Windows Server 2008 provide an integrated IPv4 and IPv6 implementation known as the Next Generation TCP/IP stack, which is installed and enabled by default.

IPv4 and IPv6 coexist in all Windows IP implementations. Windows Vista will also run in IPv6-only mode, which means the Vista machine will only handle IPv6 traffic and is only assigned IPv6 addresses.

The Easysoft ODBC-SQL Server Driver supports both IPv4 and IPv6. It can therefore be used to connect to:

Configuring Your Client Machine for IPv6

IPv6 needs to be enabled on the client machine. The procedure for this depends on the client platform. For more information, consult the IPv6 documentation for your system. For Linux systems, consult the http://www.tldp.org/HOWTO/Linux+IPv6-HOWTO/.

Configuring Your ODBC Data Source for IPv6

To connect to a SQL Server instance that is listening on an IPv6 address, set the IPv6 data source attribute to 1. If your DNS server or hosts file is set up to resolve IPv6 addresses, in the Server attribute value, specify the IPv6-enabled machine's host name. Otherwise, specify its IPv6 address. For example:

Server=myipv6machine\myinstance

IPv6 = 1

¯ OR ¯

Server=ABCD:EF12:0:0:0:0:0:3456\myinstance

IPv6 = 1

The Easysoft ODBC-SQL Server Driver supports normal and compressed IPv6 addresses. Compressed format is a short form that replaces consecutive leading zeros with two colons (::). For example, the IPv6 address shown in the previous example could be replaced with ABCD:EF12::3456.

The Easysoft ODBC-SQL Server Driver also supports the IPv4-mapped IPv6 address format, which is an IPv6 address that holds an embedded IPv4 address. For example, ::FFFF:192.168.19.46.

Threading

The Easysoft ODBC-SQL Server Driver is thread safe in accordance with the ODBC specification and can safely be used behind threaded applications.

Tracing

The ODBC calls an application makes can be traced:

Within the driver manager by an application

An application can turn tracing on in the Driver Manager by using the ODBC API SQLSetConnectAttr (...,SQL_ATTR_TRACE,...).

The trace file name may also be specified with the SQLSetConnectAttr attribute SQL_ATTR_TRACEFILE.

From within the driver manager

For the unixODBC Driver Manager, add two attributes to the [ODBC] section (create one if none exists) in odbcinst.ini.

Trace = Yes

TraceFile = logfile

For example:

[ODBC]

Trace = Yes

TraceFile = /tmp/unixodbc.log

Ensure that the user who is running the application to be traced has write permission to the log file (and to the directory containing it), or no tracing information will be produced.

From within the Easysoft ODBC-SQL Server Driver

Driver manager trace files show all the ODBC calls applications make, their arguments and return values. Easysoft ODBC-SQL Server Driver driver tracing is specific to the Easysoft driver and is of most use when making a support call.

To enable Easysoft ODBC-SQL Server Driver logging, add a LOGFILE and a LOGGING attribute to the relevant DSN section of the odbc.ini file.

For example:

[SQLSERVER_SAMPLE]

.

.

.

LOGFILE = /tmp/sql-server-driver.log

LOGGING = Yes

The LOGFILE value is the path and file name of the log file. The value shown in the example specifies a log file named /tmp/sql-server-driver.log. The LOGGING value specifies the actions to log. The value shown in the example specifies that all actions should be logged.

Ensure that the user who is running the application to be traced has write permission to the log file (and to the directory containing it).

By default, the Easysoft ODBC-SQL Server Driver appends log information to the file specified by LOGFILE. If you want the driver to generate a new log file for each ODBC session, enable logging on a per process basis. To do this, change the LOGGING entry to:

LOGGING = Process

When you set LOGGING to Process, the Easysoft ODBC-SQL Server Driver creates separate log files that only contain trace output related to a particular driver process. The log file name contains the ID of the driver process (and TDS process) that the log output is associated with. For example, /tmp/sql-server-driver.log.000076BF.B7D076B0.

To include additional information in ODBC error messages returned by the Easysoft ODBC-SQL Server Driver, add the following line to your data source:

OVERRIDE = 32

This Driver Manager log file extract shows the extra information output by setting the OVERRIDE attribute. The extra information is shown in bold text.

DIAG [08004] [Easysoft][SQL Server Driver 10.0][server='MYMACHINE\SQLEXPRESS_ADV',proc='',class=16,line=1,conn='mymachine\sqlexpress_adv']Database 'AdventureWorks' does not exist. Make sure that the name is entered correctly.