Edit C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\odbc\7.0.1\help\userguide\advanced.06.6.html
<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xml:lang="en" lang="en" xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html;charset=utf-8" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <!-- MOTW-DISABLED saved from url=(0014)about:internet --> <title>Using DataDirect Bulk Load</title> <link rel="StyleSheet" href="css/advanced.css" type="text/css" media="all" /> <link rel="StyleSheet" href="css/webworks.css" type="text/css" media="all" /> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/context.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/towwhdir.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/wwhpagef.js"></script> <script type="text/javascript" language="JavaScript1.2"> <!-- var WebWorksRootPath = ""; // --> </script> <script type="text/javascript" language="JavaScript1.2"> <!-- // Set reference to top level help frame // var WWHFrame = WWHGetWWHFrame("", true); // --> </script> <script type="text/javascript" language="JavaScript1.2" src="scripts/expand.js"></script> </head> <body class="" style="background-color: #FFFFEE;" onload="WWHUpdate();" onunload="WWHUnload();" onkeydown="WWHHandleKeyDown((document.all||document.getElementById||document.layers)?event:null);" onkeypress="WWHHandleKeyPress((document.all||document.getElementById||document.layers)?event:null);" onkeyup="WWHHandleKeyUp((document.all||document.getElementById||document.layers)?event:null);"> <br /> <div class="WebWorks_Breadcrumbs" style="text-align: left;"> <a class="WebWorks_Breadcrumb_Link" href="advanced.06.1.html#109749">3 Advanced Features</a> : Using DataDirect Bulk Load </div> <hr align="left" /> <blockquote> <div class="head_1"><a name="109749">Using DataDirect Bulk Load </a></div> <div class="Body"><a name="194401">The drivers support DataDirect Bulk Load, a feature that allows your application to send </a>large numbers of rows of data to a database or Salesforce instance. </div> <div class="Body"><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.6.html#194412', '');" name="194408">Table 3-8</a></span> and <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.6.html#194441', '');">Table 3-9</a></span> describe the bulk load behavior for the drivers.</div> <table class="Format_A" cellspacing="0" summary=""> <caption> <div class="table_title">Table 3-8. <a name="194412">Bulk Load Behavior for DataDirect Connect for ODBC</a></div> </caption> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="194416">Driver</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="194418">Bulk Load Behavior</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="194423">Oracle<sup><a name="wwfootnote_inline_99" href="#99">1</a></sup></a><br />Microsoft SQL Server<sup><a name="wwfootnote_inline_100" href="#100">2</a></sup><br />Sybase</div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="194428">The driver sends the data to the database in a continuous </a>stream instead of numerous smaller database packets. Similar to batch operations, using bulk load improves performance because far fewer network round trips are required. Bulk load bypasses the data parsing usually done by the database, providing an additional performance gain over batch operations.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="194430">DB2</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="194432">Because DB2 does not have native bulk load support, the </a>driver supports bulk through the native parameter array mechanism.</div> </td> </tr> </table> <hr /> <div style="font-family: Arial; font-size: 8pt; float: left; padding-right: 10px;"> <a name="99" href="#wwfootnote_inline_99">1</a> </div> <div class="TableFootnote"><a name="194422">Supports bulk load for Oracle9i R2 and higher.</a></div> <br style="clear: all;" /> <div style="font-family: Arial; font-size: 8pt; float: left; padding-right: 10px;"> <a name="100" href="#wwfootnote_inline_100">2</a> </div> <div class="TableFootnote"><a name="194426">Supports bulk load for Microsoft SQL Server 2000 and higher.</a></div> <br style="clear: all;" /> <table class="Format_A" cellspacing="0" summary=""> <caption> <div class="table_title">Table 3-9. <a name="194441">Bulk Load Behavior for DataDirect Connect XE for ODBC</a></div> </caption> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="194445">Driver</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="194447">Bulk Load Behavior</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="194449">Salesforce</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="194451">The driver sends data to a Salesforce instance using the </a>Salesforce Bulk API instead of the Web Service API. Using the Bulk API significantly reduces the number of Web service calls the driver uses to transfer data and may improve performance.</div> </td> </tr> </table> <div class="Body"><a name="193809">IMPORTANT: </a></div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="193810">DataDirect Bulk Load requires a licensed installation of the drivers. If the drivers are </a>installed with an evaluation license, the bulk load feature is available for prototyping with your applications, but with limited scope. Contact your sales representative or Progress DataDirect SupportLink for further information.</div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="193811">Because a bulk load operation may bypass data integrity checks, your application must </a>ensure that the data it is transferring does not violate integrity constraints in the database. For example, suppose you are bulk loading data into a database table and some of that data duplicates data stored as a primary key, which must be unique. The driver will not throw an exception to alert you to the error; your application must provide its own data integrity checks.</div> </td> </tr> </table> </div> <div class="Body"><a name="137124">Bulk load operations are accomplished by exporting the results of a query from a database </a>into a comma-separated value (CSV) file, a bulk load data file. The driver then loads the data from bulk load data file into a different database. The file can be used by any DataDirect Connect Series <span class="forbody">for</span> <span class="APIbody">ODBC</span> drivers. In addition, the bulk load data file is supported by other DataDirect Connect product lines that feature bulk loading, for example, a DataDirect Connect <span class="forbody">for</span> <span class="APIbody">ADO.NET</span> data provider that supports bulk load. </div> <div class="Body"><a name="140433">Suppose that you had customer data on a Sybase server and need to export it to a DB2 </a>server. The driver would perform the following steps:</div> <div class="picture"><a name="191291"><img class="drawing" src="images/odbcbulkload.gif" width="475" height="224" style="display: block; float: none; left: 0.0; top: 0.0;" alt="Bulk load diagram, showing the driver communicating with the Sybase server, the bulk load data file, and finally the DB2 server." /></a></div> <div class="list_procedure_first_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_procedure_first_inner" style="width: 18pt; white-space: nowrap;"> <span class="Numbers">1 </span> </div> </td> <td width="100%"> <div class="list_procedure_first_inner"><a name="140551">Application using Sybase Wire Protocol driver sends query to and receives results from </a>Sybase server.</div> </td> </tr> </table> </div> <div class="list_procedure_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_procedure_inner" style="width: 18pt; white-space: nowrap;"> <span class="Numbers">2 </span> </div> </td> <td width="100%"> <div class="list_procedure_inner"><a name="140552">Driver exports results to bulk load data file.</a></div> </td> </tr> </table> </div> <div class="list_procedure_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_procedure_inner" style="width: 18pt; white-space: nowrap;"> <span class="Numbers">3 </span> </div> </td> <td width="100%"> <div class="list_procedure_inner"><a name="140651">Driver retrieves results from bulk load data file.</a></div> </td> </tr> </table> </div> <div class="list_procedure_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_procedure_inner" style="width: 18pt; white-space: nowrap;"> <span class="Numbers">4 </span> </div> </td> <td width="100%"> <div class="list_procedure_inner"><a name="140652">Driver bulk loads results on DB2 server.</a></div> </td> </tr> </table> </div> <div class="head_2"><a name="140654">Bulk Export and Load Methods</a></div> <div class="Body"><a name="129813">You can take advantage of DataDirect Bulk Load either through the Driver setup dialog or </a>programmatically.</div> <div class="Body"><a name="145150">Applications that are already coded to use parameter array batch functionality can leverage </a>DataDirect Bulk Load features through the Enable Bulk Load connection option on the Bulk tab of the Driver setup dialog. Enabling this option automatically converts the parameter array batch operation to use the database bulk load protocol without any code changes to your application.</div> <div class="Body"><a name="143342">If you are not using parameter array batch functionality, the bulk operation buttons </a><span class="Action_bold">Export </span><span class="Action_bold">Table</span> and <span class="Action_bold">Load Table</span> on the Bulk tab of the driver Setup dialog also allow you to use bulk load functionality without any code changes. See the individual driver chapters for a description of the Bulk tab.</div> <div class="Body"><a name="143319">If you want to integrate bulk load functionality seamlessly into your application, you can </a>include code to use the bulk load functions exposed by the driver.</div> <div class="Body"><a name="134835">NOTE: For your applications to use DataDirect Bulk Load functionality, they must obtain </a>driver connection handles and function pointers, as follows:</div> <div class="list_procedure_first_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_procedure_first_inner" style="width: 18pt; white-space: nowrap;"> <span class="Numbers">1 </span> </div> </td> <td width="100%"> <div class="list_procedure_first_inner"><a name="134836">Use SQLGetInfo with the parameter SQL_DRIVER_HDBC to obtain the driverâ??s </a>connection handle from the Driver Manager.</div> </td> </tr> </table> </div> <div class="list_procedure_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_procedure_inner" style="width: 18pt; white-space: nowrap;"> <span class="Numbers">2 </span> </div> </td> <td width="100%"> <div class="list_procedure_inner"><a name="134837">Use SQLGetInfo with the parameter SQL_DRIVER_HLIB to obtain the driverâ??s shared </a>library or DLL handle from the Driver Manager.</div> </td> </tr> </table> </div> <div class="list_procedure_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_procedure_inner" style="width: 18pt; white-space: nowrap;"> <span class="Numbers">3 </span> </div> </td> <td width="100%"> <div class="list_procedure_inner"><a name="136198">Obtain function pointers to the bulk load functions using the function name resolution </a>method specific to your operating system. The bulk.c example program shipped with the drivers contains the function resolveName that illustrates how to obtain function pointers to the bulk load functions.</div> </td> </tr> </table> </div> <div class="Body"><a name="134839">This is detailed in the code samples that follow and in </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'bulk.11.2.html#122084', '');">â??Sample Bulk Load Configuration Fileâ??</a></span> in <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'bulk.11.1.html#106056', '');">Chapter 9</a></span> of the <span class="Emphasis">DataDirect Connect Series </span><span class="forbody">for</span> <span class="Emphasis" style="font-size: 8.0pt;">ODBC</span><span class="Emphasis" style="font-size: 10.0pt;"> Reference</span>.</div> <div class="head_2"><a name="129787">Exporting Data from a Database</a></div> <div class="Body"><a name="121896">You can export data from a database in one of three ways:</a></div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="130042">From a table by using the driver Setup dialog</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="130051">From a table by using DataDirect functions</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="130052">From a result set by using DataDirect statement attributes</a></div> </td> </tr> </table> </div> <div class="Body"><a name="130039">From the DataDirect driver Setup dialog, select the Bulk tab and click </a><span class="Action_bold">Export Table</span>. See the individual driver chapters for a description of this procedure.</div> <div class="Body"><a name="121954">Your application can export a table using the DataDirect functions ExportTableToFile (ANSI </a>application) or ExportTableToFileW (Unicode application). The application must first obtain driver connection handles and function pointers, as shown in the following example:</div> <div class="syntax_first"><a name="135335">HDBC</a> hdbc;</div> <div class="syntax"><a name="135336">HENV</a> henv;</div> <div class="syntax"><a name="135337">void</a> *driverHandle;</div> <div class="syntax"><a name="135338">HMODULE</a> hmod;</div> <div class="syntax"><a name="135339">PExportTableToFile exportTableToFile;</a></div> <div class="syntax"><a name="135340"> </a></div> <div class="syntax"><a name="135341">char</a> tableName[128];</div> <div class="syntax"><a name="135342">char</a> fileName[512];</div> <div class="syntax"><a name="135343">char</a> logFile[512];</div> <div class="syntax"><a name="135344">int</a> errorTolerance;</div> <div class="syntax"><a name="135345">int</a> warningTolerance;</div> <div class="syntax"><a name="135346">int</a> codePage;</div> <div class="syntax"><a name="135347"> </a></div> <div class="syntax"><a name="135348">/* Get the driver's connection handle from the DM.</a></div> <div class="syntax"><a name="135349"> </a> This handle must be used when calling directly into the driver. */</div> <div class="syntax"><a name="135350"> </a></div> <div class="syntax"><a name="135351">rc = SQLGetInfo (hdbc, SQL_DRIVER_HDBC, &driverHandle, 0, NULL);</a></div> <div class="syntax"><a name="135352">if (rc != SQL_SUCCESS) {</a></div> <div class="syntax"><a name="135353"> </a> ODBC_error (henv, hdbc, SQL_NULL_HSTMT);</div> <div class="syntax"><a name="135354"> </a> EnvClose (henv, hdbc);</div> <div class="syntax"><a name="135355"> </a> exit (255);</div> <div class="syntax"><a name="135356">}</a></div> <div class="syntax"><a name="135357"> </a></div> <div class="syntax"><a name="135358">/* Get the DM's shared library or DLL handle to the driver. */</a></div> <div class="syntax"><a name="135359"> </a></div> <div class="syntax"><a name="135360">rc = SQLGetInfo (hdbc, SQL_DRIVER_HLIB, &hmod, 0, NULL);</a></div> <div class="syntax"><a name="135361">if (rc != SQL_SUCCESS) {</a></div> <div class="syntax"><a name="135362"> </a> ODBC_error (henv, hdbc, SQL_NULL_HSTMT);</div> <div class="syntax"><a name="135363"> </a> EnvClose (henv, hdbc);</div> <div class="syntax"><a name="135364"> </a> exit (255);</div> <div class="syntax"><a name="135365">}</a></div> <div class="syntax"><a name="135366"> </a></div> <div class="syntax"><a name="135367">exportTableToFile = (PExportTableToFile)</a></div> <div class="syntax"><a name="135368"> </a> resolveName (hmod, "ExportTableToFile");</div> <div class="syntax"><a name="135369">if (! exportTableToFile) {</a></div> <div class="syntax"><a name="135370"> </a> printf ("Cannot find ExportTableToFile!\n");</div> <div class="syntax"><a name="135371"> </a> exit (255);</div> <div class="syntax"><a name="135372">}</a></div> <div class="syntax"><a name="135373"> </a></div> <div class="syntax"><a name="135374">rc = (*exportTableToFile) (</a></div> <div class="syntax"><a name="135375"> </a> driverHandle,</div> <div class="syntax"><a name="135376"> </a> (const SQLCHAR *) tableName,</div> <div class="syntax"><a name="135377"> </a> (const SQLCHAR *) fileName,</div> <div class="syntax"><a name="135378"> </a> codePage,</div> <div class="syntax"><a name="135379"> </a> errorTolerance, warningTolerance,</div> <div class="syntax"><a name="135380"> </a> (const SQLCHAR *) logFile);</div> <div class="syntax"><a name="135381">if (rc == SQL_SUCCESS) {</a></div> <div class="syntax"><a name="135382"> </a> printf ("Export succeeded.\n");</div> <div class="syntax"><a name="135383">}</a></div> <div class="syntax"><a name="135384">else {</a></div> <div class="syntax"><a name="135385"> </a> driverError (driverHandle, hmod);</div> <div class="syntax"><a name="135386">}</a></div> <div class="Body"><a name="121965">Refer to </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'bulk.11.2.html#122084', '');">â??Sample Bulk Load Configuration Fileâ??</a></span> in <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'bulk.11.1.html#106056', '');">Chapter 9</a></span> of the <span class="Emphasis">DataDirect Connect </span><span class="Emphasis">Series </span><span class="forbody">for</span> <span class="Emphasis" style="font-size: 8.0pt;">ODBC</span><span class="Emphasis" style="font-size: 10.0pt;"> Reference</span> for a full description of these functions.</div> <div class="Body"><a name="130095">Your application can export a result set using the DataDirect statement attributes </a>SQL_BULK_EXPORT and SQL_BULK_EXPORT_PARAMS.</div> <div class="Body"><a name="122375">The export operation creates a bulk load data file with a .csv extension in which the </a>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:</div> <div class="syntax_first"><a name="122743">1,0x6263,"bc","bc"</a></div> <div class="syntax"><a name="122744">2,0x636465,"cde","cde"</a></div> <div class="syntax"><a name="122745">3,0x64656667,"defg","defg"</a></div> <div class="syntax"><a name="122746">4,0x6566676869,"efghi","efghi"</a></div> <div class="syntax"><a name="122747">5,0x666768696a6b,"fghijk","fghijk"</a></div> <div class="syntax"><a name="122748">6,0x6768696a6b6c6d,"ghijklm","ghijklm"</a></div> <div class="syntax"><a name="122749">7,0x68696a6b6c6d6e6f,"hijklmno","hijklmno"</a></div> <div class="syntax"><a name="122750">8,0x696a6b6c6d6e6f7071,"ijklmnopq","ijklmnopq"</a></div> <div class="syntax"><a name="122751">9,0x6a6b6c6d6e6f70717273,"jklmnopqrs","jklmnopqrs"</a></div> <div class="syntax"><a name="122752">10,0x6b,"k","k"</a></div> <div class="Body"><a name="130150">A bulk load configuration file with and .xml extension is also created when either a table or a </a>result set is exported to a bulk load data file. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.6.html#112375', '');">â??The Bulk Load Configuration Fileâ??</a></span> for an example of a bulk load configuration file.</div> <div class="Body"><a name="131649">In addition, a log file of events as well as external overflow files can be created during a bulk </a>export operation. The log file is configured through either the driver Setup dialog Bulk tab, the ExportTableToFile function, or the SQL_BULK_EXPORT statement attribute. The external overflow files are configured through connection options; see <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.6.html#109988', '');">â??External Overflow Filesâ??</a></span> for details.</div> <div class="head_2"><a name="122392">Bulk Loading to a Database</a></div> <div class="Body"><a name="122393">The Enable Bulk Load connection option specifies the method by which bulk data is loaded </a>to a database. When the option is enabled, the driver uses database bulk load protocols. When not enabled, the driver uses standard parameter arrays.</div> <div class="Body"><a name="131928">You can load data from the bulk load data file into the target database through the </a>DataDirect driver Setup dialog by selecting the Bulk tab and clicking <span class="Action_bold">Load Table</span>. See the individual driver chapters of the drivers that support bulk load for a description of this procedure.</div> <div class="Body"><a name="134957">Your application can also load data from the bulk load data file into the target database </a>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:</div> <div class="syntax_first"><a name="135104">HDBC</a> hdbc;</div> <div class="syntax"><a name="135105">HENV</a> henv;</div> <div class="syntax"><a name="135106">void</a> *driverHandle;</div> <div class="syntax"><a name="135107">HMODULE</a> hmod;</div> <div class="syntax"><a name="135108">PLoadTableFromFile loadTableFromFile;</a></div> <div class="syntax"><a name="135110">char</a> tableName[128];</div> <div class="syntax"><a name="135111">char</a> fileName[512];</div> <div class="syntax"><a name="135112">char</a> configFile[512];</div> <div class="syntax"><a name="135113">char</a> logFile[512];</div> <div class="syntax"><a name="135114">char</a> discardFile[512];</div> <div class="syntax"><a name="135115">int</a> errorTolerance;</div> <div class="syntax"><a name="135116">int</a> warningTolerance;</div> <div class="syntax"><a name="135117">int</a> loadStart;</div> <div class="syntax"><a name="135118">int</a> loadCount;</div> <div class="syntax"><a name="135119">int</a> readBufferSize;</div> <div class="syntax"><a name="135120"> </a></div> <div class="syntax"><a name="135121">/* Get the driver's connection handle from the DM.</a></div> <div class="syntax"><a name="135122"> </a> This handle must be used when calling directly into the driver. */</div> <div class="syntax"><a name="135123"> </a></div> <div class="syntax"><a name="135124">rc = SQLGetInfo (hdbc, SQL_DRIVER_HDBC, &driverHandle, 0, NULL);</a></div> <div class="syntax"><a name="135125">if (rc != SQL_SUCCESS) {</a></div> <div class="syntax"><a name="135126"> </a> ODBC_error (henv, hdbc, SQL_NULL_HSTMT);</div> <div class="syntax"><a name="135127"> </a> EnvClose (henv, hdbc);</div> <div class="syntax"><a name="135128"> </a> exit (255);</div> <div class="syntax"><a name="135129">}</a></div> <div class="syntax"><a name="135130"> </a></div> <div class="syntax"><a name="135131">/* Get the DM's shared library or DLL handle to the driver. */</a></div> <div class="syntax"><a name="135132"> </a></div> <div class="syntax"><a name="135133">rc = SQLGetInfo (hdbc, SQL_DRIVER_HLIB, &hmod, 0, NULL);</a></div> <div class="syntax"><a name="135134">if (rc != SQL_SUCCESS) {</a></div> <div class="syntax"><a name="135135"> </a> ODBC_error (henv, hdbc, SQL_NULL_HSTMT);</div> <div class="syntax"><a name="135136"> </a> EnvClose (henv, hdbc);</div> <div class="syntax"><a name="135137"> </a> exit (255);</div> <div class="syntax"><a name="135138">}</a></div> <div class="syntax"><a name="135139"> </a></div> <div class="syntax"><a name="135140">loadTableFromFile = (PLoadTableFromFile)</a></div> <div class="syntax"><a name="135141"> </a> resolveName (hmod, "LoadTableFromFile");</div> <div class="syntax"><a name="135142">if (! loadTableFromFile) {</a></div> <div class="syntax"><a name="135143"> </a> printf ("Cannot find LoadTableFromFile!\n");</div> <div class="syntax"><a name="135144"> </a> exit (255);</div> <div class="syntax"><a name="135145">}</a></div> <div class="syntax"><a name="135146"> </a></div> <div class="syntax"><a name="135147">rc = (*loadTableFromFile) (</a></div> <div class="syntax"><a name="174239"> </a> driverHandle,</div> <div class="syntax"><a name="174240"> </a> (const SQLCHAR *) tableName,</div> <div class="syntax"><a name="174256"> </a> (const SQLCHAR *) fileName,</div> <div class="syntax"><a name="174257"> </a> errorTolerance, warningTolerance,</div> <div class="syntax"><a name="174258"> </a> (const SQLCHAR *) configFile,</div> <div class="syntax"><a name="174259"> </a> (const SQLCHAR *) logFile,</div> <div class="syntax"><a name="135154"> </a> (const SQLCHAR *) discardFile,</div> <div class="syntax"><a name="135155"> </a> loadStart, loadCount,</div> <div class="syntax"><a name="135156"> </a> readBufferSize);</div> <div class="syntax"><a name="135157">if (rc == SQL_SUCCESS) {</a></div> <div class="syntax"><a name="135158"> </a> printf ("Load succeeded.\n");</div> <div class="syntax"><a name="135159">}</a></div> <div class="syntax"><a name="135160">else {</a></div> <div class="syntax"><a name="135161"> </a> driverError (driverHandle, hmod);</div> <div class="syntax"><a name="135162">}</a></div> <div class="Body"><a name="122454">Refer to </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'bulk.11.2.html#122084', '');">â??Sample Bulk Load Configuration Fileâ??</a></span> in <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'bulk.11.1.html#106056', '');">Chapter 9</a></span> of the <span class="Emphasis">DataDirect Connect </span><span class="Emphasis">Series </span><span class="forbody">for</span> <span class="Emphasis" style="font-size: 8.0pt;">ODBC</span><span class="Emphasis" style="font-size: 10.0pt;"> Reference</span> for a full description of these functions. </div> <div class="Body"><a name="182825">Use the BulkLoadBatchSize connection attribute to specify the number of rows the driver </a>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. </div> <div class="Body"><a name="182826">A log file of events as well as a discard file that contains rows rejected during the load can </a>be created during a bulk load operation. These files are configured through either the driver Setup dialog Bulk tab or the LoadTableFromFile function.</div> <div class="Body"><a name="182827">The discard file is in the same format as the bulk load data file. After fixing reported issues in </a>the discard file, the bulk load can be reissued using the discard file as the bulk load data file. </div> <div class="Body"><a name="122403">NOTE FOR SYBASE USERS: Additional database configuration is required for destination </a>tables that do not have an index. See the "Persisting a Result Set as an XML Data File" section in your driver chapter for more information.</div> <div class="Body"><a name="201218">NOTE FOR SALESFORCE USERS: In addition to bulk Insert, the Salesforce driver also </a>supports bulk Delete, Update, and Upsert. This functionality is enabled with the SetBulkOperation function which is implemented in the driver. Refer to "DataDirect Bulk Load Functions" in Chapter 9 of the <span class="Emphasis">DataDirect Connect Series for ODBC Reference</span> for a full description of these functions. </div> <div class="head_2"><a name="112375">The Bulk Load Configuration File</a></div> <div class="Body"><a name="122531">A bulk load configuration file is created when either a table or a result set is exported to a </a>bulk load data file. This file has the same name as the bulk load data file, but with an .xml extension.</div> <div class="Body"><a name="122657">The bulk load configuration file defines in its metadata the names and data types of the </a>columns in the bulk load data file. The file defines these names and data types based on the table or result set created by the query that exported the data.</div> <div class="Body"><a name="131801">It also defines other data properties, such as length for character and binary data types, the </a>character encoding code page for character types, precision and scale for numeric types, and nullablity for all types.</div> <div class="Body"><a name="122719">When a bulk load data file cannot read its configuration file, the following defaults are </a>assumed:</div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="122720">All data is read in as character data. Each value between commas is read as character </a>data.</div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="122721">The default character set is defined, on Windows, by the current Windows code page. </a>On UNIX/Linux, it is the IANAAppCodePage value, which defaults to 4. </div> </td> </tr> </table> </div> <div class="Body"><a name="122885">For example, the format of the bulk load data file GBMAXTABLE.csv (discussed in </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.6.html#129787', '');">â??Exporting Data from a Databaseâ??</a></span>) is defined by the bulk load configuration file, GBMAXTABLE.xml, as follows:</div> <div class="syntax_first"><a name="122886"><?xml version="1.0" encoding="utf-8"?></a></div> <div class="syntax"><a name="122887"><table codepage="UTF-16LE" xsi:noNamespaceSchemaLocation=</a></div> <div class="syntax"><a name="123159">"http://www.datadirect.com/ns/bulk/BulkData.xsd" xmlns:xsi=</a></div> <div class="syntax"><a name="123160">"http://www.w3.org/2001/XMLSchema-instance"></a></div> <div class="syntax"><a name="122888"> </a> <row></div> <div class="syntax"><a name="122889"> </a> <column datatype="DECIMAL" precision="38" scale="0" nullable=</div> <div class="syntax"><a name="123155"> </a> "false">INTEGERCOL</column></div> <div class="syntax"><a name="122890"> </a> <column datatype="VARBINARY" length="10" nullable=</div> <div class="syntax"><a name="123156"> </a> "true">VARBINCOL</column></div> <div class="syntax"><a name="122891"> </a> <column datatype="VARCHAR" length="10" sourcecodepage="Windows-1252"</div> <div class="syntax"><a name="123157"> </a> externalfilecodepage="Windows-1252" nullable="true">VCHARCOL</column></div> <div class="syntax"><a name="122892"> </a> <column datatype="VARCHAR" length="10" sourcecodepage="Windows-1252"</div> <div class="syntax"><a name="123158"> </a> externalfilecodepage="Windows-1252" nullable="true">UNIVCHARCOL</column></div> <div class="syntax"><a name="122893"> </a> </row></div> <div class="syntax"><a name="122894"></table></a></div> <div class="head_3"><a name="131756">Bulk Load Configuration File Schema</a></div> <div class="Body"><a name="131757">The bulk load configuration file is supported by an underlying XML Schema defined at:</a></div> <div class="Body" style="color: #0000ff; font-style: normal; font-variant: normal; font-weight: normal; text-transform: none; vertical-align: baseline;"><span class="Cross_ref_"><a href="http://www.datadirect.com/ns/bulk/BulkData.xsd" target="external_window" name="131759">http://www.datadirect.com/ns/bulk/BulkData.xsd</a></span></div> <div class="Body"><a name="131760">The bulk load configuration file must conform to the bulk load configuration XML schema. </a>Each bulk export operation generates a bulk load configuration file in UTF-8 format. If the bulk load data file cannot be created or does not comply with the XML Schema described in the bulk load configuration file, an error is generated.</div> <div class="head_3"><a name="130186">Verification of the Bulk Load Configuration File</a></div> <div class="Body"><a name="131848">You can verify the metadata in the configuration file against the data structure of the target </a>database table. This insures that the data in the bulk load data file is compatible with the target database table structure. </div> <div class="Body"><a name="131875">The verification does not check the actual data in the bulk load data file, so it is possible that </a>the load can fail even though the verification succeeds. For example, if you were to update the bulk load data file manually such that it has values that exceed the maximum column length of a character column in the target table, the load would fail. </div> <div class="Body"><a name="131870">Not all of the error messages or warnings that are generated by verification necessarily </a>mean that the load will fail. Many of the messages simply notify you about possible incompatibilities between the source and target tables. For example, if the bulk load data file has a column that is defined as an integer and the column in the target table is defined as smallint, the load may still succeed if the values in the source column are small enough that they fit in a smallint column.</div> <div class="Body"><a name="122547">To verify the metadata in the bulk load configuration file through the DataDirect driver Setup </a>dialog, select the Bulk tab and click <span class="Action_bold">Verify</span>. See the individual driver chapters of the drivers that support bulk load for a description of this procedure.</div> <div class="Body"><a name="135218">Your application can also verify the metadata of the bulk load configuration file using the </a>DataDirect functions ValidateTableFromFile (ANSI application) or ValidateTableFromFileW (Unicode application). The application must first obtain driver connection handles and function pointers, as shown in the following example:</div> <div class="syntax_first"><a name="135282">HDBC</a> hdbc;</div> <div class="syntax"><a name="135283">HENV</a> henv;</div> <div class="syntax"><a name="135284">void</a> *driverHandle;</div> <div class="syntax"><a name="135285">HMODULE</a> hmod;</div> <div class="syntax"><a name="135286">PValidateTableFromFile validateTableFromFile;</a></div> <div class="syntax"><a name="135287"> </a></div> <div class="syntax"><a name="135288">char</a> tableName[128];</div> <div class="syntax"><a name="135289">char</a> configFile[512];</div> <div class="syntax"><a name="135290">char</a> messageList[10240];</div> <div class="syntax"><a name="135291">SQLLEN</a> numMessages;</div> <div class="syntax"><a name="135292"> </a></div> <div class="syntax"><a name="135293">/* Get the driver's connection handle from the DM.</a></div> <div class="syntax"><a name="135294"> </a> This handle must be used when calling directly into the driver. */</div> <div class="syntax"><a name="135295"> </a></div> <div class="syntax"><a name="135296">rc = SQLGetInfo (hdbc, SQL_DRIVER_HDBC, &driverHandle, 0, NULL);</a></div> <div class="syntax"><a name="135297">if (rc != SQL_SUCCESS) {</a></div> <div class="syntax"><a name="135298"> </a> ODBC_error (henv, hdbc, SQL_NULL_HSTMT);</div> <div class="syntax"><a name="135299"> </a> EnvClose (henv, hdbc);</div> <div class="syntax"><a name="135300"> </a> exit (255);</div> <div class="syntax"><a name="135823">}</a></div> <div class="syntax"><a name="135302"> </a></div> <div class="syntax"><a name="135303">/* Get the DM's shared library or DLL handle to the driver. */</a></div> <div class="syntax"><a name="135304"> </a></div> <div class="syntax"><a name="135305">rc = SQLGetInfo (hdbc, SQL_DRIVER_HLIB, &hmod, 0, NULL);</a></div> <div class="syntax"><a name="135306">if (rc != SQL_SUCCESS) {</a></div> <div class="syntax"><a name="135307"> </a> ODBC_error (henv, hdbc, SQL_NULL_HSTMT);</div> <div class="syntax"><a name="135308"> </a> EnvClose (henv, hdbc);</div> <div class="syntax"><a name="135309"> </a> exit (255);</div> <div class="syntax"><a name="135812">}</a></div> <div class="syntax"><a name="135844"> </a></div> <div class="syntax"><a name="135845">validateTableFromFile = (PValidateTableFromFile)</a></div> <div class="syntax"><a name="135846"> </a> resolveName (hmod, "ValidateTableFromFile");</div> <div class="syntax"><a name="135847">if (!validateTableFromFile) {</a></div> <div class="syntax"><a name="135848"> </a> printf ("Cannot find ValidateTableFromFile!\n");</div> <div class="syntax"><a name="135849"> </a> exit (255);</div> <div class="syntax"><a name="135850">}</a></div> <div class="syntax"><a name="135813"> </a></div> <div class="syntax"><a name="135814">messageList[0] = 0;</a></div> <div class="syntax"><a name="135313">numMessages = 0;</a></div> <div class="syntax"><a name="135314"> </a></div> <div class="syntax"><a name="135315">rc = (*validateTableFromFile) (</a></div> <div class="syntax"><a name="135316"> </a> driverHandle,</div> <div class="syntax"><a name="135317"> </a> (const SQLCHAR *) tableName,</div> <div class="syntax"><a name="135318"> </a> (const SQLCHAR *) configFile,</div> <div class="syntax"><a name="135319"> </a> (SQLCHAR *) messageList,</div> <div class="syntax"><a name="135320"> </a> sizeof (messageList),</div> <div class="syntax"><a name="135321"> </a> &numMessages);</div> <div class="syntax"><a name="135322">printf ("%d message%s%s\n", numMessages,</a></div> <div class="syntax"><a name="135323"> </a> (numMessages == 0) ? "s" :</div> <div class="syntax"><a name="135324"> </a> ((numMessages == 1) ? " : " : "s : "),</div> <div class="syntax"><a name="135325"> </a> (numMessages > 0) ? messageList : "");</div> <div class="syntax"><a name="135326">if (rc == SQL_SUCCESS) {</a></div> <div class="syntax"><a name="135327"> </a> printf ("Validate succeeded.\n");</div> <div class="syntax"><a name="135328">}</a></div> <div class="syntax"><a name="135329">else {</a></div> <div class="syntax"><a name="135330"> </a> driverError (driverHandle, hmod);</div> <div class="syntax"><a name="135331">}</a></div> <div class="Body"><a name="135275">Refer to </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'bulk.11.2.html#122084', '');">â??Sample Bulk Load Configuration Fileâ??</a></span> in <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'bulk.11.1.html#106056', '');">Chapter 9</a></span> of the <span class="Emphasis">DataDirect Connect </span><span class="Emphasis">Series </span><span class="forbody">for</span> <span class="Emphasis" style="font-size: 8.0pt;">ODBC</span><span class="Emphasis" style="font-size: 10.0pt;"> Reference</span> for a complete description of these functions.</div> <div class="head_2"><a name="109939">Sample Applications</a></div> <div class="Body"><a name="119486">Progress DataDirect provides a sample application that demonstrates the bulk export, </a>verification, and bulk load operations. This application is located in the \samples\bulk subdirectory of the product installation directory along with a text file named bulk.txt. Please consult bulk.txt for instructions on using the sample bulk load application.</div> <div class="Body"><a name="170640">A bulk streaming application is also provided in the \samples\bulkstrm subdirectory along </a>with a text file named bulkstrm.txt. Please consult bulkstrm.txt for instructions on using the bulk streaming application.</div> <div class="head_2"><a name="119463">Determining the Bulk Load Protocol</a></div> <div class="Body"><a name="145277">Bulk operations can be performed using a dedicated bulk load protocol, that is, the protocol </a>of the underlying database system, or by using parameter array batch operations. Dedicated protocols are generally more performance-efficient than parameter arrays. In some cases, however, you must use parameter arrays, for example, when the data to be loaded is in a data type not supported by the dedicated bulk protocol.</div> <div class="Body"><a name="144160">The Enable Bulk Load connection option determines bulk load behavior. When the option is </a>enabled, the driver uses database bulk load protocols unless it encounters a problem, in which case it returns an error. In this situation, you must disable Enable Bulk Load so that the driver uses standard parameter arrays.</div> <div class="head_2"><a name="122965">Character Set Conversions</a></div> <div class="Body"><a name="132457">It is most performance-efficient to transfer data between databases that use the same </a>character sets. At times, however, you might need to bulk load data between databases that use different character sets. You can do this by choosing a character set for the bulk load data file that will accommodate all data. If the source table contains character data that uses different character sets, then one of the Unicode character sets, UTF-8, UTF-16BE, or UTF-16LE must be specified for the bulk load data file. A Unicode character set should also be specified in the case of a target table uses a different character set than the source table to minimize conversion errors. If the source and target tables use the same character set, that set should be specified for the bulk load data file.</div> <div class="Body"><a name="132458">A character set is defined by a code page. The code page for the bulk load data file is </a>defined in the configuration file and is specified through either the Code Page option of the Export Table driver Setup dialog or through the IANAAppCodePage parameter of the ExportTableToFile function. Any code page listed in <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'appcodepage.03.1.html#108909', '');">Chapter 1 â??Code Page Valuesâ??</a></span>of the <span class="Emphasis">DataDirect Connect Series </span><span class="forbody">for</span> <span class="Emphasis" style="font-size: 8.0pt;">ODBC</span><span class="Emphasis" style="font-size: 10.0pt;"> Reference</span> is supported for the bulk load data file. </div> <div class="Body"><a name="132340">Any character conversion errors are handled based on the value of the Report CodePage </a>ConversionErrors connection option. See the individual driver chapters for a description of this option.</div> <div class="Body"><a name="109947">The configuration file may optionally define a second code page value for each character </a>column (<span class="Syntax">externalfilecodepage</span>). If character data is stored in an external overflow file (see <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.6.html#109988', '');">â??External Overflow Filesâ??</a></span>), this second code page value is used for the external file.</div> <div class="head_2"><a name="109988">External Overflow Files</a></div> <div class="Body"><a name="109989">In addition to the bulk load data file, DataDirect Bulk Load can store bulk data in external </a>overflow files. These overflow files are located in the same directory as the bulk load data file. Whether or not to use external overflow files is a performance consideration. For example, binary data is stored as hexadecimal-encoded character strings in the main bulk load data file, which increases the size of the file per unit of data stored. External files do not store binary data as hex character strings, and, therefore, require less space. On the other hand, more overhead is required to access external files than to access a single bulk load data file, so each bulk load situation must be considered individually.</div> <div class="Body"><a name="123017">The value of the Bulk Binary Threshold connection option determines the threshold, in KB, </a>over which binary data is stored in external files instead of in the bulk load data file. Likewise, the Bulk Character Threshold connection option determines the threshold for character data.</div> <div class="Body"><a name="112280">In the case of an external character data file, the character set of the file is governed by the </a>bulk load configuration file. If the bulk load data file is Unicode and the maximum character size of the source data is 1, then the data is stored in its source code page. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.6.html#122965', '');">â??Character Set Conversionsâ??</a></span>.</div> <div class="Body"><a name="112281">The file name of the external file contains the bulk load data file name, a six-digit number, </a>and a ".lob" extension in the following format: <span class="EquationVariables">CSVfilename_nnnnnn</span>.lob. Increments start at 000001.lob.</div> <div class="head_2"><a name="193671">Using Bulk Load for Batch Inserts</a></div> <div class="Body"><a name="193672">For all drivers that support bulk operations, except the Salesforce driver, the driver uses the </a>native bulk load protocol for database connections when the Enable Bulk Load connection option is set to <span class="Syntax">true</span>. For example, if you set the Enable Bulk Load connection option to <span class="Syntax">true</span>, the driver would use bulk load for the native parameter array insert request.</div> <div class="Body"><a name="202565">In some cases, the driver may not be able to use bulk load because of restrictions enforced </a>by the bulk load protocol and will downgrade to a batch mechanism. For example, if the data being loaded has a data type that is not supported by the bulk load protocol, the driver cannot use bulk load, but will use the native parameter array insert mechanism instead.</div> <div class="Body"><a name="202566">For the Salesforce driver, when the Enable Bulk Load connection option is set to </a><span class="Syntax">true</span> and the number of rows to be inserted in the batch is larger than Bulk Load Threshold, the driver uses the Salesforce Bulk API instead of the Web service API. </div> <div class="Body"><a name="193684">For all drivers that support bulk operations, use the Bulk Load Batch Size connection option </a>to specify the number of rows the driver loads 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.</div> <div class="head_2"><a name="193714">Using Bulk Load for Single Inserts/Updates/Deletes </a>(Salesforce Driver)</div> <div class="Body"><a name="193715">When the Enable Bulk Load connection option is set to </a><span class="Syntax">1</span>, the driver uses the Salesforce Bulk API for single Insert, Update, and Delete statements if the number of rows affected by the operation exceeds the threshold set by the Bulk Load Threshold connection property. </div> <div class="Body"><a name="194507">For example, if you set the Enable Bulk Load connection option to </a><span class="Syntax">1</span> and the Bulk Load Threshold connection option to 2000, executing the following statement would use the Bulk API if the number of rows returned by <span class="Syntax">SELECT rowid, sys_name FROM account</span> is more than 2000 rows.</div> <div class="syntax_first"><a name="193716">INSERT INTO tmpAccounts(accountId, accountName)</a></div> <div class="syntax"><a name="193717">SELECT rowid, sys_name FROM account</a></div> <div class="head_2"><a name="117945">Summary of Bulk Load Related Options</a></div> <div class="Body"><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.6.html#118037', '');" name="117949">Table 3-10</a></span> summarizes how DataDirect Bulk Load related connection options work with the drivers. See "Connection Option Descriptions" in each driver chapter for details about configuring the options.</div> <div class="Body_Wide"><a name="118073"> </a></div> <table class="Format_E" cellspacing="0" summary=""> <caption> <div class="table_title_wide">Table 3-10. <a name="118037">Summary: Bulk Load Connection Options</a> </div> </caption> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="118042">Option</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="118044">Characteristic</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="118046">Batch Size</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="118048">An integer value that specifies the number of rows at a time that the driver sends </a>to the database during bulk operations.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="118050">Bulk Binary Threshold</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="118052">An integer value that specifies the maximum size, in KB, of binary data exported </a>to the bulk data file. Any data exceeding this size is exported to an external file.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="118054">Bulk Character Threshold</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="118056">An integer value that specifies the maximum size, in KB, of character data </a>exported to the bulk data file. Any data exceeding this size is exported to an external file.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="118058">Enable Bulk Load</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="118060">When enabled, the driver uses database bulk load protocols. When not enabled, </a>the driver uses standard parameter arrays.</div> </td> </tr> </table> <script type="text/javascript" language="JavaScript1.2"> <!-- // Clear related topics // WWHClearRelatedTopics(); document.writeln(WWHRelatedTopicsInlineHTML()); // --> </script> </blockquote> <script type="text/javascript" language="JavaScript1.2"> <!-- document.write(WWHRelatedTopicsDivTag() + WWHPopupDivTag() + WWHALinksDivTag()); // --> </script> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de