skip to main content
Reference : DataDirect Bulk Load : Export, Validate, and Load Functions : LoadTableFromFile and LoadTableFromFileW
  

Try DataDirect Drivers Now
LoadTableFromFile and LoadTableFromFileW

Syntax

SQLReturn
LoadTableFromFile  (HDBC      hdbc,
                    SQLCHAR*   TableName,
                    SQLCHAR*   FileName,
                    SQLLEN     ErrorTolerance,
                    SQLLEN     WarningTolerance,
                    SQLCHAR*   ConfigFile,
                    SQLCHAR*   LogFile,
                    SQLCHAR*   DiscardFile,
                    SQLULEN    LoadStart,
                    SQLULEN    LoadCount,
                    SQLULEN    ReadBufferSize)
LoadTableFromFileW  (HDBC      hdbc,
                    SQLWCHAR*  TableName,
                    SQLWCHAR*  FileName,
                    SQLLEN     ErrorTolerance,
                    SQLLEN     WarningTolerance,
                    SQLWCHAR*  ConfigFile,
                    SQLWCHAR*  LogFile,
                    SQLWCHAR*  DiscardFile,
                    SQLULEN    LoadStart,
                    SQLULEN    LoadCount,
                    SQLULEN    ReadBufferSize)
The standard ODBC return codes are returned: SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_INVALID_HANDLE, and SQL_ERROR.

Purpose

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.
The load operation can be configured such that if any errors or warnings occur:
*The operation always completes
*The operation always terminates
*The operation terminates after a certain threshold of warnings or errors is exceeded.
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.

Parameters

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.
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. The file name must be the fully qualified path to the bulk data file.
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. The default of -1 means that an infinite number of errors is tolerated.
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.The default of -1 means that an infinite number of warnings is tolerated.
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. The file name must be the fully qualified path to the log file. Events logged to this file are:
*Total number of rows read
*Message for each row that failed to load.
*Total number of rows that failed to load
*Total number of rows successfully loaded
Information about the load is written to this file, preceded by a header. Information about the next load is appended to the end of the file.
If LogFile is NULL, no log file is created.
DiscardFile is a null-terminated character string that specifies the path (relative or absolute) and file name of the bulk discard file. The file name must be the fully qualified path to the 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.
Information about the load is written to this file, preceded by a header. Information about the next load is appended to the end of the file.
If DiscardFile is NULL, no discard file is created.
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.

Example

HDBC      hdbc;
HENV      henv;
void      *driverHandle;
HMODULE   hmod;
PLoadTableFromFile loadTableFromFile;
char      tableName[128];
char      fileName[512];
char      configFile[512];
char      logFile[512];
char      discardFile[512];
int       errorTolerance;
int       warningTolerance;
int       loadStart;
int       loadCount;
int       readBufferSize;

/* Get the driver's connection handle from the DM. This handle must be used when calling directly into the driver. */

rc = SQLGetInfo (hdbc, SQL_DRIVER_HDBC, &driverHandle, 0, NULL);
if (rc != SQL_SUCCESS) {
  ODBC_error (henv, hdbc, SQL_NULL_HSTMT);
  EnvClose (henv, hdbc);
  exit (255);
}

/* Get the DM's shared library or DLL handle to the driver. */

rc = SQLGetInfo (hdbc, SQL_DRIVER_HLIB, &hmod, 0, NULL);
if (rc != SQL_SUCCESS) {
  ODBC_error (henv, hdbc, SQL_NULL_HSTMT);
  EnvClose (henv, hdbc);
  exit (255);
}

loadTableFromFile = (PLoadTableFromFile)
  resolveName (hmod, "LoadTableFromFile");
if (! loadTableFromFile) {
  printf ("Cannot find LoadTableFromFile!\n");
  exit (255);
}
rc = (*loadTableFromFile) (
     driverHandle,
     (const SQLCHAR *) tableName,
     (const SQLCHAR *) fileName,
     errorTolerance, warningTolerance,
     (const SQLCHAR *) configFile,
     (const SQLCHAR *) logFile,
     (const SQLCHAR *) discardFile,
     loadStart, loadCount,
     readBufferSize);
if (rc == SQL_SUCCESS) {
     printf ("Load succeeded.\n");
}
else {
     driverError (driverHandle, hmod);
}