About the Easysoft ODBC-SQL Server Driver
The Easysoft ODBC-SQL Server Driver provides real-time access to SQL Server data from any application that supports ODBC.
In this section:
ODBC API functions
Use this table to find out what ODBC API functions the Easysoft ODBC-SQL Server Driver supports:
| Function | Status |
|---|---|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Not supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
Cursor support
The Easysoft ODBC-SQL Server Driver supports FORWARD_ONLY, KEYSET_DRIVEN, DYNAMIC, and STATIC cursors.
Supported data types
The Easysoft ODBC-SQL Server Driver supports the following SQL Server data types:
-
BIGINT -
BINARY -
BIT -
CHAR -
DATE -
DATETIME -
DATETIME2 -
DATETIMEOFFSET -
DECIMAL -
FLOAT -
GEOGRAPHY -
GEOMETRY -
HIERARCHYID -
IMAGE -
INT -
MONEY -
NUMERIC -
REAL -
SMALLDATETIME -
SMALLINT -
SMALLMONEY -
SQL_VARIANT -
SYSNAME -
TEXT -
TIME -
TIMESTAMP -
TINYINT -
UNIQUEIDENTIFIER -
VARBINARY -
VARBINARY(MAX) -
VARCHAR -
VARCHAR(MAX) -
XML
The Easysoft ODBC-SQL Server Driver lets you insert, update, and delete FILESTREAM data by using SQL. SQL Server stores FILESTREAM data on the file system rather than in the database file. To specify that the data should be stored externally, the FILESTREAM column attribute must be set. The FILESTREAM attribute was introduced in SQL Server 2008, and applies to VARBINARY(MAX) columns.
|
The SQLGetTypeInfo function
SQL Server treats identity as an attribute, whereas ODBC treats it as a data type. To resolve this mismatch, SQLGetTypeInfo returns the data types: INT IDENTITY, SMALLINT IDENTITY, TINYINT IDENTITY, DECIMAL() IDENTITY, and NUMERIC() IDENTITY. The SQLGetTypeInfo result set column AUTO_UNIQUE_VALUE reports the value TRUE for these data types.
For VARCHAR, NVARCHAR, and VARBINARY data types, the Easysoft ODBC-SQL Server Driver continues to report 8000, 4000, and 8000 for the COLUMN_SIZE value, even though it is actually unlimited. This is to ensure backward compatibility.
For the XML data type, the Easysoft ODBC-SQL Server Driver reports SQL_SS_LENGTH_UNLIMITED for COLUMN_SIZE to denote unlimited size.
The SQLSetConnectAttr function
The Easysoft ODBC-SQL Server Driver supports a number of driver-specific ODBC connection attributes. These are defined in /usr/local/easysoft/sqlserver/include/sqlncli.h. The Easysoft ODBC-SQL Server Driver may require that an attribute be set prior to connection, or it may ignore the attribute if it is already set:
| Attribute | Set before or after connection to server |
|---|---|
|
Before |
|
Before |
|
Either |
SQL_COPT_SS_INTEGRATED_SECURITY
Whether to use Windows or SQL Server authentication to validate the connection.
| Value | Description |
|---|---|
|
Default. Use SQL Server authentication to authenticate the connection. |
|
Use Windows Authentication to authenticate the connection. |
Windows authentication examples:
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#include <sqlncli.h>
.
.
.
/* Use Windows Authentication to validate the connection */
SQLSetConnectAttr(dbc, SQL_COPT_SS_INTEGRATED_SECURITY, (void *) SQL_IS_ON, 0);
/* Specify a Windows user name and password. mywindowsuser belongs to the */
/* same domain as the SQL Server computer, so there is no need to specify it */
/* - the Easysoft ODBC-SQL Server Driver will automatically detect the domain */
SQLDriverConnect(dbc, NULL, "DRIVER={Easysoft ODBC-SQL Server;SERVER=myserver\\SQLEXPRESS;UID=mywindowsuser;PWD=mywindowspassword",
SQL_NTS, outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_COMPLETE);-Or-
SQLSetConnectAttr(dbc, SQL_COPT_SS_INTEGRATED_SECURITY, (void *) SQL_IS_ON, 0);
SQLDriverConnect(dbc, NULL, "DSN=MYDSN", SQL_NTS, outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_COMPLETE);The Easysoft ODBC-SQL Server Driver data source specified in the SQLDriverConnect call needs to connect with a Windows user name and password. For example:
[MYDSN] Driver = Easysoft ODBC-SQL Server Driver Server = myserver\SQLEXPRESS User = mywindowsuser Password = mywindowspassword
SQL_COPT_SS_PRESERVE_CURSORS
Whether the Easysoft ODBC-SQL Server Driver preserves cursors when SQLEndTran commits or rolls back a transaction.
(You can also configure this behaviour by using the PreserveCursor data source attribute.)
| Value | Description |
|---|---|
|
Default. Cursors are closed when a transaction is committed or rolled back by using |
SQL_PC_ON |
Cursors are preserved when a transaction is committed or rolled back by using |
This C code sample uses SQL_COPT_SS_PRESERVE_CURSORS to preserve a cursor following a positioned update:
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#include <sqlncli.h>
main() {
SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt_select, stmt_update;
SQLRETURN ret;
SQLCHAR last_name[64], first_name[64], cursor_name[64], update_sql[64];
SQLSMALLINT reports_to, cursor_len;
SQLLEN indicator[3];
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, & env);
/* We want ODBC 3 support */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void * ) SQL_OV_ODBC3, 0);
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, & dbc);
/* Enable manual-commit mode */
SQLSetConnectAttr(dbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0);
/* Preserve cursors when transactions are committed/rolled */
/* back. Alternatively, add PreserveCursor = Yes to the DSN */
SQLSetConnectAttr(dbc, SQL_COPT_SS_PRESERVE_CURSORS, (void * ) SQL_PC_ON, 0);
/* Connect to Northwind through the sample DSN */
SQLDriverConnect(dbc, NULL, "DSN=SQLSERVER_SAMPLE;Database=Northwind",
SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
/* Allocate the statement handles */
SQLAllocHandle(SQL_HANDLE_STMT, dbc, & stmt_select);
SQLAllocHandle(SQL_HANDLE_STMT, dbc, & stmt_update);
/* Create dynamic, updateable cursor for the positioned update */
SQLSetStmtAttr(stmt_select, SQL_ATTR_CURSOR_TYPE,
(void * ) SQL_CURSOR_DYNAMIC, 0);
SQLSetStmtAttr(stmt_select, SQL_ATTR_CONCURRENCY, (void * ) SQL_CONCUR_ROWVER, 0);
SQLExecDirect(stmt_select, "SELECT LastName, FirstName, ReportsTo FROM Employees FOR UPDATE", SQL_NTS);
SQLBindCol(stmt_select, 1, SQL_C_CHAR, last_name, sizeof(last_name), & indicator[0]);
SQLBindCol(stmt_select, 2, SQL_C_CHAR, first_name, sizeof(first_name), & indicator[1]);
SQLBindCol(stmt_select, 3, SQL_INTEGER, & reports_to, 0, & indicator[2]);
/* Get the cursor name for use in the update statement */
SQLGetCursorName(stmt_select, cursor_name, sizeof(cursor_name), & cursor_len);
/* Move through the result set until the cursor is positioned */
/* on the row for Robert King */
do
ret = SQLFetch(stmt_select);
while ((ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) &&
(strcmp(first_name, "Robert") != 0 && strcmp(last_name, "King") != 0));
/* Positioned update of Robert King's line manager */
sprintf(update_sql,
"UPDATE Employees SET ReportsTo = 2 WHERE CURRENT OF %s", cursor_name);
SQLExecDirect(stmt_update, update_sql, SQL_NTS);
/* Commit the transaction */
SQLEndTran(SQL_HANDLE_DBC, dbc, SQL_COMMIT);
/* The cursor is still open, because SQL_COPT_SS_PRESERVE_CURSORS is set to */
/* SQL_PC_ON. Reposition the cursor and fetch the updated record. */
SQLFetchScroll(stmt_select, SQL_FETCH_PRIOR, 0);
SQLFetch(stmt_select);
/* Display updated record */
printf("%s %s reports to employee ID: %ld\n", first_name, last_name, reports_to);
SQLCloseCursor(stmt_update); /* Close cursor */
SQLDisconnect(dbc); /* Disconnect from driver */
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}SQL_COPT_SS_TXN_ISOLATION
Sets the SQL Server snapshot isolation.
| Value | Description |
|---|---|
|
Indicates that from one transaction you cannot see changes made in other transactions and that you cannot see changes even when requerying. |
The SQLSetStmtAttr function
The Easysoft ODBC-SQL Server Driver supports the following driver-specific statement attributes:
SQL_SOPT_SS_DEFER_PREPARE
Whether the Easysoft ODBC-SQL Server Driver defers query preparation until execution time.
| Value | Description |
|---|---|
|
Default. After calling Any errors in the statement are not known until these functions are executed. |
|
The Easysoft ODBC-SQL Server Driver prepares the statement as soon as Any errors in the statement will cause the prepare to fail. |
In this C code sample, deferred statement preparation is turned off. The invalid SQL statement the sample contains therefore fails as soon as SQLPrepare is called.
#include <stdio.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>
#include <sqlncli.h>
main() {
SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;
SQLRETURN ret;
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, & env);
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION,
(void * ) SQL_OV_ODBC3, 0);
SQLAllocHandle(SQL_HANDLE_DBC, env, & dbc);
SQLDriverConnect(dbc, NULL, "DSN=SQLSERVER_SAMPLE",
SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
/* Allocate the statement handle */
SQLAllocHandle(SQL_HANDLE_STMT, dbc, & stmt);
/* Do not defer query preparation. Prepare the statement */
/* as soon as SQLPrepare is executed */
SQLSetStmtAttr(stmt, SQL_SOPT_SS_DEFER_PREPARE, (SQLPOINTER) SQL_DP_OFF, 0);
/* Invalid statement */
ret = SQLPrepare(stmt, "SELECT * FROM non_existent_table", SQL_NTS);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
ret = SQLExecute(stmt);
if (ret != SQL_SUCCESS || ret != SQL_SUCCESS_WITH_INFO) {
/* Because the invalid statement was prepared immediately, */
/* SQLPrepare (below) rather than SQLExecute returns the error. */
extract_error("SQLExecute", stmt, SQL_HANDLE_STMT);
}
} else {
/* The statement is invalid and so cannot be prepared. */
/* See "ODBC from C Tutorial Part 1", on the Easysoft web */
/* site for a definition of extract_error(). */
extract_error("SQLPrepare", stmt, SQL_HANDLE_STMT);
}
}Note that if the statement contains parameters, SQLPrepare returns SQL_SUCCESS even if the statement is invalid. Any errors in the statement are not known until the statement is executed or SQLDescribeParam is called. This behaviour happens regardless of how SQL_SOPT_SS_DEFER_PREPARE is set.
For example, in the following code extract, SQLPrepare succeeds even though the parameterised statement is invalid:
/* Do not defer query preparation. */
SQLSetStmtAttr(stmt, SQL_SOPT_SS_DEFER_PREPARE, (SQLPOINTER) SQL_DP_OFF, 0);
/* This statement is invalid. The parameter marker for the */
/* status column is missing. However, SQLPrepare still succeeds */
ret = SQLPrepare(stmt, "INSERT INTO Orders (OrderId, CustId, OpenDate, SalesPerson, Status) VALUES (?, ?, ?, ?)", SQL_NTS);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
SQLBindParameter();
.
.
.
/* The errors in the statement are not known until this point */
SQLExecute(stmt);
}Unicode support
The Easysoft ODBC-SQL Server Driver is a Unicode driver that supports the Unicode version (with suffix "W") of the ODBC calls it implements. Using a Unicode driver with a Unicode application removes the need for the driver manager to map Unicode functions and data types to ANSI. This results in better performance and removes the restrictions inherent in the Unicode to ANSI mappings.
The Easysoft ODBC-SQL Server Driver supports the following SQL Server Unicode data types:
-
NCHAR -
NTEXT -
NVARCHAR -
NVARCHAR(MAX)
ANSI-Only version of the Easysoft ODBC-SQL Server Driver
The Easysoft ODBC-SQL Server Driver distribution includes an ANSI-only version of the driver that does not support the Unicode ODBC APIs. This version of the driver should not normally be needed and is only provided for use with old and non-conformant Driver Managers.
If you do need to use the ANSI-only driver, first install the driver under unixODBC. To do this, open /etc/odbcinst.ini in a text editor. Copy the section for the standard driver and paste it below the existing section. Change the [Easysoft ODBC-SQL Server] in the new section. In the Driver entry, suffix the library name with _a. For example:
[Easysoft ODBC-SQL Server] Driver = /usr/local/easysoft/sqlserver/lib/libessqlsrv.so Setup = /usr/local/easysoft/sqlserver/lib/libessqlsrvS.so Threading = 0 FileUsage = 1 DontDLClose = 1 UsageCount = 1 # Install the ANSI driver by adding a new odbcinst.ini section. # This example odbcinst.ini extract is from a Linux installation # of the Easysoft ODBC-SQL Server Driver. [Easysoft ODBC-SQL Server ANSI APIs] Driver = /usr/local/easysoft/sqlserver/lib/libessqlsrv_a.so Setup = /usr/local/easysoft/sqlserver/lib/libessqlsrvS.so Threading = 0 FileUsage = 1 DontDLClose = 1 UsageCount = 1
In your data source, specify the new driver name in the Driver entry. For example:
[SQLSERVER_SAMPLE] Driver = Easysoft ODBC-SQL Server ANSI APIs . . .
The XML data type
The XML data type lets you store XML documents in table columns or Transact-SQL variables.
The Easysoft ODBC-SQL Server Driver supports the XML data type and its associated methods: query(), value(), exist(), modify(), and nodes().
The query() method lets you use an XML Query (XQuery) definition to search XML data stored in columns and variables of the XML type. The XQuery language is a World Wide Web Consortium (W3C) standard for retrieving or defining a set of XML nodes that meet a set of criteria.
In the following example, an XQuery is specified against the Instructions column in the ProductModel table. The Instructions column data type is XML and therefore exposes the query() method. The ProductModel table is contained in the SQL Server sample database AdventureWorks.
SELECT Instructions.query('declare namespace AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
/AWMI:root/AWMI:Location[@LocationID=10]') as Result
FROM Production.ProductModel
WHERE ProductModelID=7The 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">The next example uses the query() method to construct an XML element named <Product>. The <Product> element has a ProductModelID attribute, in which the ProductModelID attribute value is retrieved from the database.
SELECT CatalogDescription.query('declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; <Product ProductModelID="{ /PD:ProductDescription[1]/@ProductModelID }" /> ') as Result
FROM Production.ProductModelThe 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
When querying or updating xml columns or variables with the xml data type methods, the data source attributes AnsiNPW and QuotedId must be set to Yes (the default value for these settings). Otherwise, queries and modifications will fail for xml data types.
|
Using large-value data types
The MAX specifier 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:
-
The defined maximum size, which for example, is 2000 for a varchar(2000) column.
-Or-
-
The value
SQL_SS_LENGTH_UNLIMITED(0) for VARCHAR(MAX), VARBINARY(MAX), and NVARCHAR(MAX) columns.
Snapshot isolation
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 ONBy default, this database option is turned 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 SQL Server-specific ODBC extensions. To use these attributes, ODBC applications need to include the sqlncli.h header file. sqlncli.h is installed in /usr/local/easysoft/sqlserver/include.
Performing bulk copy operations
bcp utility
The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of SQL.
Syntax
bcp {[[database_name.][schema].]{table_name | view_name} | "query"}
{in | out | queryout | format} data_file
[-m max_errors] [-f format_file] [-x] [-D logfile]
[-F first_row] [-L last_row] [-b batch_size]
[-n] [-c] [-N] [-w] [-V (70 | 80 | 90 | 100 | 110 | 120)]
[-q] [-C { RAW } ] [-t field_terminator]
[-r row_terminator] [-o output_file] [-a packet_size]
[-d database] [-e error_file] [-S server_name[\instance_name][:port]]
[-U login] [-P password] [-useNTLMv2] [-A APP_NAME]
[-T] [-v] [-k] [-g timeout] [-K] [-E] [-h"hint [,...n]"] [-Xe filename]
[-Xc cypher] [-G] [-O] -useNOSSL
The arguments are
database_name
The name of the database where table_name or view_name is located. The database can also be specified with the -d option. If no database is specified, the default database for login is used.
schema
The name of the schema to which table_name or view_name belongs.
table_name
The name of the destination table when importing data into SQL Server (in), and the source table when exporting data from SQL Server (out).
view_name
The name of the destination view when importing data into SQL Server (in), and the source view when exporting data from SQL Server (out).
“query”
An SQL query that returns a result set. If the query returns multiple result sets, such as a SELECT statement that specifies a COMPUTE clause, only the first result set is copied to the data file; subsequent result sets are ignored. If the database containing the target table or view is not the default for login, specify the database with the -d option.
in | out | queryout | format
The direction of the bulk copy:
-
incopies data from a file into a database table or view. -
outcopies from a database table or view to a file. If you specify an existing file, the file is overwritten. The user who is running bcp must have write permission to the directory where the file is located. When extracting data, note that bcp represents an empty string as a null character (\0) and aNULLas a null value. -
queryoutmust be specified only when bulk copying data from a query. -
formatcreates a format file based on the option specified (-n,-c,-w, or-N) and the table or view delimiters. When bulk copying data, the bcp command can refer to a format file, which saves you from re-entering format information interactively. The format option requires the-foption; creating an XML format file, also requires the-xoption.
data_file
The full path to the data file. When data is bulk imported into SQL Server, the data file contains the data to be copied into the specified table or view. When data is bulk exported from SQL Server, the data file contains the data copied from the table or view.
For the format option, you must specify nul as the value of data_file (format nul).
-m max_errors
The maximum number of times that bcp can return SQL_ERROR when bulk copying data from a query before the bcp operation is cancelled.
-f format_file
The full path to a format file:
If -f is used with the format option, format_file is created for the specified table or view. To create an XML format file, also specify the -x option.
If used with the in or out option, -f requires an existing format file.
-x
Used with the format and -f format_file options, the -x option generates an XML-based format file instead of the default non-XML format file. For example, bcp AdventureWorks.Sales.Currency format nul -f bcp.xml -x -c -U mydomain\\myuser -P mypassword -S mycomputer\\sqlexpress.
-D logfile
Turns on bcp logging and specifies the log file where the logging information is written. This can be a very useful debugging aid but it should be remembered that logging will slow bcp down, so remember to disable logging when you have finished debugging. Ensure that the user who is bcp has write permission to the log file (and to the directory containing it).
-F first_row
The number of the first row to export from a table or import from a data file. first_row should be a value greater than 0 but less than or equal to the total number rows. The default is the first row of the file.
-L last_row
The number of the last row to export from a table or import from a data file. last_row should be a value greater than 0 but less than or equal to the number of the last row. The default is the last row of the file.
-b batch_size
The number of rows per batch when importing data. Each batch is imported and logged as a separate transaction that imports the whole batch before being committed. By default, all the rows in the data file are imported as one batch. To distribute the rows among multiple batches, specify a batch_size that’s smaller than the number of rows in the data file. If the transaction for any batch fails:
-
Insertions from the current batch are rolled back.
-
No further batches are inserted.
Batches already imported by committed transactions are unaffected by a subsequent failure.
-n
Use native (database) data types when importing and exporting data. This option does not prompt for each field; it uses the native values.
-c
Use character format when importing and exporting data. Character format uses the character data format for all columns. This option does not prompt for each field; it uses CHAR as the storage type, no prefixes, \t (tab character) as the field separator, and \n (newline character) as the row terminator.
-N
Use Unicode character format when importing and exporting character data. Use native format when importing and exporting non-character data.
-w
Use Unicode character format when importing and exporting data. Use this option when importing and exporting non-ASCII data stored in NCHAR, NVARCHAR, and NTEXT columns. This option does not prompt for each field; it uses NCHAR as the storage type, no prefixes, \t (tab character) as the field separator, and \n (newline character) as the row terminator.
-V (70 | 80 | 90 | 100 | 110)
The version of SQL Server that bcp is connecting to, where:
70 = SQL Server 7.0 80 = SQL Server 2000 90 = SQL Server 2005 100 = SQL Server 2008 110 = SQL Server 2012 and above
-q
Executes the SET QUOTED_IDENTIFIERS ON statement in the connection between the bcp and an instance of SQL Server.
-C {RAW}
Specifies the code page of the data in the data file.
| Value | Description |
|---|---|
RAW |
No conversion from one code page to another occurs. |
-t field_terminator
The field terminator. The default is \t (tab character).
-r row_terminator
The row terminator. The default is \n (newline character).
-o output_file
The name of a file that receives bcp output redirected from the command prompt. For example, if you specified -o /tmp/bcp.txt and exported some data, /tmp/bcp.txt would contain something similar to:
Starting copy... 105 rows successfully bulk-copied to host file. Total received: 105 Network packet size (bytes): 4096
The contents of output_file are overwritten each time you successfully import or export data.
-a packet_size
The packet size in bytes that bcp will request when sending data to and receiving data from SQL Server. The specified packet size must be lower than 65536 bytes.
The default packet size is 4096 bytes. After a successful import or export, the bcp output shows the packet size used.
-d database
The name of the database where table_name or view_name is located. If no database is specified, the default database for login is used.
-e error_file
The name of the file to write errors to.
-S server_name[\instance_name]
The SQL Server instance that you want to connect to. To connect to the default instance of SQL Server on a server, specify only server_name. To connect to a named instance of SQL Server, specify server_name\instance_name. To include the port that the SQL Server instance is listening on, specify server_name:port.
-U login
The SQL Server login name to use when connecting to SQL Server. If the SQL Server instance uses Windows Authentication, specify the Windows user name to use to authenticate the connection. (Include the -T argument if you specify a Windows user name.) If the SQL Server instance permits SQL Server Authentication, you can also specify a SQL Server user name.
-P password
The password for login. You do not have to specify a password on the command line. If you omit the -P argument from the command line, bcp will prompt you for one when you run the command.
-useNTLMv2
If you want to use NTLMv2 to authenticate a Windows user specified with -U, include -useNTLMv2 in your bcp command. If NTLMv2 is enabled at your site and you omit the -useNTLMv2, argument, the bcp connection will fail with the error "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."
-A APP_NAME
The application name that bcp registers with SQL Server. This is returned by the SQL Server function APP_NAME(). The default application name is BCPTOOL. Use the -a argument to override the default name.
-T
Use Windows authentication to validate the connection. If this argument is specified, login must be a Windows user name.
-v
Reports the bcp version number.
-k
Empty columns retain a null value during an import, rather than have any default values for the columns inserted.
-g [timeout]
The number of milliseconds to wait for a TCP connection to the SQL Server computer to be established before returning to the bcp.
-G [req_flags]
Set the GSSFlag attribute via bcp.
-O
Connect by using an ODBC connection string. For example:
bcp myTable out my.dat -O "DSN=SQLSERVER_SAMPLE".
-K
Whether to access a SQL Server instance as a Kerberos service.
When you include this argument, the Easysoft ODBC-SQL Server Driver will attempt to obtain a service ticket for the following Service Principal Name (SPN):
MSSQLSvc/server
where:
-
serveris the name or IP address of the SQL Server computer specified with the-Sargument.
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 computer. For more information about kinit and accessing SQL Server as a Kerberos service, refer to the following Easysoft tutorial:
-M
The Service Principal Name (SPN) for a SQL Server instance that has been registered as a Kerberos service. If the SPN contains an instance name, you need to include the -M argument along with the -K argument. For example:
bcp -S "mysqlservercomputer\myinstance" -K -M MSSQLSvc/mysqlservercomputer:myinstance
-E
Use identity values in the imported data file for the identity column. If -E is not specified, identity values in the data file being imported are ignored, and SQL Server automatically assigns unique values based on the seed and increment values specified during table creation.
-h"hint [,…n]"
The hint or hints to be used during a bulk import of data into a table or view.
ORDER(column [ASC | DESC] [,…n])
The sort order of the data in the data file.
ROWS_PER_BATCH = num
Number of rows of data per batch. Used when -b is not specified, resulting in the entire data file being sent to the server as a single transaction. The server optimises the bulk load according to the value num. By default, ROWS_PER_BATCH is unknown.
KILOBYTES_PER_BATCH = num
Approximate number of kilobytes of data per batch. By default, KILOBYTES_PER_BATCH is unknown.
TABLOCK
A bulk update table-level lock is acquired for the duration of the bulk load operation; otherwise, a row-level lock is acquired.
CHECK_CONSTRAINTS
All constraints on the target table or view must be checked during the bulk-import operation. Without the CHECK_CONSTRAINTS hint, any CHECK and FOREIGN KEY constraints are ignored, and after the operation the constraint on the table is marked as not-trusted.
FIRE_TRIGGERS
Specified with the in argument, any insert triggers defined on the destination table will run during the bulk-copy operation. If FIRE_TRIGGERS is not specified, no insert triggers will run.
Remarks
-
The Easysoft bcp client is located in
installation-dir/easysoft/sqlserver/bcpwhereinstallation-diris the Easysoft installation directory, by default/usr/local. -
SQL Server identifiers can include embedded spaces and quotation marks. If the identifier contains spaces, enclose the database, table, view, or schema name with quotation marks. If the identifier contains quotation marks, enclose the database, table, view, or schema name with double quotation marks and square brackets (
[ ]).
-Xe filename
Use this option to specify a source for random data for an SSL connection. Use filename to specify a randomness device. You only need to use this option if bcp cannot find a source for random data on your system.
-Xc cypher
For an SSL connection, request a different encryption or data integrity algorithm to the ones negotiated during the SSL handshake. Separate multiple cypher suites with a colon. For example: -Xc AES:3DES
-useNOSSL
You should not normally need to include this flag.
Easysoft ODBC-SQL Server Driver extensions - bulk copy functions
In this section:
To build a program that uses the bulk copy extension library (esbcp), use this compile line format:
cc -I/usr/local/easysoft/unixODBC/include myprogram.c -I/usr/local/easysoft/sqlserver/include -o myprogram -L/usr/local/easysoft/unixODBC/lib/ -L/usr/local/easysoft/sqlserver/lib/ -lodbc -lodbcinst -lesbcp
If you did not install the Easysoft ODBC-SQL Server Driver under /usr/local amend the path accordingly.
bcp_batch
Commits all rows previously bulk copied from program variables and sent to SQL Server by bcp_sendrow.
Syntax
DBINT bcp_batch (HDBC hdbc);Arguments
hdbcIs the bulk copy-enabled ODBC connection handle.
Returns
The number of rows saved after the last call to bcp_batch, or -1 in case of error.
Remarks
Bulk copy batches define transactions. When an application uses bcp_bind and bcp_sendrow to bulk copy rows from program variables to SQL Server tables, the rows are committed only when the program calls bcp_batch or bcp_done.
Examples
#include <stdio.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>
#include <sqlncli.h>
SQLHENV henv = SQL_NULL_HENV;
HDBC hdbc1 = SQL_NULL_HDBC;
DBINT idRow = 5;
char * pPart1 = "Text chunk 1.";
char * pPart2 = "Text chunk 2.";
char * pPart3 = "Text chunk 3.";
DBINT cbAllParts;
DBINT nRowsProcessed;
void Cleanup() {
if (hdbc1 != SQL_NULL_HDBC) {
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
}
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
int main() {
RETCODE retcode;
/* Allocate the ODBC environment and save handle. */
retcode = SQLAllocHandle(SQL_HANDLE_ENV, NULL, & henv);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLAllocHandle(Env) Failed\n\n");
Cleanup();
return (9);
}
/* Notify ODBC that this is an ODBC 3.0 app. */
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLSetEnvAttr(ODBC version) Failed\n\n");
Cleanup();
return (9);
}
/* Allocate ODBC connection handle, set BCP mode, and connect. */
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, & hdbc1);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLAllocHandle(hdbc1) Failed\n\n");
Cleanup();
return (9);
}
retcode = SQLSetConnectAttr(hdbc1, SQL_COPT_SS_BCP, (void * ) SQL_BCP_ON, SQL_IS_INTEGER);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLSetConnectAttr(hdbc1) Failed\n\n");
Cleanup();
return (9);
}
retcode = SQLDriverConnect(hdbc1, NULL, "DSN=MYDSN;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO)) {
printf("SQLDriverConnect() Failed\n\n");
Cleanup();
return (9);
}
/* Initialise the bulk copy. */
retcode = bcp_init(hdbc1, "articles", NULL, NULL, DB_IN);
if ((retcode != SUCCEED)) {
printf("bcp_init(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
/* Bind program variables to table columns. */
retcode = (bcp_bind(hdbc1, (LPCBYTE) & idRow, 0, SQL_VARLEN_DATA, NULL, 0, SQLINT4, 1));
if ((retcode != SUCCEED)) {
printf("bcp_bind(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
cbAllParts = strlen(pPart1) + strlen(pPart2) + strlen(pPart3);
retcode = (bcp_bind(hdbc1, NULL, 0, cbAllParts, NULL, 0, SQLTEXT, 2));
if ((retcode != SUCCEED)) {
printf("bcp_bind(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
/* Now send this row, with the text value broken into three chunks. */
retcode = (bcp_sendrow(hdbc1));
if ((retcode != SUCCEED)) {
printf("bcp_sendrow(hdbc1) Failed.n\n");
Cleanup();
return (9);
}
cbAllParts = strlen(pPart1);
retcode = (bcp_moretext(hdbc1, cbAllParts, pPart1));
if ((retcode != SUCCEED)) {
printf("bcp_moretext(hdbc1) Failed. 1\n\n");
Cleanup();
return (9);
}
cbAllParts = strlen(pPart2);
retcode = (bcp_moretext(hdbc1, cbAllParts, pPart2));
if ((retcode != SUCCEED)) {
printf("bcp_moretext(hdbc1) Failed. 2\n\n");
Cleanup();
return (9);
}
cbAllParts = strlen(pPart3);
retcode = (bcp_moretext(hdbc1, cbAllParts, pPart3));
if ((retcode != SUCCEED)) {
printf("bcp_moretext(hdbc1) Failed. 3\n\n");
Cleanup();
return (9);
}
/* We're all done. */
nRowsProcessed = bcp_batch(hdbc1);
/* Cleanup */
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}bcp_bind
Binds data from a program variable to a table column for bulk copy into SQL Server.
Syntax
RETCODE bcp_bind (
HDBC hdbc,
LPCBYTE pData,
INT cbIndicator,
DBINT cbData,
LPCBYTE pTerm,
INT cbTerm,
INT eDataType,
INT idxServerCol);Arguments
hdbcIs the bulk copy-enabled ODBC connection handle.
pDataIs 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.
cbIndicatorIs 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.
cbDataIs the count of bytes of data in the program variable, not including the length of any length or null indicator or terminator.
pTermIs 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.
cbTermIs 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.
eDataTypeIs 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.
idxServerColThe ordinal position of the column in the database table to which the data is copied. The first column in a table is column 1. The ordinal position of a column is reported by SQLColumns.
Returns
SUCCEED or FAIL.
Remarks
Use bcp_bind for a fast, efficient way to copy data from a program variable into a table in SQL Server.
Make a separate bcp_bind call for every column in the SQL Server table into which you want to copy. After the necessary bcp_bind calls have been made, call bcp_sendrow to send a row of data from your program variables to SQL Server. Rebinding a column is not supported.
Whenever you want SQL Server to commit the rows already received, call bcp_batch.
When there are no more rows to be inserted, call bcp_done. Failure to do so results in an error.
If pData for a column is set to NULL because its value will be supplied by calls to bcp_moretext, any subsequent columns with eDataType set to SQLTEXT, SQLNTEXT, SQLXML, SQLUDT, SQLCHARACTER, SQLVARCHAR, SQLVARBINARY, SQLBINARY, SQLNCHAR, or SQLIMAGE must also be bound with pData set to NULL, and their values must also be supplied by calls to bcp_moretext.
For new large value types, such as VARCHAR(MAX), VARBINARY(MAX), or NVARCHAR(max), you can use SQLCHARACTER, SQLVARCHAR, SQLVARBINARY, SQLBINARY, and SQLNCHAR as type indicators in the eDataType parameter.
bcp_colfmt
Specifies the source or target format of the data in a user file. When used as a source format, bcp_colfmt specifies the format of an existing data file used as the source of data in a bulk copy to a SQL Server table. When used as a target format, the data file is created using the column formats specified with bcp_colfmt.
Syntax
RETCODE bcp_colfmt (
HDBC hdbc,
INT idxUserDataCol,
BYTE eUserDataType,
INT cbIndicator,
DBINT cbUserData,
LPCBYTE pUserDataTerm,
INT cbUserDataTerm,
INT idxServerCol);Arguments
hdbcThe bulk copy-enabled ODBC connection handle.
idxUserDataColThe ordinal column number in the user data file for which the format is being specified. The first column is 1.
eUserDataTypeThe 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.
cbIndicatorIs 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.
cbUserDataThe 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.
pUserDataTermThe 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.
cbUserDataTermThe 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.
idxServerColThe ordinal position of the column in the database table. The first column number is 1. The ordinal position of a column is reported by SQLColumns.
If this value is 0, bulk copy ignores the column in the data file.
Returns
SUCCEED or FAIL.
Remarks
The bcp_colfmt function allows you to specify the user-file format for bulk copies.
The bcp_columns function must be called before any calls to bcp_colfmt.
You must call bcp_colfmt once for each column in the user file.
You do not need to copy all data in a user file to the SQL Server table. To skip a column, specify the format of the data for the column, setting the idxServerCol parameter to 0. If you want to skip a column, you must specify its type.
bcp_collen
Sets the data length in the program variable for the current bulk copy into SQL Server.
Syntax
RETCODE bcp_collen (
HDBC hdbc,
DBINT cbData,
INT idxServerCol);Arguments
hdbcThe bulk copy-enabled ODBC connection handle.
cbDataThe 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.
idxServerColIs the ordinal position of the column in the table to which the data is copied. The first column is 1.
Returns
SUCCEED or FAIL.
Remarks
The bcp_collen function allows you to change the data length in the program variable for a particular column when copying data to SQL Server with bcp_sendrow.
Initially, the data length is determined when bcp_bind is called. If the data length changes between calls to bcp_sendrow and no length prefix or terminator is being used, you can call bcp_collen to reset the length. The next call to bcp_sendrow uses the length set by the call to bcp_collen.
You must call bcp_collen once for each column in the table whose data length you want to modify.
Example
#include <stdio.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>
#include <sqlncli.h>
SQLHENV henv = SQL_NULL_HENV;
HDBC hdbc1 = SQL_NULL_HDBC;
DBINT idRow = 5;
char * pPart1 = "Text chunk 1.";
char * pPart2 = "Text chunk 2.";
char * pPart3 = "Text chunk 3.";
DBINT cbAllParts;
DBINT nRowsProcessed;
void Cleanup() {
if (hdbc1 != SQL_NULL_HDBC) {
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
}
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
int main() {
RETCODE retcode;
/* Allocate the ODBC environment and save handle. */
retcode = SQLAllocHandle(SQL_HANDLE_ENV, NULL, & henv);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLAllocHandle(Env) Failed\n\n");
Cleanup();
return (9);
}
/* Notify ODBC that this is an ODBC 3.0 app. */
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLSetEnvAttr(ODBC version) Failed\n\n");
Cleanup();
return (9);
}
/* Allocate ODBC connection handle, set BCP mode, and connect. */
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, & hdbc1);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLAllocHandle(hdbc1) Failed\n\n");
Cleanup();
return (9);
}
retcode = SQLSetConnectAttr(hdbc1, SQL_COPT_SS_BCP, (void * ) SQL_BCP_ON, SQL_IS_INTEGER);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLSetConnectAttr(hdbc1) Failed\n\n");
Cleanup();
return (9);
}
retcode = SQLDriverConnect(hdbc1, NULL, "DSN=MYDSN;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO)) {
printf("SQLDriverConnect() Failed\n\n");
Cleanup();
return (9);
}
/* Initialise the bulk copy. */
retcode = bcp_init(hdbc1, "articles", NULL, NULL, DB_IN);
if ((retcode != SUCCEED)) {
printf("bcp_init(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
/* Bind program variables to table columns. */
retcode = (bcp_bind(hdbc1, (LPCBYTE) & idRow, 0, SQL_VARLEN_DATA, NULL, 0, SQLINT4, 1));
if ((retcode != SUCCEED)) {
printf("bcp_bind(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
cbAllParts = strlen(pPart1) + strlen(pPart2) + strlen(pPart3);
retcode = (bcp_bind(hdbc1, NULL, 0, cbAllParts, NULL, 0, SQLTEXT, 2));
if ((retcode != SUCCEED)) {
printf("bcp_bind(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
retcode = (bcp_collen(hdbc1, 100, 1));
if ((retcode != SUCCEED)) {
printf("bcp_collen(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
retcode = (bcp_colptr(hdbc1, NULL, 2));
if ((retcode != SUCCEED)) {
printf("bcp_colptr(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
/* Now send this row, with the text value broken into three chunks. */
retcode = (bcp_sendrow(hdbc1));
if ((retcode != SUCCEED)) {
printf("bcp_sendrow(hdbc1) Failed.n\n");
Cleanup();
return (9);
}
cbAllParts = strlen(pPart1);
retcode = (bcp_moretext(hdbc1, cbAllParts, pPart1));
if ((retcode != SUCCEED)) {
printf("bcp_moretext(hdbc1) Failed. 1\n\n");
Cleanup();
return (9);
}
cbAllParts = strlen(pPart2);
retcode = (bcp_moretext(hdbc1, cbAllParts, pPart2));
if ((retcode != SUCCEED)) {
printf("bcp_moretext(hdbc1) Failed. 2\n\n");
Cleanup();
return (9);
}
cbAllParts = strlen(pPart3);
retcode = (bcp_moretext(hdbc1, cbAllParts, pPart3));
if ((retcode != SUCCEED)) {
printf("bcp_moretext(hdbc1) Failed. 3\n\n");
Cleanup();
return (9);
}
/* We're all done. */
nRowsProcessed = bcp_done(hdbc1);
/* Cleanup */
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}bcp_colptr
Sets the program variable data address for the current copy into SQL Server.
Syntax
RETCODE bcp_colptr (
HDBC hdbc,
LPCBYTE pData,
INT idxServerCol);Arguments
hdbcThe bulk copy-enabled ODBC connection handle.
pDataA 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.
idxServerColThe ordinal position of the column in the database table to which the data is copied. The first column in a table is column 1. The ordinal position of a column is reported by SQLColumns.
Returns
SUCCEED or FAIL.
Remarks
The bcp_colptr function allows you to change the address of source data for a particular column when copying data to SQL Server with bcp_sendrow.
Initially, the pointer to user data is set by a call to bcp_bind. If the program variable data address changes between calls to bcp_sendrow, you can call bcp_colptr to reset the pointer to the data. The next call to bcp_sendrow sends the data addressed by the call to bcp_colptr.
There must be a separate bcp_colptr call for every column in the table whose data address you want to modify.
bcp_columns
Sets the total number of columns found in the user file for use with a bulk copy into or out of SQL Server. bcp_setbulkmode can be used instead of bcp_columns and bcp_colfmt.
Syntax
RETCODE bcp_columns (
HDBC hdbc,
INT nColumns);Arguments
hdbcThe bulk copy-enabled ODBC connection handle.
nColumns
The total number of columns in the user file. Even if you are preparing to bulk copy data from the user file to an SQL Server table and do not intend to copy all columns in the user file, you must still set nColumns to the total number of user-file columns.
Returns
SUCCEED or FAIL.
Remarks
This function can be called only after bcp_init has been called with a valid file name.
You should call this function only if you intend to use a user-file format that differs from the default.
After calling bcp_columns, you must call bcp_colfmt for each column in the user file to completely define a custom file format.
bcp_control
Changes the default settings for various control parameters for a bulk copy between a file and SQL Server.
Syntax
RETCODE bcp_control (
HDBC hdbc,
INT eOption,
void* iValue);Arguments
hdbcThe bulk copy-enabled ODBC connection handle.
eOptionIs one of the following:
BCPABORT
Stops a bulk-copy operation that is already in progress.
BCPBATCH
Is the number of rows per batch.
BCPDELAYREADFMT
If set to true, causes bcp_readfmt to read at execution.
BCPFILEFMT
The version number of the data file format.
BCPFIRST
The first row of data to file or table to copy.
BCPFIRSTEX
For BCP out operations, specifies the first row of the database table to copy into the data file.
For BCP in operations, specifies the first row of the data file to copy into the database table.
BCPFMTXML
Specifies that the format file generated should be in XML format. It is off by default.
BCPHINTS
SQL Server bulk-copy processing hints or a Transact-SQL statement that returns a result set.
BCPKEEPIDENTITY
When iValue is TRUE, specifies that bulk copy functions insert data values supplied for SQL Server columns defined with an identity constraint. The input file must supply values for the identity columns. If this is not set, new identity values are generated for the inserted rows. Any data present in the file for the identity columns is ignored.
BCPKEEPNULLS
Specifies whether empty data values in the file will be converted to NULL values in the SQL Server table. When iValue is TRUE, empty values will be converted to NULL in the SQL Server table. The default is for empty values to be converted to a default value for the column in the SQL Server table if a default exists.
BCPLAST
Is the last row to copy.
BCPLASTEX
For BCP out operations, specifies the last row of the database table to copy into the data file.
For BCP in operations, specifies the last row of the data file to copy into the database table.
BCPMAXERRS
Is the number of errors allowed before the bulk copy operation fails.
BCPODBC
When TRUE, specifies that DATETIME and SMALLDATETIME values saved in character format will use the ODBC timestamp escape sequence prefix and suffix. The BCPODBC option only applies to DB_OUT.
When FALSE, a DATETIME value representing January 1, 2025 is converted to the character string: 2025-01-01 00:00:00.000. When TRUE, the same datetime value is represented as: {ts '2025-01-01 00:00:00.000'}.
BCPROWCOUNT
Returns the number of rows affected by the current (or last) BCP operation.
BCPTEXTFILE
When TRUE, specifies that the data file is a text file, rather than a binary file.
BCPUNICODEFILE
When TRUE, specifies the input file is a Unicode file.
Returns
SUCCEED or FAIL.
Remarks
This function sets various control parameters for bulk-copy operations.
Example
#include <stdio.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>
#include <sqlncli.h>
SQLHENV henv = SQL_NULL_HENV;
HDBC hdbc1 = SQL_NULL_HDBC;
HDBC hdbc;
DBINT nRowsProcessed;
void Cleanup() {
if (hdbc1 != SQL_NULL_HDBC) {
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
}
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
int main() {
RETCODE retcode;
/* Allocate the ODBC environment and save handle. */
retcode = SQLAllocHandle(SQL_HANDLE_ENV, NULL, & henv);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLAllocHandle(Env) Failed\n\n");
Cleanup();
return (9);
}
/* Notify ODBC that this is an ODBC 3.0 app. */
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLSetEnvAttr(ODBC version) Failed\n\n");
Cleanup();
return (9);
}
/* Allocate ODBC connection handle, set BCP mode, and connect. */
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, & hdbc1);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLAllocHandle(hdbc1) Failed\n\n");
Cleanup();
return (9);
}
retcode = SQLSetConnectAttr(hdbc1, SQL_COPT_SS_BCP, (void * ) SQL_BCP_ON, SQL_IS_INTEGER);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLSetConnectAttr(hdbc1) Failed\n\n");
Cleanup();
return (9);
}
retcode = SQLDriverConnect(hdbc1, NULL, "DSN=MYDSN;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO)) {
printf("SQLDriverConnect() Failed\n\n");
Cleanup();
return (9);
}
/* Initialise the bulk copy. */
/* Table definition: CREATE TABLE myTable(ColA varchar(25), ColB varchar(25),ColC varchar(25)) */
retcode = bcp_init(hdbc1, "myTable", "myTable.dat", "myErrors.log", DB_IN);
if ((retcode != SUCCEED)) {
printf("bcp_init(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
/* Set the number of rows per batch */
retcode = (bcp_control(hdbc1, BCPBATCH, (void * ) 1));
if ((retcode != SUCCEED)) {
printf("bcp_control(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
/* There are three columns */
retcode = (bcp_columns(hdbc1, 3));
if ((retcode != SUCCEED)) {
printf("bcp_columns(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
bcp_colfmt(hdbc1, 1, SQLCHARACTER, 2, 25, "\t", 1, 1);
bcp_colfmt(hdbc1, 2, SQLCHARACTER, 2, 25, "\t", 1, 2);
bcp_colfmt(hdbc1, 3, SQLCHARACTER, 2, 25, "", 1, 3);
retcode = (bcp_writefmt(hdbc1, "myFmtFile.fmt"));
if ((retcode != SUCCEED)) {
printf("bcp_writefmt(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
retcode = (bcp_readfmt(hdbc1, "myFmtFile.fmt"));
if ((retcode != SUCCEED)) {
printf("bcp_readfmt(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
retcode = (bcp_exec(hdbc1, & nRowsProcessed));
if ((retcode != SUCCEED)) {
printf("bcp_exec(hdbc1) Failed. 1\n\n");
Cleanup();
return (9);
}
/* Cleanup */
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}bcp_done
When bcp_sendrow is used to bulk copy rows from program variables into SQL Server tables, rows are committed only when the user calls bcp_batch or bcp_done.
Syntax
DBINT bcp_done (
HDBC hdbc);Arguments
hdbcThe bulk copy-enabled ODBC connection handle.
Returns
The number of rows permanently saved after the last call to bcp_batch or -1 in case of error.
Remarks
Call bcp_done after the last call to bcp_sendrow or bcp_moretext. Failure to call bcp_done after copying all data results in errors.
bcp_exec
Executes a complete bulk copy of data between a database table and a user file.
Syntax
RETCODE bcp_exec (
HDBC hdbc,
LPDBINT pnRowsProcessed);Arguments
hdbcThe bulk copy-enabled ODBC connection handle.
pnRowsProcessedA pointer to a DBINT. The bcp_exec function fills this DBINT with the number of rows successfully copied. If pnRowsProcessed is NULL, it is ignored by bcp_exec.
Returns
SUCCEED or FAIL. The bcp_exec function returns SUCCEED if all rows are copied. bcp_exec returns FAIL if a complete failure occurs. Check the pnRowsProcessed parameter for the number of rows successfully copied.
Remarks
This function copies data from a user file to a database table or vice versa, depending on the value of the eDirection parameter in bcp_init.
Before calling bcp_exec, call bcp_init with a valid user file name. Failure to do so results in an error.
Example
#include <stdio.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>
#include <sqlncli.h>
SQLHENV henv = SQL_NULL_HENV;
HDBC hdbc1 = SQL_NULL_HDBC;
HDBC hdbc;
DBINT nRowsProcessed;
void Cleanup() {
if (hdbc1 != SQL_NULL_HDBC) {
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
}
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
int main() {
RETCODE retcode;
/* Allocate the ODBC environment and save handle. */
retcode = SQLAllocHandle(SQL_HANDLE_ENV, NULL, & henv);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLAllocHandle(Env) Failed\n\n");
Cleanup();
return (9);
}
/* Notify ODBC that this is an ODBC 3.0 app. */
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLSetEnvAttr(ODBC version) Failed\n\n");
Cleanup();
return (9);
}
/* Allocate ODBC connection handle, set BCP mode, and connect. */
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, & hdbc1);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLAllocHandle(hdbc1) Failed\n\n");
Cleanup();
return (9);
}
retcode = SQLSetConnectAttr(hdbc1, SQL_COPT_SS_BCP, (void * ) SQL_BCP_ON, SQL_IS_INTEGER);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLSetConnectAttr(hdbc1) Failed\n\n");
Cleanup();
return (9);
}
retcode = SQLDriverConnect(hdbc1, NULL, "DSN=MYDSN;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO)) {
printf("SQLDriverConnect() Failed\n\n");
Cleanup();
return (9);
}
/* Initialise the bulk copy. */
/* Table definition: CREATE TABLE myTable(ColA varchar(25), ColB varchar(25),ColC varchar(25)) */
retcode = bcp_init(hdbc1, "myTable", "myTable.dat", "myErrors.log", DB_IN);
if ((retcode != SUCCEED)) {
printf("bcp_init(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
/* Set the number of rows per batch */
retcode = (bcp_control(hdbc1, BCPBATCH, (void * ) 1));
if ((retcode != SUCCEED)) {
printf("bcp_control(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
/* There are three columns */
retcode = (bcp_columns(hdbc1, 3));
if ((retcode != SUCCEED)) {
printf("bcp_columns(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
bcp_colfmt(hdbc1, 1, SQLCHARACTER, 2, 25, "\t", 1, 1);
bcp_colfmt(hdbc1, 2, SQLCHARACTER, 2, 25, "\t", 1, 2);
bcp_colfmt(hdbc1, 3, SQLCHARACTER, 2, 25, "", 1, 3);
retcode = (bcp_writefmt(hdbc1, "myFmtFile.fmt"));
if ((retcode != SUCCEED)) {
printf("bcp_writefmt(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
retcode = (bcp_readfmt(hdbc1, "myFmtFile.fmt"));
if ((retcode != SUCCEED)) {
printf("bcp_readfmt(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
retcode = (bcp_exec(hdbc1, & nRowsProcessed));
if ((retcode != SUCCEED)) {
printf("bcp_exec(hdbc1) Failed. 1\n\n");
Cleanup();
return (9);
}
/* Cleanup */
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}bcp_getcolfmt
Used to find the column format property value.
Syntax
HDBC hdbc,
INT field,
INT property,
void* pValue,
INT cbvalue,
INT* pcbLen);Arguments
hdbcThe bulk copy-enabled ODBC connection handle.
fieldThe column number for which the property is retrieved.
propertyOne of the property constants.
pValueThe pointer to the buffer from which to retrieve the property value.
cbValueIs the length of the property buffer in bytes.
pcbLenPointer to length of the data that is being returned in the property buffer.
Returns
SUCCEED or FAIL.
Remarks
Column format property values are listed in the bcp_setcolfmt topic. The column format property values are set by calling the bcp_setcolfmt function, and the bcp_getcolfmt function is used to find the column format property value.
Example
#include <stdio.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>
#include <sqlncli.h>
SQLHENV henv = SQL_NULL_HENV;
HDBC hdbc1 = SQL_NULL_HDBC;
HDBC hdbc;
DBINT nRowsProcessed;
void Cleanup() {
if (hdbc1 != SQL_NULL_HDBC) {
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
}
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
int main() {
RETCODE retcode;
/* Allocate the ODBC environment and save handle. */
retcode = SQLAllocHandle(SQL_HANDLE_ENV, NULL, & henv);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLAllocHandle(Env) Failed\n\n");
Cleanup();
return (9);
}
/* Notify ODBC that this is an ODBC 3.0 app. */
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLSetEnvAttr(ODBC version) Failed\n\n");
Cleanup();
return (9);
}
/* Allocate ODBC connection handle, set BCP mode, and connect. */
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, & hdbc1);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLAllocHandle(hdbc1) Failed\n\n");
Cleanup();
return (9);
}
retcode = SQLSetConnectAttr(hdbc1, SQL_COPT_SS_BCP, (void * ) SQL_BCP_ON, SQL_IS_INTEGER);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLSetConnectAttr(hdbc1) Failed\n\n");
Cleanup();
return (9);
}
retcode = SQLDriverConnect(hdbc1, NULL, "DSN=MYDSN;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO)) {
printf("SQLDriverConnect() Failed\n\n");
Cleanup();
return (9);
}
/* Initialise the bulk copy. */
/* Table definition: CREATE TABLE myTable(ColA varchar(25), ColB varchar(25),ColC varchar(25)) */
retcode = bcp_init(hdbc1, "myTable", "myTable.dat", "myErrors.log", DB_IN);
if ((retcode != SUCCEED)) {
printf("bcp_init(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
/* There are three columns */
retcode = (bcp_columns(hdbc1, 3));
if ((retcode != SUCCEED)) {
printf("bcp_columns(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
bcp_colfmt(hdbc1, 1, SQLCHARACTER, 2, 25, "\t", 1, 1);
bcp_colfmt(hdbc1, 2, SQLCHARACTER, 2, 25, "\t", 1, 2);
bcp_colfmt(hdbc1, 3, SQLCHARACTER, 2, 25, "", 1, 3);
retcode = (bcp_writefmt(hdbc1, "myFmtFile.fmt"));
if ((retcode != SUCCEED)) {
printf("bcp_writefmt(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
retcode = (bcp_getcolfmt(hdbc1));
if ((retcode != SUCCEED)) {
printf("bcp_getcolfmt(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
retcode = (bcp_exec(hdbc1, & nRowsProcessed));
if ((retcode != SUCCEED)) {
printf("bcp_exec(hdbc1) Failed. 1\n\n");
Cleanup();
return (9);
}
/* Cleanup */
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}bcp_gettypename
Returns the SQL type name for a specified BCP type token.
Syntax
RETCODE bcp_gettypename (
INT token,
DBBOOL fIsMaxType);Arguments
tokenA value indicating a BCP type token.
fieldIndicates if token requested is a MAX type.
Returns
A string containing the SQL type name corresponding to the BCP type. If an invalid BCP type is specified, an empty string is returned.
Remarks
The BCP type tokens are defined in the /usr/local/easysoft/sqlserver/include/sqlncli.h header file.
bcp_init
Initialises the bulk copy operation.
Syntax
RETCODE bcp_init (
HDBC hdbc,
LPCTSTR szTable,
LPCTSTR szDataFile,
LPCTSTR szErrorFile,
INT eDirection);Unicode and ANSI names:
-
bcp_initA(ANSI) -
bcp_initW(Unicode)
Arguments
hdbcThe bulk copy-enabled ODBC connection handle.
szTableThe 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.
szDataFileThe 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.
szErrorFileThe 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.
eDirectionIs the direction of the copy, either DB_IN or DB_OUT. DB_IN indicates a copy from program variables or a user file to a table. DB_OUT indicates a copy from a database table to a user file. You must specify a user file name with DB_OUT.
Returns
SUCCEED or FAIL.
Remarks
Call bcp_init before calling any other bulk-copy function. bcp_init performs the necessary initialisations for a bulk copy of data between the Easysoft ODBC-SQL Server Driver and SQL Server.
The bcp_init function must be provided with an ODBC connection handle enabled for use with bulk copy functions. To enable the handle, use SQLSetConnectAttr with SQL_COPT_SS_BCP set to SQL_BCP_ON on an allocated, but not connected, connection handle. Attempting to assign the attribute on a connected handle results in an error.
When a data file is specified, bcp_init examines the structure of the database source or target table, not the data file. bcp_init specifies data format values for the data file based on each column in the database table, view, or SELECT result set. This specification includes the data type of each column, the presence or absence of a length or null indicator and terminator byte strings in the data, and the width of fixed-length data types. bcp_init sets these values as follows:
When copying to SQL Server, the data file must have data for each column in the database table. When copying from SQL Server, data from all columns in the database table, view, or SELECT result set are copied to the data file.
To change data format values specified for a data file, call bcp_columns and bcp_colfmt.
If no data file is used, you must call bcp_bind to specify the format and location in memory of the data for each column, then copy data rows to the SQL Server using bcp_sendrow.
bcp_moretext
Sends part of a long, variable-length data type value to SQL Server.
Syntax
RETCODE bcp_moretext (
HDBC hdbc,
DBINT cbData,
LPCBYTE pData);Arguments
hdbcIs the bulk copy-enabled ODBC connection handle.
cbDataThe number of bytes of data being copied to SQL Server from the data referenced by pData. A value of SQL_NULL_DATA indicates NULL.
pData
A pointer to the supported, long, variable-length data chunk to be sent to SQL Server.
Returns
SUCCEED or FAIL.
Remarks
This function can be used in conjunction with bcp_bind and bcp_sendrow to copy long, variable-length data values to SQL Server in a number of smaller chunks. bcp_moretext can be used with columns that have the following SQL Server data types: TEXT, NTEXT, IMAGE, VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(max), user-defined type (UDT), and XML. bcp_moretext does not support data conversions, the data supplied must match the data type of the target column.
If bcp_bind is called with a non-NULL pData parameter for data types that are supported by bcp_moretext, bcp_sendrow sends the entire data value, regardless of length. If, however, bcp_bind has a NULL pData parameter for supported data types, bcp_moretext can be used to copy data immediately after a successful return from bcp_sendrow indicating that any bound columns with data present have been processed.
bcp_readfmt
Reads a data file format definition from the specified format file.
Syntax
HDBC hdbc,
LPCTSTR szFormatFile);Arguments
hdbcIs the bulk copy-enabled ODBC connection handle.
szFormatFileIs the path and file name of the file containing the format values for the data file.
Returns
SUCCEED or FAIL.
Remarks
After bcp_readfmt reads the format values, it makes the appropriate calls to bcp_columns and bcp_colfmt. There is no need for you to parse a format file and make these calls.
To persist a format file, call bcp_writefmt.
The bulk-copy utility (bcp) can also save user-defined data formats in files that can be referenced by bcp_readfmt.
bcp_sendrow
Sends a row of data to SQL Server.
Syntax
RETCODE bcp_sendrow (
HDBC hdbc);Arguments
hdbcThe bulk copy-enabled ODBC connection handle.
Returns
SUCCEED or FAIL.
Remarks
The bcp_sendrow function builds a row from variables bound with bcp_bind and sends it to SQL Server.
If bcp_bind is called specifying a long, variable-length data type, for example, an eDataType parameter of SQLTEXT and a non-NULL pData parameter, bcp_sendrow sends the entire data value. If, however, bcp_bind has a NULL pData parameter, bcp_sendrow returns control to the application immediately after all columns with data specified are sent to SQL Server. The application can then call bcp_moretext repeatedly to send the long, variable-length data to SQL Server, a chunk at a time.
When bcp_sendrow is used to bulk copy rows from program variables into SQL Server tables, rows are committed only when the user calls bcp_batch or bcp_done. The user can choose to call bcp_batch once every n rows or when there is a lull between periods of incoming data. If bcp_batch is never called, the rows are committed when bcp_done is called.
Example
#include <stdio.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>
#include <sqlncli.h>
SQLHENV henv = SQL_NULL_HENV;
HDBC hdbc1 = SQL_NULL_HDBC;
DBINT idRow = 5;
char * pPart1 = "Text chunk 1.";
char * pPart2 = "Text chunk 2.";
char * pPart3 = "Text chunk 3.";
DBINT cbAllParts;
DBINT nRowsProcessed;
void Cleanup() {
if (hdbc1 != SQL_NULL_HDBC) {
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
}
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
int main() {
RETCODE retcode;
/* Allocate the ODBC environment and save handle. */
retcode = SQLAllocHandle(SQL_HANDLE_ENV, NULL, & henv);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLAllocHandle(Env) Failed\n\n");
Cleanup();
return (9);
}
/* Notify ODBC that this is an ODBC 3.0 app. */
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLSetEnvAttr(ODBC version) Failed\n\n");
Cleanup();
return (9);
}
/* Allocate ODBC connection handle, set BCP mode, and connect. */
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, & hdbc1);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLAllocHandle(hdbc1) Failed\n\n");
Cleanup();
return (9);
}
retcode = SQLSetConnectAttr(hdbc1, SQL_COPT_SS_BCP, (void * ) SQL_BCP_ON, SQL_IS_INTEGER);
if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLSetConnectAttr(hdbc1) Failed\n\n");
Cleanup();
return (9);
}
retcode = SQLDriverConnect(hdbc1, NULL, "DSN=MYDSN;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO)) {
printf("SQLDriverConnect() Failed\n\n");
Cleanup();
return (9);
}
/* Initialise the bulk copy. */
retcode = bcp_init(hdbc1, "MyTable", NULL, NULL, DB_IN);
if ((retcode != SUCCEED)) {
printf("bcp_init(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
/* Bind program variables to table columns. */
retcode = (bcp_bind(hdbc1, (LPCBYTE) & idRow, 0, SQL_VARLEN_DATA, NULL, 0, SQLINT4, 1));
if ((retcode != SUCCEED)) {
printf("bcp_bind(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
cbAllParts = strlen(pPart1) + strlen(pPart2) + strlen(pPart3);
retcode = (bcp_bind(hdbc1, NULL, 0, cbAllParts, NULL, 0, SQLTEXT, 2));
if ((retcode != SUCCEED)) {
printf("bcp_bind(hdbc1) Failed.\n\n");
Cleanup();
return (9);
}
/* Now send this row, with the text value broken into three chunks. */
retcode = (bcp_sendrow(hdbc1));
if ((retcode != SUCCEED)) {
printf("bcp_sendrow(hdbc1) Failed.n\n");
Cleanup();
return (9);
}
cbAllParts = strlen(pPart1);
retcode = (bcp_moretext(hdbc1, cbAllParts, pPart1));
if ((retcode != SUCCEED)) {
printf("bcp_moretext(hdbc1) Failed. 1\n\n");
Cleanup();
return (9);
}
cbAllParts = strlen(pPart2);
retcode = (bcp_moretext(hdbc1, cbAllParts, pPart2));
if ((retcode != SUCCEED)) {
printf("bcp_moretext(hdbc1) Failed. 2\n\n");
Cleanup();
return (9);
}
cbAllParts = strlen(pPart3);
retcode = (bcp_moretext(hdbc1, cbAllParts, pPart3));
if ((retcode != SUCCEED)) {
printf("bcp_moretext(hdbc1) Failed. 3\n\n");
Cleanup();
return (9);
}
/* We're all done. */
nRowsProcessed = bcp_done(hdbc1);
/* Cleanup */
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}bcp_setbulkmode
bcp_setbulkmode lets you specify the column format in a bulk copy operation, setting all the column attributes in a single function call.
Syntax
RETCODE bcp_setbulkmode (
HDBC hdbc,
INT property,
void * pField,
INT cbField,
void * pRow,
INT cbRow
);Arguments
hdbcThe bulk copy-enabled ODBC connection handle.
propertyA constant of type BYTE. Refer to the table in the Remarks section for a list of the constants.
pFieldThe pointer to the field terminator value.
pRowThe pointer to the row terminator value.
cbRowThe length in bytes of the row terminator value.
Returns
SUCCEED or FAIL.
Remarks
bcp_setbulkmode can be used to bulk copy out of either a query or a table. When bcp_setbulkmode is used to bulk copy out a query statement, it must be called before calling bcp_control with BCP_HINT.
bcp_setbulkmode is an alternative to using bcp_setcolfmt and bcp_columns, which only let you specify the format of one column per function call.
bcp_setcolfmt
This function provides a flexible approach to specifying the column format in a bulk copy operation. It is used to set individual column format attributes. Each call to bcp_setcolfmt sets one column format attribute.
The bcp_setcolfmt function specifies the source or target format of the data in a user file. When used as a source format, bcp_setcolfmt specifies the format of an existing data file used as a data source of data in a bulk copy to a table in SQL Server. When used as a target format, the data file is created using the column formats specified with bcp_setcolfmt.
Syntax
RETCODE bcp_setcolfmt (
HDBC hdbc,
INT field,
INT property,
void* pValue,
INT cbValue);Arguments
hdbcThe bulk copy-enabled ODBC connection handle.
fieldThe ordinal column number for which the property is being set.
propertyOne of the property constants. Property constants are defined in this table
| Value | Description |
|---|---|
|
BYTE The data type of this column in the user file. If different from the data type of the corresponding column in the database table, bulk copy converts the data if possible. The To specify the default data representation for the SQL Server data type, set this parameter to 0. |
|
The length in bytes of the data (column length). |
|
Pointer to the terminator sequence (either ANSI or Unicode as appropriate) to be used for this column. This parameter is useful mainly for character data types because all other types are of fixed length or, in the case of binary data, require an indicator of length to accurately record the number of bytes present. |
|
Ordinal position of the column in the database |
|
Collation name. |
pValueThe pointer to the value to associate to the property. It allows each column format property to be set individually.
cbValueThe length of the property buffer in bytes.
Returns
SUCCEED or FAIL.
Remarks
The bcp_setcolfmt function allows you to specify the user-file format for bulk copies.
You don’t need to copy all data in a user file to the SQL Server table. To skip a column, specify the format of the data for the column, setting the BCP_FMT_SERVER_COL parameter to 0. If you want to skip a column, you must specify its type.
bcp_writefmt
Creates a format file containing a description of the format of the current bulk copy data file.
Syntax
RETCODE bcp_writefmt (
HDBC hdbc,
LPCTSTR szFormatFile);Arguments
hdbcThe bulk copy-enabled ODBC connection handle.
szFormatFileThe path and file name of the user file to receive format values for the data file.
Returns
SUCCEED or FAIL.
Remarks
The format file specifies the data format of a data file created by bulk copy.
Table-valued parameters
Table-valued parameters, allow multiple rows or values to be passed to a query or stored procedure in one call. Table-valued parameters decrease network latency by reducing network round trips. For example, given the task of updating multiple order line items an application traditionally would call one procedure to update the order and another procedure to update the line items. The second procedure would be called multiple times, once for each line item, therefore requiring multiple database round trips to fulfill the objective.
The Easysoft ODBC-SQL Server Driver enables data to be sent as a table-valued parameter with all values in memory.
The following example inserts an order and multiple order detail rows by passing a table-valued parameter to one stored procedure.
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#include <stdlib.h>
#include <string.h>
#include "sqlncli.h"
main() {
SQLHENV env;
SQLHDBC dbc;
SQLHSTMT hstmt;
/* ODBC API return status */
SQLRETURN ret;
/* SQL parameters */
#define ITEM_ARRAY_SIZE 20
SQLCHAR CustCode[6];
SQLCHAR * TVP = (SQLCHAR * )
"TVParam";
SQLINTEGER ProdCode[ITEM_ARRAY_SIZE], Qty[ITEM_ARRAY_SIZE];
SQLINTEGER OrdNo;
char OrdDate[23];
/* Indicator/length variables associated with SQL parameters */
SQLLEN cbCustCode, cbTVP, cbProdCode[ITEM_ARRAY_SIZE], cbQty[ITEM_ARRAY_SIZE], cbOrdNo, cbOrdDate;
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, & env);
/* We want ODBC 3 support */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void * ) SQL_OV_ODBC3, 0);
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, & dbc);
/* Connect to the DSN mydsn.
* You will need to change mydsn to one you have created and tested
* The SQL Server database your DSN connects to needs:
*
* These tables:
*
* CREATE TABLE dbo.TVPOrder (
* CustCode varchar(5),
* OrdNo int identity,
* OrdDate datetime
* )
*
* CREATE TABLE dbo.TVPItem (
* OrdNo int,
* ProdCode int,
* Qty int
* )
*
*
* This user-defined table type:
*
* IF (SELECT COUNT(*) FROM sys.table_types
* WHERE name = 'TVPParam' AND schema_id = 1) = 0
* CREATE TYPE dbo.TVPParam AS TABLE(ProdCode integer, Qty integer)
*
* This procedure:
*
* CREATE PROCEDURE
* dbo.TVPOrderEntry
* (
* @CustCode varchar(5),
* @Items TVPParam READONLY,
* @OrdNo integer output,
* @OrdDate datetime output)
* AS
* SET @OrdDate = GETDATE();
*
* INSERT INTO TVPOrder (OrdDate, CustCode)
* VALUES (@OrdDate, @CustCode);
*
* SELECT @OrdNo = SCOPE_IDENTITY();
*
* INSERT INTO TVPItem (OrdNo, ProdCode, Qty)
* SELECT @OrdNo, ProdCode, Qty FROM @Items
*/
SQLDriverConnect(dbc, NULL, "DSN=SQLSERVER_SAMPLE;", SQL_NTS,
NULL, 0, NULL, SQL_DRIVER_COMPLETE);
SQLAllocHandle(SQL_HANDLE_STMT, dbc, & hstmt);
/* Bind parameters for call to TVPOrderEntry */
/* 1 - CustCode input */
ret = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 5, 0, CustCode, sizeof(CustCode), & cbCustCode);
/* 2 - Items TVP */
ret = SQLBindParameter(hstmt,
2,
SQL_PARAM_INPUT,
SQL_C_DEFAULT,
SQL_SS_TABLE,
ITEM_ARRAY_SIZE, /* ColumnSize: For a table-valued parameter this is the row array size */
0, /* DecimalDigits: For a table-valued parameter this is always 0 */
TVP, /* ParameterValuePtr: For a table-valued parameter this is the type name of the */
/* table-valued parameter, and also a token returned by SQLParamData */
strlen(TVP), /* BufferLength: For a table-valued parameter this is the length of the type name or SQL_NTS */
&
cbTVP); /* StrLen_or_IndPtr: For a table-valued parameter this is the number of rows actually used */
cbOrdNo = 0;
cbOrdDate = 0;
/* 3 - OrdNo output */
ret = SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, & OrdNo,
sizeof(SQLINTEGER), & cbOrdNo);
/* 4 - OrdDate output */
ret = SQLBindParameter(hstmt, 4, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_TYPE_TIMESTAMP, 23, 3, & OrdDate,
sizeof(OrdDate), & cbOrdDate);
/* Bind columns for the table-valued parameter (parameter 2) */
/* First set focus on parameter 2 */
ret = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 2, SQL_IS_INTEGER);
/* Col 1 - ProdCode */
ret = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, ProdCode, sizeof(SQLINTEGER), cbProdCode);
/* Col 2 - Qty */
ret = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, Qty, sizeof(SQLINTEGER), cbQty);
/* Reset parameter focus */
ret = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 0, SQL_IS_INTEGER);
/* Populate parameters */
cbTVP = 0; /* Number of rows available for input */
strcpy((char * ) CustCode, "BEAUC");
cbCustCode = SQL_NTS;
ProdCode[cbTVP] = 555;
cbProdCode[cbTVP] = sizeof(SQLINTEGER);
Qty[cbTVP] = 5;
cbQty[cbTVP] = sizeof(SQLINTEGER);
cbTVP++; /* Number of rows available for input */
ProdCode[cbTVP] = 666;
cbProdCode[cbTVP] = sizeof(SQLINTEGER);
Qty[cbTVP] = 6;
cbQty[cbTVP] = sizeof(SQLINTEGER);
cbTVP++; /* Number of rows available for input */
/* Call the procedure */
ret = SQLExecDirect(hstmt, (SQLCHAR * )
"{call TVPOrderEntry(?, ?, ?, ?)}", SQL_NTS);
}Binding procedure parameters by name
The Easysoft ODBC-SQL Server Driver supports named parameters, which allows an application to specify stored procedure parameters by name instead of by position in the procedure call.
This C code sample calls the AdventureWorks2008 stored procedure uspSearchCandidateResumes. The sample specifies the stored procedure’s parameters by name.
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
main() {
SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;
SQLHANDLE ipd;
SQLRETURN ret;
/* Procedure input */
SQLCHAR param_name_1[64], param_name_2[64],
param_name_3[64], param_name_4[64];
/* Search candidate resumes for this text: */
SQLCHAR search_string[64] = "ISO9000";
/* Enable the default values for the other procedure */
/* parameters to be overridden. Initialise variables */
/* to the corresponding parameter's default value. */
BOOL use_inflectional = 0;
BOOL use_thesaurus = 0;
SQLSMALLINT language = 0;
/* Procedure output */
SQLCHAR col_1_name[64], col_2_name[64];
SQLINTEGER col_1;
SQLSMALLINT col_2, col_1_name_length, col_2_name_length;
SQLLEN len_1, len_2;
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, & env);
/* We want ODBC 3 support */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void * )
SQL_OV_ODBC3, 0);
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, & dbc);
/* Connect to the DSN mydsn */
/* Change mydsn to one you have created and tested */
SQLDriverConnect(dbc, NULL, "DSN=mydsn;", SQL_NTS,
NULL, 0, NULL, SQL_DRIVER_COMPLETE);
/* Allocate a statement handle */
SQLAllocHandle(SQL_HANDLE_STMT, dbc, & stmt);
/* Prepare the statement that will call the procedure */
SQLPrepare(stmt, "{call uspSearchCandidateResumes (?, ?, ?, ?)}",
SQL_NTS);
/* Bind local variables to the parameters in the preceding */
/* statement. */
SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, 1000, 0, search_string, 0, 0);
SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_BIT, SQL_BIT,
1, 0, & use_inflectional, 0, 0);
SQLBindParameter(stmt, 3, SQL_PARAM_INPUT, SQL_C_BIT, SQL_BIT,
1, 0, & use_thesaurus, 0, 0);
SQLBindParameter(stmt, 4, SQL_PARAM_INPUT, SQL_INTEGER,
SQL_INTEGER, 1, 0, & language, 0, 0);
/* Bind columns in the procedure result set to local */
/* variables */
SQLBindCol(stmt, 1, SQL_INTEGER, & col_1, 0, & len_1);
SQLBindCol(stmt, 2, SQL_INTEGER, & col_2, 0, & len_2);
/* Get IPD handle. The IPD contains information about the */
/* statement parameters, such as their SQL data types, */
/* lengths, and nullability. */
SQLGetStmtAttr(stmt, SQL_ATTR_IMP_PARAM_DESC, & ipd, 0, 0);
/* Set the SQL_DESC_NAME field of the IPD to the parameter */
/* name */
SQLSetDescField(ipd, 1, SQL_DESC_NAME, "@searchString",
SQL_NTS);
SQLSetDescField(ipd, 2, SQL_DESC_NAME, "@useInflectional",
SQL_NTS);
SQLSetDescField(ipd, 3, SQL_DESC_NAME, "@useThesaurus",
SQL_NTS);
SQLSetDescField(ipd, 4, SQL_DESC_NAME, "@language", SQL_NTS);
/* Execute the prepared statement */
SQLExecute(stmt);
/* Retrieve the parameter names */
SQLGetDescField(ipd, 1, SQL_DESC_NAME, param_name_1,
sizeof(param_name_1), NULL);
SQLGetDescField(ipd, 2, SQL_DESC_NAME, param_name_2,
sizeof(param_name_2), NULL);
SQLGetDescField(ipd, 3, SQL_DESC_NAME, param_name_3,
sizeof(param_name_3), NULL);
SQLGetDescField(ipd, 4, SQL_DESC_NAME, param_name_4,
sizeof(param_name_4), NULL);
printf("Procedure input\n");
printf("===============\n");
printf("%s value:\t%s\n", param_name_1, search_string);
printf("%s value:\t%d\n", param_name_2, use_inflectional);
printf("%s value:\t%d\n", param_name_3, use_thesaurus);
printf("%s value:\t%d\n", param_name_4, language);
printf("\nProcedure Output\n");
printf("===============\n");
/* Retrieve the column names for the procedure result set */
SQLColAttribute(stmt, 1, SQL_DESC_NAME, col_1_name,
sizeof(col_1_name), & col_1_name_length, 0);
SQLColAttribute(stmt, 2, SQL_DESC_NAME, col_2_name,
sizeof(col_2_name), & col_2_name_length, 0);
printf("%s\t\t%s\n", col_1_name, col_2_name);
/* Fetch the procedure result set. */
while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
printf("%d\t\t\t%d\n", col_1, col_2);
}
/* Free up allocated handles and disconnect from the driver */
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
SQLDisconnect(dbc);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}When run, the sample produces output similar to that shown in the following shell session:
$ cc -I/usr/local/easysoft/unixODBC/include/ uspSearchCandidateResumes.c -o uspSearchCandidateResumes -L/usr/local/easysoft/unixODBC/lib/ -lodbc
$ chmod +x ./uspSearchCandidateResumes
$ ./uspSearchCandidateResumes
Procedure input
==============
@searchString value: ISO9000
@useInflectional value: 0
@useThesaurus value: 0
@language value: 0
Procedure Output
==============
JobCandidateID RANK
1 9
7 9
10 12
---SQL Server authentication modes
Users are granted access to SQL Server instances through a SQL Server login. The ways that SQL Server provides to authenticate SQL Server logins include: 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:
-
User names and passwords are encrypted.
-
Security is easier to manage (a single Windows security model instead of a separate SQL Server security model).
-
Login security improves through password expiration, minimum password lengths, and account lockout policies.
The Easysoft ODBC-SQL Server Driver supports both Windows Authentication and SQL Server Authentication. You specify the SQL Server login name and password with the User and Password data source attributes or the UID and PWD connection string attributes.
Windows authentication
The Easysoft ODBC-SQL Server Driver asks SQL Server to use Windows Authentication to validate the connection if:
-
The
Userattribute value contains a backslash (used to separate a user name from a domain), for example,mydomain\myuser.-Or-
-
The
Trusted_Connectionattribute is turned on (set toYes).
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, refer to /usr/local/easysoft/sqlserver/crypt/COPYING.
To comply with the terms of the LGPL, the encryption functions are not included in the main Easysoft ODBC-SQL Server Driver library. Instead, they are provided in the shared library file /usr/local/easysoft/lib/libestdscrypt.so. The Easysoft ODBC-SQL Server Driver distribution includes the source files for this library. The source files are installed in /usr/local/easysoft/sqlserver/crypt. The supplied Makefile will build the library on your Easysoft ODBC-SQL Server Driver platform.
SQL Server authentication
If the User attribute value does not contain a backslash, the Easysoft ODBC-SQL Server Driver asks SQL Server to use SQL Server Authentication to validate the connection. The Easysoft ODBC-SQL Server Driver sends the password to SQL Server in an encrypted form, although the encryption is less strong then that used for trusted connections. The SQL Server user name is sent in plain text.
Encrypting connections to SQL Server
SQL Server can 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 over an encrypted connection. The SSL version of driver is included in the Easysoft ODBC-SQL Server Driver distribution and should be used instead of the standard Easysoft SQL Server driver whenever an SSL connection is required.
In this section:
Accessing SQL Server over an encrypted connection
Read this topic if you need to connect to a SQL Server instance over an encrypted connection. Database administrators should refer first to Configuring and testing SSL encryption for information about setting up SSL encryption on the client and SQL Server computer.
Before following the steps in this topic, contact your database administrator for the following information:
-
Is encryption enabled in the SQL Server instance (either the Force protocol encryption option or ForceEncryption option enabled)?
-
Is the SQL Server instance using a self-generated SSL certificate?
-
If neither of the preceding points are true, where on the Easysoft ODBC-SQL Server Driver with SSL Support computer is the root certificate authority (CA) certificate installed?
To access SQL Server over an encrypted connection
-
In
/etc/odbc.ini, find theSQLSERVER_SAMPLE_SSLdata source. -
Edit the data source to connect to your SQL Server instance. For example:
[SQLSERVER_SAMPLE_SSL] Driver = Easysoft ODBC-SQL Server SSL Description = Easysoft SQL Server ODBC driver Server = MYSQLSERVERCOMPUTER\MYINSTANCE Port = Database = User = MYDOMAIN\myuser Password = mypassword
-
If SSL encryption is enabled on the SQL Server instance, set the
Encryptattribute toNo, and then skip to step 5. Otherwise, skip this step. -
Do one of the following:
-
If the SQL Server instance is using a self-generated SSL certificate, set the
TrustServerCertificateattribute toYes. -
If the SQL Server instance is not using a self-generated certificate, edit the
CertificateFileattribute value. Specify the file that contains the public key certificate of the root CA that signed the SQL Server SSL certificate. For example,CertificateFile = /usr/share/ssl/certs/ca-bundle.crt.
-
-
Use isql to test the data source. For example:
cd /usr/local/easysoft/unixODBC/bin. ./isql -v SQLSERVER_SAMPLE_SSL
Configuring and testing SSL encryption
Refer to this section if you are a database administrator who needs to configure and test the Easysoft ODBC-SQL Server Driver with SSL Support. The section shows you how to configure the driver to request an encrypted connection and verify that data is encrypted.
The section also contains information about setting up SSL encryption on the SQL Server computer. This information is intended to supplement rather than replace the Microsoft SQL Server documentation.
Installing an SSL certificate
Before you can access SQL Server over an encrypted connection, an SSL certificate needs to be installed on the SQL Server computer. SQL Server can use an SSL certificate from a trusted CA if available or generate a self-signed certificate.
| Even though SQL Server can make encryption available without an installed SSL certificate, Microsoft recommend using a certificate signed by a trusted authority whenever possible. SSL connections that are encrypted with a self-signed certificate protect against packet sniffing but do not protect against man-in-the-middle attacks. In a man-in the-middle attack, attackers route packets through their servers, which sniff the contents as they pass through. |
Refer to the following Microsoft documentation for installation information:
Testing that SSL is available on the SQL Server computer
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
-
In SQL Server Configuration Manager, double-click SQL Server Network Configuration to expand the Protocols list. Right-click Protocols for the instance that you want to connect to and click Properties. Make sure that ForceEncryption is set to Yes.
-
Restart the instance.
-
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.
| You can verify that SQL Server has successfully generated a self-signed SSL certificate by checking the SQL Server error log for a line containing: |
A self-generated certificate was successfully loaded for encryption.
If SQL Server is unable to generate a self-signed certificate, you will be unable to connect to the instance over an encrypted connection. Note that when testing SSL with SQL Server Express, we had to change the account used by the SQL Server instance from Network Service to Local System before the instance could generate a certificate.
Installing the root CA certificate
If encryption is enabled on the SQL Server computer, the client computer 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 computer rather than the SQL Server computer, 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 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 computer 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 computer 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 computer:
-
On the SQL Server computer, in the Windows Run dialog box, enter:
mmc
-
In Microsoft Management Console, on the File menu, choose Add/Remove Snap-in, and then choose Add.
-
In the Add Standalone Snap-In dialog box, double-click Certificates. Choose Computer account when prompted and then choose Next. Choose Finish.
-
Choose Close and then OK to close the Add/Remove Snap-in dialog boxes.
-
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 choose Certificates. In the right pane of the console window, right-click the CA certificate, point to All Tasks, and then choose Export.
-
Complete the Certificate Export wizard.
When prompted to choose the export format, make sure that you choose *Base-64 encoded X.509*.
-
Use FTP to copy the exported CA certificate to the client computer from which you want to access SQL Server.
Configuring the client to request an encrypted connection
SSL encryption can be enabled either on the SQL Server computer or the client computer. If you do not want to enable encryption globally on the SQL Server computer, you can enable encryption on a per-client basis.
To configure the client to request an encrypted connection:
-
In SQL Server Configuration Manager, double-click SQL Server Network Configuration to expand the Protocols list. Right-click Protocols for the instance that you want to connect to and choose Properties. Make sure that ForceEncryption is set to No.
-
On the computer 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
Driverattribute must be set toEasysoft ODBC-SQL Server SSL.Set the
EncryptandTrustServerCertificateattributes toNo. For example:[SQLSERVER_SSL_CONNECTION] Driver = Easysoft ODBC-SQL Server SSL Server = MYSQLSERVERCOMPUTER\MYINSTANCE User = MYDOMAIN\myuser Password = mypassword Encrypt = No TrustServerCertificate = No
-
Use an application such as Microsoft Network Monitor, Snort, or Ethereal to capture network traffic between this computer and the SQL Server computer.
Using Network Monitor or a network sniffer tool lets you verify that you have successfully made an encrypted connection to the SQL Server computer. When testing the Easysoft driver, we used Network Monitor on the SQL Server computer and Snort on the client computer to capture network traffic.
-
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
-
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 computer by running snort -vde:
8.E.m.p.l.o.y.e e.I.D.......=.R a.t.e.C.h.a.n.g e.D.a.t.e...... <.R.a.t.e...... 0.P.a.y.F.r.e.q u.e.n.c.y...... .M.o.d.i.f.i.e d.D.a.t.e......
-
Press
RETURNto exit isql. -
In your data source, set the
Encryptattribute toYes. -
Do one of the following:
-
To connect to a SQL Server instance on a computer where an SSL certificate has been provisioned, use the
CertificateFileattribute to specify the path to the CA certificate file. The certificate file must contain the public key certificate of the CA that signed the SQL Server certificate. The public key certificate must be in base-64 PEM format. -
If the CA’s public key was already installed on this computer, 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 computer and copied it to this computer, specify the path to the certificate file. For example,CertificateFile = /usr/share/ssl/CA/MyCA.cer. -
To connect to a SQL Server instance that is using a self-signed certificate, set the
TrustServerCertificateattributes toYes.
-
-
Use isql to connect to the data source and retrieve the same data as you did in step 4.
-
In your network sniffer, verify that the data returned by the Easysoft ODBC-SQL Server Driver with SSL Support is now encrypted.
This example output shows encrypted SQL Server data captured in Snort.
.E..{i.2.8.G.q.. .n..{X.... 4..O. &....Lt..Z.wrH.8 .W..{.........., ....1s_..).\k.6. ..4U..4..D...5.U &...I......+..w. l.W...&}x....... ....%......7...J ..C$...,j..52.~. .w. Q.qE.Q....]4 .\.Y?...|R.VOr.S .....K.W.. 2.#.T .G..+..F.....T.. @"..+-.......
Encrypting the login packet
When connecting to SQL Server, the Easysoft driver passes a user name and password to the SQL Server instance. For Windows user names, a domain name is also sent. These authentication details are stored inside a login packet that is transmitted between the Easysoft driver and SQL Server.
SQL Server uses 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.
| For Windows logins, SQL Server transmits the domain and user name in plain text in the response to the login packet. If you do not want this information sent in plain text, you need to enable encryption either on the SQL Server or Easysoft ODBC-SQL Server Driver with SSL Support computer. |
Easysoft ODBC-SQL Server Driver with SSL support attribute fields
The following attributes may be set in the odbc.ini file:
| Attribute | Description |
|---|---|
Encrypt = Yes | No | Strict |
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 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 |
EncryptDeny = Yes | No |
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)". |
HostNameInCertificate = value |
Specifies the hostname to be expected in the server certificate when encryption is negotiated. Use |
TrustServerCertificate = Yes | No |
Enables the client to request encryption even when an SSL certificate has not been installed on the SQL Server computer. 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 If the SQL Server ForceEncryption option is enabled, the Note that SQL Server 2000 cannot generate a self-signed certificate. SSL encryption is only available if the SQL Server 2000 instance is running on a computer that has a certificate assigned from a public certification authority. By default, |
CertificateFile = filename |
The file that contains the public key certificate of the CA that signed the SQL Server certificate. The CA certificate file must be in base-64 PEM format. If the CA certificate is not installed on your client computer, you need to export the certificate on the SQL Server computer and install it on the client. Examples To load a CA certificate from the root CA certificate store supplied with the OpenSSL distribution, use: CertificateFile = /usr/share/ssl/certs/ca-bundle.crt To load a private CA certificate named CertificateFile = /usr/share/ssl/CA/MyCA.cer |
Cypher = value |
The cypher suite that the Easysoft ODBC-SQL Server Driver with SSL Support will request during the SSL handshake with the SQL Server computer. A cypher suite is a set of authentication, encryption, and data integrity algorithms used to protect data exchanged between computers. During the SSL handshake part of the connection process, the SSL layer in the ODBC driver and the Schannel layer on the SQL Server computer negotiate to decide which cypher suite they will use. To find out which cypher suite is being used for a particular connection, enable Easysoft ODBC-SQL Server Driver with SSL Support logging. To do this, include these lines in your ODBC data source: LOGFILE = /tmp/sql-server-driver.log LOGGING = Yes Connect and then examine the driver log file. Look for a log file entry similar to: SSL using cypher 'RC4-MD5 SSLv3 Kx=RSA Au=RSA Enc=RC4(128) Mac=MD5' This entry shows that the ODBC driver and the SQL Server computer negotiated the following cryptographic protection for the connection: Encryption: RC4 Encryption strength: 128-bit Cryptographic checksum: MD5 Authentication: RSA (You can also display the cryptographic settings negotiated during the SSL handshake by enabling Schannel logging. Enable the "Log informational and success events" Schannel logging option to write this information to the Windows Event Viewer logs. For information about how to do this, refer to https://support.microsoft.com/kb/260729.) Use the # Request Triple DES (3DES) for data encryption. # Request Secure Hash Algorithm (SHA) for data # integrity protection. Cypher = 3DES+SHA -Or- # Request Advanced Encryption Standard (AES) for data # encryption. If AES is not available on the server, # request 3DES. Cypher = AES:3DES |
Cypher = value |
-Or- # Use Secure Hash Algorithm (SHA) to protect data # integrity. Let the SSL layers negotiate which # encryption algorithm to use. Cypher = SHA If you specify a cypher suite that is not available on the server computer, 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 Note that if you’re 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 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, refer to https://support.microsoft.com/kb/920995. |
Entropy = filename |
The Easysoft ODBC-SQL Server Driver with SSL Support needs a source of unpredictable data to work correctly. Many open source operating systems provide a "randomness device" ( 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 Entropy = /etc/entropy |
Database mirroring
Database mirroring increasess data availability by creating a standby copy of a database. In database mirroring, all updates to a database (the principal database) are automatically copied to a standby database (the mirror database). If the principal database server fails, the mirror database server takes over the role of principal server and brings its copy of the database online as the principal database.
For example, Partner_A and Partner_B are two partner servers, with the principal database initially on Partner_A as principal server, and the mirror database residing on Partner_B as the mirror server. If Partner_A goes offline, the database on Partner_B can fail over to become the current principal database. When Partner_A rejoins the mirroring session, it becomes the mirror server and its database becomes the mirror database.
In this section:
Making the initial connection to a database mirroring session
To establish the initial connection to a mirrored database, a data source needs to supply the current principal server instance (known as the initial partner). Optionally, the data source can also supply the current mirror server instance (known as the failover partner). This setting is used to connect to the mirror server if the initial connection to the principal server fails. The data source must also supply the database name. The Easysoft ODBC-SQL Server Driver will not attempt to failover to the partner database if this is not done.
In the following example data source, the principal server instance for the AdventureWorks database is 123.34.45.56:4724. The database is mirrored on 123.34.45.57:4724.
[SQL Server Database Mirroring] Driver = Easysoft ODBC-SQL Server # The current principal server instance. Server = 123.34.45.56:4724 # The current mirror server instance. If the initial attempt to # connect the principal server fails, try to connect to this server. Failover_Partner = 123.34.45.57:4724 # You must specify the database to be mirrored. Database = AdventureWorks # This login must have permission to access the database on both # the principal and mirror database server. User = my_domain\my_username Password = my_password
For more information about setting up a data source for a mirrored database, refer to 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.
| A database mirroring session does not protect against server-access problems that are specific to client computers, such as when a client computer is having a problems communicating with the network. A connection attempt to a mirrored database can also fail for a variety of reasons that are unrelated to the Easysoft ODBC-SQL Server Driver; for example, a connection attempt can fail because of a network error. |
Data source attributes for a mirrored database
This section discusses the ODBC data source attributes that are relevant for connecting to a mirrored database. For information about all Easysoft ODBC-SQL Server Driver data source attributes, refer to Connection attributes.
| Attribute | Notes |
|---|---|
Server |
Use the Server=partner_A_host -Or- Server=partner_A_host\instance_2 Note that when you specify a computer 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 # IPv4 address Server = 123.34.45.56:4724 -Or- # IPv6 address Server = 2001:4898:23:1002:20f:1fff:feff:b3a3*7022 IPv6 = 1 |
Database |
The data source must specify the name of the mirrored database. To do this, use the |
Failover_Partner |
Use the The 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, refer to the The impact of a stale failover partner name. |
User |
The login that you specify with the |
Connection retry algorithm
When you specify a failover partner with the Failover_Partner attribute, connection attempts are regulated by a connection retry algorithm that is specific to database mirroring. The connection retry algorithm determines the maximum time (the retry time) allotted for opening a connection in a given connection attempt.
If a connection attempt fails or the retry time expires before it succeeds, the Easysoft ODBC-SQL Server Driver tries the other partner. If a connection is not opened by this point, the Easysoft driver alternately tries the initial and failover partner names, until a connection is opened or the login period times out. The default SQL Server login timeout period is 15 seconds.
The retry time is a percentage of the login period. The retry time for a connection attempt is larger in each successive round. In the first round, the retry time for each of the two attempts is 8 percent of the total login period. In each successive round, the retry algorithm increases the maximum retry time by the same amount. For example, the retry times for the first six connection attempts is as follows:
8%, 8%, 16%, 16%, 24%, 24%
The retry time is calculated by using the following formula:
RetryTime = PreviousRetryTime + ( 0.08 * LoginTimeout )
where PreviousRetryTime is initially 0. For example:
Round Retry Per Attempt 1 0 + (0.08 * 15000) = 1200 msec 2 1200 + (0.08 * 15000) = 2400 msec 3 2400 + (0.08 * 15000) = 3600 msec
The impact of a stale failover partner name
The database administrator can change the failover partner at any time. Therefore, a failover partner name specified in a data source might be out of date, or stale. For example, consider a failover partner named Partner_B that is replaced by another server instance, Partner_C. If the Easysoft ODBC-SQL Server Driver supplies Partner_B as the failover partner name, that name is stale. When the failover partner name is stale, the connection attempt will fail if the initial partner specified in the data source is unavailable.
To find out the current failover partner for a mirrored database, use tdshelper:
tdshelper -s initial_partner -p port -u username -a password -f database -v
where:
-
initial_partneris the IP address or computer name of the principal instance for the database specified with-f. -
portis the TCP port that the principal instance is listening on. If the instance is listening on the default port, 1433, omit-p port. -
usernameandpasswordare the user name and password for a SQL Server login that can access the mirrored database. -
databaseis the mirrored database.
If the principal server instance reports the name of the failover partner, tdshelper displays the partner instance name in the last line of its output. For example:
cd /usr/local/easysoft/sqlserver/bin ./tdshelper -s my_initial_partner -u myuser -a mypassword -f my_mirroreddb -v tdshelper: connecting to my_initial_partner tdshelper: successfully opened connection tdshelper: successfully logged into server with diagnostic records tdshelper: diag record 01000:[Easysoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english. tdshelper: diag record 01000:[Easysoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'my_mirroreddb'. Connection: connected to my_initial_partner as myuser with mypassword, database='my_mirroreddb', partner='my_failover_partner'
Connection failover
Connection failover maintains data availability by allowing an application to connect to a backup SQL Server computer if the primary server is unavailable.
To configure connection failover, specify a primary server and additional fallback servers in your ODBC data source. Do this with the Server attribute. For example:
[SQL Server High Availability] Driver = Easysoft ODBC-SQL Server Server = sqlsrvhostA,sqlsrvhostB,sqlsrvhostC:1583 # This user name and password must be valid for all servers in the list. User = my_domain\my_user Password = my_password ClientLB = 0
By default, the Easysoft ODBC-SQL Server Driver will try to connect to the first server that you specify. If that server is unavailable (for example, because of a hardware or operating system failure), the Easysoft ODBC-SQL Server Driver will try to connect to next server in the list. Connection attempts continue until a connection is successfully made or until all the database servers in the list have been tried once.
To balance the load between database servers, set the ClientLB attribute to 1. When ClientLB is turned on, 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 computer 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, refer to Connection attributes.
Connecting to SQL Server by using IPv6
Internet Protocol version 6 (IPv6) is a revised version of the Internet Protocol (IP) designed primarily to address growth on the Internet. It is sometimes referred to as Internet Protocol Next Generation (IPng). The current version of IP, IP version 4 (IPv4), has proven to be robust but is over 20 years old and was not designed to support such widespread use as it does today.
The features of IPv6 include:
-
128-bit IP addresses to solve the problem of the available IP address pool being depleted.
-
Extensibility to account for future growth and evolution of Internet technologies and standards.
-
A simplified header format to reduce network overhead and improve performance.
-
Better protection against address and port scanning attacks.
-
Built-in support for Internet Protocol Security (IPsec) to prevent IPv6 traffic from being viewed or modified in transit.
The Easysoft ODBC-SQL Server Driver supports both IPv4 and IPv6.
Configuring your client computer for IPv6
IPv6 needs to be enabled on the client computer. The procedure for this depends on the client platform. For more information, consult the IPv6 documentation for your system. For Linux systems, consult https://www.tldp.org/HOWTO/Linux+IPv6-HOWTO/.
Configuring your ODBC data source for IPv6
To connect to a SQL Server instance that’s 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 computer’s host name. Otherwise, specify its IPv6 address. For example:
Server=myipv6computer\myinstance IPv6 = 1
-Or-
Server=ABCD:EF12:0:0:0:0:0:3456\myinstance IPv6 = 1
The Easysoft ODBC-SQL Server Driver supports normal and compressed IPv6 addresses. Compressed format is a short form that replaces consecutive leading zeros with two colons (::). For example, the IPv6 address shown in the previous example could be replaced with ABCD:EF12::3456.
The Easysoft ODBC-SQL Server Driver also supports the IPv4-mapped IPv6 address format, which is an IPv6 address that holds an embedded IPv4 address. For example, ::FFFF:192.168.19.46.
Finding out more about data types on Windows
If you need more information about a data types, for example, the precision and scale, use Microsoft’s ODBC Test to do this.
-
Download the version of ODBC Test that matches your application’s architecture from:
-
Copy both files to a folder on the machine where Easysoft ODBC-SQL Server Driver is installed.
-
Double-click odbcte32.exe.
-
Select Con > Full Connect.
-
Choose your Easysoft ODBC-SQL Server Driver data source from the list.
-
Choose Catalog > SQLGetTypeInfo.
-
Either choose SQL_ALL_TYPES=0 (1.0) or a specific data type from the DataType list.
-
Choose Results > Get Data All.
Example SQL statements
Example queries
-
To fetch all records from a table, use the asterisk symbol (
*) in your queries. For example:SELECT * FROM Customers -
To only fetch records whose values are different, use
DISTINCT. For example:-- Which different sales regions are there? SELECT DISTINCT Region AS Different_Regions FROM SalesOrders -- How many different sales regions are there? SELECT COUNT(DISTINCT Region) AS Different_Regions FROM SalesOrders
-
To filter records, use
WHERE. For example:SELECT OrderDate, SalesRepresentative FROM SalesOrders WHERE Region = 'Eastern' SELECT OrderDate, SalesRepresentative FROM SalesOrders WHERE Region = 'Eastern' OR Region = 'Western' SELECT OrderDate, SalesRepresentative FROM SalesOrders WHERE Region = 'Eastern' AND EXTRACT(YEAR FROM OrderDate) = 2025You can also supply a
WHEREclause value as a parameter. For example, to do this in Python:cursor.execute("SELECT OrderDate, SalesRepresentative FROM SalesOrders WHERE Region = ?", ['Eastern'])
-
To fetch records that don’t match the
WHEREclause pattern useNOT. For example:SELECT OrderDate, SalesRepresentative FROM SalesOrders WHERE NOT Region = 'Eastern' -
To sort the result set in either ascending or descending order, use
ORDER BY. For example:SELECT * FROM SalesOrders ORDER BY OrderDate ASC SELECT * FROM Contacts ORDER BY ( CASE WHEN Surname IS NULL THEN Title ELSE Surname END );
-
To group a result set into summary rows, use
GROUP BY. For example:SELECT COUNT(Id) As "Number", ProductID FROM SalesOrderItems GROUP BY ProductID SELECT COUNT(Id) As "Number", ProductID FROM SalesOrderItems GROUP BY ProductID HAVING COUNT(Id) > 100; -
To do calculations based on result set vales, use the SQL aggregate functions
MIN(),MAX(),COUNT(),SUM(), andAVG(). For example:SELECT Max(Quantity) FROM SalesOrderItems SELECT Sum(Quantity) FROM SalesOrderItems -
To convert between compatible data types, use
CAST. For example:SELECT CAST(Quantity AS Char(100))FROM SalesOrderItems -
To fetch records that contain column values between a given range, use
BETWEEN. For example:SELECT ProductID FROM SalesOrderItems WHERE Quantity BETWEEN 10 AND 20
-
To combine the result set of two or more
SELECTstatements, useUNION. For example:SELECT City FROM Contacts UNION SELECT City FROM Customers -
To combine rows from two or more tables, use
JOIN. For example:SELECT SalesOrders.ID, Customers.Surname, SalesOrders.OrderDate FROM SalesOrders INNER JOIN Customers ON SalesOrders.CustomerID=Customers.ID; -
To fetch records that contain column values matching a search pattern, use
LIKE. For example:SELECT Surname, GivenName FROM Customers WHERE CompanyName LIKE 'R%' SELECT Surname, GivenName FROM Customers WHERE CompanyName LIKE '_he' -
To search for columns without a value (
NULL) or with a value (nonNULL), use eitherIS NULLorIS NOT NULL. For example:SELECT * FROM Customers WHERE CompanyName IS NULL
-
To specify multiple values in a
WHEREclause, you can useINas an alternative toOR. For example:SELECT OrderDate, SalesRepresentative FROM SalesOrders WHERE Region = 'Eastern' OR Region = 'Western' OR Region = 'Central'can be replaced with:
SELECT OrderDate, SalesRepresentative FROM SalesOrders WHERE Region IN ('Eastern', 'Western', 'Central') -
To set the maximum number of records to return, use
LIMIT. For example:SELECT * FROM Customers LIMIT 10 -
To test for the existence of records in a subquery, use
EXISTS. For example:SELECT Name FROM Products WHERE EXISTS ( SELECT * FROM SalesOrderItems WHERE Products.ID = SalesOrderItems.ProductID AND Quantity < 20 )
Example inserts, updates, and deletes
-
To insert a SQL Server record, use
INSERT INTO. For example:INSERT INTO Customers ( Surname, GivenName, City, Phone, CompanyName ) VALUES ( 'Devlin', 'Michaels', 'Kingston', '2015558966', 'PowerGroup' ) -
Here’s a SQL Server linked server example:
EXEC ('INSERT INTO Customers (Surname, GivenName, City, Phone, CompanyName) VALUES (''Devlin'' , ''Michaels'' , ''Kingston'' , ''2015558966'' , ''PowerGroup'')')
-
Here’s an Oracle linked table example:
DECLARE num_rows integer; BEGIN num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@MSSQLLink ('INSERT INTO Customers (Surname, GivenName, City, Phone, CompanyName) VALUES (''Devlin'', ''Michaels'', ''Kingston'', ''2015558966'', ''PowerGroup'')'); END; / -
The Easysoft ODBC-SQL Server Driver also supports parameterized inserts. Here’s an example of doing this in Perl:
my $sth = $dbh->prepare(q/INSERT INTO Customers (Surname, GivenName, City, Phone, CompanyName) VALUES (?, ?, ?, ?, ?)/) or die "Can't prepare statement: $DBI::errstr"; $sth->execute('Devlin', 'Michaels', 'Kingston', '2015558966', 'PowerGroup'); -
To update a SQL Server record, use
UPDATE. For example:UPDATE Customers SET Surname = 'Jones' WHERE Account_Id = 'PowerGroup'The Easysoft ODBC-SQL Server Driver also supports parameterized updates. Here’s an example of doing this in Perl:
my $sth = $dbh->prepare('UPDATE Customers SET Surname = \'Jones\' WHERE CompanyName = ?') or die "Can't prepare statement: $DBI::errstr"; $sth->execute('PowerGroup');
-
To delete a SQL Server record, use
DELETE. For example:-- Delete (mark inactive) a bank account DELETE FROM Customers WHERE CompanyName = 'PowerGroup'The Easysoft ODBC-SQL Server Driver also supports parameterized deletes. Here’s an example of doing this in Python:
sql = "DELETE FROM Customers WHERE CompanyName = ?" cursor.execute(sql, 'PowerGroup')