In this exercise we are going to use MySQL, and we will read data from one table in a MySQL database and insert the data into another table in a MySQL database.
You will need to make sure that you have a MySQL server running either on a local machine or over a network to another BridgeGate server. A connection will be made to the database. BridgeGate™ uses JDBC style connections. The following are some example formats:
Note that if you are using Microsoft Access and ODBC via the System DSN, you need to first register the Access database in the System DSN from the Control Panel. From the Control Panel, find the ODBC data sources. In most Microsoft operating systems such as XP, this is found under Administrative Tools. Pick the Microsoft Access Driver (*.mdb). Enter your database name into the Data Source Name field and then press the Select button. Navigate to the location where your database is located (either local or on the network) and select the Access database you wish to register, and then click OK.
This sample walks you through creating your Inbound and Outbound Template for a database consisting of a single Record containing of 4 Fields.
We must create a connection for the database. This may be done one of two ways: predefined connections, or specifying the connection information in the template. The same information is entered regardless of which you choose; however, using a predefined connection allows you to reuse the connection rather than entering the information into each template. To create a predefined connection, select Repository from the task bar, and pick, Predefined Connections.
Enter your connection information: (1) Connection Name, (2) pick Database from the drop down list for Connection Type, (3) pick the type of database (e.g. MySQL) from the drop down list for Database Type, (4) enter the JDBC URL for your database connection, (5) enter the database login name in Username, and (6) the Password for your inbound database.
We are going to start by creating a new Workflow Group in which to place our Inbound Template, just as we have done in previous exercises. In the Inbound Templates tree, right mouse click and select New Workflow Group, and enter the name you wish to use for your new Workflow Group, such as Sample8Test. In the illustrations below, we will simply use: Sample8. Note however, that you will already have a Sample8 Workflow Group as part of your Samples package.
Once your Workflow Group has been defined, create an Inbound Template called, DatabaseIn. To do that, right mouse click in the Inbound Templates tree and pick New Template Wizard.
You should now see your new Inbound Template populated in the Inbound Templates tree.
Under Template Properties, select your predefined connection.
Before we begin creating our Templates, let's test the database connection. Select the button: Connect To Selected Database. If you have entered your connection information correctly, no errors will be reported. If an error is generated, make sure your JDBC URL is formatted correctly. For examples:
See Help for hints on how to format the JDBC URL for your type of database.
We are ready to create our Inbound Record. Right mouse click in the Records tree.
Name the Record (Record Name) SelectInbound. At this point, we can either define the fields in the database manually by using the right mouse button in the Records tree and adding fields, or, we can use BridgeGate™ to read the database metadata and pull in the records and fields we want automatically. We will use the metadata.
In Template Properties, select the "Connect to Selected Database" button (should now display "Disconnect From the Database"). In Record Properties, select the MySQLIn from our Predefined Connections and check the box, "Make this Record the Root Query." Then press the "Database Viewer" button.
A dialog will appear showing our inbound and outbound database tables (as seen below):
We can expand the inbound database table to get the list of fields in the table.
Select the "Add Fields" button. This will populate all of the fields from the inbound table.
We should save our work now. Select the Save icon from the toolbar.
The verify dialog will appear. Choose the Verify Selected Items button.
The Inbound Template should save without error.
From the Toolbar, click on the Outbound Template Builder:
Similar to the process we used to create an Inbound Template, we will select the Wizard for creating an Outbound Template. Make sure that you have selected the Workflow group you created earlier (e.g. Sample8Test) and again pick 'New Template Wizard' from the drop down list (our example will show Sample8 as the Workflow Group).
We are going to create a new Outbound Template. Name the Outbound Template, DatabaseOut.
The new Outbound Template will appear in your Outbound Template tree.
Once again, we will create a predefined connection, and use the same information as we did for the inbound predefined connection. To create a predefined connection, select Repository from the task bar, and pick, Predefined Connections.
This time, make sure to select the Data Destination Connections tab.
The information will be the same as for the Inbound, except for the Connection Name, which we will call MySQLOut. Thus, as before, enter your connection information: (1) Connection Name, (2) pick Database from the drop down list for Connection Type, (3) pick the type of database (e.g. MySQL) from the drop down list for Database Type, (4) enter the JDBC URL for your database connection, (5) enter the database login name in Username, and (6) the Password for your inbound database.
Once the predefined connection information has been added, we are ready to construct our Outbound Template. Pick Database from the Data Type in the Template Properties panel. Select the MySQLOut connection you just created. Select the Template Group (I am using Sample8 here, but yours may be named differently, such as Sample8Test), and then select the Inbound Template (databasein), and check the box, "Quote All Fields." Once this is done, press the "Connect to Selected Database" button (this should then show "Disconnect From Database").
Now we are ready to use define the Outbound Template fields using the metadata. Here we want to first define the Database Record Properties. Go to the Records tree, and click on the Record.
In Database Records Properties, type "Insert_Data" into the Description textbox, select SelectInbound in the Loop Record, and choose OutBoundDatabase from the Database Table drop down list. Finally, select the Table Viewer button.
We are going to add all the fields, so press the "Add Fields" button.
The Records tree will be populated with the outbound database fields. If you notice that the Record has '?' signs next to each Field, this indicates that your Outbound Template has not yet been mapped. The Inbound Template and its Records and Fields for Inboundwill be mapped to Outbound elements.
Toggle the 'Drag and Drop mode icon to enable Inbound Template fields to be associated with Outbound Template fields by either dragging the fields (or records) from the inbound to the outbound, or overlay by checking the Link Mode checkbox. We will overlay the elements, so check the 'Link Mode' checkbox.
If you press the "Connect to Selected Database" button in the Template Properties pane in the lower right hand corner of the screen, the metadata for the target database will become available. Let's expand both trees and drag our elements from the Inbound Template to the Outbound Template, as we have done before. Drag:
ITEM_NAME to PRODUCTNAME
ITEM_NUMBER to PRODUCTNUMBER
ORDER_NUMBER to TRACKINGNUMBER
QUANTITY to QTY
Now let's verify and save our work: select the Save icon from the toolbar.
Pick "Verify Selected Items" and save your Outbound Template.
Since we are using a database, we need to create a workflow before we can test our templates. This is because the data will be transported from our inbound tables to our outbound tables. Pick the Manage Workflows icon from the toolbar.
In the Workflows tree in the upper right hand corner, right mouse click and select the New Workflow Wizard.
Name your Workflow, databaseTodatabase:
In the Workflow Items tree, right mouse click and create a new Get Data. We will name the Get Data, fromDatabase. Let's use the predefined connection for our MySQL inbound database (MySQLIn).
Now let's create a new transformation. Right mouse click in the Workflow Items tree, and select New Translate. Name it, fromDBtoDB.
We will now define the inbound condition for the Get Data. Right mouse click in the Template Conditions panel, and create a New Inbound Condition. Name the condition: inboundData. Then select from your Sample Workflow, the name of the Inbound Template, as seen below.
We will now define the outbound condition for the Get Data. Right mouse click in the Template Conditions panel, and create a New Outbound Condition. Name the condition: outboundData. Then select from your Sample Workflow, the name of the Outbound Template, as seen below
Now create the outbound workflow for the Send Data. Right mouse click in the Workflow Items and select Add New Send Data. Name this, toDatabase.
Set the Source Workflow Item to Translate Data:fromDBtoDB, and use the predefined connection for MySQLOut, as seen below.
Now let's verify and save our work: select the Save icon from the toolbar.
Pick "Verify Selected Items" and save your Workflow. It should verify and save without error.
We are now ready to test our work. Pick the Test Workflows icon from the toolbar.
Select the Sample and the Workflow to test. Press the Test Translation button. The data will be written to the destination database tables.
The results window will now show the translation, and the data will have been written to the database.
This concludes Sample 8.