Use an ODBC document to directly read data from or write data to an external database. You can exchange data with an external database via ODBC or with an external Azure SQL database.
| 1. | Click Connectivity studio Integration Design. |
| 2. | Click the Documents tab. |
| 3. | Click New. |
| 4. | Define a meaningful name for the document. Example: If the document is used for a sales integration, you can use names like 'Sales - Order' or 'Sales - Invoice'. |
| In the Document field, type a value. | |
|
Note: |
|
| 5. | In the Project field, enter or select a value. |
| 6. | Define the applicable application for the document. For an ODBC document, for example, select a 'ODBC' application. |
| In the Application field, enter or select a value. | |
|
Note: You can only select an application that is defined for the applicable project. You can define project applications on the Projects page. |
|
| 7. | In the Document types field, select 'ODBC'. |
| 8. | Sub-task: Set properties. |
| 8.1 | Expand the Properties section. |
| 8.2 | To improve performance when processing a lot of records, you can use paging. For paging, the records are split over several threads which run these records in parallel batch tasks. Define the number of records to be processed by one batch task. |
| In the Page size field, enter a number. | |
|
Note: You can use this calculation to define the number to be entered: Page size = Total number of records / Number of available threads. |
|
| 8.3 | You can process an external document in these ways: - Direct: You can use this to import or export (big sets of) simple data. For simple data, the document lines only have a root record. When a message is run, the data is directly mapped. More technically: it only loads the data in the memory of the record table (usually the BisBufferTable). As a result, the import or export of data is faster. - Query: You can use this to import or export complex data. For complex data, the document lines have several records and parent-child relations. When a message is run, the records are stored in the record table (usually the BisBufferTable) before the data is mapped. When the message run is finished, the records are deleted from the record table. |
| In the Process type field, select an option. | |
| 8.4 | Usually, no spaces are used in table names and table field names. However, if the external database uses spaces, you can allow the use of spaces in table names and table field names. If Yes, the table names and table field names are put between brackets. |
| Select Yes in the Allow space field. | |
| 8.5 | If the document is used to read data, you can correct the value of ODBC date/time fields with the user date/time. |
| Select Yes in the Adjust date time field. | |
| 8.6 | By default, ODBC applies Unicode to support special characters. This means, all strings values get the prefix N'. However, in some cases the database does not support this syntax, which can result in errors. In this case, you can disable the use of Unicode. |
| Select Yes in the Disable Unicode field. | |
| 9. | Sub-task: Set (custom) handler. |
| 9.1 | Expand the Custom section. |
| 9.2 | For an ODBC document, these standard handler classes are available: - BisDocumentODBC: This handler class directly reads data from or writes data to an external database via ODBC. - BisDocumentODBCCData: This handler class directly reads data from or writes data to an external CData database via ODBC. The record names and field names must match the ODBC table names and field names. For each record, fill in the External table field in the Line details section. - BisDocumentODBCForDb2: This handler class directly reads data from or writes data to an external DB2 database via ODBC. You can use a customized handler class. To do so, extend a standard handler class. |
| In the Handler field, enter or select the desired handler class. | |
| 10. | Sub-task: Set ODBC commands. |
| 10.1 | If you exchange data with ODBC, you can run ODBC commands. The commands are run before or after the message is run. You can, for example, clear a table in the external database before it is filled again. |
| Expand the ODBC command section. | |
| 10.2 | Click New. |
| 10.3 | Enter the stored procedure or BCP command. To define the stored procedures, use ODBC commands and, if desired, one of these variables: - %1 = Database name - %2 = Database owner - %3 = Table name - %4 = File name - %5 = Format file name An BCP command example is: exec master..xp_cmdshell 'bcp database.dbo.custgroup out "file.txt" -c -t -T') |
| In the ODBC command field, type a value. | |
| 10.4 | Define the record of the ODBC document for which the ODBC command is run. |
| In the Reference field, enter or select a value. | |
| 10.5 | By default, the ODBC command is run when the message is finished. You can also run the command before the message is run. |
| Select the Execute before check box. | |
| 11. | Close the page. |