The standard ODBC return codes are returned: SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_INVALID_HANDLE, and SQL_ERROR.
ExportTableToFile (ANSI application) and ExportTableToFileW (Unicode application) bulk export a table to a physical file. Both a bulk data file and a bulk configuration file are produced by this operation. The configuration file has the same name as the data file, but with an XML extension. The bulk export operation can create a log file and can also export to external files. Refer to
“External Overflow Files” in
Chapter 3 of the
DataDirect Connect Series for ODBC User’s Guide for more information. The export operation can be configured such that if any errors or warnings occur:
hdbc is the driver’s connection handle, which is not the handle returned by SQLAllocHandle or SQLAllocConnect. To obtain the driver's connection handle, the application must then use the standard ODBC function SQLGetInfo (
ODBC Conn Handle,
SQL_DRIVER_HDBC).
TableName is a null-terminated string that specifies the name of the source database table that contains the data to be exported.
FileName is a null-terminated string that specifies the path (relative or absolute) and file name of the bulk load data file to which the data is to be exported. It also specifies the file name of the bulk configuration file. This file must not already exist. If the file already exists, an error is returned.
IANAAppCodePage specifies the code page value to which the driver must convert all data for storage in the bulk data file. Refer to
“Character Set Conversions” in
Chapter 3 of the
DataDirect Connect Series for ODBC User’s Guide for more information.
ErrorTolerance specifies the number of errors to tolerate before an operation terminates. A value of 0 indicates that no errors are tolerated; the operation fails when the first error is encountered.
WarningTolerance specifies the number of warnings to tolerate before an operation terminates. A value of 0 indicates that no warnings are tolerated; the operation fails when the first warning is encountered.
LogFile is a null-terminated character string that specifies the path (relative or absolute) and file name of the bulk log file. Events logged to this file are:
ODBC_error (henv, hdbc, SQL_NULL_HSTMT);
ODBC_error (henv, hdbc, SQL_NULL_HSTMT);
resolveName (hmod, "ExportTableToFile");
printf ("Cannot find ExportTableToFile!\n");
(const SQLCHAR *) tableName,
(const SQLCHAR *) fileName,
errorTolerance, warningTolerance,
(const SQLCHAR *) logFile);
printf ("Export succeeded.\n");
driverError (driverHandle, hmod);
The standard ODBC return codes are returned: SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_INVALID_HANDLE, and SQL_ERROR.
ValidateTableFromFile (ANSI application) and ValidateTablefromFileW (Unicode application) verify the metadata in the configuration file against the data structure of the target database table. Refer to
“Verification of the Bulk Load Configuration File” in
Chapter 3 of the
DataDirect Connect Series for ODBC User’s Guide for more detailed information.
hdbc is the driver’s connection handle, which is not the handle returned by SQLAllocHandle or SQLAllocConnect. To obtain the driver's connection handle, the application must then use the standard ODBC function SQLGetInfo (
ODBC Conn Handle,
SQL_DRIVER_HDBC).
TableName is a null-terminated character string that specifies the name of the target database table into which the data is to be loaded.
ConfigFile is a null-terminated character string that specifies the path (relative or absolute) and file name of the bulk configuration file.
MessageList specifies a pointer to a buffer used to record any of the errors and warnings. MessageList must not be null.
MessageListSize specifies the maximum number of characters that can be written to the buffer to which MessageList points. If the buffer to which MessageList points is not big enough to hold all of the messages generated by the validation process, the validation is aborted and SQL_ERROR is returned.
NumMessages contains the number of messages that were added to the buffer. This method reports the following criteria:
|
■
|
Check Config Col Info - The destination metadata and the column metadata in the configuration file are checked for consistency of items such as length for character and binary data types, the character encoding code page for character types, precision and scale for numeric types, and nullablity for all types. If any inconsistency is found, the driver adds an entry to the MessageList in the following format: Destination column metadata for column_number has column info mismatches from source column_number.
|
|
■
|
Check Column Names and Mapping - The columns defined in the configuration file are compared to the destination table columns based on the order of the columns. If the number of columns in the configuration file and/or import file does not match the number of columns in the table, the driver adds an entry to the MessageList in the following format: The number of destination columns number does not match the number of source columns number.
|
The function returns an array of null-terminated strings in the buffer to which MessageList points with an entry for each of these checks. If the driver determines that the information in the bulk load configuration file matches the metadata of the destination table, a return code of SQL_SUCCESS is returned and the MessageList remains empty.
If the driver determines that there are minor differences in the information in the bulk load configuration file and the destination table, then SQL_SUCCESS_WITH_INFO is returned and the MessageList is populated with information on the cause of the potential problems.
If the driver determines that the information in the bulk load information file cannot successfully be loaded into the destination table, then a return code of SQL_ERROR is returned and the MessageList is populated with information on the problems and mismatches between the source and destination.
ODBC_error (henv, hdbc, SQL_NULL_HSTMT);
ODBC_error (henv, hdbc, SQL_NULL_HSTMT);
resolveName (hmod, "ValidateTableFromFile");
printf ("Cannot find ValidateTableFromFile!\n");
(const SQLCHAR *) tableName,
(const SQLCHAR *) configFile,
(numMessages == 0) ? "s" :
((numMessages == 1) ? " : " : "s : "),
(numMessages > 0) ? messageList : "");
printf ("Validate succeeded.\n");
driverError (driverHandle, hmod);
The standard ODBC return codes are returned: SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_INVALID_HANDLE, and SQL_ERROR.
LoadTableFromFile (ANSI application) and LoadTablefromFileW (Unicode application) bulk load data from a file to a table. The load operation can create a log file and can also create a discard file that contains rows rejected during the load. The discard file is in the same format as the bulk load data file. After fixing reported issues in the discard file, the bulk load can be reissued using the discard file as the bulk load data file.
If a load fails, the LoadStart and
LoadCount parameters can be used to control which rows are loaded when a load is restarted after a failure.
hdbc is the driver’s connection handle, which is not the handle returned by SQLAllocHandle or SQLAllocConnect. To obtain the driver's connection handle, the application must then use the standard ODBC function SQLGetInfo (
ODBC Conn Handle,
SQL_DRIVER_HDBC).
TableName is a null-terminated character string that specifies the name of the target database table into which the data is to be loaded. For the Salesforce driver, the value of this parameter can vary. See
“Using the TableName Parameter with the Salesforce Driver” for more information.
FileName is a null-terminated string that specifies the path (relative or absolute) and file name of the bulk data file from which the data is to be loaded.
ErrorTolerance specifies the number of errors to tolerate before an operation terminates. A value of 0 indicates that no errors are tolerated; the operation fails when the first error is encountered.
WarningTolerance specifies the number of warnings to tolerate before an operation terminates. A value of 0 indicates that no warnings are tolerated; the operation fails when the first warning is encountered.
ConfigFile is a null-terminated character string that specifies the path (relative or absolute) and file name of the bulk configuration file.
LogFile is a null-terminated character string that specifies the path (relative or absolute) and file name of the bulk log file. Events logged to this file are:
|
■
|
Message for each row that failed to load.
|
DiscardFile is a null-terminated character string that specifies the path (relative or absolute) and file name of the bulk discard file. Any row that cannot be inserted into database as result of bulk load is added to this file, with the last row to be rejected added to the end of the file.
LoadStart specifies the first row to be loaded from the data file. Rows are numbered starting with 1. For example, when LoadStart=10, the first 9 rows of the file are skipped and the first row loaded is row 10. This parameter can be used to restart a load after a failure.
LoadCount specifies the number of rows to be loaded from the data file. The bulk load operation loads rows up to the value of LoadCount from the file to the database. It is valid for LoadCount to specify more rows than exist in the data file. The bulk load operation completes successfully when either the LoadCount value has been loaded or the end of the data file is reached. This parameter can be used in conjunction with
LoadStart to restart a load after a failure.
ReadBufferSize specifies the size, in KB, of the buffer that is used to read the bulk data file for a bulk load operation. The default is 2048.
ODBC_error (henv, hdbc, SQL_NULL_HSTMT);
ODBC_error (henv, hdbc, SQL_NULL_HSTMT);
resolveName (hmod, "LoadTableFromFile");
printf ("Cannot find LoadTableFromFile!\n");
(const SQLCHAR *) tableName,
(const SQLCHAR *) fileName,
errorTolerance, warningTolerance,
(const SQLCHAR *) configFile,
(const SQLCHAR *) logFile,
(const SQLCHAR *) discardFile,
printf ("Load succeeded.\n");
driverError (driverHandle, hmod);
The value required in the TableName parameter varies, depending on the bulk operation specified in the SetBulkOperation function. The following paragraphs describe the TableName value based on whether the Bulk Operation type is set to INSERT, DELETE, or UPSERT.
column_list is (
columnSpec[,
columnSpec]…) and
columnSpec can be
columnName or
foreignKeyColumnName EXT_ID externalIdColumnName.
The column names define the mapping between columns in the table and columns in the bulk data file. The column names can also indicate which columns are External ID columns. See
“Specifying an External ID Column” for more information.
Where column_list is the ID column, which identifies the row to delete.
Where column_list is
ID_column, <update column>[,<update column>]…
ID_column must be one of the columns in the column list. The ID column identifies which row to update; the other columns are the list of columns to be updated.
UPDATE table_name SET <update column> = ? … WHERE <ID column> = ? …
For UPSERT, column_list can be
(columnSpec[, columnSpec]…)
columnSpec can be one of the following:
where extIdColumn is the column that is checked to determine whether the row already exists in the database.
UPDATE table_name SET <table column> = ? … WHERE <key column> = ? …
The standard ODBC return codes are returned: SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_INVALID_HANDLE, SQL_NO_DATA, and SQL_ERROR.
Specifies the bulk operation to be performed when the LoadTableFromFile and LoadTableFromFileW methods are called. The bulk operation remains set until SetBulkOperation is called again. When a connection is established, the initial bulk operation is BULK_OPERATION_INSERT.
hdbc is the driver’s connection handle, which is not the handle returned by SQLAllocHandle or SQLAllocConnect. To obtain the driver's connection handle, the application must use SQLGetInfo (
ODBC Conn Handle, SQL_DRIVER_HDBC).
Operation is an integer value that specifies the bulk operation to set on the connection. It can have one of the following values:
ODBC_error (henv, hdbc, SQL_NULL_HSTMT);
ODBC_error (henv, hdbc, SQL_NULL_HSTMT);
resolveName (hmod, "SetBulkOperation");
printf ("Cannot find SetBulkOperation!\n");
printf ("Set Bulk operation(DELETE) succeeded.\n");
driverError (driverHandle, hmod);
The standard ODBC return codes are returned: SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_INVALID_HANDLE, and SQL_ERROR.
hdbc is the driver’s connection handle, which is not the handle returned by SQLAllocHandle or SQLAllocConnect. To obtain the driver's connection handle, the application must then use the standard ODBC function SQLGetInfo (
ODBC Conn Handle,
SQL_DRIVER_HDBC).
pOperation is a pointer to the location where current bulk operation specified for the connection is returned. The returned value is one of the operation values defined by
“SetBulkOperation (Salesforce Driver Only)”.