Converting from DB-Library to ODBC Bulk Copy
Converting a DB-Library bulk copy program to ODBC is easy because the bulk copy functions supported by the Microsoft® SQL Server™ ODBC driver are similar to the DB-Library bulk copy functions, with the following exceptions:
- DB-Library applications pass a pointer to a DBPROCESS structure as the first parameter of bulk copy functions. In ODBC applications, the DBPROCESS pointer is replaced with an ODBC connection handle.
- DB-Library applications call BCP_SETL before connecting to enable bulk copy operations on a DBPROCESS. ODBC applications instead call SQLSetConnectAttr before connecting to enable bulk operations on a connection handle:
SQLSetConnectAttr(hdbc, SQL_COPT_SS_BCP, (void *)SQL_BCP_ON, SQL_IS_INTEGER); - The SQL Server ODBC driver does not support DB-Library message and error handlers; you must call SQLGetDiagRec to get errors and messages raised by the ODBC bulk copy functions. The ODBC versions of bulk copy functions return the standard bulk copy return codes of SUCCEED or FAILED, not ODBC-style return codes, such as SQL_SUCCESS or SQL_ERROR.
- The values specified for the DB-Library bcp_bind varlen parameter are interpreted differently than the ODBC bcp_bind cbData parameter.
Condition indicated DB-Library varlen value ODBC cbData value Null values supplied 0 -1 (SQL_NULL_DATA) Variable data supplied -1 -10 (SQL_VARLEN_DATA) Zero length character or binary string NA 0
In DB-Library, a varlen value of -1 indicates that variable length data is being supplied, which in the ODBC cbData is interpreted to mean that only NULL values are being supplied. Change any DB-Library varlen specifications of -1 to SQL_VARLEN_DATA and any varlen specifications of 0 to SQL_NULL_DATA.
- The DB-Library bcp_colfmt file_collen and the ODBC bcp_colfmt cbUserData have the same issue as the bcp_bind varlen and cbData parameters noted above. Change any DB-Library file_collen specifications of -1 to SQL_VARLEN_DATA and any file_collen specifications of 0 to SQL_NULL_DATA.
- The iValue parameter of the ODBC bcp_control function is a void pointer. In DB-Library, iValue was an integer. Cast the values for the ODBC iValue to void *.
- The bcp_control option BCPMAXERRS specifies how many individual rows can have errors before a bulk copy operation fails. The default for BCPMAXERRS is 0 (fail on first error) in the DB-Library version of bcp_control and 10 in the ODBC version. DB-Library applications that depend on the default of 0 to terminate a bulk copy operation must be changed to call the ODBC bcp_control to set BCPMAXERRS to 0.
- The ODBC bcp_control function supports the following options not supported by the DB-Library version of bcp_control:
- BCPODBC
When set to TRUE, specifies that datetime and smalldatetime values saved in character format will have the ODBC timestamp escape sequence prefix and suffix. This only applies to BCP_OUT operations.
With BCPODBC set to FALSE, a datetime value converted to a character string is output as:
1997-01-01 00:00:00.000With BCPODBC set to TRUE, the same datetime value is output as:
{ts '1997-01-01 00:00:00.000' } - BCP6xFILEFMT
When set to TRUE, specifies that program variables bound to columns in native format, or columns stored in an operating-system file in either native or character format use the SQL Server version 6x format instead of the SQL Server 7.0 format. Columns bound in native format include columns for which bcp_bind was called with type set to 0 and varlen set to SQL_VARLEN_DATA. Columns stored in native format in a data file include columns for which bcp_colfmt was called with file_type set to 0 and file_collen set to SQL_VARLEN_DATA.
- BCPKEEPIDENTITY
When set to TRUE, specifies that bulk copy functions insert data values supplied for columns with identity constraints. If this is not set, new identity values are generated for the inserted rows.
- BCPHINTS
Specifies various bulk copy optimizations. This option cannot be used on 6.5 or earlier versions of SQL Server.
- BCPFILECP
Specifies the code page of the bulk copy file.
- BCPUNICODEFILE
Specifies that a character mode bulk copy file is a Unicode file.
- BCPODBC
- The ODBC bcp_colfmt function does not support the file_type indicator of SQLCHAR because it conflicts with the ODBC SQLCHAR typedef. Use SQLCHARACTER instead for bcp_colfmt.
- In the ODBC versions of bulk copy functions, the format for working with datetime and smalldatetime values in character strings is the ODBC format of yyyy-mm-dd hh:mm:ss.sss; smalldatetime values use the ODBC format of yyyy-mm-dd hh:mm:ss.
The DB-Library versions of the bulk copy functions accept datetime and smalldatetime values in character strings using several formats:
- The default format is mmm dd yyyy hh:mmxx where xx is either AM or PM.
- datetime and smalldatetime character strings in any format supported by the DB-Library dbconvert function.
- When the Use international settings box is checked on the DB-Library Options tab of the SQL Server Client Network Utility, the DB-Library bulk copy functions also accept dates in the regional date format defined for the locale setting of the client computer registry.
The DB-Library bulk copy functions do not accept the ODBC datetime and smalldatetime formats.
The ODBC bulk copy functions will accept the same datetime formats as the DB-Library versions of the bulk copy functions when BCP6xFILEFMT is specified. If the SQL_SOPT_SS_REGIONALIZE statement attribute is set to SQL_RE_ON, the ODBC bulk copy functions will accept dates in the regional date format defined for the locale setting of the client computer registry.
- The default format is mmm dd yyyy hh:mmxx where xx is either AM or PM.
- When outputting money values in character format, ODBC bulk copy functions supply 4 digits of precision and no comma separators; DB-Library versions only supply 2 digits of precision and include the comma separators.