This section contains extra information relating to the deployment of the Easysoft ODBC-SQL Server Driver.
The Easysoft ODBC-SQL Server Driver complies with the ODBC 3.81 specification.
The Easysoft ODBC-SQL Server Driver is Level 2 compliant.
All ODBC 3.81 calls are supported.
The Easysoft ODBC-SQL Server Driver supports FORWARD_ONLY, KEYSET_DRIVEN, DYNAMIC and STATIC cursors.
The Easysoft ODBC-SQL Server Driver supports the following SQL Server data types:
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 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:
Whether to use Windows or SQL Server authentication to validate the connection.
|
Default. Use SQL Server Authentication to authenticate the connection. |
|
Windows authentication examples:
/* Use Windows Authentication to validate the connection */
SQLSetConnectAttr(dbc, SQL_COPT_SS_INTEGRATED_SECURITY,
/* 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,
SQLSetConnectAttr(dbc, SQL_COPT_SS_INTEGRATED_SECURITY,
SQLDriverConnect(dbc, NULL, "DSN=MYDSN",
SQL_NTS, outstr, sizeof(outstr), &outstrlen,
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:
Driver = Easysoft ODBC-SQL Server Driver
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.)
|
Default. Cursors are closed when a transaction is committed or rolled back by using SQLEndTran. |
|
|
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:
SQLHSTMT stmt_select, stmt_update;
SQLCHAR last_name[ 64 ], first_name[ 64 ], cursor_name[ 64 ], update_sql[ 64 ];
SQLSMALLINT reports_to, cursor_len;
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION,
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
/* Enable manual-commit mode */
SQLSetConnectAttr(dbc, SQL_ATTR_AUTOCOMMIT,
/* Preserve cursors when transactions are committed/rolled */
/* back. Alternatively, add PreserveCursor = Yes to the DSN */
SQLSetConnectAttr(dbc, SQL_COPT_SS_PRESERVE_CURSORS,
/* Connect to Northwind through the sample DSN */
"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);
"SELECT LastName, FirstName, ReportsTo FROM Employees FOR UPDATE",
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,
/* 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 */
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 */
"UPDATE Employees SET ReportsTo = 2 WHERE CURRENT OF %s",
SQLExecDirect(stmt_update, update_sql, SQL_NTS);
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 );
printf("%s %s reports to employee ID: %ld\n", first_name,
SQLCloseCursor(stmt_update); /* Close cursor */
SQLDisconnect(dbc); /* Disconnect from driver */
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
Sets the SQL Server 2005 or later snapshot isolation attribute.
|
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 Easysoft ODBC-SQL Server Driver supports the following driver-specific statement attributes:
Whether the Easysoft ODBC-SQL Server Driver defers query preparation until execution time.
The attribute is only relevant to SQLServer 2000 and later.
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.
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION,
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
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,
ret = SQLPrepare(stmt, "select * from non_existent_table",
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
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);
/* 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,
/* 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) {
/* The errors in the statement are not known until this point */
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:
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:
Driver = /usr/local/easysoft/sqlserver/lib/libessqlsrv.so
Setup = /usr/local/easysoft/sqlserver/lib/libessqlsrvS.so
# 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
In your data source, specify the new driver name in the Driver entry. For example:
Driver = Easysoft ODBC-SQL Server ANSI APIs
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]
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
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
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:
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.
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.
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] [-O] -useNOSSL
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.
The name of the schema to which table_name or view_name belongs.
The name of the destination table when importing data into SQL Server (in), and the source table when exporting data from SQL Server (out).
The name of the destination view when importing data into SQL Server (in), and the source view when exporting data from SQL Server (out).
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.
The direction of the bulk copy:
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).
The maximum number of times that bcp can return SQL_ERROR when bulk copying data from a query before the bcp operation is cancelled.
The full path to a format file:
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.
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).
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.
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.
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.
Use native (database) data types when importing and exporting data. This option does not prompt for each field; it uses the native values.
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.
Use Unicode character format when importing and exporting character data. Use native format when importing and exporting non-character data.
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.
The version of SQL Server that bcp is connecting to, where:
110 = SQL Server 2012 and above
Executes the SET QUOTED_IDENTIFIERS ON statement in the connection between the bcp and an instance of SQL Server.
Specifies the code page of the data in the data file.
The field terminator. The default is \t (tab character).
The row terminator. The default is \n (newline character).
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:
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.
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.
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.
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.
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.
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.
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."
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.
Use Windows authentication to validate the connection. If this argument is specified, login must be a Windows user name.
Reports the bcp version number.
Empty columns retain a null value during an import, rather than have any default values for the columns inserted.
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.
Set the GSSFlag attribute via bcp. For more information, see the GSSFlag attribute in "Configuration" on page 86.
Connect via an ODBC connection string. For example, bcp myTable out my.dat -O "DSN=SQLSERVER_SAMPLE".
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):
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
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
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.
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.
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.
Approximate number of kilobytes of data per batch. By default, KILOBYTES_PER_BATCH is unknown.
A bulk update table-level lock is acquired for the duration of the bulk load operation; otherwise, a row-level lock is acquired.
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.
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.
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.
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
You should not normally need to include this flag. See the EncryptDeny flag in Encrypting Connections to SQL Server.
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.
Commits all rows previously bulk copied from program variables and sent to SQL Server by bcp_sendrow.
Is the bulk copy-enabled ODBC connection handle.
The number of rows saved after the last call to bcp_batch, or -1 in case of error.
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.
char* pPart1 = "Text chunk 1.";
char* pPart2 = "Text chunk 2.";
char* pPart3 = "Text chunk 3.";
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
/* 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");
/* 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");
/* 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");
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");
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");
/* Initialise the bulk copy. */
retcode = bcp_init(hdbc1, "articles", NULL, NULL, DB_IN);
printf("bcp_init(hdbc1) Failed.\n\n");
/* Bind program variables to table columns. */
retcode = (bcp_bind(hdbc1, (LPCBYTE) &idRow, 0, SQL_VARLEN_DATA, NULL, 0, SQLINT4, 1));
printf("bcp_bind(hdbc1) Failed.\n\n");
cbAllParts = strlen(pPart1) + strlen(pPart2) + strlen(pPart3);
retcode = (bcp_bind(hdbc1, NULL, 0, cbAllParts, NULL, 0, SQLTEXT, 2));
printf("bcp_bind(hdbc1) Failed.\n\n");
/* Now send this row, with the text value broken into three chunks. */
retcode = (bcp_sendrow(hdbc1));
printf("bcp_sendrow(hdbc1) Failed.n\n");
retcode = (bcp_moretext(hdbc1, cbAllParts, pPart1));
printf("bcp_moretext(hdbc1) Failed. 1\n\n");
retcode = (bcp_moretext(hdbc1, cbAllParts, pPart2));
printf("bcp_moretext(hdbc1) Failed. 2\n\n");
retcode = (bcp_moretext(hdbc1, cbAllParts, pPart3));
printf("bcp_moretext(hdbc1) Failed. 3\n\n");
nRowsProcessed = bcp_batch(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
Binds data from a program variable to a table column for bulk copy into SQL Server.
Is the bulk copy-enabled ODBC connection handle.
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.
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.
Is the count of bytes of data in the program variable, not including the length of any length or null indicator or terminator.
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.
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.
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.
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.
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.
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.
Is the bulk copy-enabled ODBC connection handle.
Is the ordinal column number in the user data file for which the format is being specified. The first column is 1.
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.
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.
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.
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.
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.
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.
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.
Sets the data length in the program variable for the current bulk copy into SQL Server.
Is the bulk copy-enabled ODBC connection handle.
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.
Is the ordinal position of the column in the table to which the data is copied. The first column is 1.
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.
char* pPart1 = "Text chunk 1.";
char* pPart2 = "Text chunk 2.";
char* pPart3 = "Text chunk 3.";
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
/* 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");
/* 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");
/* 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");
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");
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");
/* Initialise the bulk copy. */
retcode = bcp_init(hdbc1, "articles", NULL, NULL, DB_IN);
printf("bcp_init(hdbc1) Failed.\n\n");
/* Bind program variables to table columns. */
retcode = (bcp_bind(hdbc1, (LPCBYTE) &idRow, 0, SQL_VARLEN_DATA, NULL, 0, SQLINT4, 1));
printf("bcp_bind(hdbc1) Failed.\n\n");
cbAllParts = strlen(pPart1) + strlen(pPart2) + strlen(pPart3);
retcode = (bcp_bind(hdbc1, NULL, 0, cbAllParts, NULL, 0, SQLTEXT, 2));
printf("bcp_bind(hdbc1) Failed.\n\n");
retcode = (bcp_collen(hdbc1, 100, 1));
printf("bcp_collen(hdbc1) Failed.\n\n");
retcode = (bcp_colptr(hdbc1, NULL , 2));
printf("bcp_colptr(hdbc1) Failed.\n\n");
/* Now send this row, with the text value broken into three chunks. */
retcode = (bcp_sendrow(hdbc1));
printf("bcp_sendrow(hdbc1) Failed.n\n");
retcode = (bcp_moretext(hdbc1, cbAllParts, pPart1));
printf("bcp_moretext(hdbc1) Failed. 1\n\n");
retcode = (bcp_moretext(hdbc1, cbAllParts, pPart2));
printf("bcp_moretext(hdbc1) Failed. 2\n\n");
retcode = (bcp_moretext(hdbc1, cbAllParts, pPart3));
printf("bcp_moretext(hdbc1) Failed. 3\n\n");
nRowsProcessed = bcp_done(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
Sets the program variable data address for the current copy into SQL Server.
Is the bulk copy-enabled ODBC connection handle.
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.
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.
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.
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.
Is the bulk copy-enabled ODBC connection handle.
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.
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.
Changes the default settings for various control parameters for a bulk copy between a file and SQL Server.
Is the bulk copy-enabled ODBC connection handle.
Stops a bulk-copy operation that is already in progress.
Is the number of rows per batch.
If set to true, will cause bcp_readfmt to read at execution.
The version number of the data file format.
Is the first row of data to file or table to copy.
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.
Specifies that the format file generated should be in XML format. It is off by default.
SQL Server bulk-copy processing hints or a Transact-SQL statement that returns a result set.
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.
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.
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.
Is the number of errors allowed before the bulk copy operation fails.
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'}.
Returns the number of rows affected by the current (or last) BCP operation.
When TRUE, specifies that the data file is a text file, rather than a binary file.
When TRUE, specifies the input file is a Unicode file.
This function sets various control parameters for bulk-copy operations.
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
/* 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");
/* 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");
/* 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");
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");
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");
/* 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);
printf("bcp_init(hdbc1) Failed.\n\n");
/* Set the number of rows per batch */
retcode = (bcp_control(hdbc1, BCPBATCH, (void*) 1));
printf("bcp_control(hdbc1) Failed.\n\n");
retcode = (bcp_columns(hdbc1, 3));
printf("bcp_columns(hdbc1) Failed.\n\n");
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"));
printf("bcp_writefmt(hdbc1) Failed.\n\n");
retcode = (bcp_readfmt(hdbc1, "myFmtFile.fmt"));
printf("bcp_readfmt(hdbc1) Failed.\n\n");
retcode = (bcp_exec(hdbc1, &nRowsProcessed));
printf("bcp_exec(hdbc1) Failed. 1\n\n");
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
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.
Is the bulk copy-enabled ODBC connection handle.
The number of rows permanently saved after the last call to bcp_batch or -1 in case of error.
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.
Executes a complete bulk copy of data between a database table and a user file.
Is the bulk copy-enabled ODBC connection handle.
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.
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.
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.
SQLCHAR szPass[]="mypassword";
ret = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv );
printf( "SQLAllocHandle failed" );
ret = SQLSetEnvAttr( hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_UINTEGER );
printf( "SQLSetEnvAttr conn failed" );
ret = SQLAllocHandle( SQL_HANDLE_DBC, hEnv, &hDbc );
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);
printf("Connected successfully.\n");
printf("Connection failed.\n");
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);
printf("BCP bcp_init failed.\n");
ret = bcp_exec(hDbc, &nRowsProcessed);
printf("bcp_exec ret %d\n",ret);
printf("No rows processed\n");
printf("Only %d rows processed\n",nRowsProcessed);
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);
Used to find the column format property value.
Is the bulk copy-enabled ODBC connection handle.
Is the column number for which the property is retrieved.
Is one of the property constants.
Is the pointer to the buffer from which to retrieve the property value.
Is the length of the property buffer in bytes.
Pointer to length of the data that is being returned in the property buffer.
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.
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
/* 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");
/* 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");
/* 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");
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");
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");
/* 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);
printf("bcp_init(hdbc1) Failed.\n\n");
retcode = (bcp_columns(hdbc1, 3));
printf("bcp_columns(hdbc1) Failed.\n\n");
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"));
printf("bcp_writefmt(hdbc1) Failed.\n\n");
retcode = (bcp_getcolfmt(hdbc1));
printf("bcp_getcolfmt(hdbc1) Failed.\n\n");
retcode = (bcp_exec(hdbc1, &nRowsProcessed));
printf("bcp_exec(hdbc1) Failed. 1\n\n");
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
Returns the SQL type name for a specified BCP type token.
A value indicating a BCP type token.
Indicates if token requested is a MAX type.
A string containing the SQL type name corresponding to the BCP type. If an invalid BCP type is specified, an empty string is returned.
The BCP type tokens are defined in the /usr/local/easysoft/sqlserver/include/sqlncli.h header file.
Initialises the bulk copy operation.
Is the bulk copy-enabled ODBC connection handle.
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.
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.
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.
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.
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.
Sends part of a long, variable-length data type value to SQL Server.
Is the bulk copy-enabled ODBC connection handle.
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.
Is a pointer to the supported, long, variable-length data chunk to be sent to SQL Server.
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.
Reads a data file format definition from the specified format file.
Is the bulk copy-enabled ODBC connection handle.
Is the path and file name of the file containing the format values for the data file.
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.
Sends a row of data to SQL Server.
Is the bulk copy-enabled ODBC connection handle.
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.
char* pPart1 = "Text chunk 1.";
char* pPart2 = "Text chunk 2.";
char* pPart3 = "Text chunk 3.";
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
/* 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");
/* 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");
/* 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");
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");
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");
/* Initialise the bulk copy. */
retcode = bcp_init(hdbc1, "MyTable", NULL, NULL, DB_IN);
printf("bcp_init(hdbc1) Failed.\n\n");
/* Bind program variables to table columns. */
retcode = (bcp_bind(hdbc1, (LPCBYTE) &idRow, 0, SQL_VARLEN_DATA, NULL, 0, SQLINT4, 1));
printf("bcp_bind(hdbc1) Failed.\n\n");
cbAllParts = strlen(pPart1) + strlen(pPart2) + strlen(pPart3);
retcode = (bcp_bind(hdbc1, NULL, 0, cbAllParts, NULL, 0, SQLTEXT, 2));
printf("bcp_bind(hdbc1) Failed.\n\n");
/* Now send this row, with the text value broken into three chunks. */
retcode = (bcp_sendrow(hdbc1));
printf("bcp_sendrow(hdbc1) Failed.n\n");
retcode = (bcp_moretext(hdbc1, cbAllParts, pPart1));
printf("bcp_moretext(hdbc1) Failed. 1\n\n");
retcode = (bcp_moretext(hdbc1, cbAllParts, pPart2));
printf("bcp_moretext(hdbc1) Failed. 2\n\n");
retcode = (bcp_moretext(hdbc1, cbAllParts, pPart3));
printf("bcp_moretext(hdbc1) Failed. 3\n\n");
nRowsProcessed = bcp_done(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
bcp_setbulkmode lets you specify the column format in a bulk copy operation, setting all the column attributes in a single function call.
Is the bulk copy-enabled ODBC connection handle.
A constant of type BYTE. See the table in the Remarks section for a list of the constants.
The pointer to the field terminator value.
The pointer to the row terminator value.
The length in bytes of the row terminator value.
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.
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.
Is the bulk copy-enabled ODBC connection handle.
Is the ordinal column number for which the property is being set.
Is one of the property constants. Property constants are defined in this table
Is the pointer to the value to associate to the property. It allows each column format property to be set individually.
Is the length of the property buffer in bytes.
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.
Creates a format file containing a description of the format of the current bulk copy data file.
Is the bulk copy-enabled ODBC connection handle.
Is the path and file name of the user file to receive format values for the data file.
The format file specifies the data format of a data file created by bulk copy.
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.
SQLCHAR *TVP = (SQLCHAR *) "TVParam";
SQLINTEGER ProdCode[ITEM_ARRAY_SIZE], Qty[ITEM_ARRAY_SIZE];
/* 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);
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
* You will need to change mydsn to one you have created and tested
* The SQL Server database your DSN connects to needs:
* 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)
* 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 */
ret = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 5, 0, CustCode, sizeof(CustCode), &cbCustCode);
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 */
ret = SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT,SQL_C_LONG, SQL_INTEGER, 0, 0, &OrdNo,
sizeof(SQLINTEGER), &cbOrdNo);
ret = SQLBindParameter(hstmt, 4, SQL_PARAM_OUTPUT, SQL_C_CHAR,SQL_TYPE_TIMESTAMP, 23, 3, &OrdDate,
/* 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);
ret = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, ProdCode, sizeof(SQLINTEGER), cbProdCode);
ret = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, Qty, sizeof(SQLINTEGER), cbQty);
ret = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 0, SQL_IS_INTEGER);
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 */
ret = SQLExecDirect(hstmt, (SQLCHAR *) "{call TVPOrderEntry(?, ?, ?, ?)}",SQL_NTS);
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.
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. */
SQLCHAR col_1_name [ 64 ], col_2_name [ 64 ];
SQLSMALLINT col_2, col_1_name_length, col_2_name_length;
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)
/* 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 (?, ?, ?, ?)}",
/* Bind local variables to the parameters in the preceding */
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,
SQLBindParameter(stmt, 4, SQL_PARAM_INPUT, SQL_INTEGER,
SQL_INTEGER, 1, 0, &language, 0, 0);
/* Bind columns in the procedure result set to local */
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 */
SQLSetDescField(ipd, 1, SQL_DESC_NAME, "@searchString",
SQLSetDescField(ipd, 2, SQL_DESC_NAME, "@useInflectional",
SQLSetDescField(ipd, 3, SQL_DESC_NAME, "@useThesaurus",
SQLSetDescField(ipd, 4, SQL_DESC_NAME, "@language", SQL_NTS);
/* Execute the prepared statement */
/* Retrieve the parameter names */
SQLGetDescField(ipd, 1, SQL_DESC_NAME, param_name_1,
SQLGetDescField(ipd, 2, SQL_DESC_NAME, param_name_2,
SQLGetDescField(ipd, 3, SQL_DESC_NAME, param_name_3,
SQLGetDescField(ipd, 4, SQL_DESC_NAME, param_name_4,
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");
/* 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);
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
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.
The Easysoft ODBC-SQL Server Driver asks SQL Server to use Windows Authentication to validate the connection if:
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.
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.
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.
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:
Driver = Easysoft ODBC-SQL Server SSL
Description = Easysoft SQL Server ODBC driver
Server = MYSQLSERVERMACHINE\MYINSTANCE
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.
5. Use isql to test the data source. For example:
cd /usr/local/easysoft/unixODBC/bin.
./isql -v SQLSERVER_SAMPLE_SSL
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.
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.
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
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
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.
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:
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.
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
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:
Driver = Easysoft ODBC-SQL Server SSL
Server = MYSQLSERVERMACHINE\MYINSTANCE
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:
7. In your data source, set the Encrypt attribute to Yes.
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.
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.
The following attributes may be set in the odbc.ini file:
|
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. If you need to connect to SQL Server with strict encryption, set to Strict. Strict encryption was introduced in SQL Server 2022. In Strict encryption mode, TrustServerCertificate is ignored, the server certificate is checked and data sent between client and server is encrypted. (See also the HostNameInCertificate attribute.) |
|
|
Whether the client denies it supports encryption. You should not normally need to use this attribute. The attribute was added as a workaround for a customer who was getting the error "Client unable to establish connection: required SSL (failed to receive packet)". |
|
|
Specifies the hostname to be expected in the server certificate when encryption is negotiated. Use HostNameInCertificate if this hostname is different from the one specified with the Server attribute. |
|
|
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. |
|
|
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. 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: |
|
|
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 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: |
|
|
(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 # Request Advanced Encryption Standard (AES) for data # encryption. If AES is not available on the server, # Use Secure Hash Algorithm (SHA) to protect data # integrity. Let the SSL layers negotiate which # encryption algorithm to use. 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. |
|
|
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 : |
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.
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.
# 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.
# This login must have permission to access the database on both
# the principal and mirror database server.
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.
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.
|
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\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: # IPv6 address (SQL Server 2005 or later) |
|
|
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. |
|
|
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. |
|
|
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. |
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:
The retry time is calculated by using the following formula:
RetryTime = PreviousRetryTime + ( 0.08 * LoginTimeout )
Where PreviousRetryTime is initially 0. For example:
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
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 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.
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.
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.
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:
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/.
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
Server=ABCD:EF12:0:0:0:0:0:3456\myinstance
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.
The Easysoft ODBC-SQL Server Driver is thread safe in accordance with the ODBC specification and can safely be used behind threaded applications.
The ODBC calls an application makes can be traced:
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.
For the unixODBC Driver Manager, add two attributes to the [ODBC] section (create one if none exists) in odbcinst.ini.
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.
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.
LOGFILE = /tmp/sql-server-driver.log
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:
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:
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.
© 1993 - 2024 Easysoft Limited. All rights reserved. Oracle® is a registered trademark of Oracle® Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.
