Using the ODBC Client : Using DataDirect Bulk Load : Exporting Data from a Database

Exporting Data from a Database

You can export data from a database using either of the following ways:

• From a table by using DataDirect functions

• From a result set by using DataDirect statement attributes

Your application can export a table using the DataDirect functions ExportTableToFile (ANSI application) or ExportTableToFileW (Unicode application). The application must first obtain driver connection handles and function pointers, as shown in the following example:

HDBC hdbc;

HENV henv;

void *driverHandle;

HMODULE hmod;

PExportTableToFile exportTableToFile;

char tableName[128];

char fileName[512];

char logFile[512];

int errorTolerance;

int warningTolerance;

int codePage;

/* 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);

}

exportTableToFile = (PExportTableToFile)

resolveName (hmod, "ExportTableToFile");

if (! exportTableToFile) {

printf ("Cannot find ExportTableToFile!\n");

exit (255);

}

rc = (*exportTableToFile) (

driverHandle,

(const SQLCHAR *) tableName,

(const SQLCHAR *) fileName,

codePage,

errorTolerance, warningTolerance,

(const SQLCHAR *) logFile);

if (rc == SQL_SUCCESS) {

printf ("Export succeeded.\n");

}

else {

driverError (driverHandle, hmod);

}

See DataDirect Bulk Load Functions for a full description of these functions.

Your application can export a result set using the DataDirect statement attributes SQL_BULK_EXPORT and SQL_BULK_EXPORT_PARAMS.

The export operation creates a bulk load data file with a .csv extension in which the exported data is stored. For example, assume that an Oracle source table named GBMAXTABLE contains four columns. The resulting bulk load data file GBMAXTABLE.csv containing the results of a query would be similar to the following:

1,0x6263,"bc","bc"

2,0x636465,"cde","cde"

3,0x64656667,"defg","defg"

4,0x6566676869,"efghi","efghi"

5,0x666768696a6b,"fghijk","fghijk"

6,0x6768696a6b6c6d,"ghijklm","ghijklm"

7,0x68696a6b6c6d6e6f,"hijklmno","hijklmno"

8,0x696a6b6c6d6e6f7071,"ijklmnopq","ijklmnopq"

9,0x6a6b6c6d6e6f70717273,"jklmnopqrs","jklmnopqrs"

10,0x6b,"k","k"

A bulk load configuration file with and .xml extension is also created when either a table or a result set is exported to a bulk load data file. See The Bulk Load Configuration File for an example of a bulk load configuration file.

In addition, a log file of events as well as external overflow files can be created during a bulk export operation. The log file is configured through either the ExportTableToFile function or the SQL_BULK_EXPORT statement attribute. The external overflow files are configured through connection options; see External Overflow Files for details.

Your application can also load data from the bulk load data file into the target database using the using the DataDirect functions LoadTableFromFile (ANSI application) or LoadTableFromFileW (Unicode application). The application must first obtain driver connection handles and function pointers, as shown in the following 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);

}

See DataDirect Bulk Load Functions for a full description of these functions.

Use the BulkLoadBatchSize connection attribute to specify the number of rows the driver loads to the data source at a time when bulk loading data. Performance can be improved by increasing the number of rows the driver loads at a time because fewer network round trips are required. Be aware that increasing the number of rows that are loaded also causes the driver to consume more memory on the client.

A log file of events as well as a discard file that contains rows rejected during the load can be created during a bulk load operation. These files are configured through the LoadTableFromFile function.

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.