Open topic with navigation
Use the Database Setup dialog to set up a database connection that the Integration Service reads from when the integration is run.
|
|
If you have not previously connected to a database, you must complete the Database Setup wizard to open the Database Setup dialog. The Database Setup wizard starts when you click Database Connection Setup.
|
The connected database appears in the left navigation pane of the Database Setup dialog. Beneath the database name are properties that are specific to the database connection, such as tables and fields, custom Structured Query Language (SQL) statements, filter criteria, and so on. The following property pages may be available.
The Connection Properties property page displays information about the database connection. To access this property page, click the database connection name in the left navigation pane.
The options that are on this page vary depending on the type of database connection that you configure.
On the Connection Properties property page for a database connection, you can view the details about the connection, such as the database type or the file, database, or server name. You can also configure the database connection and create a named database connection that can be referenced by other integrations, actions, and applications in the BarTender Suite.
-
Name: Specifies the name of the database. You can modify this name by entering the name that you want in the field. This custom name can be used to access the database so that the data can be changed through automation. Any change that you make to the database name is document-specific.
-
Database Connection Setup: Starts the Database Setup wizard, which you use to update configuration settings for the current database connection or to select and configure a different database file.
-
Create named database connection: Opens the Create Named Database Connection dialog, where you can enter a name for the current connection so that it can be used by other integrations, actions, and applications in the BarTender Suite.
On the Connection Properties property page for a named database connection, you can configure the named database connection or stop using the named database connection, as follows:
|
|
When you modify the configuration settings for a named database connection, all other integrations, actions, and connections in other BarTender applications that use the named database connection are affected.
|
|
|
You can also view and manage existing named database connections by using Administration Console. For more information, refer to
Named Connections Page in the Administration Console help system.
|
Use the SQL Statement property page to write custom SQL statements for the connected record set.
By default, this property page displays the SQL statement that is generated automatically by the user's settings on the Tables, Sort Order, and Filter property pages of the Database Setup dialog.
For some database types, the Specify a custom SQL statement (Advanced) option is available. This option is intended for advanced users who are experienced in writing their own SQL statements. To create a custom SQL statement, click to select Specify a custom SQL Statement (Advanced), and then enter your SQL statement in the input field.
|
|
SQL is an advanced programming tool that is used by database programmers. If you decide that you want to learn SQL, we recommend that you obtain qualified SQL instruction or complete self-study with an SQL programming book.
|
The Tables property page displays a Tables list where you can view the tables that are available in the database connection. Click in the Alias column for a listed table to specify an alias, or alternate name, for the table. An alias lets you refer to a specific table even if the integration or document is set up to refer to it by a different name. When you assign an alias for the table name, all references to the table are maintained. For more information, refer to Assigning Aliases to Tables and Database Fields.
If your database connection includes two or more tables, you must join them by identifying the relationship between them. Use the Join Conditions section to specify these relationships by using joins. A join tells Integration Builder how data in multiple tables is related. Typically, this means identifying a field that exists in both tables. For more information, refer to About Table and Database Joins.
The Fields property page displays all of the database fields that are present in the selected tables of your database together with detailed information about the data type and the length of the data that is contained in that field. Click in the Alias column for a listed field to specify an alias, or alternate name, for the database field. An alias lets you refer to the database field even if the integration or document is set up to refer to it by a different name. When you assign an alias for the field name, all references to the old field are maintained. For more information, refer to Assigning Aliases to Tables and Database Fields.
Use the Sort Order property page to sort the records by one or more fields in your database. To select a field to sort by, move it from the Available Fields column to the Fields to Sort By column by dragging or double-clicking the field name. When you add a field to the Fields to Sort By column, you can choose to sort in ascending (1-9, A-Z) or descending (9-1, Z-A) order.
You may want to sort your records by more than one field. When you do this, any records that have identical data in the first field are sorted by the second field. For example, you may have two fields in your database: Department and Employee. You can first sort by Department, so that all employees of the same department are grouped together. Then, you can sort by Employee to place the names in alphabetical order within each department.
Use the Filter property page to specify search criteria that determine which records from your database are used by the integration or action. Filter statements are structured like a sentence that compares a database field to a value, such as "First Name begins with J". In this example, only those records in which the first name begins with a "J" are used.
Any filters that you define by using this dialog are applied before the integration or action is run. We recommend that you filter the records on the Filter property page when you are using a large database and when performance will improve if you filter the records before you run the integration.
For more information about interactive filtering, refer to Filtering Data.
Use the Options property page to set the following row options for the database:
-
Distinct rows only: Specifies that Integration Builder returns only the first instance of a row if there are duplicate rows in the database. Click to select this option when you do not want duplicate rows to be returned in the result set. Not available for all database types.
-
Limit number of rows to: Specifies the maximum number of rows that you want to be returned. Click to select this option when you want to limit the number of rows that are returned in the result set.
The Record Browser property page displays the data in the result set. By viewing the records, you can verify that your table joins, filters, and sorting options are correctly specified.
Database Setup Dialog Toolbar
The Database Setup dialog toolbar is at the bottom of the left navigation pane and includes the following icons:
Opens the Database Setup wizard, which you use to create a new database connection or connect to a database by using an existing named database connection. This option is unavailable when you have already created a database connection in Integration Builder, because multiple database joins and connections are not supported. This option becomes available when you delete the current database connection.
Deletes the database connection.
When you click this icon, the No Record Sets page of the Database Setup dialog appears. Click or Add a new record set to create a new database connection or to connect to a database by using an existing named database connection.
Refreshes the records that are listed in the Record Browser.
Opens the Save File dialog so that you can export the database connection configuration to an XML file. Exported files contain all the configuration information, including filters, sort order, records-per-item data, and database joins. Query prompts, named data sources, and global data fields are not exported.
Opens the Open File dialog so that you can import a saved database connection configuration file. When you use this option, existing configuration settings are replaced by the settings in the imported file. Not available for actions or other features that support only limited database connections.
Connection Properties